Effect of where statement in subquery (to get rid of unnecessary join)

Perhaps it is easier to see with the aid of some rewrites. Your first query can be rewritten to (using the same alias at different levels makes it a bit difficult to understand):

select *
from parent p1
where exists (
  select 1
  from parent p2
  join child c on c.parentId = p2.id
  where p2.age > 50
    and p1.id = p2.id
)

Assuming id is unique in parent this can be rewritten as:

select *
from parent p1
where exists (
  select 1
  from child c
  where c.parentId = p1.id
    and p1.age > 50
)

which in turn can be rewritten to your query.

So you can eliminate the join against parent inside the sub-select. However, your second query is a bit difficult to understand as it is written. I would suggest (I'm guessing your intention)

select *
from parent p1
where exists (
  select 1
  from child c
  where c.parentId = p1.id
)
and p1.age > 50

If parent.id is not unique the above wont hold


This query will return the times a student retook an exam after more than 12 months from the first take:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND RetakeDate::date - '12 months'::interval >= Examdate::date
GROUP BY Name

It subtracts 12 months from RetakeDate and checks if the resulting date is greater or equal to Examdate.

This query works too:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND age(RetakeDate::date, Examdate::date) >= '12 months'::interval
GROUP BY Name

The function age(timestamp, timestamp) is documented here: https://www.postgresql.org/docs/current/static/functions-datetime.html

ATTENTION

Be aware that those two queries can give different results for periods that are exactly 12 months. The following returns true:

select age('2000-12-31'::date, '2000-01-01'::date) = '12 months'::interval;

 ?column?
----------
 t
(1 row)

while this query returns false:

select '2000-12-31'::date - '12 months'::interval = '2000-01-01'::date;

 ?column?
----------
 f
(1 row)

In fact:

select '2000-12-31'::date - '12 months'::interval;
      ?column?
---------------------
 1999-12-31 00:00:00
(1 row)

while:

select age('2000-12-31'::date, '2000-01-01'::date);
       age
-----------------
 11 mons 30 days
(1 row)

It is your choice to choose age() over date - interval. To solve this, you can use > against >=.


Where are Full-Text indexes in SSMS 2008 R2

In SSMS

Tools
Options
"SQL Server Object Explorer"
"Script full text catalogs"

Default is "false"...


How do I list all databases and tables using psql?

Please note the following commands:

\list or \l: list all databases
\dt: list all tables in the current database using your search_path
\dt *.: list all tables in the current database regardless your search_path

You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).

To switch databases:

\connect database_name or \c database_name

See the manual about psql.


unable to add index to column, duplicate entry

I think you are trying to create a primary key and not a simple index. (Primary key requires all values to be unique)

To add a index in mysql follow the syntax here: https://dev.mysql.com/doc/refman/5.7/en/create-index.html


How do I validate all my constraints in postgresql?

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema )

do $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;


Listener Port 1433 for Always on Availability Group

If the listener port is 1433 and the clients also gets connected through port 1433 to the reporting server that is part of the Availability group will there be any port conflict.

If 1433 is just dedicated to sql server and no other service is using it, there wont be any conflicts.
This is documented well in BOL :

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.
If one of the instances of SQL Server is already listening on TCP port 1433 via the instance listener and there are no other services (including additional instances of SQL Server) on the computer listening on port 1433, this will not cause a port conflict with the availability group listener. This is because the availability group listener can share the same TCP port inside the same service process. However multiple instances of SQL Server (side-by-side)should not be configured to listen on the same port.


Under what conditions could restore of a huge database get delayed, in SQL server 2016, hosted on VM?

When I see someone complaining of backups "suddenly" changing, there are usually three things I look at first. In my experience, one of these three things is usually the cause.

1: Oops, there was a hardware change

Maybe a VM was v-motioned to a different host. Maybe there was a change in the storage configuration on the NAS/device that you're backing up to. Maybe there was a network configuration change.

Pinpoint the date that backup performance changed, and work with your server/hardware team to figure out if anything changed here.

2: Change in DB size

This one is usually pretty easy to find. If you're using database monitoring software of some sort, you should be able to go back and look at the historical data, and see that there was a significant change in the database size. (Full disclosure, I work for SentryOne, who makes database monitoring software.)

If you don't have monitoring data, you can query msdb on the source server to look at the database backup size over time, and see if backup growth correlates to the time you noticed RESTORE scripts slowing down:

SELECT database_name, backup_finish_date, backup_size
FROM msdb.dbo.backupset
WHERE [type] = 'd'
AND database_name = 'AdventureWorks2014' --Your database name here
ORDER BY backup_finish_date;

3: Change in transaction log number of VLFs

If it's not the first two, it's probably this one.
SQL Server breaks up the transaction log into "Virtual Log Files" -- these are just logical buckets inside your .ldf file. On restore, SQL Server processes each of these VLFs in sequence, which means more VLFs can slow down your recovery.  A high VLF count is bad.

Here's how you can reproduce the problem using the AdventureWorks demo database. I restore the clean database, and test backup/restore. Then increase the VLF count and repeat the same backup/restore:

--
-- RESTORE AdventureWorks from a pristine copy from Github
--      https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
--
USE master;
RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\SQL\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_Data.mdf'
    ,MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_Log.ldf'
    ,REPLACE;
GO
--
-- Now do a backup & restore.
--
PRINT '----- BACKUP  1 -----';
BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\temp\ADVW2014.bak'     WITH INIT, COMPRESSION;
PRINT '----- RESTORE 1 -----';
RESTORE DATABASE AdventureWorks2014_RestoreTest FROM DISK = 'C:\temp\ADVW2014.bak'      WITH REPLACE, NORECOVERY
        , MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Data.mdf'
        , MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Log.ldf' ;
GO
--
-- Lets really jack up the VLFs on this DB. Grow log file to 2GB in 1MB increments.
--
GO
USE AdventureWorks2014
DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 1)
GO

DECLARE @i int = 51;
DECLARE @sql nvarchar(max);
WHILE @i <= 2000
BEGIN
    SET @sql = N'ALTER DATABASE [AdventureWorks2014] MODIFY FILE ( NAME = N''AdventureWorks2014_Log'', SIZE = ' + CONVERT(nchar(4),@i) + N'MB );';
    EXEC sp_executesql @sql;
    SET @i +=1;
END;
DBCC LOGINFO;
GO
--
-- Now do a backup & restore.
--
PRINT '----- BACKUP  2 -----';
BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\temp\ADVW2014.bak'     WITH INIT, COMPRESSION;
PRINT '----- RESTORE 2 -----';
RESTORE DATABASE AdventureWorks2014_RestoreTest FROM DISK = 'C:\temp\ADVW2014.bak'      WITH REPLACE, NORECOVERY
        , MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Data.mdf'
        , MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Log.ldf' ;
GO

The first backup/restore will generate output like this. Notice the speeds on backup (299.826 MB/sec) and restore (403.587 MB/sec):

----- BACKUP  1 -----
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 4 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24332 pages in 0.634 seconds (299.826 MB/sec).
----- RESTORE 1 -----
Processed 24328 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Data' on file 1.
Processed 4 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24332 pages in 0.471 seconds (403.587 MB/sec).

The second backup/restore will generate output like this. Notice the speeds on backup (144.220 MB/sec) and restore (89.114 MB/sec):

----- BACKUP  2 -----
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 3 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24331 pages in 1.318 seconds (144.220 MB/sec).
----- RESTORE 2 -----
Processed 24328 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Data' on file 1.
Processed 3 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24331 pages in 2.133 seconds (89.114 MB/sec).

To fix this, you essentially need to shrink & re-grow your log file in more appropriate chunks. You can see more information on this here(my employer's site), here, here, and here.


A batch is a series of SQL commands that are sent to the database server at once. This can be faster than sending each command individually because there is less network communication.

SQLite is an embedded database; SQL commands are executed directly by the SQLite library.
There is no separate server, so it would not make sense to have batches. SQLite does not have such batches.

Another meaning of "batch" might be the multi-row form of the INSERT command:

INSERT INTO MyTable(ID, Name) VALUES (1, 'this'), (2, 'that'), (3, 'whatever');


Run this command : SHOW CREATE TABLE log_url\G

This will give you a display of the SQL needed to create the table.

It will also show you the indexes and constraints for that table.

The first index (key 1) in that table must have a column called url_id.

From the error message, you are being told that there exists a row in log_url that has 41721 already present. You can verify this with SELECT * FROM log_url WHERE url_id = 41721;


Oracle:
order by 1/0 succeeds, because that clause alone is meaningless, the optimizer automatically eliminates it from the query at parse time and it never gets executed.
SQL> select username from t1 where username like 'SYS%' order by 1/0;

USERNAME
--------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG

7 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  cnnmg28k0vspg, child number 0
-------------------------------------
select username from t1 where username like 'SYS%' order by 1/0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("USERNAME" LIKE 'SYS%')

No sort at all was performed.
Add something else and it will fail:
SQL> select username from t1 where username like 'SYS%' order by 1/0, 1;
select username from t1 where username like 'SYS%' order by 1/0, 1
                                                             *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>

Or:
SQL>  select username from t1 where username like 'SYS%' order by 1/0;

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG

7 rows selected.

SQL> select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0;
select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0
                                                                                *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>

Also if the value is not fixed at parse time (e.g it is a variable) :
SQL> variable B1 number
SQL> exec :B1 := 0;

PL/SQL procedure successfully completed.

SQL> select username from t1 where username like 'SYS%' order by 1/:B1;
select username from t1 where username like 'SYS%' order by 1/:B1
                                                             *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>


differences between SSRS 2016 and Report Builder 2016

SQL Server Reporting Services (SSRS) is a server-based platform for hosting reports. Report builder is client tool that may be used to create reports to deploy to SSRS. As such, you would use SSRS to manage security on for the reports that you may have deployed with Report Builder.


Safely quoting type names to protect against SQL-injection

How would I quote the type name to protect against SQL Injection.

You do not need format_type() for the purpose. You may have misunderstood Andrew there. All you need is the cast to regtype:

SELECT format('SELECT cast(%L AS %s);', '5.42', 'int'::regtype);

The text representation of the regtype value is the standard SQL name and quoted automatically where needed - and the value is cast to text implicitly for the concatenation. Demo:

SELECT 'varchar(20)'::regtype, 'int'::regtype, '"char"'::regtype;

regtype           | regtype | regtype
------------------+---------+---------
character varying | integer | "char"

format_type() would only serve to add type modifiers (which are lost in the cast), an orthogonal matter. Related:

PL/pgSQL regclass quoting of table named like keyword
How to introspect materialized views
PostgreSQL syntax error in parameterized query on “date $1”
Table name as a PostgreSQL function parameter


How to find a list of local MS SQL Servers?

-- Powershell

Import-Module sqlserver
'computername'| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}

