Migration of chunks not taking place in sharding

The data is not being lost - that message means that when the migration was attempted from the source shard to the target, there was already data present that matched the chunk range, which was then deleted (this is what is supposed to happen, which is why that is not an error, but a warning).  Since you are starting from scratch, it implies that you have data present in that collection, in that range before any migrations are attempted.

This condition can happen occasionally on sharded clusters that have been running for a while (and chunks move around), hence the warning rather than an error.  However in this case it suggests that this was not a clean database on at least one shard when you began.  There are either some steps missing from this description, or possibly you failed to clean up properly after a previous attempt.

In any case, I would suggest posting the full procedure that brought this about - I have tested hashed sharding with 2 shards, 1 config server and 1 mongos many, many times (occasionally live in front of others as part of a demo) and never caused this to happen with normal operations.


Different Results between stored procedure and UDF

DATEDIFF return the result in int. Change the @HoursWorked data type in your scalar value function to int instead of datetime.


Best way to design a database for a bakery store?

You have an ingredient; let's call this "strawberry mix, packet, 100g". You also have a product; let's call this "strawberry mix". There is a Product_Ingredients row which maps the product "strawberry mix" to the ingredient "strawberry mix, packet, 100g" with a quantity of 1. No need to convert anything to anything.


An index on (practice_id, client_data_provider_id, date_scheduled) would be better than the one you have (practice_id, date_scheduled, client_data_provider_id), for this particular query.

Notice the difference in order. When there are multiple equality (=) conditions and one range condition (>=, >, between, etc) in the where clause, it's better to have an index with first the columns that are checked with equality and last the column in the range condition.

This way, the index scan will have to cover a much smaller part of the index, only the values with practice_id = 753 and client_data_provider_id = '3001-753' and date_scheduled >= '2016-01-12', which is exactly the rows you want.

With the current index, it will have to scan the part of the index with practice_id = 753 and date_scheduled >= '2016-01-12' and then reject the largest part (all those that don't have client_data_provider_id = '3001-753').


SQL server 2012 inner join estimated number of rows issue

I was able to reproduce your issue in SQL Server 2014 with the legacy CE and TF 4199 enabled. I used a rowstore fact table because I have little experience with columnstore.

For your query with the filter, the query optimizer rewrites the query to something like this:

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK
where f.company_SK = 1 and d.company_SK = 1

For that query the optimizer can directly uses the histogram step containing 1 for both tables and you get 467,583,000 X 1 as the estimated number of rows.

The query without a filter will use linear interpolation:

  For joins with a single equality or inequality predicate, the legacy
  CE joins the histograms on the join columns by aligning the two
  histograms step-by-step using linear interpolation.

Note that the statistics for your dimension table do not have a histogram step with a RANGE_HI_KEY value of 1, which is where almost all of the data is in your fact table. Something is going wrong with the linear interpolation step for that value. If I create your dimension table without a 0 value then the histogram gets a step with a RANGE_HI_KEY value of 1. That fixes the estimate. In short, you got unlucky with your histogram. I don't think there is a supported way to force the histogram on the dimension table to contain a step for 1, unless perhaps you are willing to add additional filters to your query.

I don't know the exact rules of your data, but I can give you two workarounds. The first workaround is to enable trace flag 2301 for the query. That fixes the estimate for this part of the query but may have other negative effects for the rest of your query. This trace flag is documented by MS, but talk to your DBA before using it.

The second workaround is to encourage the query optimizer to use a different method of cardinality estimation for the join. The following query gave a good estimate for me and only does a single scan on your fact table:

select *
from FACT_SALES f
join (SELECT COMPANY_SK FROM DIM_COMPANY UNION SELECT 1) d
on f.company_SK = d.company_SK;


SQL Server 2008 R2 - The request failed or the service did not respond in timely fashion

When the server cannot start, the first thing to consult is SQL Server error log.
There can be different reasons, for example, banally the port assigned is occupied. But in OP case it was

  The server could not load the certificate it needs to initiate an SSL
  connection

The error message itself suggested what to do:

  Check certificates to make sure they are valid

Another option in this case was to remove encryption at all if it was configured by mistake


Oracle export not importing correctly (missing more than half of the tables)

No, the schema definition is not stored entirely in the first file:

SQL> select segment_name, bytes/1024/1024 as size_mb from user_segments;

SEGMENT_NAME    SIZE_MB
------------ ----------
T1                   80
T2                   80

$ exp bp/bp tables=T1,T2 file=expdat1.dmp,expdat2.dmp,expdat3.dmp,expdate4.dmp filesize=50M

Export: Release 12.1.0.2.0 - Production on Wed Sep 21 16:52:04 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditi
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1
continuing export into file expdat2.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                             T2
continuing export into file expdat3.dmp

continuing export into file expdate4.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

First CREATE TABLE is in the first file, second CREATE TABLE is in the second file:

$ grep -a "CREATE TABLE" *dmp
expdat1.dmp:CREATE TABLE "T1" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
expdat2.dmp:CREATE TABLE "T2" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS

When you run the import, you should provide the name of all dumpfiles, in the correct order.


SQL Server Credential Password and Secret Difference?

What is the difference between SQL Server secret and password for credentials

A credential has three fields: Name, Identity, and Secret.  Depending on what the credential is used for the fields are used differently.  For instance if the credential is used to store a Windows identity, the user name goes in Identity, and the password goes in Secret.  If it's used to store the access credentials for Azure blob storage, a SAS token is stored in Secret, etc.


Transactional replication using articles filters and delete operations

The problem was with delete operations sequence.

Problem solved.


Display all fields with null values in a particular row in SQL Server

SQL as a language allows you to easily dictate which rows to include in output, but not which columns. For this you'll need dynamic SQL (and a consuming app which is equally dynamic). An example, given this table and data:

USE tempdb;
GO

CREATE TABLE dbo.blat(pk varchar(32) NOT NULL, a int, b int,
  CONSRAINT pk_blat PRIMARY KEY (pk));

INSERT dbo.blat(pk,a,b) VALUES
  ('key 1',1,NULL),
  ('key 2',1,1),
  ('key 3',NULL,1),
  ('key 4',NULL,NULL);

One solution:

-- input param:
DECLARE @pk varchar(32) = 'key 4';

DECLARE @sql nvarchar(max) = N'SELECT pk';

SELECT @sql +=
    CASE WHEN a IS NULL THEN N',a' ELSE N'' END
  + CASE WHEN b IS NULL THEN N',b' ELSE N'' END
FROM dbo.blat WHERE pk = @pk;

SET @sql += N' FROM dbo.blat WHERE pk = @pk;';

EXEC sys.sp_executesql @sql, N'@pk varchar(32)', @pk;

Try it with all four combinations. Don't forget to clean up:

DROP TABLE dbo.blat;


Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…?

Disclaimer: I work for Percona and am one of the Percona XtraBackup developers.

You'd need to try the options and see what works best for you.

Given your needs I'd evaluate Percona XtraBackup first. It will perform the full and incremental hot backups just fine. In fact, Zmanda Recovery Manager uses Percona XtraBackup as its backend for the hot InnoDB backups.


It looks as though this is related to a MySQL bug, here is one discussion https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/13590-character-set-warning

Here is the bug report: https://bugs.mysql.com/bug.php?id=72543

A poster on StackOverflow reported that after updating the connector the issue was resolved, might this apply here? https://stackoverflow.com/questions/...ws-server-2012

If you are using HA Proxy, this may also be relevant, and there is a workaround at the end: https://jira.percona.com/browse/PS-1518


Table partition existing table where partition key is not part of the primary key

If OrderId is monotonically increasing, you can partition on that.  Then you can truncate old partitions having no data you need to retain.  Something like:

create partition function pf_OrderId(int)
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId)
)
ON ps_OrderId(OrderId)

go

--then you can examine the max OrderDate in each partition when trimming old data
select p.partition_number,
       (select max(OrderDate) MaxOrderDate
        from [Order]
        where $PARTITION.pf_OrderId(OrderId) = p.partition_number) MaxOrderDate
from sys.partitions p
where p.object_id = object_id('Order')
and p.index_id = 1

And of course you can adjust the granularity of your partitions to roughly align to your data retention requirements.  And if you have a hard requirement to purge old data then you would truncate N partitions and run a DELETE on at most one partition.  And you can always split the partition function to insert a partition boundary at important times, like overnight at the beginning of a year or quarter.

To move an existing table to a partition scheme, you drop all the indexes and the clustered primary key constraint, and recreate them on the new partition scheme.  Once you create the clustered index on the partition scheme, subsequently-created indexes will go there by default. If you don't drop the non-clustered indexes first, they will be rebuilt when you drop the clustered PK, and rebuilt again when you recreate it, and they still won't be partitioned. EG

CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId),
    index ix_Order_Orderdate (OrderDate)
)

go

create partition function pf_OrderId(int)
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

drop index ix_Order_Orderdate on [Order]

alter table [Order]
drop constraint [PK_OrderId]

alter table [Order]
add constraint [PK_OrderId] primary key clustered (OrderId)
on ps_OrderId(OrderId)

create index ix_Order_Orderdate on [Order](OrderDate)

Then verify that both the clustered and non-clustered indexes are partitioned:

select i.name index_name, p.partition_number
from sys.partitions p
join sys.indexes i
 on p.object_id = i.object_id
 and p.index_id = i.index_id
where p.object_id = object_id('Order')


Postgres JSONB - Flatten nested objects and groupings

