Set-ClusterParameter : Parameter 'OverrideAddressMatch' does not exist on the cluster object while configuring failover cluster resources

I have to reiterate that you're not doing this on a valid IP Address resource. I have no problem getting these values from any of my clustered IP resources. Tried on Windows Server 2012R2 and Windows Server 2016, both show values just fine.

Double check your input is a valid IP Address resource type and double check it holds a resource that exists in the current context.

Windows Server 2012R2

Windows Server 2016


The strong general advice is to never shrink of course.
Still, the question here is: Can shrinking ever improve performance?
Well, maybe. Consolidating all the used pages at the start of each file might have benefits for ramp-up reads, and have favourable cache effects at multiple levels, including at the storage layer. In addition, shrink will remove any empty pages from heap tables rather than move them, so that might help heap table scan performance directly.
Shrink also checks if a b-tree or heap page should be recompressed, which might be relevant if data compression is in use. Shrink also never generates forwarding pointers, so shrinking heap pages can result in "unforwarding" and stub removal.
It would still be better to take more effective action to address whatever the specific cause of poor performance is. That requires understanding the issue and knowing how to fix it, rather than (blindly) trying things that appeared to work before.


SQLite only supports some datatypes, and DATE is not one of them (https://sqlite.org/datatype3.html).

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE `Checks` (`customerId` INTEGER NOT NULL NOT NULL, `checkDate` DATE);
sqlite> insert into Checks values (1,1);
sqlite> insert into Checks values (2,'08-09-2019');
sqlite> insert into Checks values (3,'2019-09-08');
sqlite> insert into Checks values (4,'eight september 2019');
sqlite> insert into Checks values (5,'there is no checking if this really is a date!');
sqlite> select * from checks;
1|1
2|08-09-2019
3|2019-09-08
4|eight september 2019
5|there is no checking if this really is a date!
sqlite>


As the name of the "action" indicates, you are looking at the execution stack of a nested series of calls in a single Session. As you noted, one object calls another which calls another. As each successive module is executed, the calling module is put on hold and pushed down the "stack", with the current / active module being on top (level 1). When the top level module completes, if there are any calling modules in the stack (level > 1) then they all move up by one and what was at level 2 is now at level 1 and is active again, continuing where it left off when it called the sub-module.

Level 1 is what you see in sys.dm_exec_requests and other DMVs. You cannot see Levels above 1 (at least as far as I have ever seen).

What exists at each level is the query batch (one or more T-SQL statements encapsulated in either a module -- Stored Procedure, Triggers, not sure if Multi-statement Table-Valued Functions and Scalar UDFs would get their own level -- or submitted as ad hoc / Dynamic SQL). The handle (i.e. "sql_handle") attribute refers to the query batch (all statements within the batch). The line attribute should be the starting line number of the current executing statement within the batch. The offsetStart and offsetEnd attributes should be the starting and ending byte positions of the current executing statement within the batch. Because the offsets are expressed as byte positions, you need to divide by 2 in order to get the character positions (if you want to do something like SUBSTRING the batch to extract that particular statement). I am not sure if a value of -1 ever shows up here for offsetEnd, but it does show up in sys.dm_exec_requests and indicates "end of the batch".


Moving database file locations in SQL Server 2008

It's likely that all you need to do is issue the following command for each user database that was moved:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

I grabbed that line of code from these instructions published by Microsoft. So read those too.


You can do this sort of thing by creating what are essentially property bags of these values.   This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case.

This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and cons in more detail

CREATE TABLE Users
(
    UserID int identity(1, 1) primary key clustered
    ,UserName varchar(200)
);

CREATE TABLE PreferenceType
(
    PreferenceTypeID int identity(1, 1) primary key clustered
    ,PreferenceName varchar(200)
);

CREATE TABLE PreferenceValue
(
    PreferenceValueID int identity(1, 1) primary key clustered
    ,PreferenceValueName varchar(200)
    ,PreferenceTypeID int
);

CREATE TABLE UserPreference
(
    UserPreferenceID int identity(1, 1) primary key clustered
    ,UserID int
    ,PreferenceValueID int
);

ALTER TABLE PreferenceValue
ADD CONSTRAINT  FKValue_Type FOREIGN KEY (PreferenceTypeID) REFERENCES PreferenceType(PreferenceTypeID);

ALTER TABLE UserPreference
ADD CONSTRAINT  FK_UserPreference_User FOREIGN KEY (UserID) REFERENCES Users(UserID);

ALTER TABLE UserPreference
ADD CONSTRAINT  FK_UserPreference_Value FOREIGN KEY (PreferenceValueID) REFERENCES PreferenceValue(PreferenceValueID);

insert into Users (UserName)
Values ('User1' ) --tblUserID = 1
, ('User2' );   --tblUserID = 2

Insert into PreferenceType  (PreferenceName)
Values ('Religion') --Type1
, ('Smoker'); --Type 2

Insert into PreferenceValue (PreferenceValueName, PreferenceTypeID)
Values ('Christian', 1) --Value 1
, ('Muslim', 1) --Value 2
, ('Non Smoker', 2)  --Value 3
, ('Smoker' , 2); -- Value 4

--User 1 is a Christian Smoker
--User 2 is a Muslim Non-Smoker
Insert into UserPreference (UserID, PreferenceValueID)
Values (1, 1)
, (1, 4)
, (2, 2)
, (2, 3);

Visually:

So that when you want to get the information for User 1 you would run this select:

select u.UserID, u.UserName, pv.PreferenceValueName, pt.PreferenceName
From Users u
join UserPreference up on u.UserID = up.UserID
join PreferenceValue pv on up.PreferenceValueID = pv.PreferenceValueID
join PreferenceType pt on pv.PreferenceTypeID = pt.PreferenceTypeID
Where u.UserName = 'User1';

You would get the following results in the format of

UserID, UserName, PreferenceValue, PreferenceType

1, User1, Christian, Religion

1, User1, Smoker, Smoker

To include Date values within a schema similar to this, playing off of the tables that were already created but adding two new ones, you could do something like the following:

CREATE TABLE DateType
(
    DateTypeID int identity(1, 1) primary key clustered
    ,DateTypeName varchar(200)
);

CREATE TABLE UserDateType
(
    UserDateTypeID int identity(1, 1) primary key clustered
    ,UserID int
    ,DateTypeID int
    ,DateValue datetime
);

CREATE UNIQUE NONCLUSTERED INDEX
    uq_UserDateType on UserDateType (UserID, DateTypeID);  --Optional. To only allow one row per type.

ALTER TABLE UserDateType
ADD CONSTRAINT  FKDateType FOREIGN KEY (DateTypeID) REFERENCES DateType(DateTypeID);

ALTER TABLE UserDateType
ADD CONSTRAINT  FKUserDate_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID);

Insert into DateType (DateTypeName)
Values ('BirthDate') --Type1
, ('Anniversary'); --Type 2

Insert into UserDateType (UserID, DateTypeID, DateValue)
Values (1, 1, '01/01/1980') --User 1 has a Birthday of 01/01/1980
, (1, 2, '01/01/2000')  --User 1 has an Anniversary of 01/01/2000
, (2, 1, '02/01/1995');  --User 2 has an Birthday of 01/01/2000

By having these kinds of tables you can dynamically add the new date types and relate them to a user in this way.

As mentioned above, I am not sure that this is the ideal approach, as it certainly has its flaws, but this is a way that you can do this sort of thing with Date values.


Why will my ALTER and UPDATE not work in the same query in this specific instance?

Do you have the option to create the new columns outside of this stored procedure via change control?

Alternatively, you could use default constraints if your business requirements allow the columns to be not null:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table1_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NOT NULL CONSTRAINT df_app_table1_Discount DEFAULT (100);

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table2_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NOT NULL CONSTRAINT df_app_table2_Discount DEFAULT (100);
GO

If your columns must be nullable, then use the WITH VALUES clause:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NULL CONSTRAINT df_app_table1_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NULL CONSTRAINT df_app_table1_Discount DEFAULT (100) WITH VALUES;

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NULL CONSTRAINT df_app_table2_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NULL CONSTRAINT df_app_table2_Discount DEFAULT (100) WITH VALUES;
GO

Lastly, you could use sp_executesql to perform the updates. You are not introducing a SQL injection risk as the update values are not parameterised.


mysql client auto reconnect in case of ERROR 2013

Encapsulate the query in a stored procedure.  In the SP, test for errors.  When one happens, reconnect and retry the query.
In general, autoreconnect is not wise.  This is because you might crash in the middle of a multi-statement transaction.  If you don't notice the crash, it could start a new transaction in the middle.
So, depending on how critical the data is, be careful.


Do foreign keys affect performance in Oracle?

Yes.

The optimizer can remove redundant tables from a query when RI is enforced in the database.

For example, here are two tables:

create table t1 (
  t1_id int not null primary key
);

create table t2 (
  t2_id int not null primary key,
  t1_id int not null
);

The second only contains t1_ids from the first:

insert into t1
  select level from dual
  connect by level <= 100;

insert into t2
  select rownum, t1_id
  from   t1, (
    select * from dual connect by level <= 10
  );

commit;

So a count of T2 returns the same number of rows as a join of the two tables:

select count(*) from t2;

COUNT(*)
      1000

select count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

But there's no FK defined :(

So the optimizer doesn't know this. And it needs to access both tables when executing the query:

set serveroutput off

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

select *
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8p235qbxm8yn0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on
t1.t1_id = t2.t1_id