Make sure you don't have linked server (LocalDB)\ProjectsV13 pointing to itself (LocalDB)\MSSQLLocalDB

Check any alias defined


Exec Stored Procedure on particular day of week

you should probably create an Agent Job

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-2017


Sql select slow on filter for key, but not for primary key, how can I hint the priority

Your two queries are fundamentally different. The fast query is right-joining the TourneyPlayers table to Tourney, and then filtering. Based on the order of operations, it is likely seeking records on Tourney first, then seeking records on TourneyPlayers that match the TourneyId using the correct index.

In the slow query, you're inner joining the tables, so the engine is scanning the TourneyPlayers clustered index and seeking the Tourney records using the date field index. It then has to combine these record sets to produce the final result set.

You need to determine which join you require for the relationship between your tables and the result set you need, then from there you can construct your query correctly and assess your indexing strategy for these tables.

For example, do you want to return every Tourney record regardless of whether there is an associated TourneyPlayer record? Do you want every TourneyPlayer record, regardless of whether there is an associated Tourney record? Do you want only those Tourney records that have an associated TourneyPlayer record?

Once you've answered this question, you will have the answer for your join and can proceed from there with query design and index optimisation.


Copying a database that is in Retore status

You cannot use any backup statement to get a copy_only backup since the database is still restoring. At most the only option would be to use STANDBY mode but this would only provide you read access to all the data.

You can get it to STANDBY by using the following command:

RESTORE DATABASE sample_db
WITH STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sample_db_standby.file';

This will put the database in a read only state and you could use SSIS to export the data. You can also use SELECT..INTO for the tables you want to another database. It is odd that SSMS is not going to let you use the Export Data wizard even though the data is accessible in a readable state (just tested trying to use that).

Once you have gotten the data you want out you can put it back in NORECOVERY by issuing this statement:

RESTORE DATABASE sample_db WITH NORECOVERY;


Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?

I can't reproduce any warnings or performance differences in this scenario, even when joining bVarchar to varchar(10).  EG
create database tt

alter database tt collate Latin1_General_BIN
use tt
go
create type bVarchar from varchar(10)

create table a(id int primary key identity, b bVarchar, c varchar(10));
create table b(id int primary key identity, b bVarchar, c varchar(10));

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects
)
insert into a (b,c)
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects
)
insert into b (b,c)
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;

go

set statistics time on
set statistics io on

select count(*)
from a
join b
  on a.b = b.b

select count(*)
from a
join b
  on a.c = b.c

select count(*)
from a
join b
  on a.b = b.c

set statistics time off
set statistics io off

outputs
    SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 360 ms, elapsed time = 360 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 3510, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 2656, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 268 ms,  elapsed time = 117 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 184 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 280 ms,  elapsed time = 111 ms.

Completion time: 2020-10-17T09:56:35.6734439-05:00


What's the optimal way to query data using another table column as a between filter

What's the optimal query to having such a result?

Simple.

Create proper indices (order(ip) and ip(ip_from,ip_to)).

Complex.

Create a field of LINESTRING type from ip_from to ip_to. Create spatial index (RTree) by it. Use MBRContains for filtering.

From the other side - the ranges (ip_from,ip_to) must be NON-overlapped.

If it is true, then you'll obtain the result with simple

select order.uid,
       order.order_id,
       order.ip,
       ( SELECT ip.country_code
         FROM ip
         WHERE ip.ip_from <= order.ip
         ORDER BY ip.ip_from DESC
         LIMIT 1 ) country_code
from order

Indices ip(ip_from) and order(ip) (or even covering index ip(ip_from, country_code)) is enough for this query.


Query to compare two column of table 1 with two column of table 2 and update the third column in table 1 as 'y' if the record matches

One way would be

UPDATE t1
SET    t1.enabled = 'Y'
FROM   Table1 t1
WHERE  EXISTS (SELECT *
               FROM   Table2 t2
               WHERE  t1.email = t2.email
                      AND t1.number = t2.number);

Or a more concise alternative

UPDATE Table1
SET    enabled = 'Y'
FROM   Table2
WHERE  Table1.email = Table2.email
       AND Table1.number = Table2.number;


How to change MySQL 8.0 data folder. Can I use OneDrive folders?

Even if you can use OneDrive (or similar product), I would expect it to be a disaster waiting to happen.  Neither MySQL nor XyzDrive is designed to worry about fast, real-time, actions.  So, I would expect any network hiccup, or drive hiccup, or long network delay, etc, to risk a serious "disk" error being returned to MySQL, necessitating a crash.

Also, MySQL is not prepared to have two instances talking to the same files.  Will you really disconnect from one computer before connecting another??  The day you fail to do that is the day the data is corrupted beyond repair.

Even if XyzDrive and MySQL decide to make it a viable feature, performance would probably suck -- somewhere between 10x and 100x slower than a local drive, and somewhat slower than a remote drive.

(If I am wrong, please speak up.)


drawback of including primary key creation along in the table creation script

Maybe he wants to load the table with data and then create the index after. That way index fragmentation and statistics would be in good shape.

With that said a good choice of clustering key would keep fragmentation low during the data load. Kimberly Tripp has written a few posts on topic of choosing appropriate clustering keys.


Understanding about SQL index on DateTime Column

B-tree indexes don't work by comparing only for equality. They're designed (the algorithm is designed) to use ranges.

Figure 1.3 shows an index fragment to illustrate a search for the key “57”. The tree traversal starts at the root node on the left-hand side. Each entry is processed in ascending order until a value is greater than or equal to (>=) the search term (57). In the figure it is the entry 83. The database follows the reference to the corresponding branch node and repeats the procedure until the tree traversal reaches a leaf node.

References

The Search Tree (B-Tree) Makes the Index Fast See especially section 1.3, B-Tree traversal
SQL Server index structure and concepts


Does Oracle do a native conversion to numbers for varchar2 that are aggregated?

Implicit Data Conversion

  Oracle Database automatically converts a value from one data type to
  another when such a conversion makes sense.

You can find more on the above URL.

After you read it, forget everything written there, and use explicit conversion. Relying on implicit conversion is bad practice.


Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

pagefile.sys % Usage shows total system committed not what currently is utilized. This value can increase due to load when system finds out it has to back process with more page file.
Have a look at below link for detailed explanation

http://blogs.technet.com/b/perfguru/archive/2008/01/08/explanation-of-pagefile-usage-as-reported-in-the-task-manager.aspx

I would like to know what problem you are facing


Can I use the same tablespace the lob is in?

Yes.

  The total free space in that tablespace needs to be 200Gb?

No. Depends on the amount of data in your LOB segment. If your segment is 200 GB, but you have only 5 GB data in it (because the rest of the data was deleted), then you do not need 200 GB.

  During moving the lob is locked?

Not necessarily. Starting with 12.2, alter table has the move online option:

alter table mytable move online lob (lob_column) store as (tablespace users);

The above option is not available in 12.1.


How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

Digging around the internet, I've seen that this is a pretty common problem.  The common solution is to use the plain text format dump and feed it through iconv to correct the encoding.

Here is more information about that.


The Linux shell must be interpreting the double quotes before the command gets to rman. Either escape the double quotes:

rman target username/password @${RMAN_DIR}/backup.rman using \"db1\"

or enclose the command in single quotes to prevent the shell from messing with it:

rman target username/password @${RMAN_DIR}'/backup.rman using "db1"'

In the latter case you will have to leave the environment variable outside the quoted command as you do want the shell to expand it.


What is difference between Oracle Apps and Oracle Application Server?

Oracle Applications means software that helps you runs your enterprise businesses such as Accounting, Humans Resource, or Financials. But the Oracle Applications Server is the technology that, mostly installs on the server, integrates others software to work together. It can be called Middleware.