You need to unnest twice:
select x.m ->> 'manufacturer' as manufacturer,
       x.m ->> 'address' as address,
       x.m ->> 'contact' as contact,
       m.model ->> 'model' as model,
       (m.model ->> 'starting_price')::int as starting_price
from the_table t
  cross join jsonb_array_elements(t.models -> 'cars') as x(m)
  cross join jsonb_array_elements(x.m -> 'models') as m(model)

Suppressing repeating values is better done when you display the data in your application, not in SQL.
Online example


Column sort order in CREATE INDEX statement?

Since indexes can be scanned in both directions most of the time there isn't much reason for choosing DESC.  Some TOP queries could be helped by the DESC on some of the columns of a covering index but only testing will show if this helps.

One other difference is that the forward order scan can be run in parallel but the reverse order scan is always single threaded.


Why can I select multiple columns from dual?

The number of columns in a table has nothing to do with the number of columns in the projection of a SELECT statement.  You can always add additional computed columns to the projection (at least practically...  theoretically, you might eventually hit some sort of logical limit).

The fact that you are querying dual here is irrelevant.  You can do exactly the same thing with any table.  The projection in this query

SELECT e.*, 2+2 computed_one, 2+3 computed_two, 2+4 computed_three
  FROM emp e

returns all the columns in the emp table along with three additional computed columns computed_one, computed_two, and computed_three.  The fact that dual always has exactly one row makes it useful when you want the result to have exactly one row but other than that, there is no logical difference whether you use dual or a single-row, single-column table that you create yourself.


Sqlite: Finding the next or previous element in a table consisting of integer tuples

At the moment I try to find the next tuple with ("center" is (1-1-1-3))

Test
SELECT *
FROM tuples
WHERE (1,1,1,3) < (a,b,c,d)
ORDER BY a,b,c,d LIMIT 1

fiddle


Selecting Multiple Values from Same Coulmn

You can use a not exists query:

select distinct t1.name_last, t1.name_first
from the_table t1
where not exists (select *
                  from the_table t2
                  where t1.name_last = t2.name_last
                    and t1.name_first = t2.name_first
                    and t2.role = 'create');

The NOT EXISTS operator returns "true" if the sub-select returns nothing. In this case the sub-select looks for any row with the same name as the outer row but with the role create


Can low memory cause Table Spool operators in plan

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong.

MOre info

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/


A trigger that show the user that has made an insertion

Google is there for helping you out in coding. Take this as a challenge start coding , it will help you out more than someone writing it for you. Have a nice day.


The KILL command cannot kill a UOW of {00000000-0000-0000-0000-000000000000}, so that leaves you hanging on what to do next to get rid of it.

To get rid of spid -2 with the unit of work {00000000-0000-0000-0000-000000000000} you can do something, but it has side-effects:

Exec Sp_configure 'in-doubt xact transaction',2
-- This presumes ALL in-doubt trans are aborted
-- so it should be used with great caution.
GO
DBCC dbrecover('dbname'); -- or Alter database

I ran into this problem when test restoring databases.  We had one database that restored successfully, but failed during the DBCC CHECKDB because of a {00000000-0000-0000-0000-000000000000} UOW.

In that case DTC was not running on the restore server at all, but still the
{00000000-0000-0000-0000-000000000000} UOW was causing us problems as noted above.  So you could say that this was an edge case.

Using the approach above finally cleared the problem for us, but if you use this technique you need to make sure that you are not aborting DTC connections that are in active use.


SSAS Model Refresh - Not enough memory to complete this operation Error

TL/DR: Add more Memory, reduce the size of your Model(s), and/or move either the SQL Server Services or SQL Server Analysis Services to a different server (e.g. scale out)

Longer Explanation:
We went through this exercise a few months back with our Tabular SSAS production server, and actually reached out to Microsoft for "formal" recommendations as our Infrastructure team was being stingy with RAM (which I can understand as it's not exactly cheap).  Just for clarity's sake, the error we ran into was as follows:

  The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

Our server was originally setup with 64GB of memory and was hosting 2 SSAS models totaling 40GB in size.  No other SQL Server services were hosted on this machine.  Some days our models would process without issue, but most days they would fail.  We would reboot the server and then maybe they would succeed... if the wind was just right and the stars and planets all aligned.

Unlike Multidimensional (MOLAP/ROLAP/HOLAP) models, the default Tabular Models are loaded entirely into memory as they leverage In-Memory technology.  If the model(s) are unable to be loaded entirely into memory, you run into problems.

Sadly, Microsoft's documentation breaks down on what the "memory recommendations" are as I cannot find any formal document providing anything other than "minimum" levels that are needed to just run the service.  From the support ticket we filed, Microsoft's recommendations were as follows:

  For a model of size X, provision between 2X - 10X RAM on the SSAS server to be used by the SSAS service, which is further influenced by the following factors:

  Cube Processing requires 2X - 3X RAM for full processing which includes a shadow copy of the model in builtin memory.
  The number of Users/Reports connected to the cube also increase RAM requirements, at times up to 10X depending upon number of reports, volume, etc. as users/reports can generate DAX queries which perform calculation or memory materialization (which cause the engine to build an intermediate non-compressed result and can cause the memory consumption to go higher than expected).
  The number of Models being processed can also increase the memory footprint required.
  Enable the VertiPaqPagingPolicy if the setting is disabled, so SSAS can utilize the OS paging file for additional memory at the cost of processing and query performance.

What we ended up doing was increase the amount of RAM on our server, which ultimately solved our issues for the time being.  The only other real alternative "solutions" is to limit the amount of data you need in your model(s) or scale out (e.g. move services to another server) your deployment to other servers.

What I suspect is happening in your case is that your SSAS service is running out of memory because your SQL Server Service is also hosted on the same server.  Basically you need to either segregate these services from one another or have enough RAM on the server to let them run in parallel.  I would highly suggest segregating your SSAS services to a different server if possible, but licensing challenges may impact this so be sure to have enough RAM.

Other things you can fiddle with are the config settings located in the msmdsrv.ini file, but for our scenario, we didn't have much success with these making any significant differences in the eventual outcome of running out of memory.


Look in DBA_BLOCKERS and DBA_WAITERS. If you don't have these views, as SYS run @?/rdbms/admin/catblock.

You can remove the lock by killing the session, but you will then need to wait for Oracle, specifically PMON, to roll back that session's outstanding work.


Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index

First scenario, it's scanning the whole table and looking through 14553 pages of data. Second scenario, it's doing 2266 seeks (but counted as range scans) which each look at just 2 pages. So the second one is way better. Plus, many of those seeks will probably be looking at pages which have just been looked at, so on a cold cache it will be an even larger performance benefit. And, the second is more likely to parallelise better, being lots of small operations rather than a large one (which could still be parallelised, but it's more effort).


is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra

DDL generator for a Cassandra keyspace:
DESC KEYSPACE the-Keyspace-name;

CSV generator for a Cassandra table:

COPY TO
DSBulk.


Virtualized SQL Server hits 100% CPU and 0 Active Memory

Check out this interesting post from David Klee: VM Memory Counters Lie for SQL Server VMs

In particular, this quote is very relevant to your situation:

  SQL Server uses memory as a working set I/O buffer, and memory blocks in the SQL Server buffer pool are most commonly used as a read cache. Memory blocks that are repeatedly read only show up in the active counters if that block has been read from or written to in the last sample interval, which is usually 20 seconds. Therefore, SQL Servers that have an appropriate (or too high, but that’s a different story) amount of memory assigned to the VM will have most of the commonly accessed data in memory, but those blocks are rarely accessed in their entirety over that short of a sample window. The VM active memory counters will show a very low active value, but this SQL Server could actually be very busy.

This explains your first issue (related to "active" memory).  In essence, the metric you were looking at isn't reliable, so it doesn't tell us much of substance about the situation.

The other question, related to unresponsiveness, is harder to pin down without seeing wait stats from the time of the outage, or specifics of the recompiled execution plans.

If I were going to speculate, a plausible theory might be that:

queries now running at MAXDOP 2 (instead of 4) take significantly longer to complete
queries are arriving at the server at the same rate as before
so now there are more queries running at once (since they take longer to complete)
the server starts to experience thread starvation (threadpool waits) because there are so many concurrent queries running
if the queries require memory grants, they could also start queuing up for memory (resource_semaphore waits)

All of these things would combine to make your server seem unresponsive.  It might also cause the "active memory" in the VM to drop, since memory pages might be accessed less (due to everything waiting on CPU resources).


You wanted the result must be like this:

 date       | result
2018-09-01   |    0
2018-09-02   |    0
2018-09-03   |    0
2018-09-04   |    0
....
2018-09-28   |    0
2018-09-29   |    2
2018-09-28   |    1

You need to create a table or temporary table for number of days

id
1
2
3
4
...
28
29
30
31

And try to query this:

SELECT
    DATE((select CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()),'-' ,day))) as Date1,
        COUNT(o.order_id)
        as counting from orders as o
        right join numberofdays d
        on d.day = DAY(o.created_at)
        GROUP BY Date1
        ORDER BY Date1

I used RIGHT JOIN:

Hope this might help. Thanks.


Load balancing Availability groups with SQL Server Standard

I kind of like your idea, although it defeats the idea of a primary and second nodes. You should be sure to evaluate if one server can take the load of both, as if one goes down, then one server will have all the load.
You should also be careful, if the activity on one server gets to high in time, it might affect the "seconday" node activities and cause I/O bottlenecks. (It all depends on how your disks are confugred.)But other than that, it could be a good alternative and when your organization gets bigger, you will be able to afford another server to remove some load from your current setup. Just make sure to review carefully your emergency scenarios and to test them well.