Plan hash value: 3484656271

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |      1 |00:00:00.01 |      10 |
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS      |              |      1 |   1000 |   1000 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL| T2           |      1 |   1000 |   1000 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014412 |   1000 |      1 |   1000 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------

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

   4 - access("T1"."T1_ID"="T2"."T1_ID")

But add a foreign key to the mix:

alter table t2 add constraint fk foreign key ( t1_id ) references t1 ( t1_id );

And it now knows there can't be any t1_id values in t2 that don't exist in t1. So it can ignore t1:

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

select *
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8p235qbxm8yn0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on
t1.t1_id = t2.t1_id

Plan hash value: 476902662

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |   1000 |   1000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------

Poof! It's gone! :)


Master-Master Replication: Missing PK Values

Aside from @mustaccio's link, in general, mysql will guarantee auto_increments will be unique aand monotonically increasing, but for several reasons it cannot guarantee there is no gaps.

One reason is distributelly generated ones like in your case, but it can also happen with 2 concurrent write threads on the same server- so your applications should always avoid assuming anything about concrete id growth. Use last_insert_id() and ignore gaps.


Displaying null fields as 0 in mySQL select

It is unclear which of two problems you are asking about.  I will address both.
The question of missing days requires another table:
SELECT ...
    FROM table_of_days AS d         -- pre-construct a big table of lots of days
    LEFT JOIN your_table  USING(dy)
    WHERE d.dy BETWEEN ... AND ...   -- the range of days desired for this SELECT

(This is one place where MariaDB shines -- it has a seq_0_to_1000 pseudo-table from which you can easily build a list of dates.)
The LEFT JOIN will provide NULLs for days with missing info.  Then...
Here are two ways to turn NULL into 0 (there are others):
mysql> SELECT IFNULL(x, 0), COALESCE(x, 0)  FROM (SELECT null AS x UNION SELECT 123) z;
+--------------+----------------+
| IFNULL(x, 0) | COALESCE(x, 0) |
+--------------+----------------+
|            0 |              0 |
|          123 |            123 |
+--------------+----------------+

In your case, COUNT(*) provides 0; don't use COUNT(day) unless you want NULL.  In general COUNT(*) counts the number of rows, while COUNT(foo) counts the number of rows WHERE foo IS NOT NULL.  However, this is an exception; see AndriyM's Comment.


Error 1236 From Master After Restored Replication

This is a guess, but please note that the option server-id cannot be activated in a hot way. In my experience, this is a common cause of problems when setting up the replication for the first time, specially because the master accepts the following syntax without complaining:

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

And the server id seems to have taken effect:

mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.02 sec)

But it doesn't work, (it does not have effect until restart and read from the my.cnf or passed as a parameter). The slave will complain until the master is restarted.

The official documentation is misleading, while it says it is dynamic in reality it is not (or at least it is not when setup for the first time, or setup with the default, etc.). See this bug for details, mentioning the misleading documentation.

In summary, activation of the binary logs and server id setup requires a server restart, so I always recommend to do it on installation time. Change it on the [mysqld]section of your configuration file and restart the service.


hstore for versioning fields in postgresql

I would personally prefer to have one history table for each "main" table.

There is a ready-made solution for this available for PostgreSQL which implements this history using triggers, see Log Table Changes in PostgreSQL with tablelog by Andreas Scherbaum.


MySQL Continue Handler Problems inside a Function

Before  MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked.

I have explained this kind of behavior and the solution in this post:

Event Scheduler: No data - zero rows fetched, selected, or processed


Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":

Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:

SELECT
    StatMan([SC0])
FROM
(
    SELECT TOP 100 PERCENT
        [Column] AS [SC0]
    FROM [Table] WITH (READUNCOMMITTED)
    ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 16)

Notice the MAXDOP hint there (though there is no way for a user to specify the hint manually). Where sampled statistics are gathered, the internal query uses the TABLESAMPLE clause, which prevents parallelism. The engine also generates a MAXDOP 1 hint on the internal query, which is a bit redundant.

If you ever need to reduce the parallelism, the MAXDOP hint in the internal query can be overridden using Resource Governor (Enterprise only).

SQL Server 2016 adds parallel sampled statistics update.


List of method (I know) to transfer data from one Oracle DB to another

database link
Data Pump (expdp/impdp)
exp/imp
SQL Developer -> Migrate Database
custom script ( eg PERL )
CSV/XML Dump and Import
ETL software (eg Informatica

The largest problem of any of those is "what is supported?".

*Database Link is only "supported" between a "2 major versions".  eg: 10g can talk to 8i but 11g can't.

Data Pump should support a lot larger range.  I don't think expdp was made available until 10g.

Classic Export/Import is limited on what is supported.  Luckily, Oracle Support has a document for "what works with what".  Ask your supervisors to get a copy of Doc ID 132904.1 for you.  YOU MUST HAVE A VALID SUPPORT CONTRACT TO ACCESS THIS DOCUMENT.

SQL Developer might help you out.  You'll need to import a JDBC driver that can talk to 8i.  You may have to contact Oracle Support to get that driver.  (latest is a 10g driver)

custom script can have problem if you cannot load two different drivers simultanously.

CSV/XML concept needs to be carefully thought out.  beware of \n.

ETL Software still needs the appropriate driver.

REMEMBER

10g is the latest that can talk to 8i.
11g is the latest that can talk to 9i.

Oracle Support has all the knowledge of "what works with what".

Update (2019-09-23)

Someone figured out how to import the Oracle 8i JDBC thin driver into an 18c database in order to run queries against Oracle 7.

https://community.oracle.com/thread/4268386


Best Way To Create Time Series from Postgres Table Update History?

You could maintain a history table that has the same structure as the original table plus a timestamp column.

Then create a trigger that copies the old row into that history table before applying the UPDATE. During the INSERT into the history table, the trigger would populate the timestamp column to store the point in time when the change occurred. The UPDATE will then proceed normally on the main table.

Depending on your needs you might also want to store new rows when they are INSERTed or the old rows before they are DELETEd.

This approach typically does not require any changes in the application. To find out old "versions" of your data, you need to query that history table, rather than the real table. Whether that is acceptable I can't tell.


Why does the fill factor in SQL Server default to 0 (100%)?

Just throwing out a few suggestions here as to why Microsoft would do this:

They have geared their default to be best possible OLAP performance (less page reads with a fill factor of 0/100)
They are assuming that INSERTed data will most likely be at the end of the table, making the extra space per page useless
They are assuming that typically UPDATEd data will not lengthen row data very often, causing a page split

This is just guessing here.  The only people that can really answer that question accurately is the SQL Server team themselves.


For anyone else with the issue, I contacted Oracle Support and they gave me the following regex string to add-in and it worked:

.*ORA-0*(600|54|1142|1146)\D.*|[17(302|183)[^\]]*\].*


Which parameters and metrics to look at when CDC based replication is causing high load?

I wasn't able to tune up the Oracle side of parameters, due to having limited access to the database instance, but the database load has seen improvements after stripping down the tables and columns that are being replicated.

1. Determine which tables are getting updated the most.

Try running the extractor for a few hours and let the THL grow. Then, you can count the number of changes logged for each table using a command like:

./bin/thl list | gawk -f thl_summary.awk | sort -n -r

Where thl_summary.awk is:

#!/bin/gawk
# Sum up rows from a `bin/thl list` output
BEGIN {
    OFS="\t"
}
match($0, /- TABLE = (.*)/, a) {
    table=a[1]
}
match($0, /- ROW# = (.*)/, a) {
    rows[table]++
}
END {
    for (k in rows) {
        print rows[k], k
    }
}

2. Replicate only those tables and columns that are necessary.

Looking at the list of tables, ask yourself if you can exclude any of them.

Exclude CLOB columns.

They aren't really supported by synchronous CDC.
Even if you specify a subset of the columns in DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE, each and every change will be logged to the change table1. But by excluding CLOB columns, we can lessen the load of costly writes and reads.

Exclude all tables that are not really needed on the downstream database.

If necessary, add a fallback table with a subset of the columns that you really need, even if it means denormalizing a bit.
The way Tungsten configures CDC, a single update to a source row generates two rows in the corresponding change table2. These extra updates can add up if you have a frequently updated table.

Once you've determined which tables and columns to replicate, you can specify them  in a file named tungsten.tables:

aSubsetOfTheTable<TAB>column1,...,columnN
wholeTable

Then, you need to re-run the setupCDC.sh script with setupCDC.conf:

specific_tables=1

1 https://community.oracle.com/thread/2288254

2 Updated rows will generate a record with operation$='UU' ... and all the old values, and another record with operation$='UN' or 'UL' with new values. (source)


If sysaux hasn't reached it's max physical growth limit, then it is not likely the reason AWR isn't working. AWR can be disabled a number of ways, for good reason. See this link for details:

http://www.dba-oracle.com/t_awr_remove_disable.htm

https://grepora.com/2019/06/05/oracle-disable-awr-and-prevent-to-violate-diagnostic-tuning/#:~:text=Disable%20AWR%20with%20Oracle%20provided%20script&text=To%20install%2C%20run%20the%20package,disable%20the%20AWR%20related%20advisors.

If someone disabled it, you should confirm the reason why before turning it back on. It is an extra cost feature that many systems are not licensed for.


Does a database update force index rebuild regardless of updated field?

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko)

When you update an indexed column, changes must migrate through this architecture as follows:

From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1
From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file)