The first example is like Peoplesoft or SAP ERP. The latter is like Apache HTTP Server, Tomcat, Glassfish.


You probably used a keyword as name of your table. You will find the complete list of those keywords in that page of the documentation.

From my experience, the most frequent ones are columns named end or start.

Have a nice day


Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server

Have you tried this:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

First Enable Trustworthy database property

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
GO

And change the database owner to ‘sa’

USE [DatabaseName]
GO

EXEC sp_changedbowner 'sa'
GO


perform multiple conditional join on multiple tables

I got answer for my question, I did a subquery and applied rank and order by functions and get first matching values.

    SELECT
      CGI.SALES_BRAND_BSNSS_ID,
      CGI.SALES_BRAND_CD,
      SUBQRY.matrix_score_cd,
      SUBQRY.corp_group_id,
      SUBQRY.MKTBRIDGE_RUN MKTBRIDGE_RUN_CD,
      SUBQRY.MKTBRIDGE_GROW MKTBRIDGE_GROW_CD,
      SUBQRY.MACRO_SEG MACRO_SEG_CD

FROM
(
SELECT
    cgi_active_cust_seg_v.corp_group_id,
    cust_seg_score_matrix.matrix_score_cd,
    CGI_ACTIVE_CUST_SEG_V.MKTBRIDGE_GROW,
    CGI_ACTIVE_CUST_SEG_V.MKTBRIDGE_RUN,
        CGI_ACTIVE_CUST_SEG_V.MACRO_SEG,
    RANK() OVER (PARTITION BY cgi_active_cust_seg_v.corp_group_id ORDER BY cust_seg_score_matrix.mkt_bridge_run_cd DESC,
    cust_seg_score_matrix.mkt_bridge_grow_cd DESC,
    cust_seg_score_matrix.macro_segment_cd DESC,
    cust_seg_score_matrix.echeck_decile_cd DESC) VAL_MATCH
FROM
    birtab.cgi_active_cust_seg_v INNER JOIN
    birtab.cust_seg_score_matrix ON
        cust_seg_score_matrix.mkt_bridge_run_cd IN ('%', NVL(cgi_active_cust_seg_v.mktbridge_run,'%')) AND
        cust_seg_score_matrix.mkt_bridge_grow_cd IN ('%', NVL(cgi_active_cust_seg_v.mktbridge_grow,'%')) AND
        cust_seg_score_matrix.macro_segment_cd IN ('%',NVL(cgi_active_cust_seg_v.macro_seg,'%')) AND
        cust_seg_score_matrix.echeck_decile_cd IN ('%',NVL(cgi_active_cust_seg_v.echeck_decile_cd,'2')) AND
        SYSDATE BETWEEN cust_seg_score_matrix.EFFEC_DT AND cust_seg_score_matrix.END_DT

) SUBQRY
INNER JOIN
      BIRTAB.SBBID_CGI_HIST_XREF CGI ON
            SYSDATE BETWEEN CGI.SBBID_CGI_EFFEC_DT AND NVL(CGI.SBBID_CGI_END_DT,TO_DATE('31-DEC-2999','DD-MON-YYYY')) AND
            subqry.CORP_GROUP_ID = CGI.CORP_GROUP_ID
WHERE
    VAL_MATCH = 1;


Postgresql, write archive file over mount, secure against simultaneous access?

Neither NFS nor cp will protect you from partial write. The usual way of mitigating problems with write atomicity is to use a temporary file and a mv after copying (or writing) is complete.  You might use something like:

    test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/tmp/%f && \
    mv /mnt/server/archivedir/tmp/%f /mnt/server/archivedir/%f


How to repair mysql tables using myisam storage engine from multiple repairing threads?

Read carefully:

MySQL 8.0 Reference Manual  /  ...  /  Server System Variables

  If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process.

If your table have 2 indices - then 2 threads will (must) be used.

PS. I'm not sure that you will see this in processlist... this has to be checked.


Why is the result cache always disabled in Oracle 11g

Are you using Enterprise Edition? It looks like this is a EE feature:

http://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm

CTRL + F the docs and search for result cache and supported editions.


PostgreSQL 9.2; Altering a table to use another sequence

Why don't you create a new sequence:

create sequence new_sequence;

Then create the new table

create table doki_data_item2 (like doki_data_item including defaults);

Finally modify the column that is using the old sequence to use the new one:

alter table doki_data_item2 alter column seq_column set default nextval('new_sequence');

You can obviously set various parameters for the newly created sequence to suit your needs.


SQL Server generate self-signed SSL certs performance concern?

The self-signed certificate is NOT generated upon every connection/login. So no performance hit here.

Source: http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx


Why hardcoded date filter works much better than dynamic parameter?

We cannot know for sure  without both execution plans. Below are three common reasons.

Filtered indexes

It could be the use of a filtered index such as:

        CREATE INDEX IX_Order_ProductCode_CustomerId_Filtered
        ON [dbo].[Order](ProductCode,CustomerId)
        INCLUDE(date_inserted)
        WHERE date_inserted >= '2019-06-01';

This should pop up as an unmatched indexes warning in the execution plan. More on these here

Parameter sniffing

Another common reason would be parameter sniffing, where the value that runs with the stored procedure first / on plan creation is cached for following executions, until the plan is recompiled. Recompilation can for example happen due to statistics of one of the referenced tables being updated.

More on parameter sniffing here

If you want sql server to see the parameter as a hardcoded value then you would have to add OPTION(RECOMPILE) to the query.

Wrong datatypes

if the date_inserted column is a string datatype such as varchar() then your query would not be able to seek on the parameter. It would do an implicit conversion of the varchar() field to a date field on a residual predicate instead of a seek predicate.

The difference between below two queries could then  be immense

SELECT * FROM [Order] o
WHERE o.date_inserted < '2019-06-01'

DECLARE @date date = '2019-06-01'

SELECT * FROM [Order] o
WHERE o.date_inserted < @date

With more than 2 seconds execution time on the second query

Due to the residual predicate:

where the entire date_inserted column is converted to a date datatype before filtering.


You can try out this as given by user Cade Roux in an answer to the Stack Overflow question Common function / stored procedures for all databases:

  Depending upon what the SP actually does, you want to create the procedure in master, name it with sp_ and mark it as a system procedure:

From Running a master db residing stored procedure in current database context by Mladen Prajdić:

USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    *
FROM    information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess

go
USE AdventureWorks
EXEC sp_TestMasterAccess

go
USE master
DROP PROC sp_TestMasterAccess


What about using -T modifier for pg_dump to skip those tables?

For example -T topology.*


Side effects of using updlock lock hint?

So what happens if I use it in all my update queries?

Like all hints, UPDLOCK should only be used where there is a clear benefit.

  How will it affect me negatively?

It depends on the query and the execution plan. Assuming you are only proposing to add UPDLOCK to the target table, there may be no negative effects at all in very simple cases.

A "very simple case" is where all decisions about which rows qualify are made in a single plan operator. In this situation, update locks are only taken on rows that will definitely qualify, so no harm is done.

SQL Server automatically takes U locks when searching for qualifying rows even without an UPDLOCK hint, but only for the primary access method (e.g. an index seek and any associated RID or Key Lookup). These automatic U locks are special, in that SQL Server can still release them early if later operators in the plan determine that the row does not qualify after all.

When UPDLOCK is specified, any update locks taken cannot be released before the end of the transaction, even if other operators in the plan determine that the row does not actually qualify for the update. These extra U locks reduce concurrency and may cause lock escalation to a table-exclusive lock.

For these reasons, it is generally not a good idea to specify UPLOCK routinely on UPDATE queries.


Use sp_DatabaseRestore for restore when database does not exist

Turns out there was a difference in the data-file folder names between the old and the new server. The old server had a version-number lower than the actual version of the SQL Server, probably due to an earlier in place upgrade. When trying to restore from a backup with correct data folder, there is no problem.


MySQL read/write ratio from information_schema.GLOBAL_STATUS

Use the typical trick of sum(IF the column is the right one, use it, if not read 0:

SELECT sum(IF(variable_name = 'Com_select', variable_value, 0)) as `Total reads`,
       sum(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'),
           variable_value, 0)) as `Total writes`
FROM information_schema.GLOBAL_STATUS;


You should consider vertical partitioning of your table if your columns have different access pattern: some columns are often read and (almost) never updated, others are often updated.

Unless you have columnstore indexes or covering indexes for your queries, every time you need only some columns a whole row is retrived, so if you want namy rows but for limited columns vertical partitioning may be your case.

Please also take a look on this: Vertical Partitioning as the way to reduce IO

Note that MinimumRecordSize 628 + MaximumRecordSize 7928 means that your rows have fields of variable length data types such as varchar(max), and this means that you did not reach max row size. If MinimumRecordSize was equal to MaximumRecordSize this could mean that you may will not be able to add othe columns to your table, but since MinimumRecordSize is only 628, many other variable length columns can still be added.

When row-overfloing data does not fit in a row, a newe page is allocated to accomodate it and only a small pointer is left on original row that points to row-overflow page containing the value.


The compound PK of EmployeeId and AddressTypeId is exactly what you want, and you don't need anything else. The relationship is still a one-to-many relationship, as for one employee, you may have many address whether it is a home address or a work address.

The bridge table is interesting if you may have the same address for multiple employees AND that a same address may be of a different type for multiple employees. If it's not the case, this table is pretty overkill and may add more complexity for nothing.

