This looks like a SQLCMD bug. I can repro with SQLCMD 14.0.3026.27 on Windows 10.

I submitted the bug report on the SQL Server feedback site. Consider upvoting the bug report.


How will large index INCLUDE fields affect system performance?

Ever is a big word, but, in general, no, I wouldn't put a varchar(2000) field into an INCLUDE.

And yeah, the way that data is stored at the page level can seriously impact performance of the index, depending on how the index is used.

The thing is, the more rows of data you can cram into a page, the fewer pages have to get accessed, the faster your system is, for the most part. Adding a really large column means less information stored on a page, so, in the event of range seeks or scans, more pages have to be read to retreive the data, seriously slowing stuff down.

To know for sure if this is an issue on your query, or on your system, you'd have to monitor the reads, especially the number of pages that the query uses.


High Availability Options For SQL Server

For your onsite solution Standard Edition allows you to implement a two node Failover Cluster Instance with Basic Availability Group or database mirroring. https://msdn.microsoft.com/en-us/library/mt614935.aspx

For your offsite solution you could implement log shipping.

They are using the "Always On" naming convention for a lot of the high availability options now.  There is Always On Failover Cluster Instances which is the old Windows Server Cluster Service that is for hardware failover with SQL Failover Cluster Instance that is for SQL Instance failover.
Then, there is Always On Availability Groups which requires Enterprise Edition and consists of a group of databases that can failover to replicas and replicas can be read-only, if one so desires.  Basic Availability Group is a limited version of the Always On Availability Group, and is for just one database. It will completely replace database mirroring when it is removed from future versions.  Lastly, database mirroring still exists.

SQL Server 2016 Edition Feature Comparison:
https://msdn.microsoft.com/en-us/library/cc645993.aspx

If you can fulfill business requirements with Standard Edition, then there is less reason to go virtual, which adds a whole other layer of complexity.


How do I display subtotals in cube browser in SSAS 2012 (Sql Server Data Tools)?

That's right. The cube browser control within SSDT is completely different to the one that used to exist in BIDS2008 or lower. This had me going at first too. The best way i found to get around this was to launch into excel, which can be done from the cube browser tab.

I believe it even creates a temporary connection file for you too so you don't have to create that yourself. Then all you need to do is create a pivot table to display the data how you want.


MySQL Plugin failed to install due to "undefined symbol"

It's not really an answer, but I found a workaround, which is to use the Percona Audit Plugin instead (which I used to use anyway, but stopped working when I moved from 5.5 to to 5.7, but strangely now works again with 8.0). In my case I already have the support code and infrastructure to switch back to this so it's not a big task.


RMAN backup set, nothing else, how can I duplicate?

If you know the sid name, you can copy a pfile over and edit it to change the values to match the database name. Make sure that you set B_CREATE_FILE_DEST to where you want the data files.

Then do a startup nomount on the database. In RMAN do "CATALOG START WITH '/full_path_to_your_backup';" You can then use DB_FILE_NAME_CONVERT to convert the file names to be in the right directory.

You can then try to restore a control file. try list backup; and restore the oldest control file. Mount the database and try to restore the database.

It could be that the reason why the people who gave you the backup are avoiding you is because they know the backup is incomplete. But you have to try to restore what you can, then at least when you go back to them you can ask a more specific question.


Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

pg_dumpall is simply a wrapper around pg_dump. It notes farther down in the documentation for the -n --schema flag that it only dumps non-system schemas.

  Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped.

The system level things that pg_dumpall dumps instead of pg_dump are listed at the top of the pg_dumpall documentation, as you quoted above, which doesn't include a mention of system catalog tables.

So it shouldn't actually dump out the contents of pg_catalog.pg_autovacuum.

Which is actually a good thing. The system catalog tables are an intimate part of each PostgreSQL database, and automatically generated for you when you do a CREATE DATABASE. Their entries generally only make sense within the context of the database they were created in.

Additionally, pg_catalog.pg_autovacuum was last seen in the 8.1 to 8.3 releases of PostgreSQL, all of which have been unsupported since at least February 2013, so hopefully this is part of a migration path to a newer supported release.


Migrating every table to a new linked server has too many prefixes

According to SELECT - INTO Clause

  You cannot create new_table on a remote server using SELECT INTO

In other words, the superfluous prefix is the remote server

You could probably achieve your goal by creating empty tables on the remote server and alter your dynamic SQL to

INSERT INTO server.database.schema.table select from source


Deleting records in relation with other tables

IMHO the best way is: Do not delete the records.

As far as I understand your are talking about accounting and financial information. Usually this information cannot be deleted, at least for a minimum space of time, 5 years on my country. (More time for medical & health products)

If, for some reason, a customer becomes inactive, use a status field to mark him.

        status | description
        -------|-------------
          0    |   Active
          1    |   Blocked
          2    |   Deleted
          n    |   ....

However, if you must delete all this information. I'd suggest you to define and develop a function (stored procedure, udf, etc) that takes care of all required changes in your DB. May be saving historical information, just to know when, who and why this records has been deleted.


Don't preallocate many gigs of ram in Oracle Database 12c?

You probably have pre_page_sga set to true. To disable this, change the value of pre_page_sga to false:

alter system set pre_page_sga=false scope=spfile;

then restart Oracle.


Setting up MySQL master-slave replication

See the MySQL documentation or a Step by step replication setup blog if you need to review.

If these doesn't help please share the output of show slave status\G (as such it appears connected) and also output of my.cnf of master/slave.
Mainly you need to worry about server-ids, replication filters (replicate-to-db/table etc)


Well, that table will give you the answer you are looking for.  Just join onto sys.indexes to determine the type of index.  Tables are either Heaps or Clustered indexes, so a scan on that index type is a scan on the table.  See the example below, add any additional columns you are looking for.

SELECT S.name AS SchemaName
    , O.name AS ObjectName
    , I.name AS IndexName
    , I.type AS IndexType
    , I.type_desc AS IndexType_Desc
    , IUS.user_scans
FROM sys.objects AS O
    INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    INNER JOIN sys.dm_db_index_usage_stats AS IUS ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id
WHERE I.type IN (0,1) -- 0=HEAP, 1=CLUSTERED (table)


Storing date as integer (numeric), what are the advantages

To answer your first question, I would recommend using the DATETIME data type within SQL Server.  Not necessarily for performance reasons, but to leverage the RDBMS-specific functionality.  For instance, you would have to re-invent a lot of logic just to do basic date math (think DATEDIFF(), DATEADD(), DATEPART() and many other functions.  They are obviously tailored to the DATETIME data type and are easy to work with).

As for your second question, you are running into the exact problem that the first question (and my answer) is geared towards.  You are looking at 20111201 and 20120131 as dates, and your brain is telling you that should be a difference of 60 days.  Well, you're looping through based off of the delta...which is:

20120131 - 20111201 = 8930 (with the inclusive loop it'll be 8931)

In other words, your WHILE loop is executing 8931 times.  This is happening because those are integer values and your loop will not jump from 20111231 straight to 20120101.

You integers aren't going to take into account the cap of years and months (i.e. your Question 2 problem).


Dealing with non active transaction in log file and shrinking

To understand Transaction Log architecture you have few good articles

Understanding Logging and recovery in SQL Server
Transaction Log physical architecture

I would ask you to take some time and read these articles specially the first one.

  so is there any way I can remove entries from log file for all transaction other than active transaction. Because at max my transaction will not exceed 10 GB at any instance.

No you cannot. In simple recovery the space held by committed transaction is re-utilized in transaction log file. When transaction commits checkpoint truncates the logs and when log is truncated the space used by previous transaction will now be re-utilized by new one so log file will not have to grow instead use the space already there. The problem here is long running transaction  you must focus on it, this is holding log hostage and not allowing it to truncate there by increasing space.

  It is recommend not to shrink log file But then how can I ensure that my future transactions (i.e queries) will run smoothly? Does SQL Server shrink/flush/delete data from log by itself?

Yes its recommended not shrink log file. You need to make sure you do not run any transaction which does huge data manipulations. You can do whatever data manipulation you like but make sure amount of rows this manipulation is dealing is not too big. Log file just reacts to what you are running so you have control in it. Make sure after each data manipulation you manually run checkpoints

SQL Server will never shrink data and log files by itself for that you have DBCC SHRINKFILE command.

  What is the best practice to deal with log file size?

The article How to set Log file size will help you set optimum value. Basically you have to spend some time looking at transaction log growth and see how much it grows in a day or week. Currently as per your previous question the autogrowth and initial size of both data and log files are not good. You must refer to to this article to set correct autogrowth setting


SQL how to remove duplicate results from same table

Following code should work, tested in Memory table:

DECLARE @TBL AS TABLE
(
    ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    First VARCHAR(10),
    Last VARCHAR(10),
    Address VARCHAR(30),
    Quantity INT,
    Price INT,
    Discount VARCHAR(10)
);

INSERT @TBL (First, Last, Address, Quantity, Price, Discount )
    VALUES
    ('rolp', 'Gaflp', '24 street', 1, 155, '10%'),
    ('rolp', 'Gaflp', '24 street', 3, 202, '5%'),
    ('rolp', 'Gaflp', '24 street', 4, 312, '6%');

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY First, Last, Address ORDER BY Quantity) AS Row
        FROM @TBL
    )