If you are updating a non-indexed column, the only bottleneck would just be the flushing changes in dirty page over to the .ibd file over the Write I/O threads. Index change buffering would not be involved for non-indexed columns.

If you have MySQL 5.5/5.6, you may want to increase innodb_write_io_threads and innodb_log_buffer_size for increased write performance.


A solution that work in most DBMS is to use a union:

 select loc_a as location, Score, Possible_Score from T
 union all
 select loc_b as location, Score, Possible_Score from T
 union all
 select loc_c as location, Score, Possible_Score from T

Now you can sum that up as:

select location, sum(score), sum(possible_score)
from (
     select loc_a as location, Score, Possible_Score from T
     union all
     select loc_b as location, Score, Possible_Score from T
     union all
     select loc_c as location, Score, Possible_Score from T
) as TT
group by location;

If your DBMS supports LATERAL JOIN you can do something like:

select x.location, sum(x.score), sum(x.possible_score)
from T
cross join lateral (
    values (T.loc_a, T.score, T.possible_score)
         , (T.loc_b, T.score, T.possible_score)
         , (T.loc_c, T.score, T.possible_score)
) X (location, score, possible_score)
group by x.location;

LATERAL makes it possible to reference T in X, so we can use X to normalize location.


If you're using CASE expressions in a SELECT statement, you should be looking at this page in the MySQL docs.  As shown there, you use END to end a CASE expression.

If, on the other hand, you're using CASE as part of a flow control statement (like you might use in a stored procedure to conditionally execute other statements), both databases would use END CASE to end a CASE statement.


how to stop using sql server login credentials in a linked server?

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the event others come across your post with similar questions.
To help make my post clear (especially to anyone coming across it and not reading through your question), delegation is the ability of an intermediary service to hand off client credentials to a secondary service on behalf of the client.  Represented visually, as follows:

In this case Server A is the intermediary service handing off the Client credentials to the secondary service, Server B.  In your case, server_italia would be considered the intermediary server and ORDB1 would be the secondary service.
ACTIVE DIRECTORY (AD) FUNCTIONAL LEVEL
First and foremost, your Domain Functional Level heavily dictates the options available to you.  If your Functionl Level is at any version prior to Windows 2012, delegation between domains is limited to Unconstrained Delegation only.  Prior to 2012, constrained delegation was limited to within a single-domain only and would not have cross-domain capabilities.
If you're running a 2012 Functional Level or later, you have additional options, so it'll depend on your preference for administration, access control, etc.  A 2012 Functional Level does open up the ability to delegate credentials across domains via Unconstrained Delegation, Traditional Constrained Delegation, or Resource-Based Constrained Delegation.  The rest of my answer will assume you're using a 2012+ Functional Level, but if not, the Unconstrained Delegation configuration instructions are the same, regardless the functional level.
COMMON REQUIREMENTS
Regardless the type of delegation you choose (all options will be listed below), all configurations will require the following basic setup for the account you will be using to run the "intermediary" service (e.g. Server A in the image above, but this can apply to any intermediary service such as a Database, a Web Site hosted on IIS, SSIS, a .NET app, etc.).
On the Domain, the account running the service delegating credentials to other services will require the following AD properties be set:

ServicePrincipalNames: This needs to have any SPNs for Server A listed that will be delegating credentials.  In your example, those would look like the following (assuming you're on the EURO domain of the CONTOSO forest): MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433
KerberosEncryptionType: AES256 (and AES128 if you choose to use that encryption type) should be enabled.  This is important later on, but this setting will ensure Kerberos Encrypted tokens can be delegated.

NOTE: The aforementioned properties can be viewed and set via a variety of methods.  The Active Direcotory Users and Computers MMC Snapin is the most common Graphical User Interface you can use to adjust these properties, BUT this interface obfuscates a lot of these properties and is total garbage when it comes to managing Group Managed Service Accounts.  As a result, I suggest you use the ActiveDirectory PowerShell module as it allows you to set these properties explicitly via either the Set-ADUser or Set-ADServiceAccount commands.  Additionally, you can view properties of a user/service account via the Get-ADUser or Get-ADServiceAccount commands.
In addition, all SQL Servers should have a registered SPN.  You can manually create these, but if you nest the service accounts running your SQL Servers into a proper OU, self-registered SPNs will happen automatically.  Instructions on that process can be found here.
TYPES OF DELEGATION
Again, assuming you're using an AD Functional Level of 2012 or later, you have 3 options for performing cross-domain delegation.  I will list them in the easiest to configure/least secure to most frustrating to configure/most secure approaches.  Any of these will work, and how much administration/security exposure you wish to deal with will be completely up to you.
One other tidbit here, is that there is nothing preventing you from configuring an account to use multiple or all types of delegation listed below.  These configurations are not mutually exclusive.  You should do your best to avoid having any account setup under multiple delegation schemes though, as it will likely use the least secure configuration first (section on determining how your accounts are setup is below).
UNCONSTRAINED DELEGATION
Unconstrained Delegation is the easiest configuration to setup, yet least restrictive approach.  This configuration allows the Service Account running Server A to delegate any credential to any secondary service. This is visually represented in the image above with the blue arrows denoting no limitation on inbound client connection or secondary service that credentials can be delegated to.  To configure this type of delegation on the intermediary account, you only need to set the following AD property (in addition to those listed above):

TrustedForDelegation – Set this to True

This used to be all that there was to it, but with July 2019's Windows Security patch, you will need to re-enable delegation across trusts as well, as outlined in that link.  That security patch was really designed to address the serious downside of this setup, which is much better explained in this post: Hunting in Active Directory: Unconstrained Delegation & Forests Trusts.  Again, unless you're using an AD Functional Level prior to 2012, it's not recommended you use this type of delegation.
TRADITIONAL CONSTRAINED DELEGATION
Traditional Constrained Delegation limits the services Server A can delegate permissions to, such that the Service Account running Server A is only allowed to delegate credentials to services that are explicitly listed.  For instance, it can still delegate anyone’s credentials but it can only pass along said credentials to the services that are explicitly listed.  So visually speaking, we’re limiting the “destinations” we can delegate to, as denoted by the yellow arrow:

The AD Properties (in addition to those listed in the Common Requirements section above) you have to configure on the intermediary account are as follows:

msDS-AllowedToDelegateTo - This needs to be a list of all applicable SPNs that this account is allowed to delegate credentials to.  In this example, those would look like the following: MSSQLSvc/ORDB1.usa.contoso.com, MSSQLSvc/ORDB1.usa.contoso.com:1433 (assuming the ORDB1 server is on a USA domain of the CONTOSO forest).

With this configuration, you are limiting delegation to AD KERBEROS tokens only.  If you wish to delegate non-KERBEROS credentials, such as those passed via NTLM etc., you can configure this account to delegate ANY protocol by setting the following property as well:

TrustedToAuthForDelegation – Set this to True

Using the ANY protocol configuration is inherently risky though.  This blog article digs further into these properties and their ramifications, and unless there is a need to set this to true, it's recommended you don't.
From a personal standpoint, we often use Traditional Constrained Delegation because it's easy to setup and relatively secure (unless you decide to use delegation for ANY protocol, then you're setting yourself up for issues, in my opinion).
RESOURCE-BASED CONSTRAINED DELEGATION
Resource-Based Constrained Delegation limits the intermediary services that the secondary service allows delegated credentials from.  This is slightly different from the other configurations in that instead of configuring Server A like in the prior configurations, we will be configuring Server B by listing the services that it will trust delegated credentials from.  This is done without having to make any additional changes to the intermediary account running Server A. So in this scenario, when Server A attempts to pass along Bob’s credentials, Server B will identify that Server A is a “trusted” service that can delegate permissions and authentication will occur without issue.  Represented visually, it would look as follows:

To be absolutely clear here, the intermediary account (e.g. Server A) still requires the properties in the Common Requirements section above be configured.  In addition though, the destination service (i.e. Server A) requires the additional AD Property be set:

PrincipalsAllowedToDelegateToAccount - This needs to have all applicable SPNs listed of those services that are trusted to delegate credentials from.  In this example, those would look like the following: MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433

That’s it… conceptually.  Test this out to be certain, but from the articles I’ve found outlining this approach, this is really all that’s required.  In fact, this MS article runs through the process and lists out the applicable PowerShell commands necessary to set this up so a sample PowerShell program can be run to confirm the double-hop and that Resource-based Constrained Delegation will work for you.  Additionally, this delegation configuration should work across domains without issue (or requirement of a trust, from what I've read).  The downside here is that we have to configure all the “end points” which may be more administration work in the long run depending upon how your setup is.
HOW DO I KNOW WHAT DELEGATION SETTINGS I HAVE CONFIGURED?
In my opinion, identifying what delegation configuration an account is running under is about as clear as mud.  Thankfully
Willem Kasdorp posted an article along with a PowerShell script to show you what your accounts are currently configured with.  If you run this from a computer with the ActiveDirectory PowerShell module installed, you should be good to go.  Hopefully the combination of the types of delegation configurations I mention above and the matrix this script generates will give you a clear look at what you've currently got configured.
Finally, if you want some help with the AD PowerShell commands, this article does a decent-enough job of showing the proper syntax when setting some of the properties I talked about with lists of SPNs, etc.
If you have any questions, please post a comment and I'll try my best to answer promptly.


Nonclustered Index Rebuild Online on Heap Table

My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?

It can be multi threaded, the restriction is ONLY to alter table rebuild command when done with ONLINE option. I would like to quote from Enabling Compression on Heap MSDN Blog

(1) If you use ONLINE option then HEAP rebuild is single threaded. Note, this restriction is not there when you are rebuilding an index which supports multi-threaded ONLINE build.  However, in both cases, OFFLINE build does support multi-threading.

A non-clustered index rebuild can be multi threaded. Please note for index rebuild to use multiple threads the edition should be Enterprise edition or above( if there is such a edition). This is still true from SQL Server 2005 to SQL Server 2019.

For example, rebuilding a heap with three nonclustered indexes online:
ALTER TABLE dbo.Fish REBUILD WITH (ONLINE = ON);

The heap rebuild is serial:

...while the associated nonclustered index rebuilds are parallel:


Is it important to design the database the right way from the beginning?

Possible?  Probably.
Realistic?  Probably not depending on what "without affecting the way my application works" means to you.

As with anything else, if you build a piece of software on shaky foundations, it's likely that if you decide that you need to re-engineer some component down the road, it will be relatively painful and will likely involve changes to other places in your application.  If you build your application with a language or framework that needs to be changed out in the future, you'll likely need to make changes to the way the application works.  If you build an application that doesn't scale, going in to improve performance later will be more difficult will likely involve substantial changes throughout the code base.  If you build an application on a database that isn't properly designed, re-designing it, converting all the data, and propagating the changes to the application will be time consuming.

That doesn't, of course, mean that you shouldn't build anything until you're an expert in everything.  A large part of becoming an expert is making mistakes and learning from them.  If your site gets popular enough and you learn enough about databases to conclude that your initial data model was flawed and that it is worth reworking the entire data model, that probably implies that you've been quite successful and that having to work on a "version 2 architecture" is a good problem to have.  On the other hand, it's very likely worth picking up a book on basic data modeling reading up a bit on normalization as you design your data model to hopefully avoid the most easily avoided pitfalls.


Is the Memory engine adequate for a read intensive/lookup table?

I would not recommend the MEMORY storage engine
REASON #1 : No Redundancy
Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.
REASON #2 : Mild Disk I/O
No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.
Please read past posts on the pros and cons of the MEMORY Storage Engine

May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
Jan 17, 2012 : Mysql Memory table getting many locks
Jan 20, 2012 : How much memory will a MEMORY table take up?

RECOMMENDATION
Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?
Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:
STEP 01) Create the table like this:
CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:
cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf
[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL
service mysql restart

That's it.
Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).
Why not use InnoDB?

The data and index pages would have the data twice in RAM.
Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.


This should help significantly (unless everything is cat 64):

INDEX(cat_id, deleted, published)

If 'they' won't add that index (or let you add it), warm up your resume.


Why are you casting the substring as an int in the first place? The consumer is going to expect that column to output as a string in every other case. So my suggestion: Just remove the cast.

If you only want a value to appear in the resultset when it is a valid int, and you're on < 2012 where TRY_CAST() is not possible, then you can say:

else case when isnumeric(SUBSTRING(l.id, 0, charindex('-', l.id, 0))) = 1
  THEN CAST SUBSTRING(l.id, 0, charindex('-', l.id, 0)) AS int END

Note: ISNUMERIC() is not a perfect validation that it is specifically an integer, but is probably fine here, since you don't really need an int, just a string that is formatted like an int.

Update What you can do now that I understand the requirements better is convert to an int and then back to a string. Since you are on 2014 you can use TRY_CONVERT():

ELSE CONVERT(varchar(12),
  TRY_CONVERT(int, SUBSTRING(l.id, 0, charindex('-', l.id, 0))))
END

You might also consider fixing the design so that you don't have to parse these important bits of information out of bigger pieces of information, or at least move your CASE expression to a computed column or a view so you don't have to put all that logic in all (or any!) of your queries.


You should find that each example results in a LEFT SEMI ANTI JOIN with equal costs.

  The Left Anti Semi Join operator returns each row from the first (top)
  input when there is no matching row in the second (bottom) input. If
  no join predicate exists in the Argument column, each row is a
  matching row.

Out of habit I tend to use the syntax in your second example, which you could argue signals intent better than the others.


Can we tell dbcc to ignore subnormal real values?

The whole purpose of DBCC CHECKDB WITH DATA_PURITY is to check the database for column values that are not valid or are out-of-range.

In sql server 2000, it was possible to store out-of-range values in columns of certain datatypes like Float,real, datetime, etc. But moving from SQL 2005 and up, SQL Server will flag out those out-of-range values as it follows Single-precision floating-point format. Check @PaulWhite's answer here.

From BOL :

  DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

You must fix those errors reported as described in KB923247.


QUESTION #1

What is error in this procedure?

You seem to have some scope confusion on the variables
ANSWER TO QUESTION #1
PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.
SOLUTION : Change the names of the parameters so that they are distinct from the column names
create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  given_AccountStatus_id    int,
    IN  given_AccountStatus       varchar(255),
    IN  given_CreatedOn           datetime,
    IN  given_CreatedBy           varchar(255),
    IN  given_UpdatedOn           datetime,
    IN  given_UpdatedBy           varchar(255),
    IN  given_is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id,  <- Not Needed for UPDATE
        acc.Account_Status      =   given_Account_Status,
        acc.CreatedOn           =   given_CreatedOn,
        acc.CreatedBy           =   given_CreatedBy,
        acc.UpdatedOn           =   given_UpdatedOn,
        acc.UpdatedBy           =   given_UpdatedBy,
        acc.is_active           =   given_is_active
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id, <- Not Needed for DELETE
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    end if;
end

CAVEAT : Please note that I commented out two lines
QUESTION #2

Is there any other ways to implement this procedure?

ANSWER TO QUESTION #2
You could use triggers

Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Apr 06, 2012 : How to speed up audit trigger?

QUESTION #3

How it will effect the performance of the database?

ANSWER TO QUESTION #3
Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 12, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Dec 23, 2011 : How to set up triggers in MySQL for copying a field

EPILOGUE
The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.
Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).


How to make a trigger work after a certain UPDATE

Something like this will suit your needs:

create or replace trigger colors_utrig
after update on colors
for each row
begin
  if :new.tastes_good = 'y'
    then
      -- put your code here
      NULL;
  end if;
end;
/


You say you are using pt-table-checksum 2.0.1. I would recommend updating to 2.1, as there are many improvements in the tool.

Next, let me address your test. You say the slave was not updated after the first or second  commands that you ran. The second command looks to be trying to connect directly to the slave. pt-table-checksum won't report any differences unless the server you're connecting to has slaves.

Also, the --replicate-check-only option will not do any checksumming. (from the docs):

  If specified, pt-table-checksum doesn’t checksum any tables. It checks replicas for differences found by previous checksumming, and then exits.

Your first command doesn't seem to be able to connect to the slave host, which is why it doesn't report any differences. Make sure the user/pass that is connecting to the master can also connect to the slave.

Now, as for your complex setup, you are right to worry about breaking replication. With some slaves replicating only certain tables, you should heed the warning here:

  If the replicas are configured with any filtering options, you should be careful not to checksum any databases or tables that exist on the master and not the replicas.

You can specify which databases you want to checksum with the --databases option, and give a specific list of tables with the --tables option. Alternatively you can use the --ignore-databases and --ignore-tables options to provide a list of databases/tables to not checksum.

This will probably mean you will want separate pt-table-checksum commands based on which slaves you are trying to checksum. You will probably have to use the 'dsn' --recursion-method to accomplish this (I've never done it, personally)

As for load, pt-table-checksum comes with some options to throttle itself. Namely --max-load and --max-lag.

  The tool keeps track of how quickly the server is able to execute the queries, and adjusts the chunks as it learns more about the server’s performance. It uses an exponentially decaying weighted average to keep the chunk size stable, yet remain responsive if the server’s performance changes during checksumming for any reason. This means that the tool will quickly throttle itself if your server becomes heavily loaded during a traffic spike or a background task, for example.


How do I remove duplicate records in a table?

The syntax is just slightly different:

DELETE q
FROM dbo.questions_tags AS q
WHERE EXISTS
(
   SELECT 1
   FROM   dbo.questions_tags AS q1
   WHERE  q1.ctid < q.ctid
   AND    q.question_id = q1.question_id
   AND    q.tag_id = q1.tag_id
);

Personally, I prefer to use a CTE. Then I can easily swap in a SELECT to validate what I am about to delete, and easily change the where clause to validate what I am going to keep:

;WITH q AS
(
  SELECT question_id, tag_id, ctid,
    rn = ROW_NUMBER() OVER (PARTITION BY question_id, tag_id ORDER BY ctid)
  FROM dbo.question_tags
)
--DELETE q
SELECT * FROM q
WHERE rn > 1; -- to show keepers, change to = 1

  I believe these semantics match yours, but please test.

Then, of course, add a proper key constraint before you let anybody insert any new nonsense into this table.


How to GRANT/REVOKE user permissions for user on Firebird 1.5?

Firebird has had GRANT and REVOKE since its initial version. See the Interbase 6.0 Language Reference, available from http://www.firebirdsql.org/en/reference-manuals/ (near the end of the page).

Eg for GRANT:

GRANT <privileges> ON [TABLE] {tablename | viewname}
      TO {<object> | <userlist> | GROUP UNIX_group}
  | EXECUTE ON PROCEDURE procname TO {<object> | <userlist>}
  | <role_granted> TO {PUBLIC | <role_grantee_list>};
<privileges> = {ALL [PRIVILEGES] | <privilege_list>}
<privilege_list> = SELECT
  | DELETE
  | INSERT
  | UPDATE [(col [, col ...])]
  | REFERENCES [(col [, col ...])] [, <privilege_list> ...]
<object> = PROCEDURE procname
  | TRIGGER trigname
  | VIEW viewname
  | PUBLIC
  [, <object> ...]
<userlist> = [USER] username
  | rolename
  | Unix_user}
  [, <userlist> ...]
  [WITH GRANT OPTION]