TL:DR: First solution is probably the best.


Transaction log for SQL Server is too big

will I be able to do a restore to a point in time between Future full backup 1 and 2?

Yes.  What you're concerned about is the Log Chain.  The log chain starts at a full backup.  Therefore at future full backup 1 you will start a new log chain and with transaction log backups you will be able to restore to point-in-time.

BOL reference on Working with Transaction Log Backups

As for your initial method of getting your log truncated, note the following from BOL on BACKUP:

  The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model.

And I'm guessing this goes without saying, but I'll be explicit.  In order to not run into this same problem you do need to be doing routine transaction log backups.


Are there any transactions on that particular day?
Check the data "by eye" and see if it's the query that's "missing" them or if they really aren't there at all!
Your query looks OK to me.
Your table I'm less convinced about.
Having a table called "table" is a Bad Idea.
Using any reserved word as an identifier (table, column, anything else) is likely to come back and bite you at some point.  Save yourself the trouble; you should never need to "protect" the identifiers in your SQL.
What Data Type are you using for the start_time column?
I'm hoping it's a Date-related one but, given the problem you're having, I'm guessing it's not and that the values stored in it are in a different "format".
For example:
select top 5 *
from table1 ;

+--------+-----------------------+
| seq_no | start_time_as_varchar |
+--------+-----------------------+
|    111 | 25-Apr-2021 00:00:01  |
|    222 | 26-Apr-2021 00:00:01  |
|    333 | 27-Apr-2021 00:00:01  |
|    444 | 28/04/2021 00:00:01   |
|    555 | 2021-04-29 00:00:01   |
+--------+-----------------------+

The values are perfectly valid Character Representations of date values, but they're not held in [a column with] a Date Data Type, so SQL Server will do a character-by-character comparison, not a date-wise comparison.
Always store Date Values in Date[-related] Data Types.


Query to Linked Server Ignores Configured Timeout Connection

I was able to achieve it in two ways:
Configuring a timeout for the whole server
You should configure the server timeout option (remote login timeout) using sp_configure:
EXEC sp_configure 'remote login timeout', 1 ;
GO
RECONFIGURE ;
GO

Configuring a timeout for the specified Linked Server
You should configure the Connection Timeout with the value 1 or more:

If you query sys.servers by now, you should see the specified value for the column connect_timeout.

I noticed a strange behavior after configuring it. SQL Server seems to try the remote connection longer than the specified connection timeout the first time you try to run a query using a linked server (as if it was insisting to be sure it can't reach the remote server). But the following attempts of running a query it threw the connection error quickly as specified.


AlwaysOn Availability Groups back up schedules

My question is: - Do i have to schedule the jobs at primary replica and then disable them until fail-over occurs and then manually enable jobs or what is the best way to deal with these please?

No, you should just add a check with the new function sys.fn_hadr_backup_is_preferred_replica

if sys.fn_hadr_backup_is_preferred_replica('your db name') <> 1
begin
    print 'This is not preferred backup replica .....'
        -- If this is not the preferred replica, exit (without error).
end
else
begin
    print 'This is the preferred replica .. continue with backup'
        -- If this is the preferred replica, continue to do the backup.
        -- here goes the backup command .....
end

Alternatively, you can use SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.


How to enable INMEMORY JOIN GROUP in Oracle 12.2

No you need also a column for the departments table

CREATE TABLE employees(department_id int)

✓

CREATE TAble departments(department_id int)

✓

CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments(department_id));

✓

db<>fiddle here


Pretty much every implementation decision is a trade off between competing factors. Building a columnstore index is CPU intensive but afterwards queries touching many rows are fast, and updates are slow. Which is most important for your workload, on average? Is there a time window in which that amount of CPU can be consumed without breaking other parts of the system? Is the additional cost repaid in future benefit? What is the problem to which a columnstore is the solution, and has this been addressed in other aspects of the system?
You mention ETL. Often such tables are only processed as scans where every row is touched by every operation. In such cases indexes will slow processing because they must be written in addition to the table.
Likely there are reasons for the current restrictions. Understand why they were put in place. If those circumstances no longer hold (maybe a server version upgrade or better hardware now) try an experiment in a test environment.


Is the SQL Server Express memory and CPU limit per instance?

If I have 8GB of RAM in a server and I run 4 instances of SQL Express,
  will the total memory limit used by SQL Server be 1GB or 4GB?

Each instance can use up to 1GB of memory for the buffer pool.  Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool.  In your case, the maximum memory used by the four instances for buffer pool would be 4GB.

To confirm, I started two instances of the SQL Server 2008 Express Database Engine, performed some activity to load up the (separate) buffer pools, and then looked at per-instance memory utilization in a number of ways, for example using DBCC MEMORYSTATUS or by counting the number of buffers using the sys.dm_os_buffer_descriptors DMV.

The physical memory usage numbers below were obtained using simultaneous queries against the system DMV sys.dm_os_process_memory on each instance of the database engine:

SELECT
    dopm.physical_memory_in_use_kb
FROM sys.dm_os_process_memory AS dopm;

Output:

╔═══════════╦═══════════╗
║ Instance1 ║ Instance2 ║
╠═══════════╬═══════════╣
║   1102872 ║   1059812 ║
╚═══════════╩═══════════╝

Each of these is slightly in excess of 1GB as total physical memory usage includes more than just buffer pool usage, as mentioned previously.

  Would it be advisable to run multiple instances like this to enable
  each database to make better use of resources (assuming that the
  server has plenty of resources)?

If the databases on each instance are functionally independent then it is at least workable to run multiple instances of Express in this way, though you would need to pay careful attention to configuration and maintenance may be made more complex.

You might be be better served using another edition, such as the fully-featured (and very cheap) Developer Edition, if the intended usage matches the licence).  You would need to say much more about the circumstances to get a clear answer on this point.


CDC Capture Instance or State to ensure referential integrity

From Microsoft docs.  Guess I just had to find the right doc...
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-flow-components#grouping-tables-for-cdc-processing

Grouping Tables for CDC Processing

Database projects range in size from several tables to many thousands of tables. When designing initial load and CDC packages, it is beneficial to group tables in much smaller groups for easier management and efficiency. This section lists various considerations that impact the sorting of tables into small groups, where the tables in each are initially loaded and then updated as a group.

The CDC patterns supported by the CDC components assume that this grouping is already determined. Each group defines a separate CDC context that is maintained separately from other groups. For each group, initial-load and trickle-feed update packages are created. Trickle-feed updates are scheduled for periodic runs based on the rate of change processing constraints (for example, CPU and IO consumption, impact on other systems) and the desired latency.

Tables are grouped based on the following considerations:

According to the target database. All tables that are written to different target databases or undergo different processing should be assigned to different CDC groups.

Tables that are related with referential integrity constraints should be assigned to the same group to avoid referential integrity problems at the target.

Tables for which higher latency can be tolerated can be grouped so they can be processed less frequently and reduce overall system load.

Tables for which there is a higher rate of change should be in smaller groups, and tables with a low rate of change can be grouped in larger groups.

The following two packages are created for each CDC group:

An Initial Load package, which reads the entire range of data from the source tables and applies it to the target tables.

A trickle-feed update package that reads changes made to the source tables and applies the changes to the target tables. This package should be executed on a regularly scheduled basis.

CDC State

Each CDC group has a state associated with it, which is represented by a string with a specific format. For more information, see CDC Control Task. The following table shows the possible CDC state values.

StateDescription0-(INITIAL)The state that exists before any packages are run on the current CDC group. This is also the state when the CDC state is empty.

For more information about CDC Control task operations, see CDC Control Task.1-ILSTART (Initial-Load-Started)This is the state that exists when the initial load package starts. This occurs after the MarkInitialLoadStartoperation call to the CDC Control task.

For more information about CDC Control task operations, see CDC Control Task.2- ILEND (Initial-Load-Ended)This is the state that exists when the initial load package ends successfully. This occurs after the MarkInitialLoadEnd operation call to the CDC Control task.

For more information about CDC Control task operations, see CDC Control Task.3-ILUPDATE (Initial Load Update)This is the state that exists after the first run of the Update package after the initial load while still processing the initial processing range. This occurs after the GetProcessingRangeoperation call to the CDC control task.

If using the _$reprocessing column, it is set to 1 to indicate that the package may be reprocessing rows already at the target.

For more information about CDC Control task operations, see CDC Control Task.4-TFEND (Trickle-Feed-Update-Ended)This is the state expected for regular CDC runs. It indicates that the previous run completed successfully and that a new run with a new processing range can be started.5-TFSTART (Trickle-Feed-Update-Started)This is the state that exists on subsequent runs of the Update package after the GetProcessingRangeoperation call to the CDC control task.

This indicates that a regular CDC run is started, but is not finished or has not yet finished, cleanly (MarkProcessedRange).

For more information about CDC Control task operations, see CDC Control Task.6-TFREDO (Reprocessing-Trickle-Feed-Updates)This is the state on a GetProcessingRangethat occurs after TFSTART. This indicates that the previous run did not complete successfully.

If using the __$reprocessing column, it is set to 1 to indicate that the package may be reprocessing rows already at the target.7-ERRORThe CDC group is in an ERROR state.

Here is the state diagram for the CDC components. An ERROR state is reached when a state is reached that is not expected. The expected states are illustrated in the following diagram. However the diagram does not show the ERROR state.

For example, at the end of an initial load package, when trying to set the state to ILEND, if the state is TFSTART then the CDC group is in an error state and the Trickle-Feed Update package does not run (the Initial Load package does run).

Once the Initial Load package runs successfully, the Trickle-Feed Update package runs repeatedly under a predetermined schedule to process changes to the source tables. Each run of the Trickle-Feed Update package is a CDC run.


How can I connect SSIS to SQL Server 2000?

Finally I had no problems to connect to SQL 2000 from SQL 2008 R2 is perfectly posible. My problem was a configuration problem between my two virtual machines. I tried to do the connection in a normal enviroment and it worked perfectly.

Thanks to all for the comments.

Anabel


Good practices for proactively preventing queries from randomly becoming slow

Parameter Sniffing
If the data is literally not changing at all between when the query was fast to when it became slow, then likely you're experiencing a Parameter Sniffing issue. (Note this can also happen even when the data has changed, but it doesn't require the data to change for it to happen either.) This happens when parameters (such as in a stored procedure for example) are used in the query.
The reason it happens is because on the first run of the procedure when the Execution Plan is being generated, it's optimized for those first set of values supplied to the parameters of the procedure, then that query plan is cached and re-used for future runs of the same procedure (regardless of what values are passed in as parameters again).
The subsequent executions of the procedure with varying parameters might not always have the most optimal Execution Plan when the original cached plan is reused. This is dependent on a few things, bust mostly the Cardinality of your data. Generally it happens when the Cardinality of the parameters used when the Execution Plan was originally generated and cached varies a lot from the Cardinality of the values of the parameters passed in to the procedure on subsequent executions.
For example, imagine a Cars table that had 1,000,000 rows, and on 999,999 rows the Manufacturer was Honda, but in 1 row the Manufacturer was BMW. The most optimal Execution Plan to find and return the 1 row for BMW would certainly not be the same as to find and return the 999,999 rows for Honda. The uniqueness of the values within the table is the difference in Cardinality between the two values. But with a stored procedure because the Execution Plan is cached on first run, the same plan which would be sub-optimal for Honda would be used if the first time the procedure was ran BMW was passed into the parameter. (E.g. in this case, an Index Seek operation would be most performant for BMW but would be slow for Honda which would be better off with an Index Scan.)

Cardinality Estimate Issues
The previously discussed Parameter Sniffing issue is usually the result of Cardinality Estimate issues (i.e. it is a sub-problem to another problem). But you can run into Cardinality Estimate issues even without Parameter Sniffing issues.
In simplest of terms, Cardinality is the uniqueness or number of rows that exist for a specific value of a column in a Table. The Cardinality of your JOIN, WHERE, HAVING clauses' predicates impact how SQL Server's Engine chooses to find and filter down the data. This is because different operations are more performant for returning something with low Cardinality (low number of records contain that value, so it's highly unique) vs something with high Cardinality (large number of records contain that value, so not very unique).
For example, scanning the data is likely to be more performant of an operation when a large amount of records are expected to be returned for your JOIN, WHERE, HAVING clauses as opposed to seeking which is more performant for a small number of rows. Therefore different Cardinalities result in different operations for finding and filtering the data, which ultimately results in different Execution Plans.
One common thing that can cause bad Cardinality Estimates are using functions (especially non-deterministic functions) in the JOIN, WHERE, or HAVING clauses. This is because it complicates the ability of the engine to accurately determine the Cardinalities of the values of those functions during the Execution Plan generation process. Other things that can complicate your predicates are using arithmetic operators, and comparisons between different data type fields which result in implicit conversion (e.g. if one field is NVARCHAR and is compared to another that is VARCHAR).
If your queries operate on small tables or only return a small amount of data, then you may not realize you have a potential Cardinality Estimate issue. Sometimes they don't expose themselves initially until after only a small amount of data has changed in one of the underlying tables. This is definitely something you be experiencing but we wouldn't know for sure without your Execution Plans.

Outdated Statistics
The other things you could be experiencing if it's not a Parameter Sniffing issue, and your data has changed between fast and slow runs, is outdated Statistics. (Though this is less likely the case unless your data is changing a lot or your server was incorrectly configured to not auto-update statistics.)
SQL Server routinely stores Statistics about the Cardinality of the values within the Table so that it can make accurate decisions on the operations to use when generating the Execution Plan. This is to try to ensure the most optimal Execution Plan is generated.
On Tables that change rather frequently, their Statistics sometimes get stale and can result in sub-optimal Execution Plans being generated for them, and therefore those Statistics need to be manually updated.

Poor Indexing
Indexes are a way of storing the data in your tables in an efficient logical data structure, a B-Tree. Without a Clustered Index the logical data structure your tables are stored in is a Heap. A Clustered Index converts that Heap into a B-Tree which is a lot more efficient for searching. The Clustered Index determines the actual ordering of your entire table itself.
Nonclustered Indexes store copies of specific fields from your table into their own B-Trees sorted on those fields themselves. They also store a reference to the Primary Key of the table in case they need to find more information from the table that isn't stored in the Nonclustered Index itself.
It is possible that if your tables were missing the appropriate indexes that your queries would be fast enough with a small amount of data but continue to slow down as the tables grew. Furthermore it is possible if you had the appropriate indexes when the queries were originally written, and someone added, changed, or removed one of those indexes, a different Execution Plan was generated that was sub-optimal. (Yes, something as simple as adding an additional index on a table can cause an existing optimal Execution Plan to no longer be used for a specific query and instead a sub-optimal plan can be used. This is on the fault of the query optimizer of the SQL Server Engine and the statistics on that table.)

Additional Resources - Specific Topics

RedGate - Execution Plan Basics - This is a great start to end overview of what is an Execution Plan, how are they created, and will help you understand their purpose and how to use them to debug performance issues. I find the Execution Plan can answer 95% of the issues someone is experiencing, and even proactively tell you about an issue that might not currently be affecting performance but can certainly affect performance in the future.

Brent Ozar - How Execution Plans Are Made - This is a good video to give a little bit of a visual with examples on how Execution Plans are formed and to help you think a little bit like the SQL Server Engine.

Brent Ozar - Watch Brent Tune Queries - This is a good video to understand the key things to look for when query tuning. You'll get a more intimate understanding of how to use the Execution Plan to help you determine what the cause of your performance problems were. You can even check for some of these things proactively after you've written a query.

Brent Ozar - Watch Brent Tune Queries (Again) - This is Brent doing basic query tuning again, so while not the exact same video the concepts are redundant (in a good way) to help you catch on to the things one should do, even proactively after you write the query, to verify its performance will hold true in the future.

SQL Server Central - Statistics And Cardinality Estimation - Cardinality Estimation is a very important thing to understand (and is slightly more complex than understanding basic things like proper indexing). This article introduces you to what it is, and what it's purpose is in SQL Server. I highly recommend looking into other resources as well (I'll continue to update my answer when I find other good ones). This is something you can also proactively check a lot of times by looking at the Execution Plan of your query right after you've written it. There's specific things you can look at in the plan like the Estimated Number of Rows vs Actual Number of Rows being far off from each other (generally a magnitude off) that indicate when you have a Cardinality Estimate issue.

Additional Resources - General
(Hardening @LadyBug1's comments into my answer for historical reference since they are great suggestions.)
These references are generally great places to learn about SQL Server, it's engine, and how to develop for your database optimally. They are of some of the most well regarded members of the SQL community:

Brent Ozar
Kendra Little
Paul Randal
Any of Aaron Bertrand's Blogs: SQLPerformance.com, SQLBlog.org, MSSQLTips.com
Erik Darling
Jonathan Kehayias
Paul White
Grant Fritchey
RedGate's Simple Talk Blogs

If you can update your answer with a specific example (query, data used for parameters, and the Execution Plan(s)) we can more definitively tell you what the issue is and point out how to determine it.


Since you are using Powershell anyways, I'm not sure why you wouldn't just want to do the loop and return the results from each server to a data object.

If you want to break out of powershell and do it from SSMS, as long as you know your servers to begin with you can look into using Registered Server groups which allow you to run a query against multiple SQL instances at once.

Basically, from SSMS, go to View > Registered Servers.
It should appear in it's default location on the left.  From there, drill down to Local Server Groups, right click > New Server Group.
Add the group name 'CMS Group'.
Then right click on the new group and select 'New Server Registration'.  Fill in your connection details and add the server.
Repeat that process until all your servers are added to the group.

Once the group is complete you can right click on the group folder and choose 'New Query'.
A new query window will open with a pinkish red color at the bottom where you would normally see your connection details.  This new query window will run against all members of that group.


It depends on how you export your database:

if you use mysqldump, then the result is a flat text file containing the sql commands.

you can compress it manually or in one commandline like this:

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

see: http://www.ducea.com/2006/10/28/compressing-mysqldump-output/

if your question is, if the tables are stored compressed in the working directory, there are some possibilities:

if you have MyISAM tables that would work if they are read-only, you can Generate Compressed, Read-Only MyISAM Tables with myisampack

And by setting InnoDB configuration options, you can create tables where the data is stored in compressed form.

see: dev.mysql.com/doc/refman/5.5/en/innodb-compression.html

just alter the table to add the compressed option:

ALTER TABLE `tablename` ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE =4


Log shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning

I believe this will work I need to test it first. My problem is that SQL Server Evaluation edition seems to only correspond to Enterprise/Developer edition or at least that is my understanding. I do not have access to 2017 Standard edition without purchasing but do not want to purchase it without knowing if my proposed log shipping implementation would work for sure.

According to the Docs, Express Edition can also handle table partitioning in 2016 SP1+. Thus you could setup an extremely small database, create a new partitioned table, and log ship it from 2014 Enterprise Edition to 2017 Express Edition and see if it works. Express edition is free and can be easily downloaded and installed.

If it works, then it'll work in Standard. If it doesn't, then it won't work in standard and it has some but not all feature parity across editions in 2016 SP1+ with partitioning being one of those that is available across all editions.


create a user with grant option privilege.

GRANT [whatever privilege to that normal user] TO 'normal_user'@'ipaddress' WITH GRANT OPTION IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


SQL Server: Who created an audit and who altered it last?

A trace isn't a database-level object, so none of those queries you were reading about had any chance. The default trace has this information, though, under event 117 (AUDIT CHANGE AUDIT EVENT) - assuming you haven't disabled it and that the audit was created recently enough that it's still there (it's a rolling trace).

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT *
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 117
  ORDER BY StartTime DESC;