Finding integer database columns that may have their data type changed to reduce size

The following stored procedure will help identify those columns. It starts by creating a temporary table that stores the minimum and maximum values that each integer type can hold and then runs a dynamic query to find the minimum and maximum value for every table / column that defines one of those integer types. Once the min/max values have been determined it looks up the smallest type that can hold the value current values and gives a result such as the following:

table_name      column_name         current_type    min_val max_val proposed_type   proposed_min    proposed_max    space_saved
RideLegVetting  RideNumber          int             1       21      tinyint         0               255             2025654
RideLegVetting  LegNumber           int             0       99      tinyint         0               255             2025654

For each column it shows the curent type and min/max values found in the table along with a new proposed type and the min/max value it can hold. Based on the cardinality it also indicates an estimate of the amount of space that will be saved. The stored procecedure is as follows:

CREATE PROCEDURE OptimizeIntSizes AS
BEGIN
    SET NOCOUNT ON
    CREATE TABLE #DataTypeRanges
    (
        name nvarchar(128),
        size tinyint,
        low bigint,
        high bigint
    )
    INSERT INTO #DataTypeRanges VALUES (N'tinyint', 1, 0, 255)
    INSERT INTO #DataTypeRanges VALUES (N'smallint', 2, -32768, 32767)
    INSERT INTO #DataTypeRanges VALUES (N'int', 4, -2147483648, 2147483647)
    INSERT INTO #DataTypeRanges VALUES (N'bigint', 8, -9223372036854775808, 9223372036854775807)
    CREATE TABLE #Results
    (
        table_name nvarchar(128),
        column_name nvarchar(128),
        current_type nvarchar(128),
        min_val bigint,
        max_val bigint,
        proposed_type nvarchar(128),
        proposed_min bigint,
        proposed_max bigint,
        space_saved bigint
    )
    DECLARE @table_name nvarchar(128)
    DECLARE @column_name nvarchar(128)
    DECLARE @current_type nvarchar(128)
    DECLARE @proposed_type nvarchar(128)
    DECLARE @low bigint
    DECLARE @high bigint
    DECLARE @size tinyint
    DECLARE @cardinality bigint
    DECLARE @min_val bigint
    DECLARE @max_val bigint
    DECLARE @proposed_min bigint
    DECLARE @proposed_max bigint
    DECLARE @proposed_size tinyint
    DECLARE @space_saved bigint
    DECLARE @sql nvarchar(max)
    DECLARE @params nvarchar(max)
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE FindCursor CURSOR STATIC FOR
        SELECT tables.name AS table_name, cols.name AS column_name, types.name, dtr.size, dtr.low, dtr.high
        FROM sys.columns cols
            JOIN sys.tables tables ON tables.object_id = cols.object_id
            JOIN sys.types types ON types.system_type_id = cols.system_type_id
            JOIN #DataTypeRanges dtr ON dtr.name COLLATE DATABASE_DEFAULT = types.name COLLATE DATABASE_DEFAULT
    OPEN FindCursor
    FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SELECT @cardinality_out = COUNT(1), @min_val_out = MIN([' + @column_name + ']), @max_val_out = MAX([' + @column_name + ']) FROM [' + @table_name + ']'
        SET @params = N'@cardinality_out bigint OUTPUT, @min_val_out bigint OUTPUT, @max_val_out bigint OUTPUT'
        EXECUTE sp_executesql @sql, @params, @cardinality_out = @cardinality OUTPUT, @min_val_out = @min_val OUTPUT, @max_val_out = @max_val OUTPUT
        SELECT TOP 1 @proposed_type = name, @proposed_size = size, @proposed_min = low, @proposed_max = high
            FROM #DataTypeRanges
            WHERE @min_val >= low  AND @max_val <= high
            ORDER BY size
        IF @proposed_type <> @current_type AND @max_val IS NOT NULL
        BEGIN
            SET @space_saved = (@size - @proposed_size) * @cardinality
            INSERT INTO #Results VALUES (@table_name, @column_name, @current_type, @min_val, @max_val, @proposed_type, @proposed_min, @proposed_max, @space_saved)
        END
        FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    END
    CLOSE FindCursor
    DEALLOCATE FindCursor
    SELECT * FROM #Results ORDER BY space_saved DESC
END


Copy table form MS-SQL server to MySQL, is it possible?

The answer is simple... MySQL WorkBench!  Enables migrations from the following systems.

SQL Server, Access, PostgreSQL, SyBase, etc...
Manage migration projects
Source & Target selection
Object migration
Version upgrades

Here's the link... http://www.mysql.com/products/workbench/migrate/

Price is good too! ;-)

-Tony


Best Approach for Mirroring Data from SQL Server to MySQL

Does the data actually need to be replicated in the same model?

What will you be doing on the MySQL side with the data?

I would strongly consider putting either another database or instance which you control on the SQL Server side to be able to take snapshots on that side so you pull less data down (comparing the snapshot to live data should be quick) and also look at what you want to do with the data on the MySQL side, since it is possible that you actually want to transform the data during the extraction to better facilitate the load.

I think you will find that having a sandbox database or instance which you do control collocated with the vendor database will give you a tremendous amount of power, while still allowing the vendor to operate without interference.

This can also help in reducing the bandwidth (unneeded columns, denormalizing usually increases data size, but not always) and reducing the load times or the complexity of the load process.  A different model on the MySQL side which corresponds to the queries you will actually be running can also improve performance of reporting or even the load.

Having views against the source data can also help mitigate against changes - although you might not be able to use DDL triggers and schemabinding to stop changes from breaking dependencies, you could possibly catch schema changes when the extraction fails instead of after the extraction has neglected to bring down some new columns.


Is there a way to test whether DELETE will fail due to constraints?

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
  DELETE #1;
  DELETE #2;
  DELETE #3;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
END CATCH

If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.

BEGIN TRY
  DELETE #1;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH

BEGIN TRY
  DELETE #2;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH


In Microsoft SQL Server 2008, syntax generates the error "The Parallel Data Warehouse (PDW) features are not enabled."

The Parallel Data Warehouse (PDW) features are not enabled.

This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN:

Windowing function support was considerably extended in 2012, compared with the basic implementation available starting with SQL Server 2005. The extensions were made available in Parallel Data Warehouse before being incorporated in the box product. Because the various editions share a common code-base, misleading error messages like this are possible.

If you are interested, the call stack when the aggregate is verified by the parser is shown below. Because the aggregate has an OVER clause with ORDER BY, a check for PDW is issued:

This check immediately fails with a parser error:

Luckily, you do not need an windowed aggregate that supports ORDER BY framing to solve your code problem.


Does any kind of constraint in a database have a unique constraint name?

I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters.

Are you saying constraint names can't be given at table creation time? That isn't correct:

create table foo(id integer primary key);

create table bar(id integer constraint pk_bar primary key);

select table_name, constraint_name from user_constraints;

TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007856
BAR        | PK_BAR

dbfiddle here

  However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

It's exactly the same for NOT NULL constraints:

create table foo(id integer not null);

create table bar(id integer constraint nn_bar_id not null);

select table_name, constraint_name from user_constraints;

TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007860
BAR        | NN_BAR_ID

dbfiddle here

  Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

Of course, just like any other constraint:

alter table bar drop constraint nn_bar_id;

dbfiddle here


Adding warning with sp_add_alert doesn't work with "Greater Than" and SQL Server 2014

after research, I had found the answer. The Problem is the @performance_condition, my example will not work. But if I add a "SQLServer:" at the beginning of the Condition, like this:

N'SQLServer:Replication Dist.|Dist:Delivered Cmds/sec|MyFancyReplication|>|1000'

This is the Key to add custom warnings to Sql Server 2014. I believe this is a bug with the new SqlServer2k14.

Regards
Björn


In the first example, you end up with 1 + 2 IS NOT NULL - which, it is not, 3 is certainly not null. 3 IS NOT NULL evaluates to true, casting it to an int makes it 1 again. Hopefully this makes it clearer:

testdb=# select 1 is not null;
 ?column?
----------
 t
(1 row)

testdb=# select 1 is not null::int;
 int4
------
    1
(1 row)

testdb=# select 1 is not null::int + 2;
 ?column?
----------
        3
(1 row)

testdb=# select 1 is not null::int + 2 is not null;
 ?column?
----------
 t
(1 row)

testdb=# select 1 is not null::int + 2 is not null::int;
 int4
------
    1
(1 row)


Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

The 3rd query has an OR which most likely means that a scan is happening. The scan will be blocked by RegionID = 1

If you run this, it should run OK.

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 4

SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3

The alternative is that you still have query 1 transaction open...

Edit:

I can't reproduce it (SQL Server 2008 R2 x64 developer). I get an index seek on query 3.

However, in my first iteration I forgot a PRIMARY KEY on my table and query1 blocked query2 because it was a table scan. Which backs up my answer above mostly

Edit 2:

I can't get either Query 2 or Query 3 to block each other (no matter which runs first)

After comments:

A scan places an XLOCK per scanned row
OR is often non-SARGable: AND is OK. (OR has some optimisations) though)


How to upgrade MySQL to a New version..?

There's a PPA that someone has created which includes MySQL 5.5.  Since PPA's are community-contributed, the usual warnings about using with caution apply.

There is a guide in the MySQL docs for some standard practices to follow when upgrading from one version level to another.

Edit:

Here is some information from the mysql_upgrade manual page that should give you an idea of why it's important. This is in response to your comment.

  mysql_upgrade examines all tables in all databases for
  incompatibilities with the current version of MySQL Server.
  mysql_upgrade also upgrades the system tables so that you can take
  advantage of new privileges or capabilities that might have been
  added.

  mysql_upgrade should be executed each time you upgrade MySQL. It
  supersedes the older mysql_fix_privilege_tables script, which has been
  removed in MySQL 5.5.

  If mysql_upgrade finds that a table has a possible incompatibility, it
  performs a table check and, if problems are found, attempts a table
  repair.


Can a session variable be set to the contents of a function parameter using SET SESSION?

Use set_config()

CREATE FUNCTION my_function(my_param INT)
RETURNS VOID AS $$
BEGIN
    perform set_config('myapp.myvar', my_param::text, true);
END
$$
LANGUAGE plpgsql;


Question 1

Does the DML operations committed by db2 during the replication process gets included in its own bin-log?

Answer to Question 1

Yes it will, provided you have this in /etc/my.cnf on both db1 and db2

[mysqld]
log-slave-updates

If you do not have this, add it and restart mysql

Question 2

Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?

Answer to Question 2

Yes. Make sure the clocks on both DB servers are synchronized

Question 3

What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?

Answer to Question 3

In MySQL Replication, the IO Thread of a Slave will read its Master's bin-log entries and store them in a FIFO queue. For each relay log in a slave, when every entry in the currently processed relay is executed it is rotated out and discarded. If relay logs are piling up, this quickly indicates that the SQL thread died because of any SQL error. Just do SHOW SLAVE STATUS\G to find out what stopped the SQL thread. The IO Thread would conitnue collecting completed SQL statements from its Master.

Question 4

How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?

Answer to Question 4

When you do SHOW SLAVE STATUS\G, look for the following lines:

Master_Log_File : The latest binary log whose most recently command was copied to the Slave
Read_Master_Log_Pos : The latest position of the latest binary log whose most recently command was copied to the Slave
Relay_Master_Log_File : The latest binary log whose most recently command was executed on the Slave
Exec_Master_Log_Pos : The latest binary log whose most recently command was executed on the Slave
Relay_Log_Space : The sum total (in bytes) of all relay logs. By default, each relay log is the default size of a binary log (1G). If Relay_Log_Space starts to significant exceed 1G, this indicates one of two things:

SQL thread died due to SQL Error
SQL thread is busy with a long-running query

Question 4.1

If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?

Answer to Question 4.1

You must have log-slave-updates available on both DB servers in order to have an audit trail that the SQL executed on on DB server made it to the other. If you don't, you would have to do your due diligence to compare the data explicitly. Such ways would include:

Running CHECKSUM TABLE on every table you have in both DB servers to compare their contents.
Using pt-table-checksum, which is an automated version of running CHECKSUM TABLE between Master and one or more Slaves

You need not worry about infinite circular replication unless you are dealing with more that two masters. There have been rare times when someone with, let's say four Masters, removes one of the four servers from circular rep cluster. Let's suppose the the server_id is 13. It is remotely, but still, possible for binary log entries whose server_id belongs to the server that removed to be inside the relay logs on other servers. Only in such a scenario would you worry about infinite circular replication.

To circumvent such situations, MySQL 5.5 has a new option for the CHANGE MASTER TO command called IGNORE_SERVER_IDS. You would do the following to repair things on all the remaining servers:

STOP SLAVE;
CHANGE MASTER TO IGNORE_SERVER_IDS = (13);
START SLAVE;

In fact, here is what the MySQL Documentation says on this:

  IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from
  the corresponding servers are ignored, with the exception of log
  rotation and deletion events, which are still recorded in the relay
  log.

  In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more
  than once. Thus, this option is useful in circular replication when
  one of the servers in the circle is removed. Suppose that you have a
  circular replication setup with 4 servers, having server IDs 1, 2, 3,
  and 4, and server 3 fails. When bridging the gap by starting
  replication from server 2 to server 4, you can include
  IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you
  issue on server 4 to tell it to use server 2 as its master instead of
  server 3. Doing so causes it to ignore and not to propagate any
  statements that originated with the server that is no longer in use.

Question 5

On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?

Answer to Question 5

Whichever form is presented. Here is what I mean: The raw form would usually not include the auto_increment column expressed explicitly. On the other hand, it you import a mysqldump into a DB server with binary logging, the rows being inserted would explicitly be given. Either version of INSERT would be allowed execution in mysqld. In like fashion, either version of INSERT would be recorded AS IS...


I've seen many uses when you need to project 'missing data'. Eg. you have a time series (an access log for instance) and you want to show the number of hits per day for past 30 days (think analytics dashboard). If you do a select count(...) from ... group by day you will get the count for every day, but the result will only have a row for each day you actually had at least one access. On the other hand if you first project a table of days from your numbers table (select dateadd(day, -number, today) as day from numbers) and then you left join with the counts (or outer apply, whatever you fancy) then you will get a result that has 0 for count for the days you had no access. This is just one example. Of course, one may argue that the presentation layer of your dashboard could handle the missing days and just show a 0 instead, but some tools (eg. SSRS) will simply not be able to handle this.
Other examples I've seen used similar time series tricks (date/time +/- number) to do all sort of window calculations. In general, whenever in an imperative language you would use a for loop with a well known number of iterations, the declarative and set nature of SQL can use a trick based on a numbers table.
BTW, I feel the need to call out the fact that even though using a numbers table it feels like imperative procedural execution, don't fall into the fallacy of assuming it is imperative. Let me give an example:
int x;
for (int i=0;i<1000000;++i)
  x = i;
printf("%d",x);

This program will output 999999, that is pretty much guaranteed.
Lets try the same in SQL Server, using a number table. First create a table of 1,000,000 numbers:
create table numbers (number int not null primary key);
go

declare @i int = 0
    , @j int = 0;

set nocount on;
begin transaction
while @i < 1000
begin
    set @j = 0;
    while @j < 1000
    begin
        insert into numbers (number)
            values (@j*1000+@i);
        set @j += 1;
    end
    commit;
    raiserror (N'Inserted %d*1000', 0, 0, @i)
    begin transaction;
    set @i += 1;
end
commit
go

Now lets do the 'for loop':
declare @x int;
select @x = number
from numbers with(nolock);
select @x as [@x];

The result is:
@x
-----------
88698

If you're now having a WTF moment (after all number is the clustered primary key!), the trick is called allocation order scan and I did not insert @j*1000+@i by accident... You could had also venture a guess and say the result is because parallelism and that sometimes may be the correct answer.
There are many trolls under this bridge and I mentioned some in On SQL Server boolean operator short-circuit and T-SQL functions do no imply a certain order of execution


Update all foreign keys to a different primary key

Probably not interesting for the OP but I'll give it a shot anyhow. Given the current tables:

create table Businesses
( id int not null primary key
, name varchar(20) not null) engine=innodb;

create table Employees
( id int not null primary key
, name varchar(20) not null
, business int not null) engine=innodb;

insert into Businesses (id, name)
values (3,'Planet Express'),(7,'Planet Express'),(11,'Whatever');

insert into Employees (id, name, business)
values (999,'Fry',3),(666,'Bender',7),(333,'Bob',11);

To fix the immediate problem we can update a join against a derived table like:

update Employees e
join (
    select min(id) as min_id, max(id) as max_id, name
    from Businesses
    group by name having count(1) = 2
) as b on e.business = b.max_id
    set e.business = b.min_id;

select * from Employees;
+-----+--------+----------+
| id  | name   | business |
+-----+--------+----------+
| 333 | Bob    |       11 |
| 666 | Bender |        3 |
| 999 | Fry    |        3 |
+-----+--------+----------+

and then delete the duplicate business. Note that I assume that there are exactly two rows involved in each duplicate.

For the long run I would suggest several things. Names like id, name, etc are to vague, name them something like business_id and use that name throughout the model.

If the name of a Businesses is supposed to be unique, add a constraint that ensures this. Something like:

create table Businesses
( business_id int not null primary key
, business_name varchar(20) not null unique
) engine=innodb;

create table Employees
( employee_id int not null primary key
, employee_name varchar(20) not null
, business_id int not null
,    constraint fk_business foreign key (business_id)
                            references Businesses (business_id)
) engine=innodb;


Query returning data from two rows of a table in a single row

You can use an aggregate function and a CASE statement to get the data into this format:

select id,
  max(case when application ='subapp' then application end) application,
  max(case when application ='subapp' then othervalue end) othervalue,
  max(case when application ='mainapp' then othervalue end) othervalue2
from yourtable
group by id;

See SQL Fiddle with demo

Result:

| ID | APPLICATION | OTHERVALUE | OTHERVALUE2 |
-----------------------------------------------
|  1 |      subapp |  SubvalueA |  MainValue1 |
|  2 |      subapp |  SubvalueB |  MainValue1 |
|  3 |      subapp |  SubvalueA |  MainValue2 |


Proper use of varbinary type in MS SQL Server

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me.

You know what smells funnier than this approach? That line of "reasoning". The old "this is how it's always been done" is simply a means of avoiding thinking about it and discussing it. And that alone should raise a red flag, even if the code is found to be the best approach. I've been told the same thing before and it was for the same sad underlying reason: that approach had been taken for an obsolete reason (such as an older version of SQL Server didn't have a particular feature, but we were using a newer version that did have that feature). Very frustrating.

  Doesn't a table valued type work better here? More readable, performance, less error prone, etc...