SELECT  ID,
        CASE WHEN Row = 1 THEN First ELSE '' END AS First,
        CASE WHEN Row = 1 THEN Last ELSE '' END AS Last,
        CASE WHEN Row = 1 THEN Address ELSE '' END AS Address,
        Quantity, Price, Discount
    FROM CTE;


Issuing Inserts/Updates to Progress OpenEdge via ODBC

Once you've identified which one you need, probably 32 bit version, you'd need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties. After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.


I don't really understand what you mean by "looks a bit clunky and could be broken down better in a few more tables"; something looking "clunky", however that applies to database design, doesn't seem like a good reason to breaking things up. Unlike fighting the dark force, you probably should use logic, not your feelings.
For a suggestion, may be you could answer (to yourself) to some questions about your model, such as:
Why do you think that "car make/model" is a separate entity and not simply attributes of a "car" entity?
Why car.car_lot varchar(255) references car_lot.lot_id (integer)?
Why for_sale_cars? What entity does that represent?
Is customer.customer_cars varchar(255) intended to store a list of cars? That would violate 1NF. And how is that then different from saved_cars?
Why do you think you need an extra key in saved_cars?
Which of the requirements you have listed cannot be addressed by your model? Are there any requirements that are missing?


Why select max partitioned column is much slower than indexed one?

Think I mis-understood/mis-read the question initially.

  The table is partitioned on column K1. However the following query
  takes a half minute and the scan count is 170 and logical read is
  603K.

To satisfy the MAX(k1) query in this case requires a clustered index scan of each of the 70 partitions.

  However the similar query on on non-partitioned table with index on K1
  takes no time and scan count is 1 and logical read is 5.

With an index defined on K1, an ORDERED BACKWARD scan of the index is chosen, which probably translates to 4 pages read from the non-leaf index pages plus 1 for the leaf level.


MySQL FULLTEXT search (match against) not working

It's not working because "what" is a "stopword" ...
from https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html
I followed the instructions and created a new stopword list.
create table test.custom_stopwords as
select * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
where length(value) = 1;

SET GLOBAL innodb_ft_server_stopword_table = 'test/custom_stopwords';

Then I rebuilt the fulltext index and it worked with the word "what."


Updating column with multiple sub-queries

The trick here is to not to get overwhelmed by the fact that there is a spatial datatype involved.  Then it becomes a fairly simple aggregate query over a join between the pricing table and the catalog table where the join just happens to be a spatial intersection.

SELECT c.id_number, SUM(med_price)
FROM pricing_table p
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326))
GROUP BY c.id_number

Then to incorporate the scaling factor into the sum you can use a case statement in a subquery, similar to the following

SELECT p.id_number, SUM(price)
FROM (
SELECT c.id_number,
    CASE WHEN scaling_code = 'small' THEN small_price
        WHEN scale_code = 'medium' THEN med_price
        ELSE large_price
    END price
FROM pricing_table p
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326))
    ) p
GROUP BY p.id_number


What would cause an orphaned ##MS_PolicyEventProcessingLogin##?

We upgraded two servers (from SQL 2000) to SQL 2008R2 using in-place upgrade. We started getting these messages in SQL logs after upgrade. We did not change this or any other logins or users during the upgrade process.

My guess is upgrade process left two accounts (##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##) orphaned.

EXEC sp_change_users_login 'Auto_Fix', '<User Name>' fixed this issue.


Scheduling backups and restores via the SQL Agent isn't very hard. It is only one or two lines of TSQL. Just make sure that the backup finishes before the restore starts.

You do not mention which version or edition of SQL Server you are using. Some versions/editions of SQL Server support Backup compression natively. (Here is the page that talks about the capabilities of the different editions of SQL Server 2012.) If you are not using a version/edition of SQL Server that supports compression, RedGate's backup compression should be a reasonable choice. (I've not used that particular compressor. I have used a competitor, SQL Lightspeed. I believe that the products are fundamentally the same.)

In the past, I've found the larger problem is getting the backup files from one location to the other, as it is often assumed that both servers are on the same LAN and domain and that you can simply use copy.exe or robocopy.exe. I was forced to cobble together my own FTP transfer schemes back then, which is more work and testing.

Another problem is getting the user IDs synced up with the login IDs after restoring the database to the target server. If you have already done this a few times, you know what I mean. If not, here is a link to a relevant SE thread.


Functional dependencies are determined from the subject matter.  I'm inferring the FDs here from the names of the columns.

The key is Ord_NO and Prod_ID. I didn't include CUS_ID in the primary key, because it depends on   Ord_NO.

Name depends on CUS_ID which depends on Ord_NO.

This is a transitive dependency.

The only non key column which depends on the whole key is Qty_Ord.  All the rest are partial dependencies.

Edit:  I revised my answer to make the Column Names agree with the ones in the sample data.

The sample data is so small as to be anecdotal.  Deriving FDs from such a small sample is risky, at best.  You run the risk of seeing as patterns what are in reality mere coincidences.

Based on the Column names and an intuitive notion of how orders work, here are the FDs.

 Cus_ID -->  Name
 Ord_NO --> Ord_Date
 Ord_NO --> Cus_ID
 Prod_ID --> Description
 Ord_NO, Prod_ID --> Qty_Ord

While these are consistent with the sample data, they are NOT derived from it.  As I already said, that's risky with such a small sample.


How can I migrate a WordPress site from MySQL to SQL Server Express?

You will need SQL Server Migration Assistant to migrate the data. For wordpress to work with MSSQL you also need the "wp-db-abstraction" plugin.
I'm still working on that one...


availability groups - checking for primary server - cannot call a procedure with parameters

This happens because the procedure has parameters and the procedure is also called from a job step.

When you execute the query outside of the job context (ssms), it should execute just fine. The exact reason why this happens is still unclear to me, I tried getting the commands through the profiler and recreate it in my query window, but no dice there.

However, I do know that when the agent executes job steps, NON-Default behaviour can happen.
An example of this is the fact that quoted_identifier gets turned off inside the job step context:

From Microsoft:

  SET QUOTED_IDENTIFIER is ON (default)

Source

Anyhow, the workaround I used for your problem is putting it in dynamic SQL.

declare @reportYear int, @reportMonth int, @reportMonthPrevious int,@sql varchar(max)

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
               set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonthPrevious as varchar(20))
               exec(@sql)
            END
                set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonth as varchar(20))
                exec(@sql)
END


Query performance low for aggregate function

Let's see the entire SHOW CREATE TABLE; we need to see that you are missing INDEX(unitid).

Then, when it still does not run any faster, we need to discuss whether unitid is really a string, in which case you should quote '2000' or is really a number, in which case VARCHAR is the wrong declaration.


Get last result of query and set it to variable

Swap the variable and column name so you are setting the value to the variable, not the other way around. How you have it written, you are setting the column name log_id to the value of @PriceID which is currently NULL since you only declared it.

DECLARE @PriceID int
SELECT TOP 1 @PriceID = log_id
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC


Complete Failover Cluster Failure SQL Server 2012

If you evict nodes you'll end up formatting the nodes and rebuilding them.  Once a node has been evicted from the cluster you won't be able to uninstall the SQL Server instance from the node.  You can try manually removing all the registry keys and files, but honestly it's probably faster to reinstall Windows and try getting it back into the cluster.

Has Microsoft not been able to get you towards any resolution on this?