Relational databases are designed around joins, and optimized to do them well.

Unless you have a good reason not to use a normalized design, use a normalised design.

jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined.

If you can model it relationally, model it relationally. If you can't, consider json etc. If you're choosing between json/jsonb/hstore, generally choose jsonb unless you have a reason not to.

That's what I said in my blog post, which addresses just this topic. Please read the whole post. The paragraph you quoted points out that if you're choosing a dynamic structure you should choose jsonb over hstore, but the rest of the blog post is about why you should usually prefer to model relationally if you can.

So. Model the main structured part relationally. If the tables are really wide with lots of columns, this might be a sign that further normalization is required. Do not be afraid of joins. Learn to love joins. Joining many small tables will often be faster than querying and maintaining big denormalized tables. Denormalize only if you need to for specific cases, and preferably via materialized views ... but don't do it until you know you need to and have an actual concrete problem to solve.

For user-contributed data that's freeform and unstructured, use jsonb. It should perform as well as hstore, but it's more flexible and easier to work with.

One relevant thing to understand: GiST and GIN indexes like those used on jsonb are generally much less efficient than a plain b-tree index.  They're more flexible, but a b-tree index on a normal column will almost always be much, much faster.


Can a surrogate key and a primary key be in the same table?

A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key).

The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or important in some way that has meaning for the database user. Typically the primary key is the one referenced by foreign key constraints in other tables.


How to set up mysql docker container with existing ib* files?

ib* locked is another process already is running on them.
Restore the entire datadir, not just the ib* data files, to a directory.
Use the volume mount option of the container with docker/podman -v localdir:/var/lib/mysql option on the startup to pass that data directory to the container.


Mysql 5.6 - Create view with progressively increment a datetime field grouped by

How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0:

SELECT t1.contact_id,
       t1.contact_group_id,
       t1.contact_email,
       COUNT(t2.contact_id) - 1 row_number
FROM contacts t1
JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id
                AND t1.contact_id >= t2.contact_id
GROUP BY t1.contact_id, t1.contact_group_id, t1.contact_email

Use this enumeration in DATEADD().


SQL Server database stuck in Recovery after Restoring .BAK to same instance

on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy ...

You are restoring on the same database and using norecovery in your script. You should use (pseudo code) ..

restore database dbname
from disk = 'path to your backup'
with recovery, stats =10,
move 'logicalName' to 'physical_path\new_dbname.mdf',
move 'logicalLogFileName' to 'physical_path\new_dbname_log.ldf'


Database Design - Removing ambiguity without adding loop

If I understand your scenario properly, the following assertions are relevant:

A Cat has one-to-many Owners or, put another way, an Owner has one-to-many Cats.
A Cat is involved in one-to-many Visits to one-to-many Vets.
In each Visit to one specific Vet, one  Cat is taken by one-to-many Owners.

From this set of assertions I have derived (and uploaded to Dropbox) the following Cats and Vets Data Model, which depicts the way I would approach your specifications.
As you can see in such IDEF1X[1] data model, I consider important to add an entity that represents the specific event when a Cat (brought by one-to-many CatOwners) visited a particular Vet, and I called it Visit.
And yes, I also think that the CatOwner entity (that you have alredy included in your diagram) is very useful since, in addition to preventing a Person who is not an Owner of a particular Cat from taking such Cat to a Vet, it may also serve the purpose of relating a Cat and one-to-many of its Owners to a specific Visit to an individual Vet.
It is also necessary to add a many-to-many relationship to provide the advantage of associating one-to-many CatOwner ocurrences to one-to-many Visit instances (each Visit received by one particular Vet), and I propose the relationship called CatOwnerVisit to cover this requirement.
Note
1. IDEF1X is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the U.S. National Institute of Standards and Technology (NIST).


Restoring SQL DB from down SQL Server (on domain) to existing SQL server (not on domain)

Restoring a SQL Server database only requires admin rights to the destination SQL Server instance.  It doesn't matter at all if the new or old machines were members of the domain.


How to bring all replicas on a SQL availability group to an earlier state / point in time?

Yes, that is the correct approach.

From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas

Unless you remove the database from the Availability Group, you will not be able to restore the same database on the primary node.

On a side note:
If it is just dropping one table (depending on the size of the table compared to the database size) I might consider restoring the backup with a different name and recreate that table in the primary. There are many ways to do that which is not the scope of this question.


Select the Scheme and Table based on Select result in Postgresql

You can't. Columns can tell information that's useful but none of it will get you to a value without sending a query. So you'll have to have two queries,

Getting the column name,
Selecting rows where a specific column name is set to a value.

That second query requires planning. Some queries on column names don't require visiting the heap -- such as an index-only scan. Some are greatly sped up by visiting an index first, so you don't have to visit the entire heap and the values for the column are not stored on the column anyway, they're stored in a heap in PostgreSQL.

This may not be ideal, but it's what you've got to work with. See also

Search all columns as a single text per row
Hide columns that are constant across a small number of rows


Design database with versioning and without redundant information

I'd avoid using a NoSQL database for what is clearly relational data.

As far as I can tell, this design provided below might work for your system by providing versioning for all steps, components, materials, etc, without requiring unnecessary redundancy.  Clearly, if there is a new version of a given material, some of the material meta-data may not change, such as the material name, so there will naturally be some redundancy; however this design keeps that to a minimum.

USE tempdb;

CREATE TABLE dbo.Procedures
(
    ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureName varchar(30) NOT NULL
    , CONSTRAINT PK_Procedures
        PRIMARY KEY CLUSTERED
        (
            ProcedureID
            , ProcedureVersionNum
        )
);

INSERT INTO dbo.Procedures (ProcedureID, ProcedureVersionNum, ProcedureName)
VALUES (1, 1, 'Create SQL Server');

CREATE TABLE dbo.Steps
(
    StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , StepName varchar(30) NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureStepOrder int NOT NULL
    , CONSTRAINT FK_Steps_Procedure
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
    , CONSTRAINT PK_Steps
        PRIMARY KEY CLUSTERED
        (
            StepID
            , StepVersionNum
        )
);

INSERT INTO dbo.Steps (StepID, StepVersionNum, StepName, ProcedureID, ProcedureVersionNum, ProcedureStepOrder)
VALUES (1, 1, 'Deploy Virtual Machine', 1, 1, 1)
    , (2, 1, 'Install SQL Server', 1, 1, 2)
    , (3, 1, 'Configure SQL Server', 1, 1, 3);

CREATE TABLE dbo.Tasks
(
    TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , TaskName varchar(30) NOT NULL
    , StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , TaskStepOrder int NOT NULL
    , CONSTRAINT FK_Tasks_Step
        FOREIGN KEY (StepID, StepVersionNum)
        REFERENCES dbo.Steps (StepID, StepVersionNum)
    , PRIMARY KEY CLUSTERED
        (
            TaskID
            , TaskVersionNum
        )
);

INSERT INTO dbo.Tasks (TaskID, TaskVersionNum, TaskName, StepID, StepVersionNum, TaskStepOrder)
VALUES (1, 1, 'Create VM', 1, 1, 1)
    , (2, 1, 'Install Operating System', 1, 1, 2)
    , (3, 1, 'Join VM to Domain', 1, 1, 3)
    , (4, 1, 'Run SQL Server Setup', 2, 1, 1)
    , (5, 1, 'Configure SQL Server Security', 3, 1, 1)
    , (6, 1, 'Create databases', 3, 1, 2);

CREATE TABLE dbo.Materials
(
    MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , MaterialName varchar(30) NOT NULL
    , CONSTRAINT PK_Materials
        PRIMARY KEY CLUSTERED
        (
            MaterialID
            , MaterialVersionNum
        )
);

INSERT INTO dbo.Materials (MaterialID, MaterialVersionNum, MaterialName)
VALUES (1, 1, 'Virtual Machine')
    , (2, 1, 'Operating System Install Media')
    , (3, 1, 'SQL Server Install Media')
    , (4, 1, 'Disk');