Assuming that you are using SQL Server version 2008 or newer, then the answer is generally YES to all of that. I mean, the current code is a somewhat clever-ish approach to avoiding a string split operation. But, a TVP can be strongly-typed so no munging the int values in the app layer into a byte[] just so that it can be unpacked in a WHILE loop here. A single set-based UPDATE statement will be much better for performance than N number of UPDATEs based on how many IDs are in that list (the current code creates, and commits) 1 transaction per each UPDATE statement since the WHILE loop is not wrapped in an explicit transaction. Hence, 20 IDs to update = 20 transactions in the current approach. This is part of why the single, set-based UPDATE is more efficient.

Just be sure to implement the full-streaming approach which means: do not dump the values from a collection into a DataTable! Doing so, which is unfortunately what you find in most examples, is a needless waste of time, memory, and CPU. Instead, create a method that implements IEnumerable, accepts the collection of UserIDs, and returns IEnumerable<SqlDataRecord>. Then, use that method as the "value" for the SqlParameter that is the TVP. In that method, cycle through the collection, and per each element, call yield return;.

I have some example code on StackOverflow in my answer to the following question:

Pass Dictionary to Stored Procedure T-SQL

Please note that I have heard, though not yet tested, that on the more recent versions of SQL Server (2014 or newer) that there might be additional performance gains by using in-memory OLTP for the User-Defined Table Type.

Also, since you mentioned this as part of a desire for "more readable code", I would suggest using meaningful parameter and variable names :-). And yes, I do realize that this is probably not your code, but just thought I would mention it. Also, I would get rid of DECLARE @tblTmp as it isn't be used, but again, I realize that this might be a part of a larger example that you redacted.


Create a partition for a temporal table based on a period

I mean you can... but I don't think you necessarily should...

Given a setup of the form...

use [master]
create database ynot;
alter authorization on database ::ynot to sa;
go
use ynot
go
create table dbo.foo (
     id         int identity primary key
    ,info       varchar(100)
    ,StartTimeV datetime2(0) generated always as row start
    ,EndTimeV   datetime2(0) generated always as row end
    ,period for system_time (StartTimeV, EndTimeV)
)
with (system_versioning = on (history_table = dbo.foo_hist));

You can...

Remove the system versioning
Apply partitioning to the underlying history table
Reapply system versioning

...in this way

alter table dbo.foo set (system_versioning = off);
go
create partition function pf_months(datetime2(0))
    as range right for values (
         '20190501'
        ,'20190601'
        ,'20190701'
        ,'20190801'
        ,'20190901'
        ,'20191001'
        ,'20191101'
        ,'20191201'
);
go
create partition scheme ps_months
    as partition pf_months
    all to ([PRIMARY]);
go
alter table dbo.foo set (system_versioning = off);
go
drop table dbo.foo_hist;
go
create table dbo.foo_hist (
    id         int          not null,
    info       varchar(100) null,
    StartTimeV datetime2(0) not null,
    EndTimeV   datetime2(0) not null
)
on ps_months([StartTimeV]);
go
alter table dbo.foo
    set (system_versioning = on (history_table = dbo.foo_hist));
go

It works as-is, although I won't speak to interoperability, scaling, or storage management concerns at this time.

insert foo (info )
values ('stuff')
      ,('things');
go
update foo set
    info = 'objects'
where id = 1;

delete foo where id = 2;

select * from foo for system_time all;
go

Demo cleanup

use [master]
drop database ynot;
go


Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring

As you have a dedicated server, there is no reason not to use native MySQL command to issue queries directly. Avoiding phpMyAdmin means avoiding one more layer between you and database while trying to figure out what went wrong.
My first thought is - are you actually trying "select * from table" on a 45 million row table? That could cause all kinds of problems.

1) Try "select count(*) from table" first. See if you get 0 or 45M.

2) See if partition with your data is large enough. Yes, this kind of omission happens.

3) Try using MyISAM instead of InnoDB to reveal possible server misconfiguration issue.

Perhaps it makes sense to use our battle-proven commercial database migration tool Full Convert Enterprise for your data conversion? It runs on Windows only, though. Quickest path


This has nothing to do with sp_executesql. If you replace the exec call with PRINT @bulkinsert; - an easy way to perform local debugging - you will see this output:

bulk insert product from 'c:\bcp\textfiles\product.txt'
  with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")

Put that in its own window, and you will see the exact same issue as the error you received is now highlighted by IntelliSense (note the red squiggly under the "\t" and the explanation in the tooltip if you hover over it):