Either way you're looking at a cluster rebuild basically from scratch here with 21 SQL installs, which I'm sure as you know takes forever.  There are some ways to make this take less take (building a new two node cluster, installing everything there, then doing a controlled migration between clusters, use DNS to redirect connections to the new names so applications don't need to change, etc.

If this was my environment I'd burn it down and bring it back fresh, obviously with as little downtime as possible.

--sales pitch--
It might be a good idea to bring someone in that has a lot of clustering experience (me, someone else, either way) to help with the rebuild to make sure that everything is setup exactly as it should be and to help take the pressure off your internal team (who probably hasn't been getting a lot of sleep the last two weeks).  If you've got a lot of clustering experience in house then this isn't needed of course.  I'm just worried because something went horribly wrong doing something which souldn't have been that big of a deal to do.
--end sales pitch--

Disclaimer: I'm a consultant.


Move the condition from WHERE to JOIN:

FROM Object3 Object1 WITH(NOLOCK)
FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10
                                            AND Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth)
                                            AND Object2.Column11 < @startOfCurrentMonth


DAC Connection using the SSMS for executing the code

You should pass in your query according to sqlcmd syntax specifying -Q (or -q or -i for the script file) like this:

EXEC master.dbo.xp_cmdshell 'SQLCMD -S SQLServer -A -d Db1 -Q"select count(*) AS FileCount from TableName" '


There is no way to do what you are asking as this would fundamentally break the Atomicity part of ACID


Can we use SQL Express to send messages to SQL Standard using the Service Broker?

The 'client only' means that two Express instances cannot communicate directly. They have to have a Standard instance in between acting as a forwarder. Express instances can both generate and consume messages.

Messages originating on Express instances can be received on Standard or higher instances.
Messages originating on Standard or higher instance can be received on Express
Messages originating on Expres can be received on another Express instance only if it was routed to an intermediate Standard or higher instance using a forwarding endpoint, see Service Broker Message Forwarding. A single Standard instance can act as a forwarder to any numer of Express instances, limited only by processing capacity and network bandwidth.


The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE.

Please learn about "compound indexes".

Please provide EXPLAIN SELECT ....

AND wi_training.trn_deleted = 0
AND wi_training.trn_beneficiary_type = 2
AND wi_training.trn_start_date <= '2015-06-30'

Begs for the compound INDEX(trn_beneficiary_type, trn_deleted, trn_start_date).

A compound index is almost always better than Using intersect(...).

(Style recommendation:  Don't prefix all columns with the same thing; it unnecessarily clutters the SQL.)


Profile shows the (server-side) operating system resource usage by the server process, while the command-line interface reports the (client-side) statement execution elapsed time, which also includes in-client processing and the time spent sending the statement to the server for processing and receiving the results. In other words, elapsed time reported by the CLI is a superset of time measured by the "profiler". The shorter the statement execution time, the higher the proportion of the client overhead in the overall elapsed time.

See also this related question.


Passing a variable in OPENJSON WITH statement in SQL Server

It looks like there were two issues with your dynamic SQL approach:

You didn't properly escape the single quotes in the @Param variable
You can't access a table variable inside the scope of a dynamic SQL statement

For details on this, and workarounds, see Erik Darling's article Starting SQL: Dynamic SQL And Temporary Objects

With those things addressed, here's a slimmed down version of the code that demonstrates how the dynamic SQL can work, using a temporary table instead of a table variable.
CREATE TABLE #json
(
    Json_Table nvarchar(max)
);

DECLARE @Param varchar(1000),
        @Symbol char(3),
        @Sql nvarchar(max);

SET @Symbol ='GBP';
SET @Param = '$.rates.' + @Symbol;
SET @Sql = N'
SELECT  *
FROM OPENJSON((SELECT Json_Table FROM #json), N''$'')
WITH
(
    SampleValue nvarchar(max) ''' + @Param + ''',
    SampleDate date ''$.date''
);
';

INSERT INTO #json
    (Json_Table)
SELECT
    N'{"base":"USD","rates":{"GBP":0.7739357155},"date":"2020-10-15"}';

EXEC sys.sp_executesql @SQL;

See an interactive example of this at the db<>fiddle link.


Not displaying special characters imported from a file

If characters are being imported incorrectly, then there is a problem with one (or both) of the following areas, as this is really a two-step process:

BCP / BULK INSERT does not know how the file is encoded and is interpreting it incorrectly
The destination column is VARCHAR (or CHAR {or TEXT, but don't use TEXT} ) and the Collation of that destination column (not database) uses a code page that does not have a mapping for the incorrectly imported character(s).

It is important to note here that the default Collation for the database where the import table resides is not relevant. The only Collation that matters here is the Collation of each particular string column being imported into. And the Collation of each and every string column can be different, and none of them need to be the same as the database's default Collation. It is just typical to have the Collation of most columns in a database match the database's default as that will be the Collation used when creating new tables and columns and not specifying a Collation via the COLLATE keyword.

Step 1: File Encoding
BCP / BULK INSERT (or most any other code reading a file) will not know what encoding a file is using unless the file is using one of the few encodings to have a Byte Order Mark (BOM). But Extended ASCII encodings don't use byte order marks and thus cannot be programmatically determined (at least not with certainty). When using Extended ASCII encodings, you either need to specify the code page or else a default will be assumed.

As per the MSDN page for the bcp Utility, under the -C option:

  OEM  ->  Default code page used by the client. This is the default code page used if -C is not specified.

You can determine the default code page by opening a command prompt and running either mode or chcp (I prefer chcp as it also allows for changing the code page if you pass in a value).

If your default code page is 850 and yet the file was saved with an encoding of Windows-1252 Latin1 (ANSI), then there could easily be problems interpreting the file since the character mappings are not the same between those two code pages. This has nothing to do with SQL Server in any way.

The ñ character has a value of 241 on code page 1252. BUT, on code page 850 that same character has a value of 164. The file, regardless of anything else, is a series of bytes, and one of those bytes has a decimal value of 241 (because when it was saved, it was told to use code page 1252 which determined that ñ needed to be stored as 241). Now, when BCP reads the file, if it is using the default MS-DOS code page of 850, that same byte value of 241 maps to character ±. If you were to specify a code page of either ACP or 1252 (same thing) via the -C switch, then BCP will know that a byte of value 241 is actually ñ. Or, you could specify a code page of 1255 (Windows Hebrew), then BCP / BULK INSERT will interpret that same byte value of 241 as being character ס.

Step 2: Destination Column Datatype and Collation

Once BCP / BULK INSERT (or any client app) reads the data in, it exists as those mappings, not just the base byte values. Whatever characters were read into BCP / BULK INSERT will be stored in the destination column as that character, as long as that character can be mapped in the destination datatype and Collation. A destination datatype of NVARCHAR, NCHAR, and NTEXT (but don't use NTEXT) can hold all characters, so it won't matter what the Collation is. But, if the destination datatype is VARCHAR, CHAR, or TEXT then the Collation will determine the code page which in turn determines the character mappings. If the destination datatype is one of those last 3 mentioned and uses Collation associated with the same code page used for the file, then everything should work just fine. Or, if the Collation is associated with a different code page, then an attempt will be made to map the character, not the byte value.

Meaning, if BCP / BULK INSERT is working with a code page of 1252 and a character of ñ (value 241 on code page 1252), then if you import that into a VARCHAR column with a Collation of SQL_Latin1_General_CP850_CI_AS -- which uses code page 850 -- then you will see a character of ñ (same character, but value 164 on code page 850) instead of ±, which has the same 241 value on code page 850. But, if you import into a VARCHAR column with a Collation of Hebrew_CI_AS -- which uses code page 1255 -- then you will see ? instead of ס (value 241 on code page 1255) as there is no mapping for ñ on code page 1255.


How to reclaim unused space from clustered index table

You may need to run an index Rebuild (not reorganise) on the clustered index - note in standard edition this will lock the table for writes for the duration of the rebuild (and reads may be affected), which could be some hours.

Otherwise a simple DBCC SHRINKFILE might work, but be aware of this: https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/


Can we Configure Change Data Capture in another Database?

No, it will only work within the same database. After all, what do you expect to happen if the second database goes offline, gets dropped, becomes corrupt, has permissions changes, etc.?

Now, once the CDC tables are created, I guess one potential workaround would be to place INSTEAD OF INSERT triggers on them, and divert the data to identical tables in another database that you manually create. But I haven't tested this, it would hardly be supported, and you would also lost a lot of the inherent CDC functionality (think about all the LSN-based functions that would now obviously not work). And again, I don't know what would happen when the other database goes offline, gets dropped, etc...


Can I partially invalidate the MySQL table cache?

The only thing that is definitively cached for MyISAM is an index.

The key_buffer_size variables sets up how large the MyISAM Key Cache will be.

There are two suggestions I can offer

SUGGESTION #1 : Use a Dedicated Key Cache for the Table

Did you know you could create a keycache dedicated to one or more MyISAM tables? Suppose you have a table called mydb.mytable and you want to load the entire mytable.MYI file into it. Here are the steps to set this up:

STEP 01) Get the physical size of the table's .MYI file

Run this query

SELECT index_length MYISize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Suppose the query returns 1234567890 for MYISize. That's 1.114978 GB = 1177.3757 MB. Round that up to 1280M (that 1.25 GB).

STEP 02) Create the Buffer with that size

SET GLOBAL mykeycache.key_buffer_size = 1280 * 1024 * 1024;

STEP 03) Associate the cache with the indexes for mydb.mytable

CACHE INDEX mydb.mytable INTO mykeycache;

STEP 04) Load the Index Pages from the .MYI into the Dedicated Cache

LOAD INDEX INTO CACHE mykeycache;

That's all. All indexes for mydb.mytable are now in that cache only. Running these commands also removes the same index entries from the general MyISAM keycache.

You could create a startup script for mysql to preload the indexes.

I have written about this subject is past posts

Aug 28, 2012 : MySQL InnoDB Index in swap
Jun 09, 2012 : Can we assign mysql Key_buffer_size value for particular table?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM
Dec 30, 2011 : Is splitting a 'users' table for authentication purposes a good idea?

As a sidenote, I would like to suggestion something optional. You could speed up the access time of MyISAM data by changing the ROW_FORMAT of the MyISAM you want. For the same MyISAM table mydb.mytable, here is what you do:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

That's it. This a good and bad side to changing ROW_FORMAT to Fixed

GOOD SIDE : Queries hitting mydb.mytable will run 20-30% faster without any additional enhancements
BAD SIDE : This bloats all VARCHAR columns and treats them as CHAR. This will bloat indexes that have VARCHAR columns as well. This could increase table and index sizes 80-100%
My Past Posts Where I Discussed This

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Jan 03, 2012 : Optimized my.cnf for high-end and busy server
May 03, 2012 : Which is faster, InnoDB or MyISAM?

SUGGESTION #2 : Switch the table's Storage Engine to InnoDB

If you switch that table to InnoDB, you set up the InnoDB Buffer Pool in such a way that data and indexes that enter the Buffer Pool will linger a lot longer. This will simulate what your original question is asking: Partially invalidating a cache. That way, mysqldumps, full table scans, and queries like these will have roadblocks to cache stampedes. The options to tweak are innodb_old_blocks_pct and innodb_old_blocks_time.

According to the Configuration Options section of the MySQL Documentation for the InnoDB Buffer Pool:

  Setting innodb_old_blocks_time greater than 0 prevents one-time table scans from flooding the new sublist with blocks used only for
  the scan. Rows in a block read in for a scan are accessed many times
  in rapid succession, but the block is unused after that. If
  innodb_old_blocks_time is set to a value greater than time to process
  the block, the block remains in the “old” sublist and ages to the tail
  of the list to be evicted quickly. This way, blocks used only for a
  one-time scan do not act to the detriment of heavily used blocks in
  the new sublist.

  innodb_old_blocks_time can be set at runtime, so you can change it temporarily while performing operations such as table scans and dumps:

SET GLOBAL innodb_old_blocks_time = 1000;... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;

  This strategy does not apply if your intent is to “warm up” the buffer pool by filling it with a table's content. For example,
  benchmark tests often perform a table or index scan at server startup,
  because that data would normally be in the buffer pool after a period
  of normal use. In this case, leave innodb_old_blocks_time set to 0, at
  least until the warmup phase is complete.


Does it make sense to consolidate Oracle databases?

It is fairly easy to consolidate multiple databases into one real database. In Oracle, a database is the collection of files. Users connect to the database by connecting to an instance. A database can be served by multiple instances, in which case you are running RAC.

So for simply consolidating database into one single database you don't need RAC but you could, if you want/need to do this.

If you are consolidating into one database there are a few things to take into account:

namespaces
service level agreements - don't make maintentance impossible by combining conflicting slas.
performance isolation - use Resource Manager to handle this
application isolation - make sure the apps all use their own tns_alias to connect
services - give every application an own service name in the database, if possible.

When you have naming conflicts, there is a problem, combination not possible.
You might want to take some downtime for maintenance/upgrades. If you can not get a downtime from all applications at the same time, you have a problem.
Using Resource Manager you can give a certain performance guarantee for the specific services.
Services are a smart thing to use, it is the easiest thing to see how resources are used, compared to one and other.

Easiest is to run multiple instances on a single server, each serving it's own database. This is the easiest but not the smartest thing to do. Smartest is to have a single instance on a single server. This is because every instance considers itself as the master of the server. You can not very easy isolate their resource usage, as you can do in a single instance. If you want to give some performance guarantee in a multiple instance server, the setup will grow in complexity because in many cases you need to start multiple projects and users to run your databases under.

A single database can easily support a few hundred applications, a lot cheaper than using a few hundred databases. This quickly saves BIG money on a yearly basis by making good use of Oracle features.


Move database without replication taking new snapshot

Have you considered setting up a subscription on the upgraded / new server and then repointing whichever application at the new subscriber database overnight?


MySQL UPDATE to replace text in LONGTEXT field

There is an error in your query near

AS w`

Alias name "w" should instead be

`w`

Try this:

UPDATE wp_posts AS `w`
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');


Access query with "last" and display the rest too

This query selects the LAST calibration date for each tool.

SELECT t.tool_Name
    ,(
        SELECT TOP 1 c.calibration_Date
        FROM tblCalibration c
        WHERE t.tool_ID = c.tool_ID
        ORDER BY c.calibration_Date DESC
        )
FROM tblTools t

If you want to see all the tools and calibration events you have to use LEFT JOIN:

SELECT t.tool_Name
    ,c.calibration_Date
FROM tblTools t
LEFT JOIN tblCalibration c ON t.tool_ID = c.tool_ID
ORDER BY t.tool_Name, c.calibration_Date DESC --ORDER BY is optional

The first query returns all the tools and the date of last calibration of this tool. If you have 100 tools, you get 100 records, no matter how many times you calibrated each one. The second query will return all the tools and all the calibration events. It returns all calibrations for the tool instead of the last one.


MySQL InnoDB issue not using indexes correctly [Percona 5.6]

First of all: This is not because of Percona, the differences you are getting are because of the upgrade to 5.6. Percona Server rarely modifies the SQL optimiser from upstream. The changes come from the new optimiser in MySQL 5.6, which Percona Server 5.6 uses and that was heavily optimised between the two version (usually, for the best).

The reason why the execution is slower is because with an index on (item_format, item_private), it thinks that the query will be faster by getting the results using that index, but in reality, that would cause the join to be done in the "wrong" order. The best index here is (item_private) -assuming it is selective enough, if not it would be the primary key-, as it can use item_private for filtering and the hidden PRIMARY KEY inside the secondary key for ordering, while it uses the PRIMARY KEY of format for the join. Please note that (item_filtering) or (item_filtering, item_private) are not good indexes in this case.

By looking at the optimiser trace and the handler status, the problem seems to come by the predicted number of rows: the old method, in 5.6, seems to predict a full table scan, while the actual number of rows read is -more or less- the number of rows in the LIMIT clause. This seems to be a regression in the query optimiser, and it should be reported if you confirm that it is not due to any special personal configuration. It is specially bad, as it prefers the creation of a temporary table for the join (potentially on disk, so it may be very slow in some cases) over a very light scan.

You have been already told several ways of avoiding this problem for the time being: not creating an index containing item_filtering, using STRAIGHT_JOIN or forcing the usage of item_filtering (or PRIMARY).


Primary key and index column for Merge replication- Id vs GUID

I disagree with some of that advice.  GUID as a clustered PK is ok as long as you use sequential GUID generation, eg NEWSEQUENTIALID().

Sequential GUID generation, even if only "locally" sequential, mitigates the bulk of the performance problems you will experience with random GUID generation.  The alternative designs end up just needing too many indexes.

You need GUIDs for distributed key generation, and that's never going to be as efficient as using int, but the uniqueidentifier column is a requirement for Merge Replication.  So your tables can simply be

create table SomeTable(Id uniqueidentifier rowguidcol default newsequentialid(), . . .)


The best way for that is to use the sequence way which is auto increment facility,  example:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

so in that case you don't need to mention that id in the insert like:

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

also you can get the inserted id from the function

 LAST_INSERT_ID()

so here the id is auto last rec id + 1, you can see full reference about it from MySQL dev site:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html


Is time to deliver data included in time measured for a Slow Query

It is absolutely possible the application taking a long time can cause those numbers. From the slow query manual (emphasis mine):

  mysqld writes a statement to the slow query log after it has been executed and after all locks have been released

So if your application is taking a long time to release any lock that it acquired for the session, the query time written will reflect that.

Now in your specific scenario, is it possible that the slow execution times are occurring during some heavy load on your database server? That would cause abnormal query times irregardless of slow lock release.


It looks like the issue is due to data corruption on page 952663 on F:\Data\PointOfSale.mdf.

The error is not due to large data being returned. One of the pages in your result set is corrupt. You can still circumvent the corrupt pages and get back a large result set.

You can do what @jl01 suggests or you can also try a page level restore for page 952663.


Merge replication fails on executing query

"It looks like something is updating the same row at both servers with different content and made merge agent crash?"  This is handled by merge conflict tables, and would not cause the issues you are describing.  These conflict tables are located on the publisher database, and are named like:  MSMerge_conflict__.

To answer your question about what reinitialization does, by default, reinitialization will take a snapshot of your published articles, drop the articles on the subscriber side, recreate the articles on the subscriber side, and then bulk load data from the snapshot into the subscriber articles.  Since this is a production environment, and those articles need to be available on the subscriber side, this should only be used as a last resort.

What you can do is query the MSrepl_errors table on the Distribution database.  This will provide you with a command_id and an xact_seqno.  You can use these values as inputs into the sys.sp_browsereplcmds stored procedure.  This will provide you with command text that is actually failing.  Using this information, you can better understand the nature of the failure.  If a particular row cannot be inserted or deleted at the subscriber, you may have to either delete the existing row (to allow the insert) or insert a dummy row (to allow the delete), respectively.

I hope this information helps,

Matt


You want to have something like this:

This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)


What option we can disable temporary in PostgreSQL make database update faster

You should be able to increase your update speed by "disabling" foreign keys check as explained here.


Add parent_id dynamically based on another field

CREATE TEMP TABLE v2
    SELECT *
    FROM version;

UPDATE version v1
SET parent_id = (
    SELECT id
    FROM v2
    WHERE v2.order < v1.order
    ORDER BY v2.order DESC LIMIT 1
);


Save Results as CSV *with* headers in SSMS

In SSMS you need to change some options:

Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.

Changed settings are applied to new, but not existing query windows.


Resizing tablespaces/datafiles is not as straightforward as you might intuitively expect.
The following post does an amazing job at explaining all the ins and outs, and how to do it easily through Enterprise Manager:
http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php


How to retain good records when inserting from stored procedure and still catch the error message?

The TRY...CATCH block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.

Try this stored proc:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP
    VALUES (1);

    INSERT INTO #TEMP
    VALUES (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID
    FROM #TEMP;
END
GO

Running it, like this, without a TRY...CATCH, allows all the statements in the proc to run:

EXEC dbo.SomeProc;

The output:

  Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
  Divide by zero error encountered.
  The statement has been terminated.
  Run the SELECT

However, if you run it inside a TRY...CATCH:

BEGIN TRY
    EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
    PRINT (ERROR_MESSAGE());
END CATCH

You see only the error message:

  Divide by zero error encountered.

The PRINT (N'Run the SELECT'); never runs, and indeed the SELECT ID FROM #TEMP; never runs either.  Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE

From the MSDN documentation on TRY...CATCH:

  If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.

As always, the devil is in the details.  TRY...CATCH always aborts the code inside the BEGIN TRY...END TRY code block if any error over severity 10 occurs that does not close the database connection.  Execution is immediately passed into the BEGIN CATCH...END CATCH block, even if this means aborting code in a stored proc.

Be aware that if an error occurs on any row the entire insert will not happen. The only reason you're seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error.  Take for instance:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP
    VALUES (1);

    INSERT INTO #TEMP
    VALUES (1)
        , (2)
        , (3)
        , (4)
        , (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID
    FROM #TEMP;
END
GO

Using this stored proc, either inside a TRY...CATCH or with no TRY...CATCH block, will only result in a single row being inserted into the #TEMPTABLE - none of the rows in the 2nd insert will be present in the output.


What exactly is overlapping candidate key?

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N).
Additional explanation of the above, originally left in comments:
All overlapping candidate keys are groups of (e.g. two or more) candidate keys. That means the first criterion is that your relation R must have more than one candidate key (minimal super keys). For any of the candidate keys to be overlapping, each of them (again two or more) must meet a few additional conditions. 1) They both must be composite candidate keys. They must consist of more than one attribute, so a key like A will never be overlapping, but AB might overlap with another key. 2) The composite keys must share an attribute. AB overlaps with AC and BD but not CD or EF.
To summarize: two or more sets of attributes where 1) each set is a candidate key (minimal superkey) for the relation, 2) each set is a a composite key (consists of more than one attribute), and 3) one or more of the attributes of the composite keys overlaps with an attribute of another key in the set. So you can rule out MNOP and NOPL on the basis that they are not minimal superkeys. You can rule out P and L on the basis that they are not composite keys (they consist of one attribute). You're left with two keys, NO and NM, which share the attribute N, so you are finished.
Example
It may also help to have an example you can really wrap your head around. The only time I've ever seen where you will have overlapping candidate keys is when you have 1) two attributes that functionally determine one another (e.g. a one-to-one relationship between A and B where A has one B and B has one A) and 2) these attributes are part of composite candidate keys.
For example, in some system, a Customer has one CreditCard, and a CreditCard belongs to one Customer. In the Rentals table, you uniquely identify a Rental by the EquipmentId, Date and CustomerId. For convenience, you've stored CreditCard on this table as well.
This means that the following FDs hold:
{CustomerId, EquipmentId, Date} -> {CreditCard}
{CustomerId} -> {CreditCard}

But since the association is one-to-one, the following FDs also hold:
{CreditCard} -> {CustomerId}
{CreditCard, EquipmentId, Date} -> {CustomerId}

Since CustomerId and CreditCard can be used interchangeably to uniquely identify your customer.
In the scenario above, you have overlapping candidate keys:
{CreditCard, EquipmentId, Date}
{CustomerId, EquipmentId, Date}

They are overlapping because they are composite keys (they consist of more than one attribute) and because at least one of their attributes is shared (in this case, they share both EquipmentId and Date.
You said you don't care about BCNF at the moment, but to fully bring this home, the scenario above is the reason why you'll occasionally see a table that is in 3NF but not BCNF. The table above is in 3NF, but not BCNF.
3NF allows FDs where 1) the FD is trivial 2) the left side of the FD is a candidate key or 3) the right side of the FD is a key attribute (an attribute used to make any key). Since CreditCard and CustomerId are both key attributes, all FDs either meet 2 or 3.
BCNF is very similar, but it only allows conditions 1 and 2 allowed by 3NF. Since the 3rd condition is not allowed by BCNF, and both CID -> CC and CC -> CID use condition 3, this table is not BCNF, but it is 3NF.
For practical purposes, the case is fairly rare and this information is pedantic. The giveaway that your table has a problem will be the fact that CreditCard/CustomerId pairs are repeated across your table. You also may recognize that the table would not even be in 2NF without this rare condition where the right side of an FD may be a key attribute because CreditCard is a partial dependency on the primary key (it depends on CustomerId but not EquipmentId or Date.


Error replicating password change in MySQL slave

Mysql Replication is pretty hard to manage, if anyone executed wrong query it'll affect the slave too. Use the below command to fix.

  Stop slave;

  SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

If you want to exclude this error all time, add the below line in my.cnf file.

If the error code is 1396 then

  slave-skip-errors = 1396


There is a mismatch between the data and results in your question but I think you need a query like this. It's not going to be very efficient if the orders table is big:

SELECT
    o.*,            -- pick the columns you want
    p.*             -- in the result
FROM orders AS o
  LEFT JOIN price AS p
    ON p.date =
       ( SELECT pi.date
         FROM price AS pi
         WHERE pi.date <= o.date
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

Tested (with modified data in the orders table) in: SQLfiddle.


can I convert an integer to base-2 without a function

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for).  It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable


SQLite3 'OR IGNORE' leaves empty primary key IDs

Apparently, leaving out the autoincrement from the key definition does the job. The key still autoincrements itself but not when IGNORE is triggered. I found this solution here: https://stackoverflow.com/questions/47120708/sqlite-autoincrement-and-insert-or-ignore-will-produce-unused-autoincrement-key


How to aggregate results from SELECT JOIN

Try with this

SELECT
   u.id AS user_id,
   u.username AS username,
   GROUP_CONCAT(usp.id) AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id
GROUP BY u.id,u.username;

https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php#SP


Postgresql update multiple rows and skip row on error

It seems there is a UNIQUE INDEX on table A(user_id) and you're trying to assign an existing value to more than one row.

Have a look at the next example:

create table a (id int, user_id int, f_id int);
create table b (id int, f_id int);
create unique index A_user_id_key on a(user_id);

insert into a
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

insert into b
values (1, 11),
       (3, 22),
       (3, 33), --<<<< there is another row with user_id = 3
       (4, 44);

When I try to update using your current query:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;

It returns same error message:

  ERROR:  duplicate key value violates unique constraint "a_user_id_key"
  DETAIL:  Key (user_id)=(3) already exists.

You could solve by avoiding existing values:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);

This is the result:

select * from a;

id | user_id | f_id
-: | ------: | ---:
 1 |       1 |   11
 2 |       3 |   22
 3 |       2 |   33
 4 |       4 |   44

db<>fiddle here

But I'll suggest to check wich are the duplicate rows using next query:

select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id <> b.f_id);

id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33

db<>fiddle here


SQL Server logical consistency-based I/O error

First; ensure you have a reliable, recent backup of the database in question.  Test the backup by restoring it to some other server, then run DBCC CHECKDB against it to determine if the backup contains the corrupted page(s).  If the backup shows no corruption, and DBCC CHECKDB returns a clean bill-of-health for the database, you might consider just restoring the backup if it's recent enough to meet your recovery point objective.

Second, backup the database (while ensuring you don't overwrite any existing backups you may need), and restore it to some other machine.  Run DBCC TRACEON (3604);  DBCC PAGE (5, 1, 9545681) to see what kind of data is stored in the page - look for m_objId and m_indexId in the output, then check that against sys.objects and sys.indexes.  Best case scenario, the data will be part of a non-clustered index that you can simply drop and recreate with ALTER INDEX ... REBUILD.  If the data on the page belongs to a clustered index, or a heap, you run the risk that DBCC CHECKDB (repair_allow_data_loss) will cause you to lose access to the entire table, or at least part of the data stored in it.

If you manage to get the corruption issue resolved, you should determine the root cause so you can mitigate the issue, and prevent future corruption.  If hardware is an issue, replace it.

Brent Ozar has a great article detailing how to approach this problem on his blog.


Composite indexes: Most selective column first?

From AskTom

  (in 9i, there is a new "index skip scan" -- search for that there to read about that. It makes the index (a,b) OR (b,a) useful in both of the above cases sometimes!)

  So, the order of columns in your index depends on HOW YOUR QUERIES are written. You want to be able to use the index for as many queries as you can (so as to cut down on the over all number of indexes you have) -- that will drive the order of the columns. Nothing else (selectivity of a or b does not count at all).

One of the arguments for arranging columns in the composite index in order from the least discriminating(less distinct values) to the most discriminating(more distinct values) is for index key compression.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx_1 on t(owner,object_type,object_name);

Index created.

SQL> create index t_idx_2 on t(object_name,object_type,owner);

Index created.

SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name ), count(*)  from t;

COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)      COUNT(*)
-------------------- -------------------------- --------------------------      ----------
                 30                         45                       52205      89807

SQL> analyze index t_idx_1 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          2           28

SQL> analyze index t_idx_2 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave  from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          1           14

According to the index statistics, the first index is more compressible.

Another is how the index is used in your queries.
If your queries mostly use col1,

For example, if you have queries like-
select * from t where col1 = :a and col2 = :b;
select * from t where col1 = :a;

-then index(col1,col2) would perform better.

If your queries mostly use col2,
select * from t where col1 = :a and col2 = :b;
select * from t where col2 = :b;

-then index(col2,col1) would perform better.
If all of your queries always specify both columns then it doesn't matter which column come first in the composite index.

In conclusion,
the key considerations in column ordering of composite index are index key compression and how you are going to use this index in your queries.

References:
Column order in Index
It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right) ?
Index Skip Scan – Does Index Column Order Matter Any More ? (Warning Sign)


Alternative to removing ONLY_FULL_GROUP_BY

I would suggest rewriting the query in a way that minimises the number of columns necessary to put into GROUP BY. In your case you can do that by applying the grouping to the Product_Category table only.

According to your example, that table has the following entries:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          1 |           2 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

Since you want product names to be unique in the output, group this table by product_id, and for category_id select e.g. the minimum value per product:

SELECT
  product_id,
  MIN(category_id) AS category_id
FROM
  Product_Category
GROUP BY
  product_id

That will give you an output like this:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

You can see that each product is listed only once. Joining that table to the other two will not produce duplicates. Therefore, just substitute the above query, as a derived table, for the Product_Category in your query (also removing your GROUP BY from it, of course):

SELECT
  *
FROM
  Product
  JOIN (
    SELECT
      product_id,
      MIN(category_id) AS category_id
    FROM
      Product_Category
    GROUP BY
      product_id
  ) AS pc USING(product_id)
  JOIN Category USING(category_id)
;


Not able to restore a database with multiple backup files from Azure blob storage (block blobs)

I found the reason why I was not able to get the list from the backup files. Since I used Ola Hallengren's script to run my backup the files were saved in a sub folder with Instance_name>Database_name>FULL>Backupfile_name. I supposed to provide the actual backup path of the backup file by going into the properties of the backup file and then copy the path from there. Instead I was just copying the container path and then adding the backup file at the end.
It looked something like below.
https://storagesqlbackups.blob.core.windows.net/fullsqlbackup/dev-sql-01/Test_DB/FULL/dev-sql-01_Test_DB_FULL_20200821_220001.bak


Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)

Since you are already referencing the SomeList table from the SomeListAuxData table, why not add "ON DELETE CASCADE" to the foreign constraint definition on SomeListAuxData?

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);

This way, if you delete record with an id of 1 in the SomeList table, any record in SomeListAuxData referencing record 1 will be deleted as well.

As to deleting a % of rows at a time, I like to use a loop. This one deletes 10,000 records at a time, until there are none left.

delete top (10000) from SomeList WHERE TimeData < @someTime AND Id = @someListId
  WHILE @@ROWCOUNT > 0
  BEGIN
        waitfor delay '00:00:05' -- add in a delay optional

        delete top (10000) from SomeList WHERE TimeData < @someTime AND Id = @someListId

  END

Update 14:09

Using the TOP operator, you can also specify a percent of rows.

DELETE TOP(1) PERCENT
FROM SomeList
WHERE TimeData < @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) > 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) < @someValue;


This inconsistency error can be resolved by running the following command:

dbcc checkdb(database name,repair_allow_data_loss)


Is Neo4j 2 replication architecture a master-slave or multi-master?

Neo4j is a master-slave cluster.

While you can write to slaves for safety, it will take locks on the master and affect your whole cluster's performance.


Falied to load resource files of Oracle APEX plugin

After APEX upgrade from one version to another you should recompile i.war by running java -jar ords.war validate --database apex. java -jar ords.war static could also be useful.

For debug it is convinient to edit ORDS config file defaults.xml with these strings:

<entry key="debug.debugger">true</entry>
<entry key="debug.printDebugToScreen">true</entry>

Don't forget to set them false on production.


Microsoft Links for SQL Capacity planning

What I would think to do is see how your current server is performing by benchmarking the older hardware. This will allow you to get a good baseline so you do not go backwards from the migration.

To really hammer the disks have a few workstations use CrystalDiskMark and all point to the server, this will allow you to get concurrency testing also to simulate load.

As far as CPU, I default to Perfmon to see how the CPUs are holding up on the older hardware. If they are getting hammered then you either need to get more CPU or/and start tuning.

For the new server, Brent Ozar Unlimited has a first responder kit that has a good setup guide for SQL Server. However, he has a section in the setup guide that is a bit outdated, "Format the drives with 64K allocation blocks". Honestly, I don't believe this applies anymore :)

You can get this guide and a bunch of other goodies at First aid.

When you have the new server online, I would then start to have your QA team run through testing to make sure that everything works and that you are also getting the same or better performance from the server. Remember in 2014 we have a new cardinality estimator along with other features.


OPENROWSET Bulk insert Text File NULL Columns

Ended up using a combination of Power Shell and SQL, PS cleans the files first, adds the correct headers and formatting combined with a format file I have been able to get this to work and it is reliable.
If anyone does have any thoughts or suggestions please post them.


MySQLRouter on Windows 10 Unable to connect to the metadata server

I think I have solved it.

It appears that there is a difference between Group Replication and Innodb Cluster that perhaps needs to be made clearer in the documentation.

Anyway, I ran:

mysqlrouter --bootstrap root@[::1]:3306

and got:

Error: Expected MySQL Server '::1:3306' to contain the metadata of MySQL InnoDB Cluster, but the schema does not exist.
Checking version of the metadata schema failed with: Error executing MySQL query: Unknown database 'mysql_innodb_cluster_metadata' (1049)

This was the first clue that something was missing. Then I eventually found the following:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

This tells you that you need to "convert" group replication to an Innodb Cluster by running:

var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});

This tells you that:

A new InnoDB cluster will be created based on the existing replication group on instance 'localhost:3306'.

After that I tried bootstrapping mysqlrouter, and it worked.


Security for Oracle linked server from SQL Server

If the user that you added to use the Linked Server is not for the login you are currently using (and it is the only one you've set up), when you click OK, the connection test will fail because the current user will have no valid mapping to use the Linked Server.

To fix this simply create a valid mapping for your login as well. That way when you click OK the test will succeed because there is a valid mapping for the current user - if you use the same settings for the Oracle target in both mappings you'll have some confidence that it will be working for the other user(s).   Either that or ignore the error from the test and check specifically that the user you created the mapping for can use the linked server successfully.


Modifying replication distributor & publisher

Changing the IP addresses is no problem.  You'll just need to force AD replication after the machine comes up with the new IP then do an IPCONFIG /flushdns on the other machines so that they can all see each other.  Depending on which machine has it's IP changed and if you are doing push or pull replication you may not even need to do that.

Moving the distribution database is a lot harder to do.  You can shutdown all the replication agents and do a backup and restore of the distribution database to the new machine, making sure to use the KEEP_REPLICATION flag.  You'll then need to recreate all the SQL Agent jobs for replication on the new machine manually.  Then you'll need to edit the replication tables manually to change the distributor to the new name, then edit all the agent jobs to give them the name of the new distributor.  Once all that is done everything should work again.

Moving the publisher requires that the same approach be taken as moving the distribution database.


SQL Server - Update with Output not working in Parallel environment

While I agree that "the sp_getapplock/sp_releaseapplock approach [is] the most straight forward and least dirty way to manage explicit semaphore logic."

There is a reason that's not working and a way to fix it directly, and enable multiple, concurrent transactions to simultaneously fetch disjoint rows:

This should fix it:

UPDATE sys_QueueJob
    SET StartedOn = GETDATE()
    OUTPUT DELETED.Id as Result
    WHERE Id = (select top 1 qj.Id
                    from sys_QueueJob qj with (rowlock, updlock, readpast)
                    where qj.ProcessedOn is null AND qj.StartedOn is null
                    order by CreatedOn asc)

See Using Tables as Queues


MySQL SELECT query: i want show duplicated entries

If you really need it (I don't get why though), you can do

SELECT code_CAT
FROM categories a
INNER JOIN
(
  select 14 as id_code
  UNION ALL
  select 14
  UNION ALL
  select 14
  UNION ALL
  select 14
)b ON (a.id_code = b.id_code)


How to Insert JSON data to BYTEA column in the table in POSTGRESQL?

There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
BEGIN
 INSERT INTO "TBL1" ("SN","COL1","COL2")
 values(1,
    '{
      "APPLICATION": {
         "MEMORY": {
         "OPTIONS" :{
                      "SOMETHING" : "SOMETHING",
                      "format" : "SOMETHING",
                      "System" : "",
                      "IP" : "",
                      "Port" : "",
                      "template" : "",
                      "Path" : "" ,
                      "Name" : "QUEUE",
                      "URL" : ""
        }}}}', --<< add the missing curly braces!
     '{
      "APPLICATION": {
       "MEMORY": {
       "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
        }}}}'); --<< add the missing curly braces!
END;
$$
LANGUAGE plpgsql;
COMMIT;

You also don't need PL/pgSQL for this. A language sql function would be enough (you would need to remove the begin and end though)


Compare row value when records are not successive

As already suggested by Rick James in the comments, if you are working with MySQL 8+, you can make this much easier by using the LAG() analytic function to return the previous row's price. You can also get the variation using the SIGN() function instead of the CASE expression, as demonstrated in the following query:

SELECT
  pprod.price,
  DATE_FORMAT(purch.date, '%d/%m/%Y')                                   AS date,
  prov.name                                                             AS provider,
  SIGN(pprod.price
     - LAG(pprod.price, 1, pprod.price) OVER (ORDER BY purch.date ASC)) AS variation
FROM
  tb_purchase_product AS pprod
  INNER JOIN tb_purchase AS purch ON pprod.id_purchase = purch.id
  INNER JOIN tb_provider AS prov ON purch.id_provider = prov.id
WHERE
  pprod.id_product = :id_product
  AND purch.date BETWEEN :date_initial AND :date_final
ORDER BY
  purch.date DESC
;

In this query I have also rewritten the joins using the JOIN syntax. Using the explicit JOIN syntax separates joining conditions from filter conditions more clearly, which, in my estimation, makes the code more understandable and easier to maintain (but your mileage may vary).

And with the use of the LAG() function there is no need to join a second instance of tb_purchase_product, which is another obvious difference of this query from the original.

One important note regarding this query is that, as written, it works for a single id_product, which should be all right based on your filter conditions. But if you ever need to run it for multiple products simultaneously, you need to introduce partitioning to the LAG function, like this:

LAG(pprod.price, 1, pprod.price) OVER (PARTITION BY pprod.id_product
                                       ORDER BY purch.date ASC)

The PARTITION BY subclause ensures that the previous pprod.price value is for the same product as the current row.


I've knocked up a MODEL clause to do this.

Sample data:

create table sampledata
(
  dat varchar(255)
);

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe');

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe');

Query:

WITH PIVOTEDDATA AS(
SELECT   * -- RN, POSITION, DAT
  FROM   SAMPLEDATA
   MODEL
    RETURN UPDATED ROWS
    PARTITION BY(ROWNUM RN)
    DIMENSION BY (0 POSITION)
    MEASURES     (DAT ,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0) DATA, REGEXP_SUBSTR(DAT,'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0)) USERNAME )
    RULES
    (DAT[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] =
      REGEXP_SUBSTR(DAT[0],'[^\\]+',1,CV(POSITION)),
    USERNAME[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] = REGEXP_SUBSTR(DAT[0],'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT[0],'[^\\]+','')),0)))
)
SELECT *
FROM PIVOTEDDATA;

There will be an easier way to do this, but I needed some MODEL practice and my brain hurt thinking about how to do the unpivoting and keeping the last record.

SQL Fiddle.

If you've never used MODEL before, my apologies.


Why isn't mixed-mode replication default on MySQL

I asked myself this question recently as well. At first, I looked at why row-based replication wasn't enabled by default. Obviously, row-based replication creates the possibility of IO-strain on disks by the amount of data that potentially could be written to the binary log.

Not surprisingly, this is the number one disadvantage of row-based replication the MySQL documentation mentions. A bit further down, another disadvantage would be a potential issue with performance with MyISAM:

  For tables using the MyISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication.

MyISAM is the default engine of the 5.1 series. It is not until 5.5 that InnoDB is default. This replication impact on MyISAM, to me, is a strong reason not to use RBR by default.

But your question revolves around the MIXED format. Under MIXED format, the default is to use statement-based replication until a statement is called that required row-based replication. The link above gives a nice overview of what type of logging gets performed on certain situations (safe, unsafe, row injection).

I think there are too many potential situations that would cause replication to switch to row-based replication, causing some of the performance issues noted above on non-optimal  hardware. And let's face it, MySQL has a large following of users running it on non-optimal hardware.

This situation stands out to me the most:

  If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.

Note that all statements involving temporary table in row-based logging is considered unsafe, so this could cause issues with your replication setup.

Ultimately, I think it is a good idea to have STATEMENT be the default, having to make a conscious, educated decision to switch to MIXED.


Calculate size of the filtered rows of a table in sql server

You are not going to get accurate figures for space used back in time because of differences in how efficiently things are laid out in the pages as your data changes over time.

Your "inserted date" column will give you an estimate of the number of rows present, by taking the current row count and subtracting the count of rows inserted since the date you are measuring for (or just counting those inserted on/before that date). It may give an accurate reading rather than an estimate if:

the table is never deleted from
the audit date is only updated on insert not also on update
any and all updates are performed with UPDATE or MERGE and not DELETE-then-reINSERT.

You can use the row-count-at-a-date-in-the-past to estimate the total size of the pages it occupied by taking calculating (data_size_now * rows_then) / rows_now.

That is for trying to look back in time when you haven't kept logs. Maintaining accurate records going forward is easy: setup a job that runs the analysis for each table and have it store the results for later review.


mongodb: Can I create an unshard Replica set for a sharded cluster?

As per MongoDB documentation here Starting in MongoDB 3.6, all shards must be replica sets. Before you can upgrade your 3.4 sharded cluster to version 3.6, you must convert any shards that are running as standalone mongod instances to replica set shards.

  Note: To converts a standalone shard to a single-member replica set shard. The procedure assumes that the
  single member runs on the same host and port as before.


Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine

Yes, data should be cleansed before inserting.  (Apparently, you are belatedly doing the cleansing.)
This may help:  DROP INDEX on phone_num while cleaning up the numbers.  ADD INDEX after finished.
Do the UPDATEs in chunks.  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks  Chunk based on the PRIMARY KEY; check for errors; chunksize only 1000.
It would take some code, but you might benefit by having 16 (a la 16 cores) connections, each working on chunks of the UPDATE.  (Don't expect more than 8x improvement; might not get even that much.)
Please provide more details if you want to discuss further.


REGEXP_REPLACE is matching but not replacing with empty string

UPDATE files
SET files_name =
    REGEXP_REPLACE(
       files_name,
       '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)',
       '\\1'
    )
WHERE type = 5;

fiddle

UPDATE

  I executed this command, with redacted info of course...

mariadb -h ip -P port -D database -e "UPDATE files SET files_name = REGEXP_REPLACE (files_name, '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)', '\\1') WHERE type = 5;" -pPASSWORD

You need to quote a slash:

in command line
in SQL query

So you would use '\\\\1'.

You may avoid using slash by using CONCAT(CHAR(92), '1') instead of '\\1' in the query text - in that case no slaches in SQL and quoting don't need in.


A construct like the following should work for you:

select *
from (
  select *
  from schemaA.tableA
  union all
  select *
  from schemaB.tableB
  union all
  ...
)
where columnA = "value";

You'll have to check the execution plans to see if the optimizer is smart enough to push the where clause into the subquery for each table.

The other thing to remember is that you can generate your queries by querying the metadata for a list of schema and table names.


MySQL: How to calculate potential wait time?

Is this saying the average wait time is 3.2s and occurred 11,543 times
  for a total of approximately 10.5hrs of aggregate wait time?

No, this is saying the wait occurred 11,543 times for a total aggregated wait of 3.2s.
The average wait time is then 3.2s / 11,543, or 0.285 milliseconds.

Note that every file io call, including file open, read, seek, close, read, write, etc is counted, and that what is measured is FILE io (at the OS level), not DISK io (at the hardware level).

Disclosure: I happen to work at MySQL, and I am the author of the performance schema.


How to query SQL Server Memory Manager performance counters in DMV?

The information you seek is available in the DMV sys.dm_os_performance_counters.

select object_name,
       counter_name,
       instance_name,
       cntr_value,
       cntr_type
  from sys.dm_os_performance_counters
 where 1=1
   and [object_name] = 'SQLServer:Memory Manager'


SQL Server Disk throughput slower than diskspd

There are a few conceptual things to work through. When you run a tool like diskspd, you're benchmarking theoretical limits of your storage, not guaranteeing a specific performance profile of a query. Additionally, you're only testing for the configured pattern (2MB block size, queue depth of 32, 56 threads, 100% read [random? sequential?]). SQL Server has varied read/write patterns and there's no guarantee that your query follows this test pattern. Essentially, you're testing two different things with diskspd and a query.

The following two points are primarily valid if you're using a SAN.

Additionally, different storage tools run tests in different ways. sqlio sizes a 10GB test file with a null byte (0x0). diskspd appears to have a varying pattern, but it repeats nonetheless.

fio sizes with random data. I've only used vdbench once and have honestly not checked what it uses to fill files. I bring this up because most SANs will compress and deduplicate empty space and repeating patterns. As such, you're not necessarily testing performance on a files of a similar size (assuming your SQL Server data file is 10GB) on the SAN. Your data file can be compressed and deduped, to be sure, but probably not to the same level as files generated by diskspd.

This leads to a final point I'd like to make. Depending on your SAN's cache size, generally a 10GB file isn't large enough to actually even test your SAN performance (from an IOPS perspective). The SAN controller is probably able to compress and dedupe this test.dat file down to something of reasonable enough size to sit in controller cache and never actually touch the backing disks. As such, what you're testing is the path between the SAN controller and the OS, which you've identified as 1400MB/s.


How to add entries in a table column as the attributes for another table in MySQL?

You should have a table something like this:

CREATE TABLE Attendance (
    class_id ... NOT NULL,
    student_id ... NOT NULL,
    date DATE NOT NULL,
    PRIMARY KEY(class_id, student_id, date)
) ENGINE=InnoDB;

The existence of a row says that a particular student was in a particular class for the particular day.

This is perhaps the cleanest way to store the data.  Remember:  A database is a repository for data, not a reflection of reports you might like to present.  Reports are derived from raw data.

Note that the table is not limited by the number of classes, students, or dates.

This, for example, reports how many students were in each class for each day:

SELECT class_id, date, COUNT(*)  FROM Attendance  GROUP BY class_id, date;

If you want to turn that into a 2D matrix, see the [pivot] tag.  (It's a bit tricky.


Multiple m:m relationships LEFT JOIN giving duplicate records

If you want only one row of output for each Invoice, you need to figure out what to do with multiple customers tied to that one invoice.  (Etc.)  Here's one approach:

SELECT i.*,
       GROUP_CONCAT(
           ( SELECT c.name FROM invoice_customers AS ic
                           JOIN customers AS c  USING(customer_id)
                           WHERE ic.invoice_id = i.invoice_id
           ) SEPARATOR ", "
                   ) AS customers
    FROM invoices AS i
    ...


Oracle Express intermittently stops responding, requiring a OS reboot to fix

You are encountering these problems because you are running a 32-bit version of Oracle XE on 64-bit Windows. This is not a supported configuration, and is known to have many documented problems.

Due to there not being a 64-bit version of XE, you can instead install the Standard Edition (free for development) from here. If you're also using APEX, you can get it here.


Can we create SQL Server Aliases in Linux clients?

Aliases on Windows are set using the SQL Server Configuration Manager GUI. The Linux equivalent is mssql-conf, but there isn't an equivalent to aliases on Linux.
(I don't think this is a case of "isn't supported yet" related to Linux support being relatively new. My guess on the future is just a hunch & guessing rather than fact.)
If you need server name redirection, I'd recommend using a CNAME to redirect that name for all clients centrally, rather than a client by client setting. Alternatively, I'd use the hosts file to redirect from one server name to another.
If you need to also do credential impersonation (your example implies this), allowing the user to supply one credential, and using something different to connect to the database, that sounds like something to be handled by your application, rather than expecting the drivers to do it for you.


Indexed views contribute to the write overhead as much as regular indexes on the tables do, if not more.

Every time you write to the base tables, the index on the view has to be updated immediately.

As with regular indexes, there is a tradeoff: if your index slows down reasonably and gives a significant performance boost on reads, then keep it. If not, get rid of it.


Aside from the recommendations made by Danielle and J.D., I suggest you examine how the data is represented and the frequency.
For example, if the data is transmitted in a JSON or XML format, which is handy for transmission and processing, the size will be large (e.g. a numeric string vice a float or an int).  Also, string identifiers (e.g. serial number, credit card number) can be made into PK/FK relationships that could save space.
Your data sampling frequency may be higher than what is realistically usable. For example, if you get weather data at 10 samples/sec but your analysis only has 1 hour resolution, you have a lot more weather data than you can use. Perhaps resampling the data to a lower frequency (e.g average and standard deviation, low-pass filter) and storing that might be a better solution. You can always archive the raw data--archival storage for a years worth of data is not very expensive.


Oracle Restore and recover Database on the same host?

N.B. This has been editied since its original posting.

You may want to look at duplicating the database to a new name provided you have all the required backups and archivelogs to restore to the required point in time.

I am assuming that ASM is involved here, if not you might need to look at the duplicate command to see how to rename the files as it's done automatically by ASM.

If your using standard os files rather than ASM then you might need to include this in your duplicate statement once you've connected to the atarget and auxiliary databases (see below);

run
{
  SET NEWNAME FOR DATAFILE 1 TO '/oradata/instance/file1.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/oradata/instance/file1.dbf';
  <repeat for all the files in your Database>
  DUPLICATE TARGET DATABASE to newdb;
}

Now the theory should be;

Create a new pfile (from the existing database and rename the various references to the database), making sure you change any log destinations and the directories exist

Start the new instance in a no mount state

Then start

rman
Connect target sys@instance
Connect auxiliary /

Once the two connections are up.

The duplicate command to restore to a point in time should be as follows

DUPLICATE TARGET DATABASE TO newdb UNTIL TIME TO_DATE('20/07/2011 19:45:00','DD/MM/YYYY HH24:MI:SS')

-- This should be prior to when you created the Test table so the database will be restored upto that point in time. It theory at least.

another option i do find useful is to resize the logfile and groups

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('/oradata/instance/redo01a.log',
               '/oradata/instance/redo01b.log') SIZE 200K REUSE,
      GROUP 2 ('/oradata/instance/redo02a.log',
               '/oradata/instance/redo02b.log') SIZE 200K REUSE;

or with ASM something like

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('+DATA',
               '+FRA') SIZE 200K REUSE,
      GROUP 2 ('+DATA',
               '+FRA') SIZE 200K REUSE;

There are several options you can also specify, I believe one is to an scn, sequence or until a date time. You might need to do a to_date convertion for the last option. putting these together should help you get what you're after.


Are you running from the 10gen repository or from the default Debian/Ubuntu repo? I recommend using the official 10gen repository.

Check this link out - [10gen MongoDB How-To Install on Ubuntu:] http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. It is best to uninstall the previous mongodb installation prior to this change, which will also require you to modify your repository source (in /etc/apt/source.list) but this is also detailed in the link above. I recommend using upstart over sysvinit and the processed is outlined in the How-To.

With the 10gen Ubuntu set-up, the configuration file is in /etc/mongodb.conf.

There are several ways that you can you can run a separate mongod process, one would simply be running it from the cli -

sudo -u mongodb mongod --dbpath /var/tmp/mongotest --logpath /var/tmp/mongotest_log --port 3001 &

which will produce (using 'ps')

mongodb   2210  3.3  1.5 259012 15300 pts/0    Dl   11:48   0:00 mongod --dbpath /var/tmp/mongotest --logpath /var/tmp/mongotest_log --port 3001

and you can connect via the cli -> mongod --port 3001

Another way is creating a copy of /etc/mongodb.conf -

sudo cp /etc/mongodb.conf /etc/mongodbnew.conf and editing the following lines in the new file -

# mongodbnew.conf

dbpath=/var/lib/mongodbnew

#where to log
logpath=/var/log/mongodb/mongodbnew.log

port = 27018

I have changed the dbpath (where the mongodb files are stored) from /var/lib/mongodb to /var/lib/mongodbnew; the log path has changed from mongodb.log to mongodbnew.log and the port has changed from the default of 27017 to 27018 (you will also have to remove the # to uncomment the line).

I also changed the top line to reflect the new name of this configuration file.

You will also have to create the data directory because it won't exist and without it, the mongod process won't start and ensure that the owner and group is mongodb -

sudo mkdir /var/lib/mongodbnew
sudo chown -R mongodb:mongodb mongodbnew/

Additionally, create and change the persmissions of your log file -

sudo touch /var/log/mongodb/mongodbnew.log && sudo chown mongodb:mongodb /var/log/mongodb/mongodbnew.log

To start your new mongod process (in the background) from the cli, type

sudo -u mongodb /usr/bin/mongod --config /etc/mongodbnew.conf &

which send the following to the screen -

"all output going to: /var/log/mongodb/mongodbnew.log"

Check that the mongod processes are running with "ps" -

sysadmin@ubuntu:/var/lib$ ps auwx | grep mongo | egrep -v 'grep|sudo'
mongodb    921 11.3  1.5 627672 15608 ?        Ssl  10:56   4:30 /usr/bin/mongod --config /etc/mongodb.conf
mongodb   2137  7.8  1.5 627672 15880 pts/0    Sl   11:36   0:00 /usr/bin/mongod --config /etc/mongodbnew.conf

To verify that you can connect, run the Mongo shell -

$ mongo --port 27018
MongoDB shell version: 2.0.5
connecting to: 127.0.0.1:27018/test
>

With lsof, you should now see your two mongod processes, the original bound to 27017 and the new one bound to 27018.

$ sudo lsof -i :27017
COMMAND PID    USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mongod  921 mongodb    6u  IPv4   9066      0t0  TCP *:27017 (LISTEN)
sysadmin@ubuntu:/var/lib$ sudo lsof -i :27018
COMMAND  PID    USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mongod  2137 mongodb    6r  IPv4  11923      0t0  TCP *:27018 (LISTEN)

To ensure that the two processes run at start-up, copy the original init configuration file -

sudo cp /etc/init/mongodb.conf /etc/init/mongodbnew.conf

which should result in two files such as -

$ ls -lart /etc/init/mongo*
-rw-r--r-- 1 root root 536 May  8 15:51 /etc/init/mongodb.conf
-rw-r--r-- 1 root root 554 Jun 11 11:43 /etc/init/mongodbnew.conf

Edit the new mongodbnew.conf file using vi or whatever you prefer to look like this -

## Ubuntu upstart file at /etc/init/mongodbnew.conf

limit nofile 20000 20000

kill timeout 300 # wait 300s between SIGTERM and SIGKILL.

pre-start script
    mkdir -p /var/lib/mongodbnew/
    mkdir -p /var/log/mongodbnew/
end script

start on runlevel [2345]

stop on runlevel [06]

script
  ENABLE_MONGODB="yes"
  if [ -f /etc/default/mongodbnew ]; then . /etc/default/mongodbnew; fi
  if [ "x$ENABLE_MONGODB" = "xyes" ]; then exec start-stop-daemon --start --quiet --chuid mongodb --exec                /usr/bin/mongod -- --config /etc/mongodbnew.conf; fi

    end script

I have tested this on Ubuntu 12.04 and it appears to work well, including after a reboot. I haven't run it in production obviously. Apologies, if there's any typo's above but there's a lot of information and I may have missed something.

Finally, here's a link on Replica Sets that might help you as it has come examples on starting multiple mongod instances from the cli - http://www.mongodb.org/display/DOCS/Replica+Set+Tutorial.


Having trouble with Merge Join with SSIS

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy