Order of composite key for best lookup performance

For a query that is doing an equality seek on both columns it doesn't matter. You'd need to look at your other queries to see how they would benefit from either possibility.

An index on (A,B) could also serve for an equality seek on A but not an equality seek on B for example.

Also if you had ORDER BY A,B on a query then an index on A,B could avoid a sort.

You say you have no such queries and all are strict equality using both columns.

As a tiebreaker you might consider whichever leading column would make the most useful histogram but for the query pattern you describe where neither A or B are ever queried except together to look up a key in an unique index neither will be useful.

So in your case it doesn't matter.

BTW: There is a persistent belief that putting the most selective column first is required.

This doesn't make any difference here. Either way SQL Server will be able to seek on both columns. Contrary to the StackOverflow answer linked in the comments there is no big difference in efficiency either way (I guess there might be a minor benefit in CPU time by checking the column most likely to differ first and thus potentially avoiding the need to do a comparison for the second key column in the composite key but even if true this would be a micro optimisation)

Sometimes this will make sense for other queries in your workload though. E.g if there are queries against both A and B individually and the index wasn't covering for either of them but one of the columns was selective enough to make a plan with lookups worthwhile then putting the most selective first would make sense in that instance.


With SQL Server's Query Store, how can cleanup avoid deleting "least expensive" queries?

You can also use STALE_QUERY_THRESHOLD_DAYS to clean purely based on time rather than the size-based cleanup parameter.


Can I combine the results from multiple columns into a single column without UNION?

It is unclear to me what is a "more elegant way".

Oracle you can use the following statement to make columns to rows

select all_name
from foo
unpivot (all_name for col_name in (
  his_name,
  her_name,
  other_name));

This is the syntax diagram of the select statement

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

Neither the WHERE,GROUP BY, HAVING, LIMIT, SELECT, INTO, FOR UPDATE nor the LOCK IN SHARE MODE clause can increase the number of rows defined by the FROM clause.
So if table_references equals foo the  query cannot contains more rows than the table foo.
So MySQL does not have such an "elegant" way to unpivot a table.

A way to do such unpivoting without the use of UNION can be done buy using a join. We want to create 3 rows for each row of the foo table, so we create an auxiliary table containing three rows
and (cross) join it to the foo table. Now we have three rows in our query for each row in the base table foo. Each query row can be filled by the appropriate data. Instead the ELT function one can use IF or CASE.

SQL Fiddle

MySQL 5.6 Schema Setup:

create table foo (
  his_name varchar(10),
  her_name varchar(10),
  other_name varchar(10));

insert into foo(his_name,her_name,other_name) values ('one','two','three');
insert into foo(his_name,her_name,other_name) values ('four','five','six');

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Pivot Query:

select elt(aux.line,foo.his_name,foo.her_name,foo.other_name) all_name
from foo  cross join  aux

Results:

| all_name |
|----------|
|      one |
|     four |
|      two |
|     five |
|    three |
|      six |

Of course there are different ways to create a table containing the three rows  with values 1,2,3:

SQL Fiddle

Using an auxiliary table:

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Using an auxiliary table:

select line
from aux

using a constant expression:

select 1 line
union all
select 2
union all
select 3

counting row numbers:
I found it here

SELECT
 @rownum := @rownum + 1 line
 FROM
(SELECT @rownum := 0) r, INFORMATION_SCHEMA.COLUMNS t
where @rownum<3

using one of the dictionary views:

SELECT
 ordinal_position  line
 from INFORMATION_SCHEMA.COLUMNS t
where table_catalog='def'
and table_schema='information_schema'
and table_name='COLUMNS'
and ordinal_position between 1 and 3

Results:

| ORDINAL_POSITION |
|------------------|
|                1 |
|                2 |
|                3 |


Microsoft SQL Server partitioning with on premise server

Yes this is possible, but is not a good idea. You can theoretically run a web application anywhere and punch firewall holes so that the web application can access a database on prem. This opens up a whole lot of issues as the database will have to be on a secure network segment and can wreck havoc on security policies.

You can make this more secure by using SSL for the database connection and use strict access lists on the firewalls, use Site2Site VPN and so forth but you are accessing the database over wide area network which can affect other users.

But in any case you will have to re-route all traffic from this customer to a different server, which will make the application more complex so it's probably easier to install the application on-prem for this customer.


Can I move data and full text search files to another drive and change the drive letter?

I just did something like this with my system databases for a SAN migration and it didn't seem to have any negative impact. Having said that, I tested it in a dev/test environment. Do you have an opportunity to test this in a dev/test environment?

Also, there's not much of a need to reboot. Just stop the services, do the move, and start the services.

One final consideration is that you copy the files appropriately. Managing NTFS permissions issues can be a real pain so use something like XCOPY (which is what I used) or RoboCopy to ensure that you preserve the integrity of those permissions.


make MariaDB Auto-Increment-Increment dynamic

No, the auto increment functionality of MySQL (or MariaDB) does not provide some kind of "dynamic" auto increment values like you request. The formula you arranged is all MySQL can do.

In your case you might be better of in just abandoning the auto increment column and use a custom column instead. You can fill this for example with UUIDs. This does not guarantee uniqueness of generated IDs, but the possibility to generate the exact same IDs is very low.


You are correct in your initial assumptions here. If the row don't move (including due to a page split), then a scan/seek won't read it twice. But, as you say, perhaps the query is done through an NC index for which the row index was moved, hence the double read.

Regarding terminology, there are some standard phenomena defined in ANSI SQL, which are then used in conjunction with isolation level to describe the isolation levels. I.e., isolation level A eliminates phenomena X and Y but not Z. Such phenomena include dirty reads (read something that has been modified but later might be rolled back), phantom (read some data, read it again and a new row occurs in that set). Double read isn't among those phenomena defined in ANSI SQL, perhaps since the standard is too posh to bother about physical implementations (like indexes) and in such a world encountering the same row twice isn't even on the table? I.e., what you describe here is what we in the SQL Server community use to call something like "double read", but don't expect to see the terminology formalized for this phenomena.

(I realize that I probably over/misuse the word phenomena here, my bad in that case!)
Missing committed rows or encountering them multiple times due to an allocation-ordered scan over changing data is specific to using read uncommitted isolation (enabled by the NOLOCK hint here). See also Previously committed rows might be missed if NOLOCK hint is used by Microsoft's Lubor Kollar.
As David Browne suggested you could consider reporting off of a database snapshot - if you can’t use a an isolation level that produces correct results.
One can also consider ultra low-tech solutions. I'm thinking backup and restore, and then report off the restored database. You can combine full backups with log backups. This is more feasible when the database isn't too large, of course.


SQL20448N "05/09/2016" cannot be interpreted using format string "YYYY-MM-DD" for the TIMESTAMP_FORMAT function

The error message is very clear. I suppose the type of CRTNG_DTE is text/varchar and at least one of the values is not in the YYYY-MM-DD format (05/09/2016 clearly isn't!) and the conversion TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') fails.

The only unclear in the message is the mention of TIMESTAMP_FORMAT() function. This part of the DB2 docs, function TO_DATE() clears that:

  The TO_DATE scalar function is a synonym for the TIMESTAMP_FORMAT scalar function.

The confusion may stem from wrong assumptions about what the TO_DATE() function does.

The TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') is trying to convert strings that have the format YYYY-MM-DD to a date. The dates internal format is irrelevant and you cannot modify it. You cannot choose what format the dates are internally saved as.

Their internal format is a representation of date that is different from DBMS to DBMS and may also change from version to version. And developers need not care what that representation is!

Another issue is the first conversion:

YEAR(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'))

Since TO_DATE() is expecting a string (to be converted) and a format string but you pass to the function a date and a format string, DB2 converts first the date value (CURRENT_DATE) to a string (I guess using the default in your installation output format of MM/DD/YYYY) so it then tries to calculate:

TO_DATE('05/09/2016', 'YYYY-MM-DD')

which of course fails for the same reason as described above.

So, to solve the issue (or issues):

Use YEAR(CURRENT_DATE) instead for the first calculation.
Make sure that all values of the CRTNG_DTE column have been saved with the format ('YYYY-MM-DD') you expect.


You did not specify your RDBMS, but here is a solution that uses SQL Server and dynamic SQL.  If you are using a different RDBMS, you could probably get it to work with minimal changes.

Since you are generating column headings (Username1, Username2, etc.) based on how many UserName's have the same LastLogon value, I used an intermediate table (PivotTemp) to store both the original data as well as the generated column names.

I added a few more rows to the test data to verify the output.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (LastLogon datetime, UserId int, UserName1 varchar(30))

insert into table1(LastLogon,UserId, UserName1) values
('2018-06-14 21:00:00',1,'John'),
('2018-06-20 15:03:00',2,'Jack'),
('2018-06-14 15:31:00',3,'Mary'),
('2018-06-18 07:18:54',4,'Ben'),
('2018-06-18 07:18:54',5,'Simon'),
('2018-06-18 07:18:54',5,'Barry'),
('2018-06-20 17:03:54',6,'Andrew'),
('2018-06-20 17:03:54',6,'Stuart')

--The solution
drop table if exists PivotTemp
select * into PivotTemp from
(
select LastLogon, UserName1 as UserName, 'UserName' + convert(varchar(5),ROW_NUMBER() over(partition by LastLogon order by LastLogon)) as GeneratedUserNameHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedUserNameHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(' + QUOTENAME(c.GeneratedUserNameHeading) + ',''N/A'') as ' + QUOTENAME(c.GeneratedUserNameHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

print @cols
print @colsCoalesce
--select * from PivotTemp
set @query = 'SELECT LastLogon, ' + @colsCoalesce + ' from
            (
                select LastLogon
                    , UserName
                    , GeneratedUserNameHeading
                from PivotTemp
           ) x
            pivot
            (
                 max(UserName)
                for GeneratedUserNameHeading in (' + @cols + ')
            ) p '

execute(@query)

| LastLogon               | UserName1 | UserName2 | UserName3 |
|-------------------------|-----------|-----------|-----------|
| 2018-06-14 15:31:00.000 | Mary      | N/A       | N/A       |
| 2018-06-14 21:00:00.000 | John      | N/A       | N/A       |
| 2018-06-18 07:18:54.000 | Ben       | Simon     | Barry     |
| 2018-06-20 15:03:00.000 | Jack      | N/A       | N/A       |
| 2018-06-20 17:03:54.000 | Andrew    | Stuart    | N/A       |


Efficiently perform multiple similar updates

One of the nice things about INNER JOIN syntax is that it actually allows you to separate filter criteria from join criteria. The two statements in your question could be collapsed into a single query as follows:

UPDATE t1
SET t1.foo = temp.newFoo,
    t1.bar = temp.newBar
FROM dbo.Table1 t1 -- please always use schema qualifiers
INNER JOIN @TempTable TEMP
  ON TEMP.baz = t1.bar
INNER JOIN dbo.Table2 t2
  ON t1.thingId = t2.thingId
WHERE (TEMP.moveMessages  = 1 AND t2.type = 'M')
   OR (TEMP.moveDocuments = 1 AND t2.type = 'D');


How to show distinct column one time and different seperately?

This really is something that the display layer should handle.  But it can be done in SQL Server.  The below sample should show you the basic method.

The Setup

DECLARE @SampleTable AS TABLE
    (
    Keys VARCHAR(10) NOT NULL
    , Val VARCHAR(20) NOT NULL
    );

INSERT INTO @SampleTable
    (Keys, Val)
VALUES ('one', 'rose')
    , ('one', 'apple')
    , ('one', 'grapes')
    , ('two', 'boxes')
    , ('two', 'jars')
    , ('three', 'ships');

SELECT Keys, Val
FROM @SampleTable;

The Query, using ROW_NUMBER and PARTITION BY with a CASE statement to selectively hide the key name.

WITH CTE_Table AS
    (
    SELECT S.Keys
        , S.Val
        , ROW_NUMBER() OVER (PARTITION BY S.Keys ORDER BY S.Keys, S.Val) AS GroupOrder
    FROM @SampleTable AS S
    )
SELECT Keys = CASE WHEN C.GroupOrder = 1 THEN C.Keys ELSE '' END
    , C.Val
FROM CTE_Table AS C;


Have a Class table, with a foreign key to Teacher - the person who taught the class.  Give Class columns for the start and end time. This can be either the MySQL time type (09:00:00 for 9am) or an integer type (9=9am, 23=11pm), it doesn't matter.  From these you can calculate the duration of a class.  The work a teacher performs is the sum of the duration of his classes:

select
    t.TeacherID,
    sum(c.EndTime - c.StartTime)
from Teacher as t
inner join Class as c
    on c.TeacherID = t.TeacherID
group by
    t.TeacherID

(Sorry if that doesn't parse - I work with SQL Server and am not familiar with MySQL syntax. Hopefully you get the idea.)

To cope with schedule conflicts add a day-of-week column to Class.  Enforce the rule about a teacher being in only one class at a time through triggers or a unique index.  Since your schedules are weekly the combination of day-of-week and start time would be a sufficient primary key.

As it is likely your school will offer the same subject more than once, you may find it beneficial to separate the concept of a class (Tuesday at 10am) from an actual instance (5th of March at 10am) to the delivery of that instance (Dave taught on 5th March at 10am).  A separate table for each of these would be best.


pgAdmin3 can't connect properly to Postgres 9.2

Other than not using features that cause errors, no, there is no workaround. Since in this case one of the features that causes an error appears to be listing tablespaces during startup, you're out of luck.

Just compile a newer version of PgAdmin-III from sources or use the command-line psql client that comes with PostgreSQL 9.2.

PostgreSQL's system catalogs aren't 100% compatible from version to version. That's intentional; if they had to be exactly the same it'd be very hard to make changes and improvements to PostgreSQL. Programs that use the system catalogs should be prepared to require updates when a new PostgreSQL version comes out. Most tools that use the catalogs - like psql, PgAdmin-III, pg_dump, etc - do so because it's the only way to get detailed information about some of the inner workings of the system. You just need to update them when you update the server.


Reseed identity column in all empty tables

Since you can reset the IDENTITY by issuing a simple TRUNCATE:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Though this will only work for tables not referenced by a foreign key. If you have foreign keys referencing these tables (likely), then you need:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DBCC CHECKIDENT(N''' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N''', RESEED);' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Splitting won't give you much performance gain. Joining this table using ID (Should have Clustered Index created for this columns) will give you a better performance.


MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica

Please remember that MySQL Replication is single threaded by nature.
It has two threads

I/O Thread transmission of binlog events from the Master into the Slave's relay logs
SQL Thread processes binlog events collected in the Slave's relay logs in the order
received

From the looks of the variables, I would recommend just two temporary changes:
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

This will loosen up the writes done by the SQL threads because you have binary logging on the Slave. Every transaction written on the Slave has to be logged and the binlogs flushed. Setting sync_binlog to 0 lets the OS cache/flush rather than mysqld.
You also want to loosen the strictness of ACID compliance on the Slave by setting innodb_flush_log_at_trx_commit to 2. This will cause the InnoDB storage engine with more regularity rather than flushing on each transaction commit.
You will have to set innodb_flush_method to O_DIRECT to further assign flushes of pages to mysqld rather than the OS.
I have mentioned these things before

Dec 07, 2012 : Dynamic change to innodb_flush_log_at_trx_commit
Feb 10, 2012 : Is it safe to use innodb_flush_log_at_trx_commit = 2
May 04, 2011 : Clarification on MySQL innodb_flush_method variable

Since innodb_flush_method is not dynamic, you must restart mysqld.
Please add the following variable to your my.cnf
[mysqld]
innodb_flush_method = O_DIRECT

Then, restart mysqld. After that login to mysql and run
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
START SLAVE; /* If replication does not start automatically */

Replication should start catching up. Once caught up, change the variables back with
SET GLOBAL sync_binlog = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

UPDATE 2021-02-16 10:12 EST
Why would one think of using these variables on a temporary basis ?
Over the years, I have had clients that would have one of the following setups:

sync_binlog=0 and innodb_flush_log_at_trx_commit=2 : This would be on read-only slaves
sync_binlog=1 and innodb_flush_log_at_trx_commit=1 : This would be for slaves you would want to become a Master should you wish to failover.

SCENARIO : Suppose you had a Master and 3 Slaves. You have the Master with sync_binlog=1 and innodb_flush_log_at_trx_commit=1. The Slave would have sync_binlog=0 and innodb_flush_log_at_trx_commit=2.
If you wish to have a failover, you would set up one of the slaves that is fully caught up (Seconds_Behind_Master=0) to have sync_binlog=1 and innodb_flush_log_at_trx_commit=1. Then point applications to the that new Slave and setup the other Slaves to replicate from the newly promoted Master.
You can use things like ProxySQL / Orchestrator to set up such steps for you.
This just one example of why these variables would be changed on a temporary basis.
CAPTAIN'S LOG : SUPPLEMENTAL
If you have the redo logs (ib_logfile0,ib_logfile1), binary logs, slow logs, or generals logs stored in the same data volume as the data, they can slow down writes to the database. How so ???
At the disk level

All Logs are written sequentially
All data is usually written in random order

Storing logs on a separate disk volume can also speed up write performance. I learned this from a Facebook Engineer's Blog. (See also How do I determine how much data is being written per day through insert, update and delete operations? and
MySQL on SSD - what are the disadvantages?)


It may not fully answer your question, but:

If you specify a user mapping for a superuser for postgres_fdw you can specify it without a password. It will then rely on the .pgpass from the server.

By using a mapping for a superuser you will however need to think about what security concerns this raises for your usage.

Data source:

dbname=sales
host=sales.db
user=sales_reader

Own DB:

dbname=dwh
host=dwh.db
user=dwh_writer
~/.pgpass of the postgres OS user contains:        sales.db:*:sales:sales_reader:verysecret

Example session showing the difference between NOSUPERUSER and SUPERUSER:

CREATE SERVER sales FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'sales.db', dbname 'sales');
GRANT USAGE ON FOREIGN SERVER sales TO dwh_writer;
CREATE USER MAPPING FOR dwh_writer SERVER sales OPTIONS (user 'sales_reader');
CREATE USER MAPPING FOR postgres   SERVER sales OPTIONS (user 'sales_reader');

CREATE FOREIGN TABLE sales_summary (
    item_id text,
    amount bigint,
    date_sold date
)
SERVER sales
OPTIONS (schema_name 'sales_data', table_name 'sales_summary');

GRANT SELECT ON sales_summary TO dwh_writer;

\c dwh dwh_writer

dwh_writer@dwh=> SELECT count(*) FROM sales_summary;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

\c dwh postgres

postgres@dwh=# SELECT count(*) FROM sales_summary;
 count
-------
     2
(1 row)


How to get more physical details when executing an SQL query?

You should use pg_stat_statements.

Modify postgresql.conf as follows:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
track_io_timing = on

and restart PostgreSQL.

Then

CREATE EXTENSION pg_stat_statements;

and find all the data you want in the view pg_stat_statements.


Unable to create connection on MySQL Workbench Edition in Mac?

Common issues:

Is MySQL server running? check that by making sure you have 1 or more processes called mysqld
Is MySQL listening on the 127.0.0.1 address? Make sure it is not running with skip-netwoking or with bind-address different from 0.0.0.0, *, or 127.0.0.1 (it could be only listening on a public ip)
Is there an user called root? It should be there if you just installed it
Did you write the right password- in the latest MySQL versions, mysql gets installed with a random default password. You need to know that or connect to the socket (localhost + socket, not 127.0.0.1 + 3306) using the unix_autentication plugin
What error do you get? It is not the same to get a "could not connect" than a "connection denied"- the first one could be points 1 and 2, the second 3 and 4. Can you connect using the command line "mysql"?
Do you have the latest mysql workbench version? Latest authentication methids may not be supported on old workbench versions

This is a very common question, please read the manual first for troubleshooting: https://dev.mysql.com/doc/refman/5.7/en/can-not-connect-to-server.html


How do you calculate the CPU Affinity Bitmap?

Your binary representation of the mask should be:

1100 0000  0000 0000  0000 0000  0000 0000

Converting that to an unsigned 32-bit integer would equate to your original calculation:  3221225472.

But the affinity mask tasked a signed 32-bit value.  In that case, the binary to signed 32-bit value would be:  -1073741824.

Same bits, just different interpretation/conversion (signed vs. unsigned).

  I can't use the GUI, because all my processes need to be reusable on Server Core instances.

As an aside, you can and should use SSMS remotely, so the fact that you could have target database servers on a server core OS shouldn't prevent you from remotely using a GUI (like SSMS).  On that same note, I always prefer T-SQL at the very least for reusability and for documentation purposes, if nothing more than just knowing exactly what is happening when you do something with no GUI tricks/abstractions/assumptions.


Postgres Order By but only the most recent of a particular type

Solution 1

CREATE TABLE pet ( name text, type text, date date);

INSERT INTO pet VALUES
 ('Nemo', 'Fish', 'June 1, 2016'),
 ('Fido', 'Dog', 'January 1, 2016'),
 ('Felix', 'Dog', 'February 1, 2016'),
 ('Whiskers', 'Cat', 'April 1, 2016'),
 ('Marlin', 'Fish', 'August 1, 2016'),
 ('Shifu', 'Cat', 'March 3, 2016');

Using PostgreSQL-specific DISTINCT ON clause we can do this in a single table scan:

SELECT name, type, date
FROM (
  SELECT DISTINCT ON (
    type,
    CASE WHEN type<>'Dog' THEN date END
  )
  name, type, date
  FROM pet
  ORDER BY
    type,
    CASE WHEN type<>'Dog' THEN date END,
    date DESC
) AS subq1
ORDER BY date;

Solution 2

Using more ANSI-compatible SQL, like UNION and LIMIT. It will work on MySQL, DB2 and some others. Similar solution can be done on Oracle, just replace LIMIT with ROWNUM.

(
  SELECT * FROM pet
  WHERE type = 'Dog'
  ORDER BY date DESC
  LIMIT 1
) UNION ALL (
  SELECT * FROM pet
  WHERE type <> 'Dog'
)
ORDER BY date;

Notes on performance

With small tables (less than 1000000 rows), any solution will work. DISTINCT ON is slightly faster:

Iterations: 100000
Query:   /*q1*/ SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date;
Time:    24.834 s (31%)
Average: 0.248 ms
Rows:    500000
Winner:  80914 times (80%)
Query:   /*q2*/ ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date;
Time:    26.778 s (33%)
Average: 0.268 ms
Rows:    500000
Winner:  12881 times (12%)
Query:   /*q3*/ select name, type, date from ( select pet.*, case when type = 'Dog' then rank() over (partition by type order by date desc) else 1 end as rnk from pet ) as x where rnk = 1 order by date;
Time:    27.490 s (34%)
Average: 0.275 ms
Rows:    500000
Winner:  6205 times (6%)

With "big data", make sure your query does only one, indexed, table scan.


The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function).

If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table.

 CREATE TABLE t (
     a integer,
     b integer
 );
 CREATE VIEW v AS SELECT t.a, t.b FROM t;
 CREATE RULE mirror AS
    ON INSERT TO v DO INSTEAD
    INSERT INTO t (a, b) VALUES (new.a,new.b), (new.b,new.a);

Incidentally, using a view will allow you to enforce your A-B, B-A pairing by also rewriting UPDATEs and DELETEs using other rules.


What is the safest way to switch the binlog format at runtime?

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.


Change Data Capture is supported in the Developer and Enterprise Editions of SQL Server up to and including SQL Server 2016 RTM.

SQL Server from 2016 with Service Pack 1 and higher also supports CDC in Standard Edition.

Source: https://www.microsoft.com/en-us/sql-server/sql-server-editions


The Binary Path in Preferences is for the Postgres binaries, not for the backup file. Revert the Binary Path to the default (probably C:\Program Files\PostgreSQL\$version\bin where you replace $version as appropriate for your installation).

Enter the path to the backup file in the Tools > Restore dialogue and not in Preferences.


Timestamp don't carry any relationship with date and time. Absurdly enough, it is allowed to convert to datetime, but it won't be the date and time that you would hope for. I.e., no relationship with the date and time when the row was inserted/modified. As shown here:

CREATE TABLE #t(c1 timestamp)
INSERT INTO #t DEFAULT VALUES
SELECT c1, CAST(c1 AS datetime) FROM #t

0x00000000000007D5  1900-01-01 00:00:06.683

Since timestamp was such a stupid name for the type, MS decided that we should start calling it rowversion instead. Sound thinking. But the implementation sucked since the name rowversion is treated like an alias to timestamp at parsetime. I.e., specify rowversion in the CREATE TABLE, and when you script it, you'll see timestamp instead.

Wisely enough, MS do not allow this type to be converted to the new date and time types.

So, timestamp/rowversion is out of the question if you want to carry a meaningfule date and time with it. That leaves any of the datetime types, and make sure it is populated. Or use CDC or CT as suggested by Dan Guzman.


Does "allow remote connections to this server" in sql server impact SSIS packages?

I basically asked the same question a few years ago, take a look at my thread here: What does the setting allow remote connections actually do.


sql job cancel triggers failure notification

Because SSIS package are executed as step of type CmdExec. When you cancel this step the process being run is forced to end prematurely.  Consequently premature termination of a process is considered a failure. Behind the scene cancelling a job calls for sp_stop_job

For other type of job steps in a SQL agent job for e.g. T-SQL command performing a backup, when you cancel this job it finishes the currently executing step so it is not considered a failure.


LOCK TABLES dname WRITE is not a valid PostgreSQL statement. It's a MySQL statement. You can't just take a MySQL dump and restore it to Postgres. You'll need to convert it either manually or find some kind of converter script.


It is stored where all binaries are from MySQL Server (if you didn't change the default location)
C:\Program Files\MySQL\MySQL Server 8.0\bin

And yes MySQL creates a sql file(dump), depending on the options you can have different commands like triggers and so on includes or excluded.
for a complete list of options always read up on the manual


SQL Server - Benefits of splitting databases across different logical drives

is there any performance benefits to keep the data files split across different logical drives?

If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes.  For instance, they can be hosted across separate SAN controllers, have different caching policies, be separately monitored, etc, even if the LUNs share the same underlying storage.
If each volume maps to a separate set of physical disks, then splitting them up is very costly, as you can't pool and share IO resources between the volumes.  And as most database file IO is background IO, you should normally pool all the IO resources for all database files together to maximize efficiency, sharing, and per-database peak-IO throughput.
So, it depends, and you need to work with your SAN experts to choose, and to appropriately configure the SAN and the server (eg IO Queue Depth).


How do I create a new mongo collection with custom indices from an existing mongo db?

Your title and question have slightly different goals (copy vs. modify).

To copy a collection between databases you can use the cloneCollection method:

https://docs.mongodb.org/manual/reference/method/db.cloneCollection/

Once you have a collection in the new database that you want to modify you are able to create all the indexes you want to the existing data.

Some of the considerations to be aware of based on the larger data size of the collection is whether you want to block other activities for the creation of the index.  By sending the creation to the "background", you can prevent this:

https://docs.mongodb.org/manual/reference/method/db.collection.createIndex/#options

The other issue, you might want to be aware of, is that index builds in the background generally take longer to build.  One option is, presumably if you are running a replica set, is to build the index on one node at a time, after you remove each node from the replica set.  You can build the indexes once the node is out, and then put it back into the replica set, and move on to the next node.

As far as removing unnecessary fields, you can do that as you go, and just process through the data in batches.  Even if you do it in one query, if it dies, MongoDB will still maintain all the data you already updated, so it can pick up where it left off removing fields that exist in the remaining documents.


Are there any special characters that are never allowed in SQL?

The statement terminator in SQL Server is a semi-colon ;.

http://msdn.microsoft.com/en-us/library/ms177563.aspx

As queries cannot contain a semi-colon, except as the last character to identify the end of the query, this should be enough for you to separate out each statement.


Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int

You have:

max(cast(Date_Last_Data_Changed as varchar(255))) >1900

So you're converting to a string, and then comparing that string to an integer. I think you meant:

max(convert(char(8), Date_Last_Data_Changed, 112)) > '19000101'

Or just

max(Date_Last_Data_Changed) > '19000101'

Note that converting to varchar(255) without a style (e.g. 112) yields a string that will not sort the way you expect, since it is sorting as a string, and when you put month name first, it doesn't work. Dec < Jan, for example.


Short answer:  Not to worry.
Long answer:
I did a survey of over 200 MySQL and MariaDB version names -- 40% ended with -log.  They went back to version 4.1, perhaps farther.
(Interestingly, none of the 8.0 versions ended in -log.)
(I've been using MySQL for 20 years, and have never heard why some have "-log".  And I have not noticed a problem.)
For more trivia:
SHOW VARIABLES LIKE 'version%';


I got my answer from the below link:
generate/analyze trace for error 1461

One may know by using the following statements in sqlplus:

oradebug setmypid
oradebug eventdump system

This did the magic :)


How can I design Address table with different types?

Your design appears to be pretty good overall, but I would make the following recommendations:

You already store the client's First Name and Last Name in the clients table, so you can probably remove the client_address_personal_info table altogether. This will reduce redundant data and lead to increased performance and easier management and reporting.
You can probably normalize the name of the client_address_corporate_info table to something similar as your clients table, e.g. corporations
Similarly to the first point, you should either remove the phone_number field from the clients table or the client_addresses table so it's only stored in one place. (Your choice which table you want to keep it in, I've seen it done both ways.)
One other general recommendation (though this is tangentially related to normalization) is you should size your varchar fields to something that is capable of fitting all potential future values. While obviously we can't predict the future, my point is generally leave enough space for larger values that are logically possible. VARCHAR(1) vs VARCHAR(1000) isn't going to affect performance or disk space is any measurably different way. So while most First Names or Business Names can fit inside a VARCHAR(16) field, I'm sure there's names out there that are 25+ characters long. My general starting point on something like that is VARCHAR(50) (and only in rare cases do I use a smaller sized VARCHAR).


Does plan recompilation happens to plan guide?

As I understand it, a plan guide is not compiled like an execution plan. Therefore, it cannot be recompiled. That being said, a plan guide does result in an execution plan, either through its hints or by specifying a specific plan to use. The resulting or specified execution plan could be affected by auto update statistics.

If auto update statistics determines that a statistics update is necessary, query plans dependent on those statistics would be recompiled.


If I understand you correctly you can enumerate the invoices per vendor and pick the last one (order by desc):

with invoices(invoice, vendor, cdate) as
      (select 0, 0, date '2018-01-08' from dual union all
       select 1, 0, date '2018-08-02' from dual union all
       select 2, 1, date '2018-08-05' from dual union all
       select 3, 2, date '2018-08-10' from dual union all
       select 4, 2, date '2018-08-10' from dual
      )
      select invoice, vendor, cdate
      from (
        select invoice, vendor, cdate
             , row_number() over (partition by vendor
                                  order by date desc, invoice desc) as rn
        from invoices
      ) t
      where rn = 1;


Why SQL Server Service Account connects to the DAC?

I recon that this is port/virus scanner that your security team might be using to find out vulnerabilities e.g. weak passwords. I had the same issue and eventually I figured out that it was the port scanner.

Look into windows event log as well.


Is there a graceful or safe restart for mysql like for apache httpd?

Any "requested" shutdown sequence in MySQL (short of kill -9) is somewhat graceful, since transactions in progress (on transactional tables) are rolled back, but here are a couple of ways to make a restart as clean as possible.

Note: if you are shutting down the server for an upgrade, then don't use this process; instead, follow the process detailed in this answer.

Otherwise, if you're just restarting an otherwise-healthy server so that you can change a read-only global variable or something similar, here is a graceful path:

First, enable innodb_fast_shutdown if it isn't already.  This isn't directly related to the gracefulness of the shutdown, but it should bring your server back faster.

mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_fast_shutdown = 1;
Query OK, 0 rows affected (0.01 sec)

Next, instruct the server to close all open tables as soon as no currently-running queries are referencing them.  This step also has nothing to do with the graceful shutdown, but it will make the subsequent step go faster:

mysql> FLUSH LOCAL TABLES;
Query OK, 0 rows affected (41.12 sec)

The FLUSH TABLES statement (with the optional LOCAL keyword, which avoids an unnecessary but otherwise harmless flush of any slaves) will block and your prompt won't return until all of the tables can be closed.  Once each table has been "flushed" (closed), if a query subsequently references the table, it will be automatically reopened, but that's okay.  What we're accomplishing with this step is making less work for the final step:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (13.74 sec)

mysql>

This statement flushes all tables (hence the advantage of getting some of that out of the way less disruptively with the prior step) and acquires a global (server-wide) read-only lock on them.

You can't have a global read lock until every currently running "write" query (i.e., pretty much everything but SELECT) is done.  Issuing the lock request will allow existing queries to finish but won't allow new ones to start.

Your prompt doesn't return until you hold this global lock, so every query that is in progress when you request the lock is able to finish, and you know they're finished, because you get the prompt back.   Any subsequent queries that try to write anything to any table will just stall, changing no data, waiting indefinitely for the lock, until...

you change your mind about the restart and release the lock manually (UNLOCK TABLES;)
you restart the server, or
you accidentally or intentionally disconnect the command line client from this thread (so don't do that).  Keep this window connected and sitting at the mysql prompt:

Resist the temptation to close this.

mysql>

This idle console prompt is what's holding the global lock for you.  Lose this, lose the lock.

From another console window, restart MySQL the way you normally would, either with initscripts (e.g., your local variant of service mysql.server restart) or with mysqladmin shutdown followed by a manual restart.


object_id is indexed three times

PRIMARY KEY
UNIQUE KEY link
FOREIGN KEY fk_object

object_term is actually indexed once

fk_term
In UNIQUE KEY link, object_term is not the lead column.

UPDATE 2011-12-09 19:42 EDT
In the First try:

object_id is unique (PRIMARY KEY)
(object_id,object_term) is unique (this means object_id can only be associated with one object_term at any given moment)

In the Second try:

object_id is not unique
(object_id,object_term) is unique (this means object_id can only be associated with more than object_term)

UPDATE 2011-12-09 19:57 EDT
If you are concerned about diskspace taken by indexes, you have a decision to make
OPTION 1 : Diskspace is no object
This can only be beneficial if your queries effectively use covering indexes and you are willing to live with (and provide for) large diskspace.
OPTION 2 : Diskspace is a precious commodity
It's time to do some forecasting. Both object_id and object_term are BIGINT. You can cut space down some as follows:
SELECT * FROM relations PROCEDURE ANALYSE();

This will not dump all the rows. It will sample the data and come back with a recommedation for the best column definitions given the data.
Why is that important?

TINYINT UNSIGNED is one byte (Range 0 to 255)
SMALLINT UNSIGNED is two bytes (Range 0 to 65535)
MEDIUMINT UNSIGNED is three bytes (Range 0 to 16777215)
INT UNSIGNED is four bytes (Range 0 to 4294967295)
BIGINT UNSIGNED is five bytes (Range 0 to 2^40 - 1)

If you know the number will not surpass certain values, you can change the column definitions to accommodate less diskspace. At the very least, let PROCEDURE ANALYSE() suggest it for you.
OPTION 3 : Diskspace can be a bottleneck
The more house guests, the more housekeeping to accommodate. If a host can take care of 20 house guests, keep them all 20 happy, and the host is happy as well, the host can always care 20 house guests over and over again. Add one more guest, and the host's quality of services goes down hill. The same goes with indexes. If your queries are fast enough to warrant keeping indexes around, you need to select which indexes are really necessary, and eliminate indexes that are redundant (unwanted house guests). The host must make sure the each house guest's request (query) can be satisfied (done with covering indexes)
Nice Links on Covering Indexes

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/


How to isolate single values from multiple records

This kind of formatting should be usually done in the front end, not in SQL.

You can however get the display you want using window functions, no PL/SQL required.

select case when rn = 1 then alpha_code end as alpha_code,
       beta_code,
       case when rn = 1 then value end as value
from (
  select alpha_code, beta_code, value,
         row_number() over (partition by alpha_code order by beta_code) as rn
  from the_table
)
order by beta_code;

SQLFiddle example: http://sqlfiddle.com/#!4/28ca3/1


For me this problem was caused by a database connection left open by Visual Studio paused in debug mode.  Stopping the debug process allowed the query to complete immediately.


You will want to use the 'transfer logins' script provided by MS.  Pinal had a write up of it here.  I do not remember if I used this to move logins TO 2012, I think I did to setup a QA environment but not sure.  You might need to make minor adjustments but I think it'll work fine.

Keep in mind if you use AD logins and changed domains with no trusts, those users will need to be recreated with the correct security permissions which this script does not do.  This post references SQLSoldier (A SQL guru) and has a handy script.


Finally, it comes to end.

The problem is I need to replace the below line.

local   all             postgres                                peer

 to

local   all             postgres                                peer  map=root

Thats it, no more changes.

Got this from:

This link


HTML entity decoding with oracle DBMS_XMLGEN.convert

Here it is

WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x;


How can we find out if this is being called by another job?

Search all your job steps for the string with job name that ran by other job (at least that is what you suspect).
This query is from this website.

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE   js.command LIKE N'%KEYWORD_SEARCH%'
GO

why would there not be any history for the job that ran?

Depending of how your job history is set up.  See my blog post.
Another good reference here.


MySQL: cannot run change master to inside mysql

The host, user, and password values are supposed to be enclosed in single quotes.

You are also better off executing it as Dynamic SQL

DELIMITER //
DROP PROCEDURE IF EXISTS changeMasterTo //
CREATE PROCEDUREchangeMasterTo(IN ip char(20),IN port int,IN user
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
    SET @sql = 'STOP SLAVE';
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    SET @sql = CONCAT('CHANGE MASTER TO MASTER_HOST=',QUOTE(ip));
    SET @sql = CONCAT(@sql,',MASTER_PORT=',port);
    SET @sql = CONCAT(@sql,',MASTER_USER=',QUOTE(user));
    SET @sql = CONCAT(@sql,',MASTER_PASSWORD=',QUOTE(password));
    SET @sql = CONCAT(@sql,',MASTER_AUTO_POSITION=1,MASTER_SSL=1');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END;
//
DELIMITER ;


Case style of tables and field (Naming Conventions)

It all boils down to personal preference and consistency within your team (or just yourself). Nothing worse than seeing different styles mixed.

As for SQL Server, be mindful of sp_yourProcName, as "sp_" prefix for a stored procedure can do funny things (read more about this from Aaron Bertrand).

Consistency is king. Make your choice and stick with it.


The table-valued function requires static columns (names and types). If you want dynamic columns in result-set, use procedure instead of function.

E.G.

Create Procedure X(@tables varchar(50)) as
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)

        DECLARE oCur CURSOR FOR
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56'

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END

            SET @ssql =  left (@ssql,len(@ssql)-3)
            set @sql = 'select * from '+@tables+' where ' + @ssql
            exec(   @sql)

           close ocur;
        deallocate ocur;
GO
X 'Tree'


How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database?

I back up all of my transaction logs at the same time, every two hours.  I haven't had any issues with Log growth since I set that up, and I haven't had any issues with replications because of the log backups.

I think the best thing is to watch for how fast your logs grow, and plan your log backups accordingly.  Alternatively, how fast you want to be able to restore databases will also affect how often you make backups.


How to generate a reference key between rows

As I had some time to think about it, I came up with this solution. I would appriciate a comment on this.

SET @ref = LAST_INSERT_ID() + 1;

INSERT INTO txn (reference) VALUES (@ref), (@ref);

I'm a bit worried about concurrent connections, which could result in identical reference numbers for unrelated transactions.

EDIT:

The upper solution will NOT work, since LAST_INSERT_ID() returns the last inserted ID of the current connection, which in this case will always be 0.

So another idea would be this:

-- This is the parent transaction
INSERT INTO txn (reference) VALUES (0);
-- Get ID of parent
SET @ref = LAST_INSERT_ID();
-- Insert related/child transactions
INSERT INTO txn (reference) VALUES (@ref), (@ref);
-- Update parent with its own ID
UPDATE txn SET reference = @ref WHERE id = @ref;


Is there a way to auto resume an incomplete job after sql server restart?

In a nutshell maybe. If you have a sql job without a notion of persistence then no:( The job will continue to run according to schedule but it will not pick up from an earlier starting point unless you have to logic to do so.


Installing SQL Server with Virtual Account / (managed local accounts)

Based on your description, I don't think you'll have any issues with either account type. One important consideration is whether you have any internal/corporate or external compliance requirements with regards to the service account used by databases.

Depending on the actual policy or compliance requirement, you might find explicit wordings about password management which doesn't exist with VAs. Real security impact is almost never the issue as most of these policies (that I've worked with anyway) are either really dated or written by a "general IT professional" or both. Trying to convince whoever manages these policies or the auditors has never worked for me. Just educating them on how VAs work can be an exercise in futility.

If you aren't affected by such policies/requirements and are unlikely to be in the foreseeable future then you'll be fine with VAs. Else, you're pretty much pushed down the path of MSA or some other form of human/tool managed account.


Any way around unique index 16 column max

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column:

alter table t add all_keys as c1+c2+c3+...+c18 persisted;
create unique index i18 on t (all_keys);

See Creating Indexes on Computed Columns.

Another approach is to create an indexed view:

create view v
with schemabinding
as select c1+c2+c3+...+c18 as all_keys
from dbo.t;

create unique clustered index c18 on v(all_keys);

See Creating Indexed Views.

Both approaches allow for a partial key aggregate: aggregate c1+c2+c3 as k1, c4+c5+c6 as k2 etc. then index/create indexed view on (k1, k2, ...). Thia could be beneficial for range scans (index can be used for search on c1+c2+c3.

Of course, all + operation in my example are string aggregation, the actual operator to use depends on the types of all those columns (ie. you may have to use explicit casts).

PS. As unique constraints are enforced by an unique index, any restriction on unique indexes will apply to unique constraints as well:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    constraint unq unique
      (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18));
go

Msg 1904, Level 16, State 1, Line 3
The index '' on table 't' has 18 column names in index key list.
The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, creating the constraint on a persisted computed column works:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    all_c as
        c1+c2+c3+c4+c5+c6+c7+c8+c9+c10+c11+
        c12+c13+c14+c15+c16+c17+c18
        persisted
        constraint unq unique (all_c));
go

Obviously, the persisted column consumes the space on disk so the approach may be bad for a very large table. The indexed view approach does not have this problem, it only consumes the space for the index, not the space for the computed column and index.


Will transactional replication push a CREATE INDEX immediately? Or do I need a snapshot?

You'll have to push it to the subscriber yourself.  "Copy..." only applies to the snapshot.

From MSDN :  Frequently Asked Questions for Replication Administrators

How do I add or change indexes on publication and subscription databases?

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


SQL Server detected a logical consistency-based I/O error - tempdb

normally,824 means that there should be some hardware related issues ...Since tempdb is geting corrupt again and again , i would suggest you to involve hardware team\vendor and get your disks analyzed ...also check the application\system logs and see if there are disk related errors there...

Insufficient storage space on disk can cause error code 824
Hardware device or driver software which acts as a medium may cause errors
File system inconsistency can also be reason for SQL server logical consistency error
Damage caused to the database files may be a prime cause for the problem as well
Corruption of the file system can also give rise to SQL fatal error 824

Use the SQLIOSim utility to find out if these 824 errors can be reproduced outside of regular SQL Server I/O requests.


Sql Profiler: what does Duration measure for exec sp_execute

The short answer is that the unit description is mentioned in the answer to this question:

https://stackoverflow.com/questions/2590304/unit-of-measurement-for-duration-column-in-sql-profiler

Also, just to add on to Aaron Bertrand's comment, here is some information on why SQL Profiler should be avoided, and what to use instead (at least through SQL Server 2012):

Performance Impact: Profiler Tracing vs. Server Side SQL Tracing, by Linchi Shea

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Basically, SQL Profiler can have a large impact on the remote server whereas SQL Trace on the server side has much less impact on the remove server.
Google the following phrases for more info:

Server-Side Tracing and Collection

SQL Server Performance Statistics Using a Server Side Trace

Automating Server Side Tracing in SQL Server

How to identify SQL Server performance issues, by analyzing Profiler output?

Good luck.


postgresql trigger unexpected behaviour by many copies of inserted row

Why do you do an update on tbl2? Anyway you must add a where clause with the primary key of the new row to the update. You are now updating all rows in tbl2.

Also the insert is inserting new.* for every row already in tbl1.

Why don't you rewrite the insert into something:

insert into tbl2 (field1, field2, ...) values (new.field1, new.field2, ...);

Now you can set the correct values and do not need an update afterwards.


Need help with my MySQL PROCEDURE #1193 - Unknown system variable

You need to declare variable first and then only you can set the value.

Something like:

DECLARE price_per_meter DECIMAL(6,2);

Then other part is just logics.


log_reuse_wait_desc says ACTIVE TRANSACTION but DBCC OPENTRAN reports nothing

Because this is AdventureWorks and obviously not a production server, you're probably not going to get great answers here. I'd just restart the server. (Is that the right answer? No, but we're obviously talking about a development playground box here, and I wanna get you across the finish line quickly.)

If you really wanted to troubleshoot it, try sp_WhoIsActive:

sp_WhoIsActive @show_sleeping_spids = 1, @show_system_spids = 1, @show_own_spid = 1

Just because if you're getting started with transaction troubleshooting, I'd rather give you something easier to use than DBCC commands.


What are the key points to avoid table locks?

First a quick recap on what a deadlock is. Let's say you have two sessions, 1 and 2, and two resources, A and B. The following sequence then occurs:

Session 1 gets a lock on resource A
Session 2 gets a lock on resource B
Session 1 requests a lock on resource B, but has to wait until session 2 has released it. We are fine up until this point
Session 2 requests a lock on resource A - but session 1 is blocked and can never go forwards to release it. Deadlock occurs.

There is only a very limited amount your DBA can do about this because this situation originates in the application, it merely manifests itself in the database. It is best to adopt a convention of always locking resource in the same order. If we had the convention of always locking in the order A, B then our scenario would be:

Session 1 gets a lock on resource A
Session 2 requests a lock on A, but has to wait for session 1
Session 1 gets a lock on resource B, completes its work, and releases both
Session 2 gets locks on both A and B, completes its work, and releases both. No deadlocks.


Oracle second date range comparison ignored in where clause

Not enough information, so I will just leave this here:

Bug 14073795  Wrong results on select statement with TRUNC(date) or ROUND(date)

Bug is fixed in 11.2.0.4.


SSIS - Fixed width flat file mapping tool/technique

Two options come to mind, both are features of the can't-say-enough-good-things-about-this-free-tool BIDS Helper.

Create Fixed Width Columns

The first is the Create Fixed Width Columns feature. It's as simple as Create a Flat File Manager pointing at the correct file and define 1 column.

Once that's done, then you right click back on the Connection Manager and use the context sensitive feature of "Create Fixed Width Columns..."

In the next window, you paste the tab delimited list (Excel) into the editor

Oh yeah, it's that's simple. If you need to do some tweaking for column types and such, then you go in and change it as normal with edit.

Biml

I like the Business Intelligence Markup Language for my SSIS development. There's lots of benefit to it but at a most basic level, use it for things like godawful flat files, especially when they provide no header rows (I'm looking at you, you mainframes).

It has often been my experience when dealing with legacy systems, that they have their file definitions in COBOL copybooks. Those devs will send me Excel files with the column layout like

DATABASE FIELD NAME    START LOC   LENGTH
SEND.DT                 1          STRING(08)
SEND.TIME               9          STRING(08)
DT                     17          STRING(08)
TERM                   25          STRING(04)
%ZONE                  29          STRING(01)

It ain't fancy, but Excel formulas made short work of it. So I'd create 4 new columns based on the above.

Sanitized Name (removed invalid characters for column names)

=SUBSTITUTE(SUBSTITUTE(B3, ".", "_"), "%", "PCT_")

Length (extracted the numbers, note this still has a leading 0 but it doesn't hurt)

=MID(E3,LEN("STRING(")+1,LEN(E3)-LEN("STRING(")-1)

XML (this builds out the actual XML for a flat file definition)

=CONCATENATE("<Column Name=""",F3,""" Length=""",G3,"""  DataType=""AnsiString""  ColumnType=""FixedWidth""  CodePage=""1252"" />")

DDL (Might as well build the target table while I'm at it)

=CONCATENATE(",   ", F3, " varchar(", G3, ")")

The first column of the DDL gets the lead comma dropped and then wrapped with CREATE TABLE dbo.FOO()

Ultimately, that XML results in something that gets slapped into a FlatFileFormat tag and then it's a simple matter of creating the Connections collection and finally the package itself. Looks a lot but it's actually pretty simple, once you have a pattern.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

    <FileFormats>
        <FlatFileFormat
            Name="FFF Pickup"
            CodePage="1252"
            RowDelimiter="CRLF"
            IsUnicode="false"
            FlatFileType="RaggedRight">
            <Columns>
                <Column Name="SEND_DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="SEND_TIME" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="TERM" Length="04"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <!--
                ad nauseum
                -->
                <Column Name="RPRTD_PU_PCS" Length="5"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

    <Connections>
        <FlatFileConnection
            Name="FF Pickup"
            FileFormat="FFF Pickup"
            FilePath="C:\ssisdata\Operations\Input\Pickup Report Pickups.txt"
            CreateInProject="false"
        />
    </Connections>

<Packages>
    <Package Name="PickupLoad" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
        <Tasks>
            <Dataflow Name="DFT Load Pickups" >
                <Transformations>
                    <FlatFileSource
                        Name="OLE_SRC Pickup"
                        ConnectionName="FF Pickup"
                        RetainNulls="true">
                    </FlatFileSource>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>

Oh and a similar question from SO with a disturbingly similar answer. Wish I had looked at Skinner's comments before I rewrote this answer.


How do i increase the total MyISAM indexes?

Your key_buffer_size is way too big.

Since your MyISAM indexes is that small, set it to 128K.

You need to open my.cnf and add this under the [mysqld] group header

[mysqld]
key_buffer_size = 128K;

You don't need to restart mysql. Just run this

SET GLOBAL key_buffer_size = 1024 * 128;

Give it a Try !!!

Based on the mysqltuner output, you can drop the innodb_buffer_pool_size to 3G.

For more clarification on sizing MyISAM and InnoDB caches, see my old post What are the main differences between InnoDB and MyISAM? on how to compute the ideal sizes for both MyISAM and InnoDB.


I have 10 tables in schema icmdb2admin each table has one column named pdfname i need to update the values in the pdfname column with L1.pdfname.V1

You can retrieve the list of tables that have one column name pdfname by using the information_schema.columns view. It makes for clearer code.

Let's assume this is your (very simplified) scenario:

CREATE SCHEMA icmdb2admin ;
SET search_path = icmdb2admin ;

CREATE TABLE t1 (pdfname varchar(100)) ;
CREATE TABLE t2 (pdfname varchar(100)) ;
CREATE TABLE "with an awful name"(pdfname varchar(100)) ;
CREATE TABLE "no no"(i integer);

INSERT INTO t1 VALUES('hi'),('you');
INSERT INTO t2 VALUES('ha'),('you');
INSERT INTO "with an awful name" VALUES('he'),('you');

You get the list of the tables you want by means of the following query:

SELECT
    table_name
FROM
    information_schema.columns c
WHERE
    column_name = 'pdfname'
    AND table_schema = 'icmdb2admin' ;

That will return

| table_name         |
| :----------------- |
| t2                 |
| with an awful name |
| t1                 |

You then will use this information to EXECUTE dynamic command using PLPGSQL with any of the table names retrieved from the previous query:

DO
$$
BEGIN
    EXECUTE format('
        UPDATE
             "icmdb2admin"."%s"
        SET
             pdfname = ''L1.''|| pdfname || ''.V1''
        ',
        't1') ;
END;
$$

That will change (only) table t1.

NOTE: The format function allows for better readability, and adequate type formatting and prevention of SQL injection than plain concatenation.

At this point, to update all the concerned tables, you would use a loop, in a fashion similar to the one you already devised:

DO
$$
DECLARE
    tbl character varying ; -- Not the whole record, only what you're interested in
BEGIN
    for tbl IN
        SELECT
            table_name
        FROM
            information_schema.columns
        WHERE
            column_name = 'pdfname'
            AND table_schema = 'icmdb2admin'
            /* AND whatever else you need */
    loop
        -- raise notice '%', tbl ;  -- debugging tool
        EXECUTE format('
            UPDATE
                 "icmdb2admin"."%s"
            SET
                 pdfname = ''L1.''|| pdfname || ''.V1''
            ',
            tbl) ;
    end loop ;
END ;
$$

And your tables will now have:

SELECT * FROM t1;

| pdfname         |
| :-------------- |
| L1.L1.hi.V1.V1  |
| L1.L1.you.V1.V1 |

SELECT * FROM t2;

| pdfname   |
| :-------- |
| L1.ha.V1  |
| L1.you.V1 |

SELECT * FROM "with an awful name";

| pdfname   |
| :-------- |
| L1.he.V1  |
| L1.you.V1 |

You can check a step-by-step example (with some limitations due to restrictions on dbfidle at dbfiddle here

NOTE: table t1 has been update twice in this example, on purpose. To avoid this possible problems, you could probably rewrite the UPDATEs adding a WHERE NOT pdfname LIKE 'L1.%.V1' to prevent double updates during testing.

References in PostgreSQL documentation:

EXECUTE
format
information_schema.columns


If there's a where clause in the DELETE statement, then the server still needs to perform a SELECT internally to the DELETE statement in order to delete only the specified records.

You may be only issuing one DELETE statement, but that's a convenience of SQL. The Database software has to process that statement and perform the necessary logic to do the job under the hood.  This is one of those things it does that you don't normally think about until you happen to notice it, as you did now.


Data warehouse: Can I tell any details from Source System on delays or blocks?

Okay, I am going to assume that we are talking about ETL operations for loading data into your data warehouse. One great option that you have available to you since you are using SQL Server 2012 is creating a SSIS Catalog and migrate your ETL jobs to be hosted there. This offers you tons of reporting and performance counters that you can use to track and troubleshoot various ETL issues.

After you identify a baseline it really is performance tuning on your long running ETL's, SQL Server Data Tools shows performance statistics and you can manually run them to see where it it taking the most of your time. Start with the query to the source system and make sure it is tuned properly, then check your lookups and inserts and updates. There are many ways to increase performance of your ETL's. Basically ask for the minimum amount to get done what you need done, make sure you are doing set based operations for your updates as opposed to row based.

There is much more performance tuning tips depending on what you find by digging into your ETL's.


Does running a COUNT() statement using NOCOUNT ON; consume much

NOCOUNT does not mean "don't count" - it just means "don't report the count." Reporting can result in a lot of unnecessary chatter between server and client (which is usually ignored), and can incorrectly be interpreted as a result set by some APIs. At my previous jobs one of our coding policies was that SET NOCOUNT ON was required in every procedure, and I still promote it as a best practice. There are some APIs that rely on this message, however, and in fact your own .NET code may be relying on .RecordsAffected today.

As for the count, you can use sys.dm_db_partition_stats but be aware that this does not guarantee 100% accuracy - like taking a direct count with NOLOCK, it will not account for transactions in progress.

I also suggest a couple of improvements on @garik's answer:

SELECT
    sch = OBJECT_SCHEMA_NAME(i),
    obj = OBJECT_NAME(i),
    [RowCount] = rc
FROM
(
    SELECT
        i = [object_id],
        rc = SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0,1)
    GROUP BY [object_id]
) AS x
ORDER BY [RowCount] DESC;

This will take partitioning into account, includes the schema as well as object name, and identifies heaps and clustered indexes explicitly (who knows when Microsoft will start giving hypothetical indexes or other system-defined indexes negative index_id values).

EDIT:

As for the question about counting on a primary key, you will see no difference in performance between COUNT(*) and COUNT(key_column) - if you inspect the plans, they should be identical. Be careful, though, about using COUNT(nullable_column) - personally I think COUNT(*) is safer. That said, for millions of rows, it is quite rarely a realistic requirement to get a 100% accurate count. Retrieving the row counts from the DMV above should be more than adequate and doesn't have a chance of impacting the table itself (or being slowed down by operations on the table).


Can't connect to ODBC database with pyodbc. Which SQL Server driver should I install on the Raspberry Pi?

I believe you are looking for FreeTDS or for pymssql. From pymssql docs:

  A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.


phpMyAdmin and mysql not showing the same database

The database server that you are accessing using phpmyadmin and the mysql monitor (command line client) are different. If you look at the versions, phpmyadmin is accessing a 5.5.1-m2-community version, while you are accessing 5.6.17-community on the command line.

Either you are accessing a remote server or you have two separate installations of mysql on the server. If the later, use netstat -aon | more or the Task Manager to locate the mysql processes running on your machine (and on which port they are listening).


The documentation surprisigly contains a solution:

SELECT timestamp(current_date);

(which is basically the same as a comment above).


The SQL Server knows only about the column content if you're creating a index or a statistic for this column. If the companyID is always populated with 1 you never want to create an index for it - not even as last part of a multi column index. The recommended indices are sometimes stupid and should be considered as guess.


DB2 9.7 LUW - How do I SET SESSION_USER when I am DBADM,SECADM?

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user


Changing a column's data type fills the transaction log

the very first thing you have to do is to change AUTOSHRINK to false. There is absolutely no reason to ever have it true. See AUTOSHRINK: Turn it OFF!.

Both operations you are attempting require a size-of-data update in a single transaction. A single transaction requires that much log, irrelevant of the recovery model. You must increase the log size to accommodate your transaction.


SQL Server table size increased with column data type change

We need more info. Stuff like:

Is the table clustered or a heap?

What indexes do you have?

How did you do the change? ALTER TABLE ALTER COLUMN, or use some GUI?

If you used ALTER TABLE, can you post those commands?

If you used some GUI, what SQL did it generate?

For instance, I did a test. I had a heap, and used ALTER TABLE ALTER COLUMN. Evidently, the old data was still there for the tinyint -> bit change. This means that the row lengh increased, and subsequently more space was needed. Also, since it was a heap, forwarding pointers were left at the old row locations, for the rows that were moved. Why was the old data still there? One would have to ask MS. Sometimes they are clever, and for instance delay a change until you actually modify data, so it can do it as a a meta-data only change.

The smallint -> tinyint didn't change number of bytes/pages. This was indeed done as a meta-data only change.

So, in order to examine your case, we need a repro from you, with your table structure including stuff like I mentioned above (heap or clustered table, other indexes etc).

For the record, since my table was a heap, I could reclaim the space by doing an ALTER TABLE REBUILD. Probably easier and faster than creating a new table.

Here's the script I used, with some comments. In case you want to dig deeper.

USE tempdb

SET STATISTICS IO ON

DROP TABLE IF EXISTS x1

SELECT TOP(100000)
 CAST(0 AS tinyint) AS a
,CAST(0 AS tinyint) AS b
,CAST(0 AS tinyint) AS c
,CAST(0 AS tinyint) AS d
,CAST(0 AS tinyint) AS e
,CAST(0 AS tinyint) AS f
,CAST(0 AS tinyint) AS g
,CAST(0 AS tinyint) AS h
,CAST(0 AS tinyint) AS i
,CAST(0 AS smallint) AS j
INTO x1
FROM sys.columns AS a, sys.columns AS b

--Verify data types
EXEC sp_help x1

--Below proc can be found at my web-site, https://karaszi.com/spindexinfo-enhanced-index-information-procedure
EXEC sp_indexinfo 'x1' --266 pages

ALTER TABLE x1 ALTER COLUMN a bit
EXEC sp_indexinfo 'x1' --306 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN b bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above

ALTER TABLE x1 ALTER COLUMN c bit
ALTER TABLE x1 ALTER COLUMN d bit
ALTER TABLE x1 ALTER COLUMN e bit
ALTER TABLE x1 ALTER COLUMN f bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above

ALTER TABLE x1 ALTER COLUMN g bit
EXEC sp_indexinfo 'x1' --346 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN h bit
EXEC sp_indexinfo 'x1' --346 pages

ALTER TABLE x1 ALTER COLUMN i bit
EXEC sp_indexinfo 'x1' --378 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN j tinyint
EXEC sp_indexinfo 'x1' --378 pages, above was meta-data only operation

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('x1'), NULL, NULL, 'DETAILED')
--Pages are full

--Lets look at a row
DBCC TRACEON(3604)

SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('x1'), null, null, 'DETAILED') AS a
WHERE page_type_desc = 'DATA_PAGE'
--Grab some page number from above, the column allocated_page_page_id

--Check out the page
--Note all dropped columns, they are still there
DBCC PAGE('tempdb', 1, 268, 3)
--Also, we got forwarded records because of the row length increased

--Rebuild the data
ALTER TABLE x1 REBUILD
EXEC sp_indexinfo 'x1' --170 pages, data reclaimed
DBCC PAGE('tempdb', 1, 544, 3)


Clarify - Need to add Multiple foreign key constraints

Instead of one column per position in TeamList, you can use lookup tables for Positions and Players. The TeamList table then defines which player is in which team and in what position for each round. You then don't need to enforce integrity on all players being from the same team because there is one row per player that defines the player:team relationship in TeamList (playerID and teamID).

I've included some DDL below as an example - you may need to add more data columns,etc. but it should be a good starting point.

Positions table:

 CREATE TABLE [dbo].[Positions]
    (
        [PositionID] [int] NOT NULL,
        [position_name] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED
    (
        [PositionID] ASC
    )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

The players table can be created as (not including team associations, but you could add this here):

CREATE TABLE [dbo].[Players](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [player_name] [varchar](500) NULL,
PRIMARY KEY CLUSTERED
(
    [playerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

You can then create your TeamList table like so

 CREATE TABLE [dbo].[TeamList]
    (
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [PositionID] int null,
    [PlayerID] int not null,
    CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED
    (
    [TeamListID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Positions] FOREIGN KEY([Position])
    REFERENCES [dbo].[Positions] ([PositionID])
    GO

    ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Positions]
    GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[Players] ([playerID])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Players]
GO

-- Original FK definitions on round, team, coach, etc. go here


Biztalk is compatible with ALWAYS ON from SQL Sever 2016: https://docs.microsoft.com/en-us/biztalk/core/high-availability-using-sql-server-always-on-availability-groups
You have here a brief introduction and a setup of Microsoft Distributed Transaction (MS DTS). Microsoft says that is available starting from SQL Server 2016 Service Pack 2.
I found a link that is talking about best practice and is mentioning Biztalk which is exaclty your scenario but.... it doesn't give any example about how to setup MS DTS with Bitztalk.


How do I clear a table from SQL Server's cache?

There is no way of doing this.

DBCC DROPCLEANBUFFERS doesn't accept any parameters for a specific database or object. Internally SQL Server can do this at a database level however as when a database is AUTO_CLOSEd all corresponding pages are removed from the buffer cache.

Also internally SQL Server can mark certain pages such that they will be the first ones kicked out by the lazy writer. This is used by DMVs such as sys.dm_db_index_physical_stats to avoid flushing the buffer pool as alluded to in this article but this functionality is not exposed in any way to us (even though it might be useful to be able to specify the same if doing a one off scan of a large table for example).


Postgresql synchronous_commit off and select queries

Have the sync program update a counter or timestamp in some dummy table and commit the update:

begin;
select * from async_inserted;
-- maybe do something in the external system here
update dummy set synctime=now();
commit;
-- or maybe do something external here instead

Of course if the update or commit fails, that could still leave you not knowing what state the external system should be in.  You might need to use prepared transactions (aka "2 phase commit") to bond the two systems together atomically.


I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

Set your transaction log large enough so it will not grow during normal operation.  All update activity stops in the database when the logs grows, so you want to minimize log growths.
For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS.  You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process.  While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged.  At some point, there was activity that caused it to grow to that size (index rebuild, etc.).  There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size


Practical Aspects of Making Changes to Databases

A really important thing to remember about SQL databases is that architecture is king, it governs how difficult everything else is, from querying to modifying its structures later. Business requirements will sometimes force you to avoid what is traditionally best practice or break a particular design pattern. It's okay that it happens, but certain patterns can make your life a lot easier.

For the most part we rely on using traditional development tools to manage our database design manually. I personally work almost exclusively in Visual Studio and SQL Server Management Studio for MS SQL Server. We use VS to manage our SQL Server project within our larger product code base, utilize our TFS Source Control, and use the comparison tools to generate patch scripts. I do most of my scratch pad development, testing, and management of our local development DBs via SSMS. For 3rd party tools I prefer Redgate's SQL Prompt and SQL Search, not particularly cheap though. They have some other cool toys, but those are the only two I use every single day; to the point that I feel sluggish without SQL Prompt. Though the latest version of SSMS seems to have improved its intelli-sense to almost the same level of SQL Prompt.

Adding to a database structure (especially in a well designed relational model) is almost always easier than modifying or removing something from said structure. Creating new tables and relationships doesn't pose a risk to any of your existing data or queries since it's all additive. Worst case you'll have to populate some of those new tables with existing data. On the other hand modifying or deleting existing columns and tables may require you to migrate data or modify queries to maintain data integrity. It'll be tempting to be lazy about this sometimes, don't be! You'll thank yourself later.

I personally believe good database design is a matter of keeping things relatively simple and adapting when necessary: using Foreign Keys, naming columns after their FK target field, maintaining constraints, indexing high volume tables, relying heavily on INNER JOIN relations, and avoiding 'negative' logic (NULLs, NOTs, implicit relationships, ORs, etc...) when possible can make your life a lot easier to maintain your database over time and will get you most of the way through most projects. We easily spend 25-50% of our time undoing 'bad' design decisions or lazy workarounds from previous and less experience developers.

Unfortunately this is only a very high level view, we could talk about specific topics for days. I would definitely recommend poking around various blogs and picking up a book or two to get a grasp of some of the things I've mentioned above.


The big difference in the serial plan that I can see is this expensive TOP operator that has been introduced by the optimizer:

The TOP is probably there due to row goals, there is a 4-part series on this at SQLPerformance.com:

Setting and Identifying Row Goals in Execution Plans
Row Goals, Part 2: Semi Joins
Row Goals, Part 3: Anti Joins
Row Goals, Part 4: The Anti Join Anti Pattern

(Part 1 and maybe part 2 are relevant for this query.)

I can't confirm for sure in your case because you're using 2016 and the showplan properties that expose row goal information weren't added until 2017 CU3 (and were added in 2016 SP2, just released this week). You can see them if you show the output from running the original, serial query on the server adding the following (taken from part 1 of the series above):

OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612);

Estimates are different all over the place, so that tells me that data and/or stats are different, but probably mostly stats since local and server both give the same actual rows.

I suggest a rewrite, changing IN to EXISTS, simplifying the subquery (a lot of redundant work there), and removing the GROUP BY (which is unnecessary busy work, and may be the culprit here):

SELECT p.id_prov,
       p.name,
       ps.val
FROM dbo.provider_payment pp
INNER JOIN dbo.provider p
  ON p.id_prov = pp.id_prov
INNER JOIN dbo.payment_settings ps
  ON ps.id_prov_payment = pp.id_prov_payment
WHERE pp.payment_method = 'PROTX'
  AND ps.setting = 'VendorName'
  AND p.is_deleted = 0
  AND p.status = 'Y'
  AND EXISTS
  (
    SELECT 1
      FROM dbo.deposit_payment dp
      WHERE dp.id_prov = p.id_prov
        AND dp.payment_method = pp.payment_method
        AND dp.create_date > '2018-01-01'
  );

Updating stats can't hurt, but let us know if the rewrite helps. You could also try hinting the newer cardinality estimator using OPTION (QUERYTRACEON 2312); and in case you are hit by anything that has since been fixed in the optimizer you can also try 4199.

Leaving the local variance out, the two plans on the server have the same predicate on the scan of deposit_payment, except that the parallel plan uses a probe for id_prov. So I really don't think the predicate has anything to do with any performance differences:

The only difference between server and local is that the local serial plan handles the id_prov filter later, in a hash match. Exercise to the reader to determine if that's better or worse. :-)

I would focus on writing simpler queries without unnecessary and/or redundant logic (and perhaps introducing a supporting index on deposit_payment to help this type of query without a scan/predicate) rather than trying to discover bugs. :-)


How can i replicate many database into one, by avoid primary key collision?

I have a data warehouse that multiple sources of data feed into. I generate a new PK, but bring in the original PK from the source along with it. With a lot of the feeds I also bring in a 'source key' that's typically an environment variable or something to identify the actual source, and then create a composite key to differentiate between original PKs that may be the same and from different sources. In my aggregations this data is stored, and update a dimension to keep a record of these sources, so I can query based on that table.

I read a Brent Ozar post a while back of a technique to alter the seed for one lot to subtract from 0 so you end up with negative integers. For more than 2 you could expand it with number patterns (odd/even, multiples, ending-in). It wasn't so much for this scenario, as you need a lot of foresight to choose which pattern to implement based on the number of sources you think you may have.


SQL Server hitting System.OutOfMemoryException but server has 10GB free

That exception usually doesn't refer to the SQL Server itself running out of memory, but instead the client workstation that is processing the results running out of memory.

You will generally see this if you are outputting a very large result set into the Output window.

To troubleshoot the issue (and possibly resolve it) allow SQL to write the results to a file rather than trying to put it all in the output window.


... is being backed up to the data drive as of now.

So, what happens when you lose a node? What would happen if that server crashed and burned or you lost the data drive? Now you've lost the database backups...

  My question is does this 2 node setup require 2 backup drives? One for secondary and one for primary? Please clarify

The best option would be to have an SMB 3.x share to which these backups are sent. You could also tie into a 3rd party backup software like Commvault, netbackup, etc., by writing your backup process to hit local disk then copy to the 3rd party service/appliance.

The reason is that the AG could be primary on, potentially, any node. If we're backing up to local disk, the backups could be spread across many nodes. This means we might not be able to have an entire backup chain when it comes time for a disaster.


Database structure for bonus system in MySQL

You have to make complex primary index that refers to the other tables:

CREATE TABLE `bonuses` (
    `c_id` INT(10) UNSIGNED NOT NULL,
    `s_id` INT(10) UNSIGNED NOT NULL,
    `bonus` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`c_id`, `s_id`),
    CONSTRAINT `FK_sellers` FOREIGN KEY (`s_id`) REFERENCES `sellers` (`id`),
    CONSTRAINT `FK_customers` FOREIGN KEY (`c_id`) REFERENCES `customers` (`id`)
)
ENGINE=InnoDB;

Primary keys are always unique thus you can't create the pair seller-customer that already exists. Foreign keys with restrictions prevents creation of pair with nonexistent seller/customer/both.