<role_granted> = rolename [, rolename ...]
<role_grantee_list> = [USER] username [, [USER] username ...] [WITH ADMIN OPTION]

And for REVOKE:

REVOKE [GRANT OPTION FOR] <privileges> ON [TABLE]
          {tablename | viewname}
       FROM {<object> | <userlist> | <rolelist> | GROUP UNIX_group}
   | EXECUTE ON PROCEDURE procname
       FROM {<object> | <userlist>}
   | <role_granted> FROM {PUBLIC | <role_grantee_list>}};
<privileges> = {ALL [PRIVILEGES] | <privilege_list>} <privilege_list> = {
SELECT
   | DELETE
   | INSERT
   | UPDATE [(col [, col ...])]
   | REFERENCES [(col [, col ...])] [, <privilege_list> ...]}}
<object> ={
   PROCEDURE procname
   | TRIGGER trigname
   | VIEW viewname
   | PUBLIC
   [, <object>]}
<userlist> = [USER] username [, [USER] username ...]
<rolelist> = rolename [, rolename]
<role_granted> = rolename [, rolename ...]
<role_grantee_list> = [USER] username [, [USER] username ...]

The Firebird documentation used to be fragmented in the original Interbase 6 documentation and a language reference update which only described the changes introduced in newer versions.

However there now is a complete Firebird 2.5 language reference.


Copying Tables (partly replicating) databases remotely

Create 2 DBLinks between the 2 databases(DB1 to DB2 and vice versa), and use these links to insert/update data by creating triggers for insert/update for the tables.


3rd Party Platform Unique Identifier for Joins vs Internal Identifiers

To be safe you'll want both. Internally you should create and use your own unique IDs to maintain a clean relational model with FKs between your tables while keeping a reference to the eBay unique ID in an appropriate table. If you need to reference something by the eBay ID you will have it available, but should the eBay unique ID rules change it does not affect the consistency of your existing table relations and architecture.


how to find number of empty tables in any schema?

When you say 'empty' tables, I take that to mean tables with zero rows.

Using the example in this post as a starting point,

WITH TablesAndRowCounts
AS (
    SELECT SCHEMA_NAME(sc.schema_id) SchemaName
        ,sc.NAME + '.' + ta.NAME TableName
        ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0
        AND pa.index_id IN (1,0)
    GROUP BY sc.schema_id
        ,sc.NAME
        ,ta.NAME
    )
SELECT *
FROM TablesAndRowCounts
WHERE rowcnt = 0
and SchemaName = 'HR'


Finally we were able to sort it out: It was a GO in a block comment /* ... GO ... */.

We tested the code in SSMS were the GO is ignored (as it is in a block comment). osql does not ignore it! We had the two batches in this block comment in a previous update. The first was ignored. The second was executed because the GO in the comment broke the comment.

Thanks to @gbn, @JNK and @Brandon for their help!


You could make use of sysstat (also here) and/or nmon - great free tools! Among the sysstat tools are iostat and mpstat (check the link). There's also top - you could look at that also! The pidstat (no separate wiki entry - see sysstat) might be right up your alley - I would urge you to explore the man pages!
What you want are baselines so that when things start to go wrong, you know:

what has been happening over the last minute, hour, day, week, month, year...?

what's happening now - is your I/O swamped, or is it your CPU or your RAM? Also, the network should be under surveillance!

what's happened when you changed parameter X or cancelled report Y

You should be saving the output. The example is for top, but you should be keeping all of your monitoring stats so that you can graph it and trace problems when they occur. Ideally, I would be putting the output into a database - MySQL in your case. For example, sar (System Activity Report) is the sysstat tool for commands:

used to report on various system loads, including CPU activity,
memory/paging, interrupts, device load, network and swap space
utilization

Even though sysstat isn't part of the Linux kernel proper, I would consider it an "honourary kernel module".
Normally, tool recommendations are off-topic here, but these tools have been around since forever and are unlikely to disappear anytime soon. If these don't suit you, a quick internet search will reveal many more - you really are spoilt for choice when it comes to (impressive) F/LOSS monitoring tools!


Heaps have a few special challenges that you can't experience with clustered indexes:

Forwarded Records
Captive Pages

I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them.

At this time, you can't reorganize a Heap table, and rebuilding a Heap table will also rebuild any nonclustered indexes on it. It may be cheaper to drop them, rebuild the Heap table, and then recreate the nonclustered indexes afterwards.

You can read more about this stuff here:

sp_BlitzIndex Self Loathing Indexes
How To Fix Forwarded Records
Mysterious Forwarded Records
Forwarded Fetches and Bookmark Lookups


Cannot connect in SSMS with Windows Authentication

In windows authentication, you don't have to type the password, it will validate by AD.

The reason for Login error could be your login does not have access to the database server.

Please ask the database administrator to grant access to your login.


If you have multiple control files then you can try to copy another one over this corrupted one. Otherwise you need to restore it from an (RMAN) backup.


Connecting to postgres from windows to virtual machine

When you connect to the PostgreSQL instance via the forwarded port in the VM you're not really connecting to localhost, the port is being forwarded to the actual IP of the virtual network interface. You need to modify pg_hba.con to allow connections from IP addresses other than 127.0.0.1 for this to work.

To open up to everything add the following to pg_hba.conf. You can optionally  restrict the address to your local machine's IP address (or a subset of it):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             all                     md5

Once it's updated restart the server or send the master process a SIGHUP so it reloads the pg_hba.conf file.

FYI, you can verify that this is what happening by creating a "server" using nc and trying to connect to it from your local machine.

On the VM forward the port 12345 to your local machine and try the following. Note that the "Connection from ..." output will come after you do the next step. See how it's not 127.0.0.1:

your-pgvm$ nc -v -l 12345
Connection from 10.0.2.2 port 12345 accepted

On your Windows machine:

your-comp$ nc localhost 12345


How to change the backup and archive destination to a network drive in oracle 11g windows

Network drives are managed by system as a drive, then for rman, it should be a disk device :

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\192.168.20.200\HOME\DEVELOPMENT\JOE';

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

I have never tested something like that, but I think it should work.


Best solution for PostgreSQL partial table replication

Our company works with a technology called symmetric-ds to replicate our tables, from server to client, and from client to server. Take a look at the site.


Is inserting multiple queries takes same time as that of single query?

In SQL Server at least (can't speak for the other RDBMS you've mentioned, sorry), a single statement will scale better than multiple, to a point. You can test this yourself using your exact statements and data, of course; nobody here can test that for you with your specifics, and your specifics may tilt things one way or another. "Which is faster, x or y?" questions are generally discouraged here because you can test them in your own environment much faster than any of us could throw guesses and logic at you. Especially when you're trying to get an answer for all of the database platforms you've listed - nobody is an expert in all of those and any such answer would either be very biased (like this one) or far too blanket to be useful.
In general, though, the overhead of preparing individual statements (and possibly sending them separately depending on your code and the behavior of your provider) should add up, much like @mustaccio said:

If you want to send three letters, would you go to the post office once carrying three letters, or three times carrying one letter?

This would especially be true in your scenario if each small statement is broken up across individual packets and even different connections. Again, I have no idea how your provider works or how your code sends these statements - if it's a single batch of a variable number of statements, that can actually be harder for SQL Server to optimize than individual single-statement batches, since SQL Server optimizes at the batch level.
Note that the VALUES() clause has an arbitrary limit of 1,000 values, so you may need to create multiple statements depending on how many values you have. The reason is concern over compile time, as Paul White explains here. Also note: Oracle has the same restriction.
According to Martin Smith's testing, compile time at least is minimal and relatively invariable up to a little over 250 values. See these charts (for details please see his answer):

If you go with a VALUES() clause be aware that each variation (meaning number of actual value sets) will generate its own plan, regardless of whether you use properly parameterized statements or just inline constants, and this is true even when the database's parameterization setting is set to simple. So you may want to consider using the optimize for ad hoc workloads server setting (lots of info here and here to prevent single-use variations from filling the plan cache (generally a good idea for most systems anyway, unless you are CPU-bound and compilation costs are proven to be excessive).
A better answer to this problem is to use table-valued parameters (TVPs), which allow you to send sets of structured data through a single parameter, giving you an efficient way to pass data and a single plan that can be reused regardless of the number of values passed. The catch here is I'm not sure Java understands what those are (in C# you can send, say, a DataTable as a Structured parameter).


There is no performance difference
Best practice is explicit JOIN and separate filters (your 2nd example)

Note:

This changes for OUTER JOINs because you'd change it to a inner join

The quick way:

SELECT a.colum
FROM
   A
   LEFT JOIN
   B  ON A.ID =  B.ID AND B.STATUS  = 1
WHERE
   a.foo = 'bar'

With separate filters:

SELECT a.colum
FROM
   A
   LEFT JOIN
   (SELECT * FROM B WHERE STATUS  = 1) B ON A.ID =  B.ID
WHERE
   a.foo = 'bar'


Disabling SKIP_CORRUPT after using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

You can disable it with the same procedure.

begin
  dbms_repair.skip_corrupt_blocks
  (
    schema_name => '...',
    object_name => '...',
    object_type => dbms_repair.table_object,
    flags=> dbms_repair.noskip_flag
  );
end;
/

dbms_repair.skip_flag enables, dbms_repair.noskip_flag disables this behaviour.


Determining Whether Query Load is Too High for PostgreSQL Server

This is not a horrible load for modern server hardware.

On a write heavy DB its going to be dependent on the Hard-disk layer, triggers, functions, indexes and constraints that have to be processed/checked.

An easy way to get to the information you want is to install pgAdmin,  and use the dashboard to see what is going on. Number of connections, transactions per second, tuples in and out

From there need to study system load using,  some kind of System Monitor,  i like glances,  this will get the information on the CPU , Network and HD loads,

If there is significant wait times on the CPU or HD,  can adjust the log settings in postgresql.conf to record which queries take longer than X time


Most efficient way to retrieve data by timestamps

To check if an index is working, use SELECT COUNT(*) rather than SELECT *, otherwise you're not only measuring index performance but also the time it takes to transfer all the data across your client application, which in this case is a whooping 50,000 rows!

You can significantly simplify your query and index by date datatype instead of timestamp, which is smaller and therefore it should perform better:

CREATE INDEX foo_foo_date_idx ON foo
USING btree ((foo_date::date));

select count(*) from foo
where foo_date::date = CURRENT_DATE;

You could reduce that index even further by using a smallint calculated as the difference between a set date (e.g. 1/1/2010 +- 32768 days = Apr-1920 to Sep-2099):

CREATE INDEX foo_foo_date_idx ON foo
USING btree (((foo_date::date - '2010-01-01'::date)::int2));

select count(*) from foo
where (foo_date::date - '2010-01-01'::date)::int2 = ('today'-'2010-01-01'::date);


Are there any way to execute a query inside a string value in PostgreSQL?

You pull it down to the client, and send it back to the server. That makes this a really bad idea. Even if you can dynamically generate the types of the return column in a string, which is proven here, you can't dynamically return anything with standards-compliant SQL.

If you're using psql, there is a command to automate that processes. After something returns a string of sql, you can run it with \gexec

As they say in the source above,

  As long as you get the generated sql, you may do whatever you like with that. You may create function, view etc.

So you can do that kind of stuff too. That is, things that don't return a dynamic list of columns, like issue a CREATE FUNCTION or a CREATE SQL command.


How to model complex systems like networks in Postgres

TL;DR: Normalize, normalize, normalize. Joins good, unions bad. Narrow tables with lots of rows good, wide tables with few rows bad. Fewer more general tables good, lots of very specific tables bad.

Denormalized structure

You're trying to model relations as objects. This can work, but it gets clumsy fast. It looks like a pretty direct mapping of your C++ classes, so it has lots of tables for specific types of things, and isn't normalized.

PostgreSQL has table inheritance features that make this approach more manageable, if you really want to pursue it. However the use of table inheritance is basically incompatible with the use of foreign keys due to the lack of unique index support across all child relations of an inheritance base relation. This makes inheritance much less useful than it could otherwise be.

Instead try to model it relationally. There are two schools of thought here, bottom-up and top-down modelling. In practice you use a mix of the two most of the time. Your goal is a well normalized database structure.

First, though...

IPAddress and MACAddress are unnecessary

You're also modelling IPAddress and MACAddress as entities, which they aren't. They're just scalar values. As far as I can tell these are probably direct translations of a C++ class or C struct  that stores IP addresses as four uint8_t octets, MAC addresses as six uint8_t octets, etc. This is completely unnecessary in the database and should be done away with entirely. PostgreSQL provides native data types that serve these specific needs:

inet
macaddr

... but if you have to remain cross-database portable you would be better advised storing your IP addresses as numeric with a boolean indicating IPv4 or IPv6, rather than storing them as octets. You need numeric because a bigint (signed 64-bit integer) isn't big enough for an IPv6 address.

BTW, in the network a MAC address may have multiple IP addresses. While no IP address within a given connected subnet may have multiple MAC addresses, the same IP address may appear in discrete networks with different MAC addresses, so I wouldn't try to model this at the schema level. If I did, I'd create an NetworkIP relation with a primary key of (network_id integer REFERENCES Network, ipaddr) to allow the same IP to exist only on disconnected networks. But then you have to consider multilevel NAT and it gets horrifying. Don't go there. I'd leave them as scalars.

Relational model

Now that we've done away with MACAddress and IPAddress, lets look at structure.

You should flatten everything, but not like this:

  It has 48 ports of 1 gig ethernet, 48 ports of 1 gig SFP and 48 ports of 10 gig SFP Plus. All in all, there are more than 1000 columns!

Rather than "wide", go "tall". Don't fear joins, joins are your friend.

You have network devices, of various specific kinds, where each kind has various additional characteristics. Each device may have one or more IP addresses and MAC addresses assigned to it (e.g. switch management addresses). Additionally, each device may have zero or more ports, and each port has one MAC address and zero or more IP addresses. To simplify things we define management interfaces as virtual ports.

So lets model that core structure.

CREATE TABLE device (
    id serial primary key,
    device_type text not null,
    device_name text,
    description text
);

-- A port is a logical port, which may correspond to a physical
-- plug, or a virtual port like an internal management interface,
-- IPMI controller, etc.
CREATE TABLE port (
    device_id integer not null references device(id),
    port_name text not null,
    port_mac macaddr not null,
    port_is_virtual boolean not null default 'f',
    PRIMARY KEY (device_id, port_name),
    UNIQUE(device_id, port_mac)
);

-- Now the IP address(es) for a port
CREATE TABLE port_ip (
    port_id integer not null references port(id),
    port_ipaddr ipaddr not null
);