CREATE TABLE dbo.NeededMaterials
(
    NeededMaterialID int NOT NULL
    , NeededMaterialVersionNum int NOT NULL
    , MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , CONSTRAINT FK_NeededMaterials_Material
        FOREIGN KEY (MaterialID, MaterialVersionNum)
        REFERENCES dbo.Materials (MaterialID, MaterialVersionNum)
    , TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , CONSTRAINT FK_Tasks
        FOREIGN KEY (TaskID, TaskVersionNum)
        REFERENCES dbo.Tasks (TaskID, TaskVersionNum)
    , PRIMARY KEY CLUSTERED
        (
            NeededMaterialID
            , NeededMaterialVersionNum
        )
);

INSERT INTO dbo.NeededMaterials (NeededMaterialID, NeededMaterialVersionNum, MaterialID, MaterialVersionNum, TaskID, TaskVersionNum)
VALUES (1, 1, 1, 1, 1, 1)
    , (2, 1, 2, 1, 2, 1)
    , (3, 1, 3, 1, 4, 1)
    , (4, 1, 4, 1, 1, 1)

CREATE TABLE dbo.Orders
(
    OrderID int NOT NULL
        PRIMARY KEY CLUSTERED
    , OrderDate datetime NOT NULL
    , CustomerName varchar(30) NOT NULL
);

INSERT INTO dbo.Orders (OrderID, OrderDate, CustomerName)
VALUES (1, '2018-03-15 09:24:00', 'Max Vernon');

CREATE TABLE dbo.OrdersProcedures
(
    OrderProceduresID int NOT NULL
        CONSTRAINT PK_OrdersProcedures
        PRIMARY KEY CLUSTERED
    , OrderID int NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , CONSTRAINT FK_OrdersProcedures_Procedures
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
);

INSERT INTO dbo.OrdersProcedures (OrderProceduresID, OrderID, ProcedureID, ProcedureVersionNum)
VALUES (1, 1, 1, 1);

Show the order details:

SELECT o.CustomerName
    , o.OrderDate
    , p.ProcedureName
    , p.ProcedureVersionNum
FROM dbo.Orders o
    INNER JOIN dbo.OrdersProcedures op ON o.OrderID = op.OrderID
    INNER JOIN dbo.Procedures p ON op.ProcedureID = p.ProcedureID AND op.ProcedureVersionNum = p.ProcedureVersionNum;

╔══════════════╦═════════════════════════╦═══════════════════╦═════════════════════╗
║ CustomerName ║        OrderDate        ║   ProcedureName   ║ ProcedureVersionNum ║
╠══════════════╬═════════════════════════╬═══════════════════╬═════════════════════╣
║ Max Vernon   ║ 2018-03-15 09:24:00.000 ║ Create SQL Server ║                   1 ║
╚══════════════╩═════════════════════════╩═══════════════════╩═════════════════════╝

SELECT p.ProcedureName
    , s.StepName
    , t.TaskName
    , t.TaskStepOrder
    , m.MaterialName
FROM dbo.Procedures p
    LEFT JOIN dbo.Steps s ON p.ProcedureID = s.ProcedureID AND p.ProcedureVersionNum = s.ProcedureVersionNum
    LEFT JOIN dbo.Tasks t ON s.StepID = t.StepID AND s.StepVersionNum = t.StepVersionNum
    LEFT JOIN dbo.NeededMaterials nm ON t.TaskID = nm.TaskID AND t.TaskVersionNum = nm.TaskVersionNum
    LEFT JOIN dbo.Materials m ON nm.MaterialID = m.MaterialID AND nm.MaterialVersionNum = m.MaterialVersionNum
ORDER BY p.ProcedureName
    , s.ProcedureStepOrder
    , t.TaskStepOrder;

Results:

╔═══════════════════╦════════════════════════╦═══════════════════════════════╦═══════════════╦════════════════════════════════╗
║   ProcedureName   ║        StepName        ║           TaskName            ║ TaskStepOrder ║          MaterialName          ║
╠═══════════════════╬════════════════════════╬═══════════════════════════════╬═══════════════╬════════════════════════════════╣
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Virtual Machine                ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Disk                           ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Install Operating System      ║             2 ║ Operating System Install Media ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Join VM to Domain             ║             3 ║ NULL                           ║
║ Create SQL Server ║ Install SQL Server     ║ Run SQL Server Setup          ║             1 ║ SQL Server Install Media       ║
║ Create SQL Server ║ Configure SQL Server   ║ Configure SQL Server Security ║             1 ║ NULL                           ║
║ Create SQL Server ║ Configure SQL Server   ║ Create databases              ║             2 ║ NULL                           ║
╚═══════════════════╩════════════════════════╩═══════════════════════════════╩═══════════════╩════════════════════════════════╝

Cleanup:

DROP TABLE dbo.OrdersProcedures;
DROP TABLE dbo.Orders;
DROP TABLE dbo.NeededMaterials;
DROP TABLE dbo.Materials;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Steps;
DROP TABLE dbo.Procedures;


Database atomic operations implementation

A transaction is started for each statement that occurs outside of an explicit transaction block. Whether a commit is automatically issued following the statement is dependent on the RDBMS configuration. MySQL has the autocommit option, SQL Server has IMPLICIT_TRANSACTIONS, PostgreSQL is always auto commit.

PostgreSQL:

  In the standard, it is not necessary to issue START TRANSACTION to
  start a transaction block: any SQL command implicitly begins a block.
  PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after
  each command that does not follow START TRANSACTION (or BEGIN), and it
  is therefore often called "autocommit". Other relational database
  systems might offer an autocommit feature as a convenience.

InnoDB:

  In InnoDB, all user activity occurs inside a transaction. If
  autocommit mode is enabled, each SQL statement forms a single
  transaction on its own. By default, MySQL starts the session for each
  new connection with autocommit enabled, so MySQL does a commit after
  each SQL statement if that statement did not return an error.

SQL Server:

  SQL Server operates in the following transaction modes.

  Autocommit transactions - Each individual statement is a transaction.

  Explicit transactions - Each transaction is explicitly started with
  the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or
  ROLLBACK statement.

  Implicit transactions - A new transaction is implicitly started when
  the prior transaction completes, but each transaction is explicitly
  completed with a COMMIT or ROLLBACK statement.


Time stamp of Active and Inactive session by JDBC

Unless the application or middle tier supports distinguishing the application sessions from the database sessions (for example setting module, action), this will not happen.

The closest you can get to this is querying V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY, but this is just sampled data, and it requires Enterprise Edition + Diagnostic Pack licensed.


Hundreds of TCP connections opened by sqlservr.exe

Connection pooling is designed to keep connections open.  If your connection pool is set with a maximum pool size of 100 connections, you may well see 100 connections open on the server per intentional connection opened by the client.

I tested this with a simple VB.Net client targeted against my local SQL Server.  The .Net code is:

Module Module1

    Sub Main()
        Dim cb As SqlClient.SqlConnectionStringBuilder = New SqlClient.SqlConnectionStringBuilder
        cb.ApplicationName = "MyTestApp"
        cb.ConnectTimeout = 30
        cb.DataSource = "<servername>"
        cb.InitialCatalog = "tempdb"
        cb.IntegratedSecurity = True
        cb.MultipleActiveResultSets = False
        cb.Pooling = True
        cb.MinPoolSize = 10
        cb.MaxPoolSize = 100
        cb.WorkstationID = My.Computer.Name
        Using cn As New SqlClient.SqlConnection(cb.ConnectionString)
            cn.Open()
            Using c As New SqlClient.SqlCommand("SELECT DB_NAME();", cn)
                Dim r As SqlClient.SqlDataReader = c.ExecuteReader()
                While r.Read
                    Console.WriteLine(r.GetValue(0))
                End While
                r.Close()
            End Using
            Console.ReadKey() ' wait for the user to press a key before we exit.
        End Using

    End Sub

End Module

The code creates what looks like a single connection to my SQL Server, and executes a single very simple query returning the name of the database.  It then "closes" the data reader, returning the connection to the pool.

Running this, then looking at the SQL Server connections with this query:

SELECT des.session_id
    , des.login_time
    , des.program_name
    , des.status
FROM sys.dm_exec_sessions des
WHERE des.program_name = 'MyTestApp';

shows the following results:

╔════════════╦═════════════════════════╦══════════════╦══════════╗
║ session_id ║       login_time        ║ program_name ║  status  ║
╠════════════╬═════════════════════════╬══════════════╬══════════╣
║         65 ║ 2018-04-11 15:18:00.517 ║ MyTestApp    ║ sleeping ║
║         66 ║ 2018-04-11 15:18:00.520 ║ MyTestApp    ║ sleeping ║
║         67 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         68 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         69 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         70 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         71 ║ 2018-04-11 15:18:00.530 ║ MyTestApp    ║ sleeping ║
║         72 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         73 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         74 ║ 2018-04-11 15:18:00.537 ║ MyTestApp    ║ sleeping ║
╚════════════╩═════════════════════════╩══════════════╩══════════╝

Connection pooling is creating 10 connections (the minimum I specified in the code above), and keeps them open as long as the app is running.


Drop 15 days records in monthly partition

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should help speed up the queries and make it easier to maintain the partitions.


how to create database(1 CDB and 2 PDB) by dbca silent mode with response file, oracle 12c

I think you need to add a 'pdbAdminPassword' parameter to your response file.

The following worked for me:

[oracle@ora12c66 CDBORCL]$ dbca -silent -createPluggableDatabase -sourceDB cdborcl  -pdbName pdb4 -createPDBFrom default  -pdbAdminUserName pdb4  -pdbAdminPassword qwe123 -createUserTableSpace true -sampleSchema true -TEMPLATENAME General_Purpose.dbc
Creating Pluggable Database
4% complete
12% complete
21% complete
38% complete
85% complete
Completing Pluggable Database Creation
100% complete
Look at the log file "/u01/cfgtoollogs/dbca/cdborcl/pdb4/cdborcl0.log" for further details.


How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.


What does the presence of rsAccessDenied mean?

Check the rights to the folder and report in SSRS. You can do this in the "Security" option under the "Folder Settings" menu. Click "Edit Item Security" and provide your user(s) with the correct rights and permissions.


MYSQL - How I can filter only Debtor and only Creditors using action_type column?

Test

SELECT -- client_id,
       area_id,
       SUM(CASE WHEN action_type='dr'
                THEN amount END) total_debits,
       SUM(CASE WHEN action_type='cr'
                THEN amount END) total_credits,
       SUM(CASE WHEN action_type='cr'
                THEN amount END) - SUM(CASE WHEN action_type='dr'
                                            THEN amount END) total_debtors,
       CASE WHEN SUM(CASE WHEN action_type='cr'
                          THEN amount END) - SUM(CASE WHEN action_type='dr'
                                                      THEN amount END) > 0
            THEN 'dr'
            ELSE 'cr' END balance
FROM tbl_balancesheet
GROUP BY -- client_id,
         area_id


which one is faster: "insert on duplicate update" OR "update with case"

col2=if(values(col2) IS NULL, col2, values(col2)) is equal to col2=COALESCE(values(col2), col2).
If ODKU causes UNIQUE (col2) violation, the whole query will fail (see fiddle, first query).
The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).

fiddle

Finally:

  which faster: “insert on duplicate update” OR “update with case”

If no errors in queries and data INSERT .. ODKU is faster.


Are there situations where queries are formed geometrically?

In response to the latitude/longitude data, relational database systems like SQL Server have spatial data types (http://msdn.microsoft.com/en-us/library/bb933876.aspx) which allows you to handle that kind of stuff. You don't really need to skip rows or anything crazy like that to satisfy a need to make stuff "look" right, if that makes any sense. What is it that you're trying to do? Say if rowA is 200 rows away from rowB, then do X?


Double Concurrent Restore from one backup for 3 node AG?

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice?

It is definitely faster, since the wizard is doing it in sequential order.

You can use sqlcmd or powershell runspace or (possibly coming in dbatools cmdlets) to do parallel restores.

To even speed up that, split the full backup into stripes and enable instant file initialization.

note: make sure that there is no adhoc log backup happening since it can break your lsn and your restore.


MongoDB pinging failed for distributed lock pinger caused by WriteConcernFailed:waiting for replication timed out. Error details: { wtimeout: true }

Your SECONDARY nodes are lagging on replication and your write concern dictates that write to the database should be acknowledged with X nodes, but before the system got enough acks, there was write timeout.

Check rs.printSlaveReplicationInfo() on all replica sets during time when you have hight load.


Why Sql Server management studio use English regional settings instead my local Windows settings?

SSMS is inconsistent. For some data types, it uses comma (I'm Swedish) as decimal separator. For other types, it uses dot.
For money and float SSMS presents comma, but for decimal I get dot. None of them gives me a thousand separator.
SELECT CAST(3000.1 AS money), CAST(3000.1 AS float), CAST(3000.1 AS decimal(9,2))
I guess that one could consider the inconsistency a bug.
SSMS was never designed to be an end-user tool, so we pretty much have to accept what we are given. If we don't like how SSMS present the data returned from SQL Server we can open a feature request for this to Microsoft: https://feedback.azure.com/forums/908035-sql-server
I wouldn't have too much hope for this to be addressed.


The mysql-bin.xxxx files are growing very fast

Are you using replication?  If not, do you have the binlogs turned on for backups?

Do you have expire_logs_days set?  To what?

Give us some clues of what write queries are going on.

Are you UPDATEing all the rows in large tables?  If so, this is a sign of a mis-designed schema.

Are you doing massive DELETEs?  Let's discuss alternatives.


Overwrote database with old mysql dump. How can I restore it with a more recent backup

You won't get a much better answer than here.
Basically, the part of this document that you want is here:

Restoring your MySQL Database
Above we backup the Tutorials database into tut_backup.sql file. To
re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine

If, as is your case, you only wish to restore 1 database of many in a schema, pay particular attention to the line above - it could save you a lot of time and trouble!

Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Note in particular the space between -u and uname and the missing space between -p and your password. This threw me for quite a while.
If you're in any sort of DBA role, this really should be your bread and butter. Many backup strategies also feed the backup into gzip or similar. The way to restore these is also covered.

To restore compressed backup files you can do the following:
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

Again, if you are in any sort of DBA role, you should be doing this at least every three months in order to ensure that all of your backup steps

a) work, and

b) can be performed efficiently with minimum service disruption to business/users.


How to get list of databases that are mapped to a login?

The simplest way is inside SQL Server Management Studio (SSMS). Go to the  Security tab, right click the user name, and select the option User Mapping. Wait a bit, and a list of all DBs on the server comes up, with a check mark next to the ones that the user has access to.

If you also want to know what roles the user has in each of the DBs, you can click on the database name, and the database role membership will show in the bottom window.


We use Microsoft Sync Framework at enterprise to sync in one way or two ways several
database tables. It works amazing!. Sync framework is totally compatible with SQL 2008 R2 and you wont have any data loss if you configure it well.

The only thing you need to do is provisionate the source and destination tables, and you
can do it with .NET to have automatic task.

Here you have a microsoft example to sync SQL DATABASES with .sql setup files and project
example:

http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab/view/SourceCode#content

Here you have a full example to sync data between sql servers:

http://rvramos.wordpress.com/2010/10/19/learning-microsoft-sync-framework-2-0-the-easier-way/

Provisioning server example:

//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
string scope = “sync_customer“;
// Create a scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope);
//GetDescriptionForTable gets the schema of the table, so that tracking tables and triggers
//can be created for that table
DbSyncTableDescription tableDetailsCustomer = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Customer”, serverConn);
//add the table description to scope
scopeDesc.Tables.Add(tableDetailsCustomer);
//Create the scope provisioner
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
//We have to skip the creation of the table Customer because it is already created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//Now we can provision the
serverserverConfig.Apply(serverConn);

Sync Example:

//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=Server1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//client connection
SqlConnection clientConn = new SqlConnection(@”Data Source=Client1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
//should be equal to the one we provision in the server and client
string scope = “sync_customer“;
//create an orchestrator
SyncOrchestrator sync = new SyncOrchestrator();
//specify that we will upload to the server first then download from the server
sync.Direction = SyncDirectionOrder.UploadAndDownload;
//specify the client
sync.LocalProvider = new SqlSyncProvider(scope, clientConn);
//specify the server
sync.RemoteProvider = new SqlSyncProvider(scope, serverConn);
//synchronize it
sync.Synchronize();

EDIT:

It is recommended to use between databases with the SAME version (Sql server 2008 standard, enterprise, express). but with same version to avoid problems. Never tried with 2008 to 2005 but I read is not convenient.


MongoDB cross datacenter replication without elections or high data availaility

You could created a Replica Set with a Hidden Member. The hidden member will take a read/only copy of your data. However, if the hidden member goes down then the primary steps down to secondary, i.e. your MongoDB becomes read/only.
You can define an Arbiter. It is not necessary to create the Arbiter service on a dedicated host, define it on your primary node. It is just a small mongod service which consumes almost no CPU/memory resources or disc space.
If you have a Primary + Arbiter on one host and the Hidden Secondary on another host then the second may stop and your application continues running normal. Of course, is your primary goes down then the application is not available anymore.


DML queries with different performance on similar servers

A large query_cache_size can hurt performance.

innodb_buffer_pool_size is quite different between the servers.  How big is the dataset?


Missing data when trying to import large columns using powershell

The column you are having trouble with us a varchar(Max) column. This is one of several "LOB data types", which can be up to 2GB.

The invoke-sqlcmd PowerShell cmdlet will, by default, truncate strings to 4000 characters. This can be very useful to prevent accidently pulling back many GB of data from a query without realizing it. This is similar to the default query timeout, which prevents accidental long-running queries.

A "surprise" LOB column containing 2GB per row could pull back 1TB of data in as few as 500 rows--which would have unintended performance issues for other users on the server and possibly even on the local network, assuming a low bandwidth network.

To override that default, simply provide the -MaxCharLength parameter to indicate you want more than 4000 characters from your varchar(Max) column. (Similar to the way you already supply the -QueryTimeout 0 parameter.


Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

Back in August 2014, I answered this question : How to start mysqld with read-only data directory?
I mentioned how you could make the InnoDB Architecture completely read-only.
You can also read MySQL Documentation (Configuring InnoDB for Read-Only Operation)
The only reason ibdata1 would grow would be MVCC (needed to support transaction isolation). I wrote about such uncontrolled growth of ibdata1 in my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Turning its features removes any need to write anything related to MVCC inside ibdata1.