The issue is you are using a double-quote (") as a string delimiter - this is not valid in T-SQL. You need to use single-quotes('') to delimit strings, but because the statement itself is embedded in another string, you need to double them up in order to escape them. So:

... @filepath + N''' with (FIRSTROW = 2,
    FIELDTERMINATOR = ''\t'', rowterminator = ''\n'');';

In fact, in the answer you cite, those are single quotes, not double quotes - perhaps a transcription error.


Browser service in sql server clustering

There really isn't a short answer because there are a few hidden questions in the question.

A few thoughts to help here:

1.) The Browser service is not cluster aware, so it generally would be just running on each node. The browser is really used to handle incoming connections to a SQL instance. When you don't have a fixed port, are using named instances and in other situations the browser handles the "finding" of the instance a client desires to connect to. So if you have it running on the active node and it is being used to direct connections, I would make sure it is automatic and running on each node.

2.) That said the browser shouldn't prevent DLLs from being found or take any part in preventing or allowing a failover. So the issue you are having with failing over is most likely not related to the browser but something else. Instead you should be looking at things like - Have you failed over before? Is this a new install of a second node? Have there been any required restarts missed on that node? Did the install throw up any errors? Are there issues in the clustering logs? Can you post the exact error you are receiving? Have you searched for that exact error?


When and how would MyISAM concurrent insert = always be a problem?

The only immediate harm I can think of is dealing with contention from the UPDATE.

Normal INSERT, UPDATE, and DELETE commands execute a full table lock. Doing ON DUPLICATE KEY UPDATE should work the same.

In addition, what if the row's size changes to the point that the row needs to be bigger that the row's original allocation because of increasing the length of a VARCHAR column? That would require writing the row to a new part of the MyISAM table.

The only exception to this concern is when the MyISAM table

has no TEXT or VARCHAR fields
has no TEXT fields, has VARCHAR fields but ROW_FORMAT=Fixed

The MyISAM table would probably have some contention issues if you have TEXT fields because of the lack of length control or possible overflow of data to handle no matter what the table's ROW_FORMAT is set to.


Bad data, bad data types, and bad formatting aside, if you're getting a month of data for present and previous year, you don't need to have four variables; one will do. But it should be a date, not a string. I also recommend avoiding BETWEEN since it only works in an extremely narrow set of circumstances.

DECLARE @startdate date = '20170101';

DECLARE @HoldingPattern TABLE
(
  salesman varchar(100),
  saledate date
);

-- let's ignore the illogical dates
INSERT @HoldingPattern (salesman, saledate) VALUES
 ('BOB', '20170123'),('BOB', '20170130'),('BOB', '20170124'),
 ('BOB', '20170130'),('BOB', '20170130'),('BOB', '20170130'),
 /*('BOB', '20170230'),*/('BOB', '20170227'),('BOB', '20170331'),
 ('BOB', '20170327'),('BOB', '20160123'),('BOB', '20160130'),
 ('BOB', '20160124'),('BOB', '20160130'),('BOB', '20160330'),
 ('BOB', '20160330'),('BOB', '20160330'),('BOB', '20160227'),
 /*('BOB', '20160231'),*/('BOB', '20160227');

SELECT
  Salesman,
  [2017 Data] = COUNT(CASE WHEN saledate >= @startdate
    AND saledate < DATEADD(MONTH, 1, @startdate) THEN 1 END),
  [2016 Data] = COUNT(CASE WHEN saledate >= DATEADD(YEAR, -1, @startdate)
    AND saledate < DATEADD(YEAR, -1, DATEADD(MONTH, 1, @startdate)) THEN 1 END)
FROM @HoldingPattern
GROUP BY Salesman;

Result:

Salesman    2017 Data    2016 Data
--------    ---------    ---------
BOB         6            4

Further reading:

#BackToBasics : Dating Responsibly
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
What do BETWEEN and the devil have in common?


Transactional Replication breaks when schema is changed in Publisher

From Make Schema Changes on Publication Databases:

Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.


Can mariaDB contain indices only with data coming from remote tables?

No: local instance can't know when remote tables are updated.

Also, I suggest you use SPIDER instead of CONNECT.


How to design tables for airbnb style booking system

Use timestamp ranges for booking availability. There is good documentation in official PostgreSQL docs.

You may find this lecture helpful:
Range Types and Temporal: Past, Present, and Future

And my blog post about generating timestamp ranges could also be helpful:
Generating ranges in PostgreSQL


Cannot shutdown old postmaster when upgrading to Postgres 9.2

The postmaster.pid should be inside the previous version's usr/local/var folder. Simply renaming this file should solve the problem.


Are you sure this is an RMAN backup set? Maybe it is just a dump file made while the database is online. For 9i you only had exp and not the newer expdp available to make dumps so you could try to import it with imp and see what the output is:

imp system/password@abc FULL=Y FILE=file_decrypted_379299.bak


What does 'exclude rows that do not contain values' checkbox do in SQL Profiler?

It's basically this

WHERE .. AND TheValue NOT NULL

Like/Not like filters only works on values that exist for that event.
No value is always true and passes the filter: this checkbox removes that

For example:

You want to see SQL:BatchStarting events
You filter on Duration

Duration is not valid for SQL:BatchStarting, so the filter is ignored


Diagnosing a maintenance plan that's ballooned in execution time

Question you asked is a very generic one and people working with database have this challenge all the time.  There is no one thing you can do will give you the answer right away.  Your case is more difficult than others as your databases are hosted by third party.

Troubleshooting long running process is a science and art at the same time. Everybody has their own way and it is not unique.

First you want to make sure your reorganize process still doing the same work.  Which is not easy in this case because few factors:

Number of index.
Size of each index.
Fragmentation level of each index.

Once you check that out and confident that the amount of work is same, you can use method called ‘waits and queues’ to find out why the duration increased?

A SQL server process can be in three states.  Running, Runnable and Suspended.  I suggest you read this more here.  There is nice method for capturing wait stats for one single operation.

Once you know what resources your process was waiting for and how long (during the whole duration) the waits were, you should be able to quantify the run time. You can be blocked but underneath that is also a wait because some other process is holding the resource that you need.

As you are in a hosting environment and if you have baseline number of your hardware you should check those out too, to make sure nothing changed there. I understand you did check with them but I suggest you confirm what they are saying.

Few things to check:

storage performance
Network


Redundant fields in ORDER BY for efficient sorting?

This is actually pretty straightforward. When performing a query, SQL Server first looks to identify the rows to be returned. If there are WHERE clause elements, those are checked before the system even considers using an index used for the ORDER BY.

This makes perfect sense if you consider the possibilities.

No index on anything in the WHERE clause -- must perform table or clustered index scan
Index on one element in the WHERE clause -- Scan all rows as filtered by the one element for other matches
Index on all elements in the WHERE clause -- Select all rows based on the index. Must look up data in record.
Index on all elements in the WHERE clause, and INCLUDE of the data elements -- Select all rows based on the index. Use the data elements in the INCLUDE statement.
Index on all elements in the WHERE clause plus all data elements -- Select all rows based on the index, and use the data elements embedded in the index.
Index on the data elements only -- If there is a clustered index, looking up data on that index will be most efficient. Unknown if the query optimizer would scan the data element index to try to make the data retrieval more efficiend, but it is doubtful.

Basically, your best index would be:

CREATE INDEX MyIndex ON MyTable (Key1, Key2, Sort1, Sort2)

The data would be searched on the Key information (which is what an index is designed for), then use the additional information, which is already sorted in the index, for the output.

Edited


For encrypting data files at rest, you can use a third party product called NetLib Encryptionizer for SQL Server that basically does TDE through a filter driver.

For encrypting backups, you can use any number of third-party backup products. The most popular ones in alphabetical order are Idera SQL Safe, Quest Litespeed, and Red Gate SQL Backup - all of which can encrypt backups on any version/edition of SQL Server that they support. I've also seen cheaper ones, but I'd just caution you that you should only use backup products that have 24/7 support available. Don't play around with restores.

I have no affiliation with any of those products. (I used to work for Quest.)


Backup VLDB replicated database in SQL Server?

I don't think this is a very good idea because replication is not just as bullet proof as a sound backup strategy. A blocking situation could stop replication jobs and you could easily be out of sync for some time. Or maybe a network card will fail and replication will be dead for a period. This means you won't be up to date for some time, and also, you will be without a good backup of the main, updated site.

Similar technologies, that will deliver updates to a copy of the main db, are mirroring and log shipping, but they both rely on full recovery model and log backups.

I'd suggest that you'd better study compression for your backups. Starting from SQL 2008 (Enterprise) and SQL 2008 R2 (from Standard) you have the option of compressing the backups using native SQL compression. In addition you should investigate data compression (in place compression for tables and indexes data). These will give you some help by making the backups smaller.

There are also 3rd party backup tools that will be able to help with compression if you need more complex solutions. See this site for question about how to make SQL Server backups as small as possible.

Also, you might think about moving old data from big tables to a history database, and play with connection strings in your app.

You could also try to make the backups locally, in a folder on the main server, and then just copy it to the tape (maybe backup to disk + direct copy will take less than backup to tape).

Sorry for the emphasis: YOU JUST WON'T BE SAFE WITHOUT TESTED PRODUCTION BACKUPS!


is shared storage safe in Postgres if all servers are read-only?

In the answer you linked to, Craig says:

  It's possibly practical to do it with minor changes to PostgreSQL( if all the servers are read-only, but I haven't investigated it as it's a pretty uninteresting use-case.

(emphasis mine).

The problem is, there is no way to start a PostgreSQL server and tell it "you are only allowed to look at this data directory, you are not allowed to write anything at all". Even a PostgreSQL server operating as a "hot standby" takes the liberty of writing arbitrary files inside its data directory (e.g. postmaster.pid, temporary files used for on-disk sorts, rewriting hint bits, and so on). If two or more servers were coaxed into operating in hot standby mode against the same data directory, chaos could ensue.

There is simply no "look but don't touch" mode for a Postgres server, so the answer to:

  What about if all [Postgres server instances operating against the same shared data directory] are read-only?

is: not possible today without changing Postgres to implement a true read-only mode.


Why is mixing column collations in a single database considered bad?

The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me.

You are entirely correct here.

  Why is it considered such a serious error by some?

For the same reason that you will often hear / read that "you should never use:"

CURSORs
GOTO statements
SQLCLR
WITH (NOLOCK)
etc, etc, etc

Some features / options / technologies are more complicated than others and generally require more knowledge by the user because the chances of getting into trouble when using it are much greater than the chances of not having any problems. So, it is easier to have generalized rules against such things for the general population. In fact, when writing up "Coding Standards" at work, I will always have a rule to never use CURSORs, yet I use them myself because I know both "when" to use them and "how" to use them effectively. But folks who only occasionally write queries shouldn't be expected to know that. This is also similar to "don't edit the Registry unless you absolutely know what you are doing", or rules that we make as parents for our (very young) children where we need to tell them not to do something simply because they are not capable of traversing the complexities of when it is ok to do a particular thing or how to go about doing it.

In the case of Collations, this is a very complex and confusing topic, and you can run into both hard-errors (these are an issue but less of an issue since they are obvious and hence easy enough to fix) and into "odd" behavior where it is hard to explain why things are acting the way that they are (why some items are filtered, or not filtered, outside of expectations, OR why sorting is acting outside of expectations). And sadly, there seems to be a rather large amount of misinformation floating around which furthers the mass confusion. I am actually working on a project to greatly increase the general knowledge of Collations and encodings, etc and hopefully counteract the misinformation and myths, but not yet ready to release it (when done I will update this with a link to it).

For Collation, you need to use what makes the most sense for the business case. The notion of not mixing Collations in a table or database is a default approach, but if you look at the Collations used for the various columns of the system catalog views, you will notice a variety of Collations being used. So I agree with the main quote in the question that IF the Collations are going to be different, it should be intentional, but there is nothing inherently wrong with it.

Regarding this from the question (emphasis added):

  While configuring the Octopus Deploy Server, the setup fails with a FATAL error during the initialization of the OctopusServer-instance. The article related to the error-message does not explain why this is a requirement

I checked the linked documentation page and it does indeed explain why it is a requirement. I have copied the pertinent info from that documentation below:

  You must ensure you also change the collation of all objects in the Octopus Database, otherwise errors can occur when modifying the database during Octopus version upgrades.  New objects created will use the updated collation, and when attempting to (for example) perform SQL joins between these and existing objects using the original collation, collation mis-match errors may occur.

They are saying that their code, in the Octopus database, has JOINs between string columns and could likely have new code introduced in a future upgrade that has additional JOINs on new string columns. New columns, either via CREATE TABLE or ALTER TABLE ... ADD, will be assigned the default Collation of the database if the COLLATE keyword was not specified for the new string column(s). And JOINs between string columns that do not have the same Collation will generate a Collation mismatch error. They also seem to be allowing the user to choose their own Collation (possibly to accommodate different locales) since they say at the top that the only requirement is that the Collation be case-insensitive. And since the Collation of the database that their code lives in isn't guaranteed to always be the same, they can't use the COLLATE keyword to force the same Collation across all new string columns (well, they technically can, but that requires Dynamic SQL so not easy to deal with when generating update scripts). If they were able to use the COLLATE keyword, then they could get away with having the Database's default Collation be different than the string columns. That would avoid the hard "Collation mismatch" errors, but would still leave open the possibility of comparison operations involving one of those string columns and a string literal or variable resulting in "odd" behavior as it would use the column's Collation and not the Database's Collation. Of course, that could very well be expected behavior. But since this is a 3rd party app, the behavior should be what they intended rather than a 50 / 50 chance between a) what the user wanted (or didn't object to) and b) what the user considers a bug (and then wastes the vendor's support time on a wild goose chase and/or blogs about how their software is buggy).


Why is my SP_HELPINDEX showing a lot of indexes?

The indexes you are seeing are hypothetical indexes created by Database Engine Tuning Advisor.

DTA leaves a bit of a mess behind if it crashes or if you kill it because it runs too long.

They all include _dta_ in the name and you can see the hypothetical in the description.

Try the query from Brent as you will most likely have some of these on other tables too.

WITH hi AS (
SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[index_id], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
SELECT [hi].[Table] ,
       [hi].[Index_or_Statistics] ,
       CASE [hi].[Type]
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
       END AS [T-SQL Drop Command]
FROM [hi]

You should delete those, they aren't used anyway

EDIT:
Not sure why but the OP had issues with the query from Brent and this select worked on his environment

SELECT t2.NAME AS table_name
    ,t1.NAME AS index_name
FROM sys.indexes t1
INNER JOIN sys.tables t2 ON t2.object_id = t1.object_id
WHERE t2.NAME = 'MyTable'
    AND t1.NAME LIKE '%_dta_%'
    AND t1.is_hypothetical = 1


2 tables with foreign keys to 3rd table with primary key

Why shouldn't it be possible? Suppose table1 holds all countries, table2 all cities and table3 has another foreign key to a table with all international organisations. They both need a foreign key to a country. You can even have a table4 that holds all regions. It also needs a foreign key to table1.


Why does Oracle still not support Multi-Row insert?

The plain answer is that not all RDBMSes support all of the various ANSI standards. Oracle just happens to not support this particular part of it.

Oracle does however support INSERT ALL INTO.

By the way, an example of insert into dual is terribad - You can break older versions of Oracle by doing an INSERT into DUAL.


How to add new columns to MySQL database retaining existing data?

Answering for main question -

The Best way, use any kinds of database modelling software.
      Including    MySQL Workbench. Where all changes first do on schema
      diagram, than    sync with any number of database. Many of them
      prepare for You clear    SQL scrip which You can run independently. Very good tools - DBSchema.
Other possible way - use any kind of database synchronisation
software, such as:

Next programs (not only) has functional for sync structure of databases

SQLyog and
MySQL Workbench
Navicat


How to select sub-object with given keys from JSONB?

You can remove given keys with the - operator (the inverse of what you have in mind):

jsonb - text[] → jsonb
Deletes all matching keys or array elements from the left operand.
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}

But I wouldn't know of a built-in function or operator doing what you ask for in the current Postgres 13.
Remarkably, it's the other way round with  SQL SELECT statements. There you get a positive list with SELECT a,d,e FROM tbl, but cannot simply get "all columns except [a,d,e]" like you can get "all keys except [a,d,e]" from a JSON object with the above operator. I would love to have the complementary feature for each.
Also can't think of an easy way with the SQL/JSON path language (Postgres 12+).
Workaround for jsonb
You can create a simple function like:
CREATE OR REPLACE FUNCTION f_jsonb_select_keys(_js jsonb, _keys text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT jsonb_object_agg(t.key, t.value)
FROM   jsonb_each(_js) t
WHERE  t.key = ANY (_keys);
$func$;

db<>fiddle here
Call:
SELECT f_jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

Returns:
{"a":42,"d":97}

You get NULL for NULL or empty input for either argument (as demonstrated in the fiddle). So I declared the function STRICT (a.k.a. RETURNS NULL ON NULL INPUT), even though the nested function jsonb_object_agg() is not STRICT. That could mess with function inlining, but since this one cannot be inlined anyway (containing an aggregate function), we might as well. See:

Function executes faster without STRICT modifier?
Can declaring function volatility IMMUTABLE harm performance?

IMMUTABLE is only kind of true. Keep reading.
Order of keys?
jsonb does not preserve the order of keys (keys are ordered in deterministic fashion internally), so we need not bother about the order of input rows in the aggregation. Except when there can be duplicates, then the latest copy from the input prevails. The manual:

Because the json type stores an exact copy of the input text, it
will preserve semantically-insignificant white space between tokens,
as well as the order of keys within JSON objects. Also, if a JSON
object within the value contains the same key more than once, all the
key/value pairs are kept. (The processing functions consider the last
value as the operative one.) By contrast, jsonb does not preserve
white space, does not preserve the order of object keys, and does not
keep duplicate object keys. If duplicate keys are specified in the
input, only the last value is kept.

Meaning, if there can be duplicate keys in the input, the order of rows is significant. My function does not explicitly order rows, so the result would not strictly be IMMUTABLE. But the input comes directly from jsonb_each() and there cannot be duplicate keys in jsonb (unlike json). Possible duplicates in the input array of keys are irrelevant for the = ANY construct. So duplicates can never happen. IMMUTABLE after all.
Again, this would prohibit function inlining - if it could be inlined to begin with.
Equivalent for json
For completeness: the same for json instead of jsonb:
The first variant does not try to preserve the given order of keys, and it eliminates possible duplicates in the input array of keys (but not possible duplicates from the json input!):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
  RETURNS json
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value)
FROM   json_each(_js) t
WHERE  key = ANY (_keys);
$func$;

The second variant preserves the given order of keys and keeps all possible duplicates. (If the same key is 2x in the json input, and 3x in the input array of keys, you get it 6x in the result.):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
  RETURNS json
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value ORDER BY ord)
FROM   unnest(_keys) WITH ORDINALITY k(key, ord)
JOIN   json_each(_js) t USING (key);
$func$;

About WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?

I made the json functions STABLE because json_object_agg() is only STABLE as opposed to jsonb_object_agg(), which is IMMUTABLE.

You could use json_build_object ('a', js->'a', 'd', js->'d', 'e', js->'e') but that would include all keys, with a NULL value if not found. Not exactly your request. And you couldn't tell the difference between a missing key and the same key with an actual NULL value.


Are there performance benefits to turning off archivelogging? Is it wise?

The normal answer is: "Use archivelog mode". Unless you are taking cold backups you will not be able to recover from them without the log files archived during the backup.

If you are taking hourly cold backups and are running in noarchivelog mode, you risk losing an hours worth of committed transactions (which you don't mind). The purpose of archiving the logs is so that you can recover all (or nearly all) of the committed transactions after a media failure.

Whichever mode you choose Oracle still writes logs to the online redo log files - in archivelog mode you enable the archiving of those files as they fill up.

Performance benefits will not normally be significant - here is a list of special cases where there is a difference in redo generation.

Seeing as your 'backups' are ZFS snapshots, you do not need log files for restoring the database - reverting to a snapshot will be similar to recovery after a power loss and will use the snapshotted online redo logs to roll forward to a consistent state. In that specific case, and seeing that you do not mind losing recent data, noarchivelog mode may be acceptable to you.

Not having the standby will still increase your recovery time in the case of hardware failure on the primary however - you may wish to consider that.


(Expanding on my comment on the question.)

Without a unique constraint on the combination of AreaId and ParameterTypeId, the given code is broken because @UpdatedId = target.Id will only ever record a single row Id.

Unless you tell it so, SQL Server can't implicitly know the possible states of the data. Either the constraint should be enforced, or if multiple rows are valid, the code will need to be changed to use a different mechanism to output the Id values.

Because of the possibility that the scan operator will come across multiple matching rows, the query must eager spool all the matches for Halloween protection. As indicated in the comments, the constraint is valid, so adding it will not only change the plan from a scan to a seek, but also eliminate the need for the table spool, as SQL Server will know there is going to be either 0 or 1 rows returned from the seek operator.


How to determine when REORG with LONGLOBDATA is required (DB2)

There doesn't seem to be a specific metric that you can use, but indirectly the high ratio of FPAGES to NPAGES and, if you have indexes, INDCARD to NDEL will tell you that there is a significant number of logically deleted rows. Unfortunately, this does not work for tables where rows are UPDATEd but not DELETEd, so I guess you will just want to run regular reorgs on tables where LOB columns are regularly modified.


JOIN on LIKE when input udtt count is > 1

I'd start by breaking this out to get a better idea of which part is slow. This should clarify the execution plan overall.

From there you can start sorting out other techniques, like temp tables, recompile hints, indexes, etc.

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )

SELECT b.*
FROM @inputUDTT a
INNER JOIN Tabl b
ON (@inputCount > 1 AND b.name = a.val)

UNION ALL

SELECT b.*
FROM @inputUDTT a
INNER JOIN Tabl b
ON (@inputCount < 2 AND b.name like '%' + a.val+ '%')

For example, in the first query where you have an equality predicate, the optimizer can choose different join types (though it may only ever choose nested loops because of the low row estimates for a table variable. In the second query, the only join type available with no equality predicate is nested loops.

Hope this helps!


How to monitor InnoDB Disk I/O Required for MySQL?

Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are the values you need to monitor for buffer reads and disk read for the Buffer Pool.
What you are looking for is a cache miss rate of 1% or less based on this
Innodb_buffer_pool_reads X 100 / Innodb_buffer_pool_read_requests

I have written posts like this before

Feb 04, 2014 : Tuning key_reads in MySQL
Jun 08, 2012 : How to improve MySQL Server Performance..??

You should also be tracking how much data is being poured into the InnoDB transaction logs

Innodb_os_log_written : The number of bytes written to the InnoDB redo log files.
Innodb_log_write_requests : The number of write requests for the InnoDB redo log.
Innodb_log_writes : The number of physical writes to the InnoDB redo log file.

EXAMPLE
you could take the delta of Innodb_os_log_written (bytes written in a one second window) and divide it by the delta of Innodb_log_writes and track how many bytes are being flushed to the logs per second. If the result seems too high for you, you could tweek innodb_flush_method, innodb_flush_log_at_trx_commit, innodb_log_file_size and things of this nature.


Maybe you are experienceing what is described at the end of the following article:

http://www.niwi.be/2013/02/16/replication-status-in-postgresql/

  In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing (this is generally the same limitation as MySQL's SHOW SLAVE STATUS output).


SSMS Object Explorer Details causes deadlock while job is running

In my case, I was able to solve the problem by reducing the size of the transaction in the stored procedure, ala the comment provided by @Aaron Bertrand.  The entire stored procedure was wrapped in one begin/end transaction.  As the stored procedure progressed, my management studio instance would lock up.  Reducing the transaction to just the critical inserts/updates, and adding some error handling, eliminated the problems I was having with SSMS.


TL;DR: Except for a specific bug in PostgreSQL 9.3 prior to 9.3.4 which it's vital that you patch, index corruption after crash is never expected and hasn't been since PostgreSQL 7.1 was released in 2001.

The advice/folklore you got was wrong.

  I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered from an issue where if the machine holding the server failed (e.g. kernel crash), when the machine was restarted, Postgres would start but the indexes on the database might be corrupted.

That way predates 8.4 unless you're using hash indexes, which are documented not to be crash safe and are not recommended.

WAL logging of indexes was introduced in the deep dark distant past of mid-2001. PostgreSQL has been crash safe for an exceedingly long time. That advice was wrong.

MySQL switched to crash-safe by default (by defaulting to InnoDB tables) in MySQL 5.5, in 2009. Prior to that its default storage engine, MyISAM, isn't crash-safe and will happily corrupt indexes and tables. So your folklore isn't only wrong, it's backwards.

If you had corruption after a crash under 8.4, then either:

you hit a bug (not impossible, but not likely either) in PostgreSQL, the kernel, the filesystem, etc;
You were using LVM before write-barrier support was fixed, and had barriers enabled on your file system;
your storage subsystem lied to the kernel about fsync(); or
you disabled crash safety by setting fsync=off.

There have been a couple of index-related bugs over time, but all the others I know of have been minor and/or hard to trigger, and few have been related to crash recovery.

The issue with 9.3 is different. That's a very specific bug that we're all rather embarrassed about. It occurred when a replica PostgreSQL server was promoted to a master after certain other operations had happened in a particular order. It's a really bad bug because it affects the table contents, not just indexes, so it's vital that you patch to 9.3.5 or higher. To learn more about it than you ever wanted to search for "multixact bug".

So:

  Is manual rebuilding of indices an expected task after a system crash?

Not unless you are using hash indexes, which the manual specifically wants you about.

  If not, has it ever been, and when was that changed?

PostgreSQL 7.1, back in 2001.

If you read the release notes you'll find the occasional bug and bug fix, but other than the aforementioned multixact bugs in 9.3, nothing too serious.

  Is it possible to avoid the need for manual reindexing?

Do nothing. Congratulations, you don't need manual reindexing.

Simply:

Leave fsync set to on, the default
Leave full_page_writes set to on, the default
Don't kill -9 the postmaster but no other processes then delete postmaster.pid and start PostgreSQL again. The latest releases even detect it if you do this and try to prevent damage.
Don't explicitly specify hash indexes when creating an index. PostgreSQL will never use a hash index unless you ask it to.

Bonus points if you:

Keep PostgreSQL reasonably up to date, i.e. apply point release patches
Use decent quality SSDs if you're going to use SSDs, otherwise any database will be prone to corruption. Make sure they have a supercapacitor or other power-loss protection.


Can I make PL/SQL functions more strongly typed?

I think you are fighting against how Oracle works with implicit type conversion but you could suppress that conversion with custom types. Looks ugly but kind of does what you asked:
drop type vtype;
drop type ntype;

create type vtype as object(vvar varchar2(32767));
/

create type ntype as object(nvar NUMBER);
/

DECLARE
   PROCEDURE do_something(
      in_ IN vtype);

   PROCEDURE do_something(
      in_ IN ntype);

   mystery_variable_1 vtype;
   mystery_variable_2 ntype;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN vtype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with vtype argument ' || in_.vvar);
   END do_something;

   PROCEDURE do_something(
      in_ IN ntype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with ntype argument ' || in_.nvar);
   END do_something;
BEGIN
   mystery_variable_1.vvar := 'Hello, World!';
   mystery_variable_2.nvar := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Output:
 30     do_something(mystery_variable_1);
 31     do_something(mystery_variable_2);
 32     do_something(mystery_variable_3); -- I want this to throw a compilation error.
 33  END;
 34  /
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
   *
ERROR at line 32:
ORA-06550: line 32, column 4:
PLS-00306: wrong number or types of arguments in call to 'DO_SOMETHING'
ORA-06550: line 32, column 4:
PL/SQL: Statement ignored

Bobby


How MongoDB indexes differs from MySQL indexes?

The quick answer is yes. Indexes in MongoDB mostly follow the same logic for usage and creation as you would do in MySQL.

Nevertheless, as the two databases are different (MongoDB is document based, not relational, etc) there are some aspects you might want to consider (for instance, there are no joins - your data model/organization needs to reflect this differences to ensure a good performance).

You might want to check the Indexing Strategies docs page that covers the best ways to take advantage of MongoDB indexes.

Also, besides the "traditional" option, MongoDB has much more index types that can help you achieve different things.


Find the second highest salary of employees

I'm going to address this part of the question:

  ...this is the execution plan can anyone explain the steps more?

Assuming the table definition looks something like this:

CREATE TABLE dbo.Employee
(
    [E-Number]  integer NOT NULL,
    [E-Name]    nvarchar(50) NOT NULL,
    Department  nvarchar(30) NOT NULL,
    Salary      smallmoney NOT NULL
);

Empty table

The following query (not my recommendation, just an example):

SELECT DISTINCT
    E1.Salary
FROM dbo.Employee AS E1
WHERE
    1 =
    (
        SELECT
            COUNT(DISTINCT E2.Salary)
        FROM dbo.Employee AS E2
        WHERE
            E2.Salary > E1.Salary
    );

Gives this execution plan (with no data in the table):

The numbers correspond to the Node ID property of each operator.

The E1 Table Scan (4) returns all rows (in no particular order). For each row:

The Nested Loops join (3) executes the inner side, passing the current E1.Salary value as a parameter.
The E2 Table Scan (8) finds rows matching E2.Salary > E1.Salary
The Distinct Sort (7) groups rows by E2.Salary.
The Stream Aggregate (6) counts the number of rows (1 per distinct E2.Salary). The new count column is given the label Expr1008.
The Compute Scalar (5) converts the native count data type (bigint) to integer because the query specifies COUNT rather than COUNT_BIG. The new column is given the label Expr1004.

As rows emerge from the Nested Loops join, the Filter (2) applies the predicate Expr1004 = 1
The final Distinct Sort returns the distinct E1.Salary values.

With duplicate Salary values

If we add a few rows to the table, with some duplicate Salary values:

INSERT dbo.Employee
    ([E-Number], [E-Name], Department, Salary)
VALUES
    (1, 'A', 'D1', $1),
    (2, 'B', 'D1', $1),
    (3, 'C', 'D1', $2),
    (4, 'D', 'D1', $2),
    (5, 'E', 'D1', $3),
    (6, 'F', 'D1', $3);

The query above produces a plan with a couple of new operators (highlighted):

The new Sort orders rows by E1.Salary. The ensures that any duplicate Salary values are presented to the join sequentially.

The Lazy Index Spool saves the output from the operators below it as they arrive. These are the Stream Aggregate, Distinct Sort, and E2 Table Scan (with predicate), which as described before filter E2 (using the current value of E1.Salary), remove duplicate E2.Salary values, and count the resulting rows. The Lazy Index Spool saves recomputing the result in case the same Salary value from table E1 is presented on the next iteration of the Nested Loops join.

Because the Sort ordered rows from E1 by E1.Salary, any duplicates are presented to the join one after the other, so the saved result in the spool can be reused instead of re-running the Stream Aggregate, Distinct Sort, and E2 Table Scan each time.

The index on the spool is keyed by Salary, making it quick to find the saved COUNT(DISTINCT E2.Salary) result for the current E1.Salary value.

If you look at the Actual Executions value for each operator in the plan, you will see that the Lazy Index Spool executes 6 times (once per outer row), whereas the Stream Aggregate, Distinct Sort, and E2 Table Scan execute only 3 times (once per distinct salary value).

The optimizer introduces the extra Sort and Spool as a performance optimization, based on automatically collected statistical information about the data in the table.


SQL Server Replication - Publication and Subscriber schema differ, need to sync

I would use Visual Studio's Schema Comparison tool, or some third party equivalent; it will visually show you what's different and optionally create a script to sync them. Just beware that depending on the change, it may try to copy the table to a work table and then drop the source and rename the new table, which will fail if the table is replicated.


VIM returns "Error detected while processing" and "Sorry, the command is not available in this version"

Single-User Solution

Run select-editor and select the proper version of vim. This is what psql does on initial run. This will drop a file ~/.selected_editor which will be used by psql when it calls sensible-editor.

Multi-User Ubuntu/Debian

If you're on Debian it's best to set it system-wide, especially if you're the only user on the system.

Have psql delegate to the system. To configure psql to use your default editor, run

echo SELECTED_EDITOR="editor" > ~/.selected_editor

Configuring Debian and Ubuntu system-wide for the right editor, simply run

update-alternatives --config editor

Or set it your self,

update-alternatives --set editor /usr/bin/vim.gtk3

Checking

You can always check what version of vim you're using by issuing :version, this should say something like this

Huge version with GTK3 GUI.

If it doesn't, and it instead says

Huge version without GUI.

Or,

Small version without GUI.

You're likely without the needed features. Visual block replace specifically requires -vreplace


SQL Server is configured to allow remote connections

Your named Pipes connections are disabled, try fixing this