-- A socket is a physical plug, which generally has a logical
-- port associated with it.
CREATE TABLE socket (
  device_id integer references device(id),
  socket_index integer not null,
  socket_name text,
  -- This might be a good use for an enum type, but for now
  -- just use a check constraint
  socket_type text not null check (socket_type IN ('rj45', 'sfp', 'sfpplus'),
  PRIMARY KEY(device_id, socket_index)
);

-- A port may have a socket or not, and a socket may have a port
-- or not, so model via a join table. If you want to prevent
-- a socket having multiple ports or a port having multiple sockets
-- you could use additional unique constraints.
CREATE TABLE port_sockets (
   device_id integer references device(id),
   port_name text references port(port_name),
   socket_index integer references socket(socket_index),
   PRIMARY KEY (device_id, port_name, socket_index)
);

See how it's "vertical" - we have a port table with entries for each port for each host, etc.

So now you want to model switches. These are network devices. They have a certain number of ports based on the model.

You can keep track of the number of ports directly in the entity, or you can just count the number of ports for that switch when you want to know. While keeping track of the port count in the switch entity is a denormalization that duplicates data, in this case it's probably worth it. So I'd do something like:

CREATE TABLE switch (
  switch_id serial primary key,
  device_id integer references device(id),
  nports integer not null,
  model text,
  management_port_name text,

  constraint switch_management_port
   foreign key (device_id, management_port_name)
   references port(device_id, port_name)

);

etc.

The switch is a network device, and ports are associated with it via the device table.

All this is a bit hand-wavey since I don't know exactly what you need to model, but should help you start thinking about it in a way that'll get you moving again.

Learn to love joins.

Modelling connections

To model connections between devices you might want to use an adjacency list to model it as an undirected cyclic graph. That sounds hairy, but it's really not, it's just a simple table saying "this one is connected to this one" without bothering to say which is "from" and which is "to", treating them as symmetrical.

There's lots of established literature on querying adjacency lists from SQL. You'll probably want to get friendly with recursive common table expressions (WITH RECURSIVE) if you want to do it efficiently, but for simple cases you can just do loops in C, or fetch relevant subetss of the adjacency list and search it with boost::graph.

e.g.

CREATE TABLE socket_connection (
    first_device_id integer references device(id),
    first_socket_index integer,
    second_device_id integer references device(id),
    second_socket_index integer,

    PRIMARY KEY (first_device_id, first_socket_index,
                 second_device_id, second_socket_index),

    UNIQUE(first_device_id, first_socket_index),

    UNIQUE(second_device_id, second_socket_index),

    FOREIGN KEY (first_device_id, first_socket_index)
      REFERENCES socket(device_id, socket_index),

    FOREIGN KEY (second_device_id, second_socket_index)
      REFERENCES socket(device_id, socket_index),

    -- A simple way to prevent both (1, 1, 2, 1) and (2, 1, 1, 1)
    -- being stored as separate connections:

    CONSTRAINT first_device_id_is_always_lowest
      CHECK (first_device_id < second_device_id)
);

... which looks hideous, but is actually just a mapping between two sockets plus a lot of sanity checking.

A note on composite keys

I've used composite keys like (device_id, socket_index) a lot above.

Some people like them, some don't. There are advantages and disadvantages to their use. If you want to use generated surrogate keys everywhere, that's fine too.

Reinventing the wheel

You might think "hey, has this been done before?".

I'm quite sure it has. Whether the existing models match your needs is less certain, but I suggest looking over some of them before you go and invent your own from scratch.


It's stored in the Content column in the Catalog table as XML, and the Content column is in binary - so you'll need to decode it from binary and find the Enabled XML element.  For example, this will give you a list of the shared Data Sources with their enabled/disabled status:

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.Enabled
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('Enabled[1]', 'bit') AS [Enabled]
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON


It seems there is no alternative to solve the problem without using a sub query, what is a performance constraint in my case, so I decide move to another approach.

I gave up about the trimmed means (thanks @Linas) and move to a percentile_disc indicator. Once all I wanted was to give a measure of latency for each request I did a simple query as follow:

select
  mdate,
  mtime,
  percentile_disc(0.997) withing group (order by latency) as "response"
from mtable
where url like '%MyPartialUrl%
group by mdate,mtime
order by mdate,mtime

As this way I grab the biggest latency took to answer a request for 99.7% of all users. In fact I am grabbing several percentiles following six sigma approach to show how much slowness per million requests we are getting on production.

Query took no more than 8 seconds to be executed, using the trimmed means before it was around 90 minutes.

Thanks all.
Regards


MySQL performance, UNION then JOIN, or JOIN on each part of the UNION?

It depends.  Let's dissect each scenario.

( SELECT UNION ALL SELECT ) JOIN

breaks down into

create tmp table
do first select into that tmp (n1 rows)
do second select into that tmp (n2 rows)
dedup because of ALL -- change to DISTINCT to skip this step (n3=n1+n2, or fewer rows)
join n3 times

And...

( SELECT JOIN ) UNION ALL ( SELECT JOIN )

breaks down into

create tmp table
do first select and join into that tmp (n1 rows & n1 joins)
do second select and join into that tmp (n2 rows & n2 joins)
dedup because of ALL -- change to DISTINCT to skip this step (n3=n1+n2, or fewer rows)

So it depends on which case you have.

If the JOIN filters out some rows, then whatever follows it will have less work.
If you switch to UNION DISTINCT, that will shrink the subsequent work.
If the JOIN adds lots of bulky columns, the join-first case is building a bigger tmp table.

Watch out for <autokey0> -- it means you have a tmp table without an index, but 5.6 was smart enough to create one for you.  Your SELECT, as written, should not need that unless ThingsDetails does not have an index on ThingsId.  If that is the case, then add an index rather than taking advantage of autokey.


SQL Server 2008 - Cross database performance on same physical machine and server instance

You should not see performance degredation when querying across databases on the same instance. The same query analyzer, buffers, etc. are in use.

Now, if you were to attempt the same across distinct SQL instances on the same server you could see a performance impact. The query would have to be analyzed by two distinct query analyzers and more system resources would be used for caches, buffers, etc. You would also force results to go through the VIA protocol (assuming it's enabled) between the instances. MS DTC could be involved, and if permissions are inadequate on one of the instances a poor(er) query plan may be created.


Compare two databases and get the differences

For item #1, you can use the Database Diff feature in SQL Developer - and yes, it should work on Oracle 10g.

This is a free feature in a no-cost tool provided by Oracle Corp.


Why was default of innodb_file_per_table changed?

The default value has been ON, since 5.6.6.

You're looking at the 5.5 documentation, which only refers to the defaults in 5.5 -- not 5.7.  In version 5.5.7, a decision was made to revert the defaults to match 5.1 in an effort to avoid a replication incompatibility.

  the default values for innodb_file_per_table and innodb_file_format are reverted to the MySQL 5.1 default values—that is, OFF and Antelope, respectively. (Bug #56318, Bug #11763590)

  https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-7.html

In 5.6.6 (before 5.6 GA) the default value changed back to ON.

  The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually.

  https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html

In 5.7, the default is still ON.


Use Dark Theme on SSMS Results Grid, Object Explorer and other windows

These options are ignored as of SQL Server 2005 SP2 and still not fixed.

See the relevant connect item: SP2: Results Grid Background color property ignored by Management Studio. archive link

The status is closed as fixed, but only because it's been converted into an internal issue

  Thanks for this feedback. I have converted this request to either a
  defect (something is broken) or a Design Change Request (something new
  to add to the product) and so I'll close this ticket out. We will
  consider your information for SQL Server 2008 or a subsequent release.
  Thanks!
  - Buck Woody, SQL Server Program Manager

There is a second connect item: SSMS 2012 Grid Results background color does not work (unfortunately not archived) which is closed as wontfix.

  Hello Chris. Thanks for the feedback. We triaged this issue and at
  this time do not plan to address this in a future version of SQL
  Server.
  -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

There is however one user commenting

  I found the answer! This is actually controlled by the tooltip color
  you set up in your windows environment. Man, That was a PAIN to find!

But unless that works I fear you are out of luck


Hot and Cold data Storage using partitioning

You want RANGE LEFT instead of RANGE RIGHT, and you only need a single boundary value at the three-year-mark.
Make the partition function like this:
Create Partition Function [MonthlyPartitionArchive] (datetime)
 as Range LEFT For Values (@Previousyear);

The scheme should then be:
Create Partition Scheme RangePartScheme as Partition [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA]);

There is no way to create a dynamic partition function.  You'll need to schedule a job to alter the partition function as needed.  Look at this answer for details about how to automate it.


leading 0 missing in column when exporting to csv

Leading zeroes are removed if the exporter thinks the column is numeric.  You need to force the exporter to treat that column as text.

For instance, this might work:

chr(34) + (Right(products_vendor1.shortISBN13,5)) + chr(34)


Can I truncate log in simple recovery mode?

You have two choices:

Break the delete into chunks, as Jonathan explained and as I blogged about here:

Break large delete operations into chunks

Give the transaction log more space to grow to accommodate the log activity (and don't shrink it after every delete; all this does is make the next delete take longer). If you run a delete command that deletes 100K rows, the log has to be able to record all of that activity, regardless of recovery model.

For a much more thorough treatment of the subject, see:

Why Does the Transaction Log Keep Growing or Run Out of Space?


PostgreSQL query suddenly very slow after new column addition

It looks like we needed to run ANALYZE users!

I had run ANALYZE activities, but that wasn't the problematic table. I guess the query planner thought that the api_user_for column would be instructive to help it remove rows, but the column was almost entirely filled with NULL values, so it would not have helped much at all.

Obviously running a simple ANALYZE would also have solved the problem.


Getting an error when trying to start the database (end-of-file on communication channel)

If your database is in archive log mode and you don't need it, disable it. This prevents that archive logs are written. Check with archive log list.

If Automatic archival ist enabled, and you don't need online backups, disable it with alter database noarchivelog; – the database needs to be in nomount state to do it.

With rman you can delete the not needed archivelogs (or backups).

rman target /
delete archivelog all
exit

This should clean up your FRA. Of course do this only for testing purposes, don't do this in a production environment! But as you are using Ubuntu as base for your installation I don't think it is a production DB.


Am fetching data from database i need to pass variable in my query.but am not getting how to pass variable in query

Do the JOIN in SQL, not with a client-side loop.  Cutting down on the number of round-trips to the server helps; cutting down on the number of queries to parse and execute helps, even though the resulting query is more complex.

But the real slowdown is not having the result of LOWER(REPLACE(suggestion,' ','')) as an indexed column in the table.


What is the best and easiest way to turn off SQL Server when not in use?

You may need to access SQL Server Configuration from the Start menu by selecting Microsoft SQL Server and then Configuration Tools, and then follow Thomas' path.

You can also configure those services so that they don't start automatically. I have mine set on "Manual", so if I want to use the SQL Server server, I just start the service. Ditto for SSIS, SSAS, SSRS, etc.

Since I have two versions of SQL Server running on my development machine, I don't want all of this stuff eating up my resources.


Selecting single row based on a condition

SELECT id, uid
FROM studenttable
GROUP BY id, uid
HAVING SUM( CASE WHEN subject='English'
                 THEN 0
                 ELSE 1
            END ) = 0


Database mirroring is limited to an original database

Yes, database mirroring works from one principle server to one mirror server. Only one mirror copy can be used. For multiple readable replicas, consider AlwaysOn Availability Groups, introduced in SQL Server 2012 Enterprise Edition.


Actually Temp table and Table variable use tempdb (Created on Tempdb). Please check the below code which I will use to create Temp Table and Variable Table. and check where they were created.

BEGIN TRAN

DECLARE @DtmStartDateTime DATETIME = GETDATE()

-- Create Temp Table and Table Variable

CREATE TABLE #TempTable
(CandidateID INT)

DECLARE @TempVairable TABLE (EmployeeID INT)

-- Check Where Temp Table and Table Vairable Created
SELECT * FROM tempdb.sys.all_objects
WHERE type = 'U' -- User Table
AND create_date >= @DtmStartDateTime

-- This Filter will be used to check the     new tables

ROLLBACK


Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
  where the ( COPY = french ) is located?

Text search configurations are stored in pg_catalog.pg_ts_config.

They can also be displayed with \dF[+] in psql.

  Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules?

No. The COPY clause in CREATE TEXT SEARCH CONFIGURATION clones a text configuration and has nothing to do (besides the name) with the SQL COPY command meant to import data from the outside of the database.

  Also can I use a dedicated database table in order to load my own unaccent rules as well?

No.

The latest version of PostgreSQL (12) comes with unaccent rules that correctly support Greek (as far as I understand) solving the issue #15347 mentioned in the question. For on-premise PostgreSQL instances, DBAs can get the latest unaccent.rules and copy it into an older PostgreSQL version, and making sure that the indexes that depend on unaccent get reindexed. With instances managed by a third party you would have to ask them.


Get a list of databases that have a certain table

This is not possible by a single query, because queries cannot span multiple databases, and the list of tables is only contained within the individual databases.


There is no perfect index for this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId

It is because the index cannot get past IS NOT NULL to get to the ORDER BY.

In general,

WHERE a=1 AND b=2 ORDER BY c

can make good advantage of INDEX(a,b,c) or (b,a,c)

But, this cannot:

WHERE a=1 AND b!=2 ORDER BY c

(IS NOT NULL is similar to != 2 in that it is a "range", not a single value.)

To optimize this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

there are two possibilities.  The Optimizer will pick one, sometimes the less optimal one:

INDEX(CampaignId, CustomerId)
INDEX(CampaignId, PKCampaignCodeId)

The first will do the filtering, but still have to sort and limit.
The second will do some of the filtering, avoid the sort, but might have to look at a lot more than 25 rows.

There is no benefit of having all 3 columns in an index; the third column  will not be used.  (And, apparently, it causes trouble with your first SELECT.

More on creating indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql  (though it does not fully address all your questions)


Has somebody got a function that will extend hashbytes to strings of arbitrary length?

If you insist, then

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len
          BREAK
    END
    RETURN @res
END

BUT may be better to use this CLR function and hash data of any length into real MD5 hash?

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}


Understanding repeatable read isolation level

Serializable means that there is some order the transactions can be run in without overlapping and we'll end up with the same answers and the same state of the database as we get by running the transactions in parallel with serializable isolation level.
Given two transactions, A and B, the only valid states of the system are

All of A followed by all of B, OR
All of B followed by all of A.

That's it. If the system can end up in any other state the transactions are not serializable. If we can show that two transactions running in parallel do not correspond to one or other of these states then those transactions are not serializable.
Think about a table with 4 rows, ID values 2, 4, 6 and 8. There are two transactions A and B. A counts the number of rows. B inserts two rows, ID values 3 and 7.
If they run A->B then A returns 4. If they run B->A then A returns 6. Those are the only possible answers if we are to guarantee serialization.
So A starts under Repeatable Read isolation. It will perform a table scan. A reads row 2 and takes a lock, then reads row 4 and takes a lock.
Now B starts in parallel with A. B tries to insert row 3. Nothing prevents it; A has never read a row with ID 3 to take a lock on it. Then B inserts row 7 and commits, releasing its locks.
Transaction A continues reading. It has just finished with 4 so the next row is 6, then comes 7 (Tx B has committed so its lock on 7 has been released) and finally 8. So A has seen rows 2, 4, 6, 7 & 8 - five rows! This workload is not serializable.
This is the scenario in the second bullet point in the quote from Korth.
The "problem" is the phantom rows produced by B. They overlap the range of data to be read by A. But Repeatable Read does not issue range locks so B is free to do this. Under Serializable isolation B's insertion locks would have prevented A taking range locks or A's range locks would have blocked B's insertions.


Merge and formatting of data from two tables

Table creation and sample data script:

CREATE TABLE #Slot
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    Free    smallint NOT NULL CHECK (Free > 0),

    PRIMARY KEY ([Day], [Time], Venue)
);

INSERT #Slot
    ([Day], [Time], Venue, Free)
VALUES
    (1, '08:30', 'RM 1', 10),
    (1, '08:30', 'RM 2', 10),
    (1, '10:30', 'RM 1', 20),
    (1, '10:30', 'RM 2', 20),
    (2, '08:30', 'RM 1', 10),
    (2, '08:30', 'RM 2', 10),
    (2, '10:30', 'RM 1', 30),
    (2, '10:30', 'RM 2', 10);

CREATE TABLE #Booking
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    [User]  varchar(10) NOT NULL
);

INSERT #Booking
    ([Day], [Time], Venue, [User])
VALUES
    (1, '08:30', 'RM 1', 'Jill'),
    (1, '08:30', 'RM 2', 'Jill'),
    (1, '08:30', 'RM 1', 'Jack'),
    (1, '08:30', 'RM 1', 'Mary'),
    (1, '08:30', 'RM 2', 'Mary'),
    (1, '08:30', 'RM 2', 'Jill'),
    (2, '10:30', 'RM 1', 'Ken'),
    (2, '10:30', 'RM 1', 'Ken');

Query:

SELECT
    s.[Day],
    s.[Time],
    s.Venue,
    s.Free,
    Used =
    (
        SELECT COUNT_BIG(*)
        FROM #Booking AS b
        WHERE
            b.[Day] = s.[Day]
            AND b.[Time] = s.[Time]
            AND b.Venue = s.Venue
    )
FROM #Slot AS s
ORDER BY
    s.[Day],
    s.[Time],
    s.Venue;

Output:

╔═════╦══════════╦═══════╦══════╦══════╗
║ Day ║   Time   ║ Venue ║ Free ║ Used ║
╠═════╬══════════╬═══════╬══════╬══════╣
║   1 ║ 08:30:00 ║ RM 1  ║   10 ║    3 ║
║   1 ║ 08:30:00 ║ RM 2  ║   10 ║    3 ║
║   1 ║ 10:30:00 ║ RM 1  ║   20 ║    0 ║
║   1 ║ 10:30:00 ║ RM 2  ║   20 ║    0 ║
║   2 ║ 08:30:00 ║ RM 1  ║   10 ║    0 ║
║   2 ║ 08:30:00 ║ RM 2  ║   10 ║    0 ║
║   2 ║ 10:30:00 ║ RM 1  ║   30 ║    2 ║
║   2 ║ 10:30:00 ║ RM 2  ║   10 ║    0 ║
╚═════╩══════════╩═══════╩══════╩══════╝


Where to put glogon.sql in 32-bit ODAC (11.2.0.2.1)?

Thanks to the link provided by Jack Douglas, I found the solution.

I needed to create a login.sql file in the same folder as sqlplus.exe and I had to set the SQLPATH environment variable to this path.

From the article I got the impression, that setting the variable was not necessary.


Mapping a shared physical folder to filestream

this isn't exactly how filestream and filetables work, you can't specify the name of an existing share as SQL server will try and create the share that you specify in SQL Server Configuration Manager.  This is because the share is not mapped directly to a folder on the filesystem like a normal share, but an NTFS data container that lives in the filestream filegroup that is created for the database.

There is a misconception with filestream/filetable that because the data is stored in the filesystem, it is just a normal folder, this isn't entirely correct.

When you create your database, you specify that one of the filegroups is a filestream filegroup, this isn't like a normal filegroup and is actually a series of NTFS folders called Data Containers, this is where your files actually get stored, you can't modify this folder directly, but you can look at the contents (not that there's much human readable stuff in here though, you can find your actual files in here though if you look hard enough through the GUID folders and oddly named files :)).

The file share that you use to copy the files into the table is actually a representation of the data inside these data containers that is presented to Windows explorer via a filter driver which uses the streaming APIs to make the changes to the data containers and add the rows to the filetable etc.

So if you want to specify the location that the files are physically stored, you need to specify that location as where you want the filestream filegroup stored, then you can change the share name to whatever you want.


Being sure there are a maximum of two subjects makes life a lot easier:

select st.*, su1.*, su2.*
   from student st
   left outer join subject su1
     on su1.id = (select min(sub_id) from rel where stud_id = st.id)
   left outer join subject su2
     on su2.id = (select max(sub_id) from rel where stud_id = st.id)
     and su2.id > su1.id

which gives us:

1|Student 1|4|Subject 4|5|Subject 5
2|Student 2|7|Subject 7||
3|Student 3|8|Subject 8||


Restore Postgres Database on New Machine

Try dumping the database, recreating, verifying it's empty, and re-importing? If it does it again then check the actual file you're importing and see if it has everything listed 2x.


How much time should a backup of a 100Gb database take using streaming server setup?

You are plausibly in the range where network bandwidth is limiting.  I get 9 hours versus your reported 23, but that could be accounted for by overhead, inefficiencies or competing network usage.

You can try turning on network compression, which requires changing the backup method, which in turn will require you to configure "ssh_command".

ssh_command=ssh postgres@192.168.0.15
backup_method = rsync
network_compression=true

Of course this will impose some CPU overhead on the server for it to do the compression.


SSIS Package - "Subquery returned more than 1 value" - Inserting one record into a table

In my case there was a trigger that trying to update one of the rows. The unfortunate thing was that the query in the trigger returned more than one row in the subquery.

As it turned out for me the trigger was only relevant for one particular supplier (an IF condition in the trigger), also luckily for me, the previous occupant of my position hadn't reviewed this trigger, and it was no longer needed.

Removed the trigger. However in other scenarios I would have had to modify the trigger to return one row only.


Does row-based replication with "binlog_row_image=minimal" cause issues when ALTER TABLE reorders columns?

Parallel replication allows concurrent transactions out of order, but does not allow concurrent non-transactional updates. Certain SQL statements — including DDL statements — cannot be rolled back, so they cause a "barrier" such that they wait until all parallel workers catch up, and workers are blocked from executing changes after the non-transactional change.
This means there's no race condition. DDL effectively forces a kind of "synchronization".


Try using with -c which is character instead of -n native

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -c -T

bcp "Database_B.dbo.tbl_Employee" in "D:\test.txt"-S Server_Name -c -T

Or an alternative is to create a format file.

Create format file syntax is:

bcp Database_A.dbo.tbl_Employee format -null -f D:\BCPFormat.fmt -n -T

Export as normal with native:

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

Import with -k keeps nulls and format file, no -n flag needed.

bcp Database_B.dbo.Employee in "D:\Test.txt" -S Server_Name -f D:\BCPFormat.fmt -k -T