Once again, thank you to Vladimir for providing the formula!
The correct way to accomplish what I need is this:

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET
        iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1),
        step =       (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable


Answer to your question will depend on the answer to the following.

Most queries need 75% meaning 75MB in your case.

Do you mean all queries combined need 75MB?
Each of those queries need 75MB individually?

If all queries combined need 75MB then you can have multiple smaller files and you will not increase overall space because those files should not grow.

If each query needs 75MB then are they running in parallel or serial? If parallel you must be using more than 100MB today.  If serial than I do not see why you need multiple tempDB files.

Are you seeing contention on PFS and SGAM pages?

I suggest you review these articles regarding your question.

https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d


If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different?

tl;dr No.

The binlogs contain a set timestamp command before every transaction exactly to prevent this situation. This will only affect the session scope of the SQL thread so running queries meanwhile won't report date 600 seconds in the past.

To take a look you can run:

$ sudo mysqlbinlog --base64-output=DECODE-ROWS binlog.XXXXX |head -30

#150613  9:06:38 server id 1  end_log_pos 478584 CRC32 0xd86fcd7b   Query   thread_id=88627 exec_time=0 error_code=0
SET TIMESTAMP=1434179198/*!*/;
BEGIN
...


When you want to print object property values, then it should be used with special syntax $() shown as below:-

Write-Output "Removing old software from directory: $($site.INSTALLDIR)"

In case you want to print the variable name as well, put a backtick before the variable usage. Example:-

Write-Output "Removing old software from directory (`$site.INSTALLDIR): $($site.INSTALLDIR)"


Difference between net start MongoDB and mongod

You can always run mongod with a command. But if you decide to run mongod as a Windows service, in first step you need to set up a service by running mongod with --install option:

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --config "C:\Program Files\MongoDB\Server\3.4\mongod.cfg" --install

Then, you can use net start/stop to use the installed service:

net start MongoDB


How to truncate all tables with a specific name in all databases

In SQL Server 2000 you can do this using a cursor against sysdatabases and building a check against each db's sysobjects table for the error_log object. If it exists, truncate. Rinse & repeat.

DECLARE @sql NVARCHAR(4000), @db SYSNAME;

DECLARE c CURSOR
  LOCAL READ_ONLY FORWARD_ONLY STATIC
  FOR SELECT name FROM sysdatabases WHERE dbid > 4
  AND CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'Status')) = 'ONLINE';

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@db) +'.dbo.sysobjects
    WHERE name = ''error_log'' AND xtype=''U'')
    TRUNCATE TABLE ' + QUOTENAME(@db) + '.dbo.error_log;';

  EXEC sp_executesql @sql;

  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

In SQL Server 2005 and up, you can do this without the explicit cursor (and using the more modern catalog views), but in 2000 this method would not be safe - depending on how many databases you have you would risk exceeding 4000 characters, and you can't use a MAX type in SQL Server 2000 (or declare TEXT/NTEXT as a local variable):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  +'.sys.tables WHERE name = ''error_log'')
      TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.error_log;'
  FROM sys.databases
  WHERE database_id > 4
  AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;


SELECT s.*
FROM stackoverflow s, ( SELECT reference, MIN(time) time
                        FROM stackoverflow
                        GROUP BY reference
                       ) o
WHERE s.reference = o.reference
ORDER BY o.time ASC, reference DESC, time ASC;

fiddle

Everything is simple. You want to sort groups by the datetime of the most aged record in the group. The subquery obtains this info, and it is used in main query by joining it to each record in a group. For to understand replace s.* with * in output and analyze the result.


In PostgreSQL is there a way to rank/weigh columns so as to order the results?

Full text search has detailed weighting mechanisms.

For simple pattern matching:

SELECT *
FROM   t
WHERE  col_a ILIKE '%keyword%' OR
       col_b ILIKE '%keyword%' OR
       col_c ILIKE '%keyword%'
ORDER  BY col_a ILIKE '%keyword%' OR NULL
        , col_b ILIKE '%keyword%' OR NULL
        , col_c ILIKE '%keyword%' OR NULL;

I don't use col_a  ILIKE '%keyword%' DESC, because that would sort NULL columns first. We want to treat NULL just like FALSE, which is achieved this way.


How to use OR operator in CASE THEN statement

Assuming two booleanexp1s is a misprint:

select col1, col2
from table1
where ((booleanexp1) AND (col1 IN (a, b)))
   or ((booleanexp2) AND (col1 IN (c, d)))

PS. not (booleanexp1) is one possible option of (booleanexp2)...


MySQL server upgrade, how to adapt variables?

If your dataset size is only 1,3MB, then buffer_pool_size = 256M gives you a lot of  growing room.

If HDD, change innodb_io_capacity back to the default of 200.

The slowlog (which you have already turned on) will provide info on what to fix if you have performance problems.

Has the server crashed?  If so, let's see details on that.

Do you have other apps crammed into the tiny 2GB of RAM?  Changing to 8GB is likely to help with that.


Retrieving month from date field in my database

This works

SELECT MONTH(CURDATE());

With the final query like

SELECT    *
FROM      tbl_orders
WHERE     MONTH(date) = @month
AND       YEAR(date) = @year;

Here @month and @year are numeric values from the administrator. Defaults could be

SET @month = MONTH(CURDATE());
SET @year = YEAR(CURDATE);

This is the quick and dirty solution applicable for a small table. If performance is an issue, consider putting a binary tree index on the date column or breaking it down into (month, year) with a hash index.

Binary tree index allows comparison like so

WHERE     date >= CONCAT(@year, '-', @month, '-', '01')
AND       date <  CONCAT(@year, '-', @month+1, '-', '01');

But this does not work when @month=12 unless you do some complicated date math, which can be done elsewhere. Note that you can do concatenation elsewhere too, to enable query caching.

With hash index

SELECT    *
FROM      tbl_orders
WHERE     month = @month
AND       year  = @year;


SELECT COUNT(*) * 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)

  …still quite slow (115 seconds on a 500M row test)…

You will find that SAMPLE BLOCK is a great deal faster because it does a tiny fraction of the IO that SAMPLE does. Of course if the data isn't evenly distributed it will cause the result to be less accurate, but this can be more than offset by increasing the sample size.

…but…

  In Oracle, we can use statistics…This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

This is the real problem. Out of date stats cripple the CBO and are therefore "a bad thing". You likely need to change your DBA policy.


There are several choices:

SQL Power Architect (download without registration from Google Code). This is open source, but it seems there is no further development.
Oracle's Data Modeler (but it only supports Oracle, SQL Server and DB2. PostgreSQL for example is not supported)
A free version of TOAD Data Modeler - although that has some serious limitations on the size of the model
Open ModelSphere a general purpose modeler (including UML) but can also be used for ER modelling


Showing data that is not already in the data base

SELECT args_list.id
FROM ( SELECT 1 id
       UNION ALL
       SELECT 3 ) args_list /* the ID's you need to check for presence */
LEFT JOIN names ON args_list.id = names.id
WHERE names.id IS NULL

In MySQL 8+ you can transfer the id's list into the query as CSV string and parse it to separate values in CTE. It will be something like (not tested)

WITH src AS ( SELECT '1,3' src ),
     RECURSIVE
     num AS ( SELECT 1 num
              UNION ALL
              SELECT num + 1
              FROM num
              WHERE num < (SELECT MAX(LENGTH(src) - LENGTH(REPLACE(src, ',', ''))
                           FROM src) ),
     ids AS ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(src, ',', num), ',', -1)
              FROM src, num )
SELECT ids.id
FROM ids
LEFT JOIN names ON ids.id = names.id
WHERE names.id IS NULL


SQL Server-MySQL master-master replication

(I'm putting this in an answer, as it's way too long for a comment.)

We have a scenario similar to yours for our bug tracking system. We use it internally, of course, but customers can also submit issues through a page we created on our customer SharePoint site.

What we decided to do was host the database and website only at the office and provide external access from there (which we were already doing for some of our SaaS customers). If the internet totally bombs out (rare), it's more important that we can continue to work than for our customers to be able to submit new issues.

In your scenario, I don't know how critical the data is, how much data there is, or how important it is for external users to be able to write data.

Perhaps you could consider using a database at the alternate location as a read-only secondary, but direct all writes to the primary. While this will probably involve some application changes to separate read-only and read-write connections, this type of solution might be enough to satisfy the requirements for the small amount of time the office internet is down.

Regardless, I stick by my recommendation to not mix MySQL/SQL Server if you can avoid it. IMO, you'll be better off long-term by directing resources into proceeding with the existing migration plan, and hold off on developing a more robust replication solution until that stage of the project is complete.

Also, definitely try your best to avoid any master-master replication scenario. These can be highly non-trivial to configure and support at the best of times. The $ and time that will be spent developing and debugging a solution involving master-master heterogeneous replication will be astronomical, and probably won't ever work correctly 100% of the time (actually, probably nowhere close to that). Not that a built-in homogeneous replication solution will be perfect either, but at least in that case, you can call for customer support if something blows up and you don't know how to fix it; if you roll your own solution, you're on your own.


SQL Server Data Tools for VS 2015 - Anyone using for SSIS development to multiple versions?

Visual studio 2015 SSDT work with SQL Server 2016 and has a backward compatibility to SQL server 2014 and 2012 only.

Target version of SQL Server    |   Development environment for SSIS packages
2016    |   SQL Server Data Tools for Visual Studio 2015
2014    |   SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2013
2012    |   SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2012
2008    |   Business Intelligence Development Studio from SQL Server 2008

The following link contains very helpful informations:

For SQL Server 2008 /R2 editions

Integration Services versions Side by side

You have to install Microsoft Visual Studio 2008 to deploy package for SQL Server 2008 or SQL Server 2008 R2 by default. (newer versions will not work correctly)

There are a lot of links saying that newer versions of visual studio cannot deploy SSIS 2008 packages correctly:

https://stackoverflow.com/questions/10451053/can-sql-server-data-tools-work-with-sql-server-2008-ssis
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bff5e145-1c63-4221-b2ba-009ea96a9e05/can-i-use-vs2012-to-create-and-deploy-ssis-for-sql2008r2?forum=sqlintegrationservices
https://www.sqlservercentral.com/Forums/Topic1560322-364-1.aspx


mysql ssl replication - fails while attempting to connect to master

The manual gives an explanation for IO error number 1129 and also the command that solves the problem:

  Error: 1129 SQLSTATE: HY000 (ER_HOST_IS_BLOCKED)

  Message: Host '%s' is blocked because of many connection errors;
  unblock with 'mysqladmin flush-hosts'

Note that this error may be a consequence of another underlying problem.


What is the difference between different utf8mb4 binary collations?

There are three differences as far as I can tell (according to their documentation):

Case-mappings (for LOWER() / UPPER() functions):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-uca

The LOWER() and UPPER() functions perform case folding according to the collation of their argument.

The difference between the two collations in this context is that the _0900_ version, being based on a newer version of Unicode, quite likely has more mapping definitions (and possibly even some corrections).

Padding vs No Padding (of trailing spaces):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-pad-attributes

The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations

Essentially, utf8mb4_bin ignores trailing spaces while utf8mb4_0900_bin does not ignore them. See the documentation (linked above) for an example.

Sorting (performance only, not the ordering):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-collating-weights

For _bin collations except utf8mb4_0900_bin, the weight is based on the code point, possibly with leading zero bytes added.

For utf8mb4_0900_bin, the weight is the utf8mb4 encoding bytes. The sort order is the same as for utf8mb4_bin, but much faster.

Translating that into human, they are saying that for a code point such as U+FF9D, utf8mb4_bin will see the UTF-8 encoded byte sequence of EF BE 9D and convert that into 00 FF 9D. But, utf8mb4_0900_bin will not convert it into the code point value. This is due to the UTF-8 byte sequence already being sequential, hence the ordering is the same as it for the code point values. So why bother with that extra conversion step?


If the restaurant is that complicated I would use a simple solution. I would have an ingredients table and a entrée table to reference those ingredients. For example is you have a choice of pasta, sauce, cheese, and a meat on the side. I would have a table to store that information. I would then have a desserts and drink table that is separate. You can store your cost in the ingredients table and then do a calculation. The order table would then reference all of the previous information. This is just my thought process, but it may work for you.


Things to look at / try:

Looking at the documentation again (now that you have posted the config file and configuration options), I think your setting of client charset is incorrect. You specify --with-iconv-char-enc=UTF-8 when configuring the build for the ODBC driver, so I suspect you need to also use UTF-8 when specifying the client charset option (or whatever encoding your app code is using).
Try rebuilding FreeTDS with --enable-odbc-wide-tests. This might help narrow down where the issue is (assuming the test uses Supplementary Characters).
Did you see the note about "Version 0.95 removed support for iconv" on the FreeTDS page for How to build: Configure and make? Not sure what, if any, impact this has, but if nothing else helps then you need to verify this part (i.e. that your build of iconv works and that it is being used).
Looking at the unixODBC website, the main page has a version history and you claim to be using 2.3.0 which, according to that history, was released in April 20th, 2010 (almost 6 years ago). Since then there have been several "wide character"-related bug fixes, as well as many other fixes. Even if some other change (#1 perhaps) fixes the immediate issue, I would still upgrade to the most recent version of this driver, 2.3.4, which was released on August 31st, 2015.
Something to consider, perhaps, but if the Microsoft driver does work, why not just switch to using that?


ALTER TABLE DROP COLUMN - server status 'Stopped'

Open another connection to the same server (just click the home screen tile again, which you used to open the current connection). It will tell you if the server is available or not. Maybe the UI got stuck somehow and did not update properly when the operation ended. Don't forget the connection could also have timed out and you need to re-connect (which usually happens automatically, but somethings things just go wrong).


I'm trying to enable SQL Filestream on Windows Server 2012 and SQL Server 2016 64 bit, Management Studio 2017

Root Cause?

During October we installed SQL Server 2016 on the server – but the desktop icons were still set to SQL Server 2017. When I used the 2016 Configuration Manager, it seemed to work.

Multiple versions installed on TEST Server:

Configuration manager on TEST Server with Configuration Manager 2017:

SQL Server Version on TEST Server:

Using SQL Server 2016 Configuration Manager:

So basically we were running the wrong version of configuration manager. Also we did a server restart. When I had previously tried to configure it on both 2016, 2017 configuration manager, it didn't work.


Database size can exceed the RAM capacity as it could contain normal disk based tables as well as In-Memory tables. However, the size of memory based tables should not exceed the available memory. This available memory could be less than the RAM capacity as there are other processes that will be consuming some amount of RAM.

List of various out of memory issues that could arise:

Resolve Out Of Memory issues

The memory required for In-Memory tables is not just the table size but it should also include the following:

indexes
row versioning
table variables
growth

Estimating memory requirements for memory optimized tables:

Estimate Memory Requirements for Memory-Optimized Tables


SQL Server - separate database for reports?

The answer is: yes, there is a benefit to doing it.  Reports on on operational database will use a lot of resources and will interfere with the performance of the operational system.  Remember that database performance is subject to mechanical constraints (disk heads moving back and forth and rotational latency as we wait for the right sector to make its appearance under the head).  You have two broad options for a reporting strategy:

Replicate your database onto another server and move the reporting sprocs onto it.  Reports are run off the replicated server.  This is the least effort and can re-use your existing reports and stored procedures.
Build a Data Warehouse that consolidates the data from your production systems and transforms it into a form that is much friendlier for reporting.  If you have a lot of ad-hoc statistical reporting that could be done acceptably from a snapshot as of 'close of business yesterday' a data warehouse might be the better approach.


Creating Auto Number field with preceding number of 0's?

Without getting into a technical argument, once you start getting fussy about the display of a number, it is no longer just a number. (Mathematically, we refer to a number as its value, while its display is referred to a numeral).

Why is this important? What you are asking for is not actually a number, but some way of displaying it. The short answer is that you will need to convert it to a string.

If you want to zero-pad a number, you use a combination of string operations:

::char will convert a value to a string
lpad(string,length,padding) will left-pad a string with the given padding.

Combining the two you get:

select lpad(id::char,4,'0'), …
from …;

where id is presumed to be your auto-numbered column.


What's the difference between partitions and many tables?

The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.

However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.

Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.

Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.


Can I use MySQL-SERVER tunning variables in MYSQL CLUSTER?

Yes, all this tuning variables are described in MySQL Documentation


So, dump both tables into two files and then see the difference with diff:

 mysql -h host1 -NB -e "SELECT * FROM db.table ORDER BY id" > t1
 mysql -h host2 -NB -e "SELECT * FROM db.table ORDER BY id" > t2

 diff -u t1 t2


You can use NOT EXISTSas:

SELECT t2.rid
FROM t2
WHERE NOT EXISTS (
    SELECT 1
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'YY'
)
AND EXISTS (
    SELECT 1
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'XX'
);

Your index T2:index(tag_id) is already covered by T2:unique(tag_id,rid) som you can get rid of that

I don't work much with MySQL, but I get the impression that JOINs are often preferred over EXISTS/NOT EXISTS. Translating the query (Note the DISTINCT):

SELECT DISTINCT t2.rid
FROM t2
JOIN t3 AS t31
    ON t2.tag_id = t31.id
   AND t31.tag_name = 'XX'
LEFT JOIN t3 AS t32
    ON t2.tag_id = t32.id
   AND t32.tag_name <> 'YY'
WHERE t32.id IS NULL;


An example - http://sqlfiddle.com/#!9/9eecb7d/59602

The explanation - COUNT(<expr>) counts all non-NULL values. Comparison operators return boolean values 1(=true) or 0(=false) and both are non-NULL so both would be counted by COUNT(). When you add the or NULL part, the entire expression is still 1 for true but becomes NULL when the comparison returns false (because of the ternary logic definition used in SQL). So the COUNT() only count "trues".

Thats for standard SQL. In MySQL you can use a different trick. Because true and false are actually 1 and 0, you can just sum(t.Status = 'completed') to get the right number without using NULLs.


Oracle query performance when filtering results

SELECT ident_account,ident_instrument,
        nvl(available, 0) + nvl(reserved, 0) + nvl(restrctd,0) AS total,
        nvl(available, 0) AS available,nvl(reserved, 0) AS reserved,nvl(restrctd, 0) AS restrctd,
        validfrom, minvaliduntil as validuntil
   FROM (
         SELECT h.ident_account, h.ident_instrument, h.validfrom, h.validuntil,
                rank() over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc) as rnk,
                sum(case when IDENT_BALANCEGROUP = 1
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as available,
                sum(case when IDENT_BALANCEGROUP = 2
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as reserved,
                sum(case when IDENT_BALANCEGROUP = 3
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as restrctd,
                min(validuntil) over(partition by h.ident_account, h.ident_instrument order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as minvaliduntil,
                h.amount,b.ident_balancegroup
           FROM holdings h INNER JOIN balancetype b ON h.ident_balancetype = b.ident_balancetype
        )
  WHERE rnk = 1;


This may be too late but try looking at the File settings for ReportServer and ReportServerTempDB, just like you normally would through SSMS on the instance. They may be defaulted to a few MB with a 1MB autogrowth.


A PostgreSQL backup leaves no trace in the database. If you use WAL archiving, you will get a *.backup file for each online file system backup in the WAL archive directory, but that's all.
You best use a third-party backup solution like pgBackRest, which will provide such information.


Minimizing the number of parent rows for a distinct column in a child table

I think i found the solution. On the delete step I put the these two order by clauses in

delete #myResultSet where id <>
    (
    select top 1 id
    from #myResultSet t1
    where #myResultSet.c_enum = t1.c_enum
    order by count(aKey) over(partition by aKey),
             count(bKey) over(partition by aKey, bKey)
    )

This gives me 10,13,87 which I think is the optimal solution I can get with my data set.

If I am wrong or there is a better way to do this I am welcome to more answers.


Does SQL Server 2008 R2 10.50.6560.0 Support TLS 1.2?

Build 10.50.6560.0 does support TLS 1.2.  I was able to resolve the problem by installing a new certificate from our CA.  Apparently the older certificates that are on the server are not capable of supporting TLS 1.2.

TLS 1.2 support for Microsoft SQL Server includes information regarding the client and server versions that are required to support TLS 1.2.


If your clients cope well with being disconnected, and don't lose data, you should probably use the first process: Force a checkpoint, then perform an immediate restart. That'll get the server back up and running faster than a fast restart will, at the cost of greater disruption to currently active clients.

Client applications will get errors when they next run an SQL statement. Well written applications will throw away the database connection and retry, so the user won't notice/see anything.

Less well written applications will report an error to the user.

Truly badly written applications will silently lose data (if they swallow exceptions) or perform only part of an operation (if it isn't properly using transactions). These clients will also have problems under other circumstances, and must be fixed.


Large queries on MyISAM tables crashing MySQL

I altered each of the tables that was using MyISAM as the engine, and the query time greatly decreased.  My site has also stopped locking up and responds much quicker during peek usage times.


Best practice - use of derived table or use the original table in join?

Your colleague is right.  Method 3's subquery can waste compute.  Therefore use Method 2.

Method 1 and Method 3 do not yield the same results.  The WHERE clause in Method 1 performs the filtering after joining the tables.  In effect, it turns the LEFT JOIN into an INNER JOIN.

Method 2 is equivalent to Method 3 because the AND clause performs the filter on TableB before the join (which is what the derived query does).

I created a SQLFiddle which illustrates the case.  Full code follows:

CREATE TABLE TableA(
    Field1 int
  , Field2 varchar(10)
  , Field3 varchar(10)
  );

CREATE TABLE TableB(
    Field1 int
  , Field4 varchar(10)
  , Field5 int);

INSERT INTO TableA
SELECT 0, 'Zero', 'Inner'
UNION ALL
SELECT 1, 'One', 'Inner'
UNION ALL
SELECT 2, 'Two', 'Left';

INSERT INTO TableB
SELECT 0, 'Zero', 0
UNION ALL
SELECT 1, 'One', 1
UNION ALL
SELECT 5, 'Five', 5;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method1
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
WHERE TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method2
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
    AND TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , DTable.Field4
  , DTable.Field5
INTO Method3
FROM TableA
  LEFT JOIN (SELECT *
             FROM TableB
             WHERE TableB.Field5 <> 0) DTable
  on TableA.Field1 = DTable.Field1;

SELECT *
FROM Method1;

SELECT *
FROM Method2;

SELECT *
FROM Method3;


Is there a sql command I can use to manually start replication?

Sample

-Publisher [ServerName] -PublisherDB [DatabaseName] -Distributor [ServerName] -Publication [PublicationName] -DistributorSecurityMode 1


SQL Developer Data Modeller and Data Dictionary question

The data dictionary will only show what it can.

Users can design their schema with or without referential integrity defined in the actual database.

When you import a data dictionary, using SQL Developer, to a new or existing relational data model, the foreign key constraints will be shown by default - if they are there to begin with.

Now, specific to the tool, it CAN attemp to infer any relationships. It does this by looking for common column names sprinkled around your tables, like an 'XYZ_ID' column, that happens to be a PRIMARY KEY in a driving table.

I have step-by-step instructions for this here, on my blog.


Do database servers need load balancing?

If you want to have a solution for automated failover you could take a look at Galera replication with Galera load balancer up front of your servers.
Galera will allow you to have a multi master cluster that uses the InnoDB storage engine. You can write and read to all servers.
If you don’t want to use a load balancer you can make your database logic aware of multiple database servers and distribute load in the database abstraction logic.

Continuent also provide a really nice MySQL replication/clustering product (Tungsten), the basic software is free however the full clustering system costs money.

Memcached: You should always implement caching whenever possible and try to cache as much static or infrequently changing data as you can. This is another thing you can handle in your database abstraction logic.

Implementing a true multi master system using a software like Galera or Tungsten with Memcached can provide HA and speed if done correctly.


Does SELECT * create additional overhead even when you want all columns?

Strictly answering your question, no there is no additional overhead when you want all columns returned.  But it is undesirable as you mentioned. I do not want to reiterate why it is undesirable as most of the points are discussed in this answer.


How can I maintain old information after an update?

You create an extra table with customer ID and all other fields that will possibly change over the time.

In the orders table, you link to the id of this new table, not to the costumers tables.

customers(customer_id, first_name, last_name...)
customer_info(id, customer_id, address, other field)
orders(id, customer_info_id, order_date, ...etc)


I think what you want is NOWAIT

  To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP LOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.

That said, I'm not sure why you would want to do this. I've never had to do this. It sounds like a bad idea. It's quite normal to wait for a lock to update the rows. Retrying transactions is usually a longer-process. If you're afraid of issuing an update you don't want to a modified row, you don't have to worry about it. Dirty-writes are prevented.

UPDATE foo
SET baz = 1
WHERE bar = 2;

Will never update where a bar isn't 2. If it's waiting for a lock, after it gets the lock it'll check bar=2 and set baz=1 by rewriting the row.

I just can't see this as being more than trivia, or an extremely esoteric use-case.

I would keep it simple if you go the NOWAIT method becuase I don't think it matters anyway.

BEGIN;
  SELECT *
  FROM foo
  WHERE bar=2
  FOR UPDATE NOWAIT;

  UPDATE foo SET baz=1 WHERE bar=2;
END;

That whole transaction will rollback if it can't get the lock right away.


The forensic information may still be available in the default trace if the table was created recently. The default trace writes to rollover files with a maximum of 5 files of 20MB each (100MB total) so the event may have rolled off, depending on system activity.

Below is an example T-SQL query. This is the same source used by the SSMS Schema Change History report (right-click on the server in SSMS Object Explorer and select Reports-->Standard Reports-->Schema Change History).

SELECT
     trace.TextData
    ,trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.LoginName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
    --,*
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
JOIN sys.trace_events AS te ON
    trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EveNtClass
    AND tsv.subclass_value = trace.EventSubClass
WHERE
    te.name = 'Object:Created'
    AND trace.DatabaseName = N'YourDatabase'
    AND trace.ObjectName = N'YourTable'
    AND tsv.subclass_name = 'Commit'
ORDER BY trace.StartTime;


Error when installing SQL Management Studio 2014 from product installer

Finally worked it out.

My issue was actually two issues. Neither one was giving good error feedback. I had to stumble on fixing them both to get the installer to work as intended.

Firstly, I reduced the list of features being installed.

/ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SSMS /UpdateEnabled=False

Secondly, I changed the length of the path leading to the SSMS installer at the time it was executed.

The previous execution path was:

C:\Program Files\Application Directory\Components\MS SQL Server\SQL Server 2014\SP1\Express\x86\SQLManagementStudio_x86_ENU.exe

The new execution path is:

C:\Program Files\Application Directory\Components\MSSQL2014SP1EXPx86\SQLManagementStudio_x86_ENU.exe

With both changes in place, the automated installation works as intended.


SQL is designed to work with rows and columns, it does not do CSVs very well, especially ones that use multiple delimiter symbols with different meaning.

You should figure out how to present your CSV in the form:

ID,GroupID

where IDs that need to be grouped have the same GroupID. Once that CSV is imported, it will be very easy to group your rows using a join to the imported data:

SELECT
  d.GroupID,
  AGG(...),
  ...
FROM
  YourTable AS t
  INNER JOIN ImportedData AS d ON t.ID = d.ID
GROUP BY
  d.GroupID
;


Specific issues with the query

PostgreSQL defaults to READ COMMITTED isolation, and it looks like you're not using anything different. In READ COMMITTED each statement gets its own snapshot. It cannot see uncommitted changes from other transactions; it's as if they simply haven't happened.

Now, imagine you run this simultaneously in three sessions, on a setup with three entries in jobs, zero in jobs_running and zero in jobs_completed:

insert into piper.jobs_running
select x.fid from (
  SELECT fid FROM piper.jobs
  except
  select fid from piper.jobs_running
  except
  select fid from piper.jobs_completed
 ) as x limit 1
returning(fid)

Each run will select the three jobs in jobs. Because their snapshots were all taken before any of them committed a change, or even created the uncommitted row, *all of them will find zero rows in jobs_running and jobs_completed.

So they all claim a job. Probably the same job, because even though there's no ORDER BY, the scan order will be the same.

Locking

Row locking crosses transnational boundaries, and lets you communicate between transactions to enforce ordering. So you might think this would solve your problem, but it won't.

If you take a FOR UPDATE lock on the row entry, so the row is locked exclusively, the lock is held until the transaction commits or rolls back. So you'd think that then the next transaction would get a different row, or if it tried to get the same row it'd wait for the lock to release, see that there was now an entry in jobs_running, and skip the row. You'd be wrong.

What'll happen is that you'll lock all rows. One transaction will successfully get the locks on all rows. The other transactions, which will do the same index or sequential scan, will generally try to lock the rows in the same order, get stuck on the first lock attempt, and wait for the first transaction to roll back or commit. If you're unlucky, they might start locking different sets of rows and deadlock against each other instead, causing a deadlock abort, but usually you're just getting no useful concurrency.

Worse though, the first transaction picks one of the rows it locked, inserts a row into jobs_running and commits, releasing the locks. Another transaction is then able to continue, and locks all the rows.... but it doesn't get a new snapshot of the database state (the snapshot gets taken at statement start), so *it can't see that you inserted a row into jobs_running. Thus, it grabs the same job, inserts a row for that job into jobs_running, and commits.

Condition re-checks

PostgreSQL has a quirky feature not included in most databases where, if a transaction blocks on a lock, it re-checks that the selected row still matches the lock condition when it gets the lock after the first transaction commits.

That's why the example in https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres works - it relies on qualifier re-checks in WHERE clauses after reclaiming a lock.

The use of locking forces everything to run serially, so in practice you might as well have a single connection doing the work.

Isolation, ACID, and reality

Transaction isolation in PostgreSQL is not the perfect ideal where transactions run concurrently, but their effects are the same as if they were executed serially.

The only real world database that would deliver perfect isolation would be one that exclusively locks every table when a write transaction first accesses it, so in practice transactions can only be concurrent access if it's to different parts of the DB. Nobody wants to use such a database in situations where concurrency is desirable or useful.

All real world implementations are compromises.

READ COMMITTED default

PostgreSQL's default is READ COMMITTED isolation, a well-defined isolation level that permits non-repeatable and phantom reads, as discussed in the PostgreSQL manual on transaction isolation.

SERIALIZABLE isolation

You can request stricter SERIALIZABLE isolation on a per-transaction basis or as a per-user, per-database or (not recommended) global default. This provides much stronger guarantees, though still not perfect, at the cost of forcibly rolling back transactions if they interact in ways that couldn't happen if they had run serially.

Because your concurrent queries will always try to grab the first job, no matter how many jobs there are, all but one will abort with serialization failures. So in practice you don't get any useful concurrency and might as well have a single connection handing out jobs to workers.

(Note that prior to PostgreSQL 9.1 SERIALIZABLE offered much weaker guarantees and would not detect quite a lot of cases where transactions were interdependent.)

Auto-re-run SERIALIZABLE

PostgreSQL does not auto-re-run SERIALIZABLE transactions that abort due to serialization failures. There are cases where this would be quite useful, but other cases where doing so would be completely wrong and dangerous - especially where read/modify/write cycles via the application are involved. At this time there is no support for automatically rerunning transactions on serialization failures. The application is expected to retry.

Don't write a DIY queuing system

It looks like what you're trying to do is write a queuing system. Consider not doing that. Writing a robust, reliable and correct queueing system is hard, and there are a few good ones already available that you could adopt. You have to handle things like crash-safety, what happens when someone takes a task then fails to complete it, race conditions around completion just as you give up on the task handler completing it, etc. There are lots of subtle concurrency problems. Don't try to DIY.

9.5 and SKIP LOCKED

PostgreSQL 9.5, still in development, adds a feature that makes queuing quite a bit easier.

It lets you say that if when you SELECT ... FOR UPDATE, if a row is locked, you should ignore it and carry on to find the next not-locked row. This is very useful when combined with a LIMIT as it makes it possible to say "find me the first row someone else isn't already trying to claim". So it becomes very simple to write queuing that grabs jobs safely and concurrently.

Until that feature is available I strongly suggest that you stick to single connection queue management, or use a well-tested task queue system.


Can we use save transaction command only in nested transactions?

I am a newbie to SQL and I came across a situation where I have to use save transaction

Savepoints are rarely used (or useful) and it's unusual that "a newbie" would "have to use save transaction".

  what is the difference between having a save point and not having a save point transaction in a Stored procedure where we don't use nested transactions

Save Points allow partial rollback.


Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

The non-clustered indexes on a heap will use the fill factor, but a heap is not an index (although it does appear in sys.indexes).

Since there is no way to reorganize a heap it can't have a fill factor. Fill factor only comes into the picture when rebuilding or creating an index; it only applies to indexes.

I believe How Forwarded Records are Read and Processed in a SQL Server Heap by Uwe Ricken may help you out. Specifically regarding how SQL Server uses PFS to determine inserts for records into the heap, and also how a non-clustered index uses the RID in relation to the heap.


No matter how many processors you throw at it, SQL Server will never "parallelize" across multiple statements in the same batch. It will completely finish update_query_1 before it starts insert_query_2, and it will completely finish insert_query_2 before it starts update_query_3.

Nothing about any of the statements in your batch is asynchronous unless you are talking about starting jobs, invoking Service Broker, or triggering some side effect. But none of these things would technically be part of this batch.


Postgres Database too large to start service

Try start with --single-mode to truncate (PG document)

Usage

postgres --single -D /usr/local/pgsql/data other-options my_database

Note

  The postgres command can also be called in single-user mode. The
  primary use for this mode is during bootstrapping by initdb. Sometimes
  it is used for debugging or disaster recovery; note that running a
  single-user server is not truly suitable for debugging the server,
  since no realistic interprocess communication and locking will happen.
  When invoked in single-user mode from the shell, the user can enter
  queries and the results will be printed to the screen, but in a form
  that is more useful for developers than end users. In the single-user
  mode, the session user will be set to the user with ID 1, and implicit
  superuser powers are granted to this user. This user does not actually
  have to exist, so the single-user mode can be used to manually recover
  from certain kinds of accidental damage to the system catalogs.

  Note that the single-user mode server does not provide sophisticated
  line-editing features (no command history, for example). Single-User
  mode also does not do any background processing, like automatic
  checkpoints.

Example

postgres@xxx-yyy$ ./bin/postgres --single  -D data/ postgres

PostgreSQL stand-alone backend 9.6.0
backend> truncate t1 ;
2016-11-23 07:53:17.340 UTC: proc=18481,user=,db=,host=LOG:  statement: truncate t1 ;
backend> vacuum full t1 ;
2016-11-23 07:56:44.541 UTC: proc=19135,user=,db=,host=LOG:  statement: vacuum full t1 ;

In terms of postgres process related to this mode

ps -ef | grep postgres
postgres 19135  4510  0 07:56 pts/1    00:00:00 /opt/PostgreSQL/9.6/bin/postgres --single -D data/ postgres

If --single-mode does not help you, please add more space (as @ypercube mentioned), then start postgres server and truncate data.


merge 3 mysql tables into one with no duplicates

SELECT tt0.number
     , tt1.`stuff 1`
     , tt1.`stuff 2`
     , tt2.`data 2`
     , tt2.`data 5`
     , tt3.`info 1`
     , tt3.`info 4`
FROM (   SELECT t1.number FROM t1
       UNION DISTINCT
         SELECT t2.number FROM t2
       UNION DISTINCT
         SELECT t3.number FROM t3
     ) tt0
LEFT JOIN t1 tt1 ON tt0.number = tt1.number
LEFT JOIN t2 tt2 ON tt0.number = tt2.number
LEFT JOIN t3 tt3 ON tt0.number = tt3.number

This query assumes the number field is primary or at least unique key in each table.

UPDATE:

  Unfortunately this gave me errors saying that there were duplicate entries for number

If You got error You describe that is NOT MySQL error.

http://www.sqlfiddle.com/#!9/22d82dc/1


Move Oracle Database to New Server with Dataguard in place

Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe
  different than New Primary database due to old Primary database is
  still running. If this happen, can i apply log from old Primary to New
  Primary? I'm not sure..

You can have multiple standby databases for a primary database. Create a full backup and restore it as a standby on the new machine, update the Data Guard configuration and keep that standby too in sync with archivelogs. When the time comes, simply perform a switchover to this new standby database.

  Switch the existing Standby database to be Primary database and create a new standby database in new server. Then switch back Primary
  role to new database in new server.

No, this is just unnecessary risk and extra work.


How can I reduce the memory usage of a tiny MEMORY table in MySQL?

Although data_length and index_length aren't exact multiples of read_buffer_size, they are based on it.  So adjusting read_buffer_size in my.cnf is the solution (thanks @Wilson Hauck), as Shane Bester pointed out on the MySQL forums:

  memory tables grow by 'read_buffer_size' bytes when they need extending (https://forums.mysql.com/read.php?92,405702,417606#msg-417606)

Based on performance tests by Peter Zaitsev in 2007 (https://www.percona.com/blog/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/), the default 128K setting is generally best, and using smaller values brings very little penalty.


There is no command to execute for this. What you need are the following:

Oracle MySQL Executive Summary (under Supported Versions Affected)
MySQL 5.1 Release Notes

I would be concerned because MySQL 5.1 was last updated Dec 03, 2013 and the Last Critical Patch to MySQL 5.1 was Jan 2014 (See Patch Availability Table under column Patch Availability and Installation Information)

On Oct 17, 2014, I posted the answer to Any known issues upgrading from MySQL 5.1.73 to 5.6.21? following the security patch announcement. MySQL 5.1.73 has had no security patch applied. Only MySQL 5.6 (5.6.21) and 5.5 (5.5.40) have new patches.

What's crazy is that MySQL 5.6.20 was released two months prior to MySQL 5.6.21. Again, no mention of MySQL 5.1.73.

I highly recommend you upgrade to MySQL 5.6.21.


That specific character is from the character set Chinese_Hong_Kong_Stroke_90

There doesn't seem to be an easy way to "figure it out" from the output.  The most reliable way is to query the collation off the database.

select collation_name from sys.databases;


Howto reliably ship WAL log files between master and slave

I'd use streaming replica "out of the box".  It will restart itself after a network hiccup just fine.  If you are afraid the WAL will get recycled before it is streamed, use a replication slot to prevent that from happening.


Setting MySQLDump to keep running in the background after session times out

I think I found the problem. Apparently the nohup command ended at the | symbol, so when the session expired the second part of the command stopped with the session.

To get round this I ran:

nohup sh -c 'mysqldump -hxxx -P3306 -uxxx -pxxx --dump-slave --databases a b c d --ignore-table=a.xxx -F | mysql -h127.0.0.1 -P3306 -uxxx -pxxx' &

I checked it was running, and then EXITed the shell, and so far, it is still running.


How to design database for market place website

My approach would be to think in terms of objects. Like John Eisbrener mentioned, think of the types of products that would share common attributes, like model number, weight, ISBN, and so on. But also think of the people that would be involved. A vendor table can list the people selling, and a customers table would list the people buying.

As far as a best practice that I try to live by is don't repeat your data. A table recording a sale should not include a customer's delivery address, that should be referenced through the customers table by using a foreign key constraint. But most of all open up a program like Visio and start with the basic information tables you pretty much know you'll need, and build it out. It's much easier to make changes there than altering tables later


Ensure you have enough space in / and data dir.

Kill all processes for mysql in ps -ef | grep -i mysql
If you see any any ibdata file generated under your default data directory you may remove them also iblogfile1,2 since its a fresh installation no need to safe backup them.
chown -R mysql:root /var/lib/mysql
chmod -R 775 /var/lib/mysql also the same permissions to /etc/my.cnf.
Now try to start the instance by issuing /etc/init.d/mysql start and tail -f /var/log/mysqld.log. It should start without any issues if you get any error message .


MySQL stalls and strange threads/connections behavior is observed

Thundering herd.  Or, so many people in a grocery store that no one can move their cart.

When Threads_running is 349, each of them is waiting and waiting and waiting for a tiny slice of CPU and a tiny amount of I/O and ...  The effect is that they all take 'forever' to finish.  Meanwhile, others trying to get in are let in because you have such a high max_connections, etc.

But, that does not explain what pushed the herd over the cliff.  It could be something as trivial as a big SELECT that happens to block what the herd normally does.

Do you have the slowlog turned on?  With a low value of long_query_time?  If so, you may already have the answer in the slowlog.  Since you did not panic and reboot, that slow Select (if it really exists) will be in the slowlog.  Based on the graphs, I would guess it might have a time of 80-110 seconds.

As for the dip in Threads_cached, that gets a bit complex.  First, let me explain the non-dip parts.  Before the 'event', about 200 connections were made; each lasted long enough for there to be a 'steady' 200 connected.

After the event, there were so many waiting to get in that it pushed the number up to about 350.

Having thread_cache_size = 500 wastes memory; suggest you lower it to 250.

As for the dip, I will do some guessing.  So many connections were trying so hard to get things done that new connections were denied access.  (This may be deliberate on the part of MySQL, or it may be a side effect of everyone clambering for various mutexes.)

It is also unwise to have max_connections = 1000; this just invites scenarios like this.  Instead, have the clients 'take the heat' when something hiccups -- by being denied access to MySQL.  Furthermore, this makes it easier to say "Oops, were are having a problem please wait, and please do not press Send again, nor Refresh!"  Or simply give a "500".

Bottom line:  I think the slowlog can point to the cause of the event.


Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?

It has grown smarter in v10.

I think the reason it wasn't smart enough before is described here:

https://www.postgresql.org/message-id/1788.1481605684@sss.pgh.pa.us

I don't know how to summarize that, so I'll just say "booleans in indexes are confusing"


Proper way to reset/cleanup a connection

Assuming libpq, you can call PQtransactionStatus to find out if there
's a transaction open.

If you only have SQL then you can call

select xact_start <> query_start from pg_catalog.pg_stat_activity where pid=pg_backend_pid();

That round-trip will add latency to the cleanup process, but this will not be a problem so long as there's no process blocked waiting for the freed connection.


Dump first N documents of a collection with mongodump

There is no direct way to limit number of records to export via mongodump; however it is possible indirectly, using the query parameter.

First you need to find last(n) documents you need to export.

Let's say you want to export top 50 documents. First find 50th document _id – if you are finding first(n), then sort ascending order and use $lte operator down in query.

db.col.find({},{_id:1}).sort({_id:-1}).skip(49).limit(1)

Note down the _id value.

Now pass your _id to mongodump and run it from console. (not from mongo shell)

mongodump \
--db <some_db> \
--collection <some_col> \
--query '{_id:{$gte:ObjectId("previously noted _id")}}'

Note: this solution exports the last(n) documents instead of first(n), but it is simple to adapt it.


Separate archive tables or soft delete for inventory database

I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier.  If the users don't need the data any longer, then I would go with the archive tables.

Based on your description above, I would suggest going with the Soft Delete version.  I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.

As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.

We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.

A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.


I'm getting a PL/SQL: unhandled user-defined exception error but there's no error

This turned out to be a false lead -- there was not an error at all, but rather it had been killed by the developer after it hung. I wonder why it indicates "Line 13" in every case where the error occurs.


Access 2010 ODBC Connection to SQL Server 2005

The INSERT command comes in two flavors (this is for SQL Server but there's a good chance it'll be the same for Access, too):

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

For more details and further in-depth coverage, see the official MSDN SQL Server Books Online documentation on INSERT - a great resource for all questions related to SQL Server!


I have a query on two big tables that is too slow!

Probably problem is here:

(t2."TimeS"-t1."TimeS")>='0'

Postgresql does not use index for this. Can you try just:

t2."TimeS" >= t1."TimeS"


Can we back up mysql databases by copying files?

If you are able to shut your database server down (and my understanding is that you are indeed able to) - thereby disabling your application - then that's the easiest indeed.

You can, after shutdown, just copy+paste your data directory. Where is your data in the first place? Just issue:

SELECT @@datadir;

From within your MySQL server.

By the way, while you must backup your data someplace safe, I do not expect any of the apt-get or aptitude commands to actually touch your data.

NOTE that the page you've linked to has some pretty crazy ideas, like:

[!!DO NOT DO THIS!!] find / -iname ‘mysql*’ -exec rm -rf {} \;

I encourage you to look someplace else, given the above query. It will remove any and all mysql* file from your root filesystem. Including your own application's mysql-config.ini files (if any, of course), including libdbd-perl-mysql -- without clean uninstall... I mean, this is really BAD advice.

There's no point in backing up your my.cnf file, delete the file then put it back again. The same holds for your datadir.

Just uninstall MySQL and re-install is enough -- but that, too, is kinda funny, since both are done via apt-get -- so you basically reinstall the same version... At least get yourself newer version of MySQL.


Why does MyISAM support FULLTEXT Searching and InnoDB does not?

Back in the day, people have been clamoring for FTS for InnoDB and feverishly looking for workarounds. Sometime around January 2005, InnoDB found a sponsor to look for a developer.

I am sure one of the biggest gotchas that developers worried about was making FULLTEXT Searching (FTS) work in harmony with Transactions. This is especially needed since the tokenization for inserted strings is performed only at commit time, so a full-text search does not see the uncommitted data.

Please keep in mind that although there are tutorials on FTS with InnoDB, MySQL 5.6 still has a bug or two out there for InnoDB/FTS.


Sample YAML Configuration Files for MongoDB?

Here are several examples of YAML configs for Linux (Windows paths and options are a little different), essentially explicitly setting some defaults and commonly used settings.

First, a standalone mongod with the default port, path, journal settings - this would be the type of configuration used for local testing, with a few extras so show the general style:

storage:
    dbPath: "/data/db"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/data/db/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 127.0.0.1
    port: 27017
    wireObjectCheck : false
    unixDomainSocket:
        enabled : true

Some notes on this config:

You generally don't want to have the object check off (wireObjectCheck: false) in production, but for a bulk load of data for testing purposes, it will speed things up a little and is a minimal risk in such an environment
This would not work for replication unless all member of the replica set were on the loopback IP address (as this is the only binding specified), so beware

Now, let's look at a sample config file for a typical production replica set member with authentication enabled and running as part of a sharded cluster:

storage:
    dbPath: "/data/db"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
replication:
    oplogSizeMB: 10240
    replSetName: "rs1"
processManagement:
    fork: true
net:
    bindIp: 192.0.2.1
    port: 27018
security:
    keyFile: "/data/key/rs1.key"
    authorization: "enabled"
sharding:
    clusterRole: "shardsvr"

Some notes on this configuration:

Again there are explicit declarations of defaults and implied settings (port is implied by clusterRole for example), generally this is recommended to avoid confusion
The IP binding is now the external IP address only, so communication on the loopback IP will now fail, but replication can work to remote hosts
The oplog defaults to 5% of free space, so it is common on large volumes to be more conservative and explicitly set the size allocated

Next up, a sample mongos config:

sharding:
    configDB: "config1.example.net:27019,config2.example.net:27019,config3.example.net:27019"
    autoSplit: true
systemLog:
    destination: file
    path: "/var/log/mongos.log"
processManagement:
    fork: true
net:
    port: 27017
    bindIp: 192.0.2.2
    maxIncomingConnections: 5000
security:
    keyFile: "/data/key/mongos.key"
    authorization: "enabled"

The only required changes here are removals that don't apply to the mongos (since it does not store data) and the addition of the configDB string, which must be identical on all mongos processes.  I added the maximum connections setting as an example, it's not required but can often be a good idea for larger clusters.

Rounding out the sharded cluster we have a sample config server, which is really a subset of the replica set member with some minor changes:

storage:
    dbPath: "/data/db"
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 192.0.2.3
    port: 27019
security:
    keyFile: "/data/key/config.key"
    authorization: "enabled"
sharding:
    clusterRole: "configsvr"

Finally, MongoDB 3.0 (not yet released at the time of writing this) will introduce several new options, especially with the introduction of the new storage engines.  Therefore, here is an example of how to configure the same replica set member, but this time with the WiredTiger storage engine and (the default) snappy compression method (note: altered from original because of SERVER-16266, and added sample engineConfig):

storage:
    dbPath: "/data/db"
    engine: "wiredTiger"
    wiredTiger:
        engineConfig:
            cacheSizeGB: 8
        collectionConfig:
            blockCompressor: snappy
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
replication:
    oplogSizeMB: 10240
    replSetName: "rs1"
processManagement:
    fork: true
net:
    bindIp: "192.0.2.1,127.0.0.1"
    port: 27018
security:
    keyFile: "/data/key/rs1.key"
    authorization: "enabled"
sharding:
    clusterRole: "shardsvr"

As a final bonus addition, I showed how to bind multiple IP addresses using a list, in this case an external IP and the loopback IP.


Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

Because is a DELETE. RECEIVe is just syntactic sugar for something like

DELETE from queue
OUTPUT DELETED.*
WHERE status = <receivable>;

If you enable retention then is an UPDATE, and will show as an UPDATE. There are some details, like how conversation group locking is done, how the proper message ExactlyOnceInOrder semantics are ensured and the syntax tree of RECEIVE is not truly possible to be built in T-SQL. As I said, details...

Is no coincidence I recommend in Using Tables as Queues to use the OUTPUT clause of DELETE to 'dequeue' from a table...


why should I join 2 tables with 1 table apart

It's a bit of a guess because you haven't provided the link to the challenge (in markdown you have to make links like [this problem](http://whatever.com) but clicking your link just takes to http://this%20problem, so I guess the markdown is broken
You've said you have tables:
Hacker - I guess this is like a user table
Challenge - I guess this is a problem to solve
Submission - I guess it's an answer a hacker has made to a problem

So we can clearly have "one hacker may answer many challenges" and "one challenge has many hackers answering it" ie a many many relationship so we need a table in the middle to split it down and track which hacker answered which challenge, and this is the Submissions table doing this breakdown
This interesting part here, and possibly put in deliberately to trip people up, is that Challenge also has a hacker Id column. For some reason hacker rank's tables track a hacker-challenge relationship that is nothing to do with a submission ie the hacker ID in the challenge table is something where there can be only one hacker for that challenge, such as the hacker that created it, or the hacker that first got perfect score on it, or the hacker who is responsible for marking the answers on it, or the hacker that most recently took it..
Whatever the reason, you said you joined on that hacker ID which in my mind is the wrong thing to do because it bears no relation to the question of multiple hackers who have submitted: it could be that the hacker in the challenge.hacker_id has never even made a submission on the challenge, they simply wrote it and have never answered it. It therefore cannot give you the answers the challenge seeks. It's probably meant to catch you out and it's one reason why when I'm designing a database schema I aim to put the reason for a column into its name in cases like this; ie in the Challenge table I might have called it author_hackerid or moderator_hackerid to describe not only where it links to (hacker table on hacker ID) but why it links there (author, moderator, firstwinner, recenttaker) especially if there are multiple people.. (though in such a case it may make more sense to have another table, like submission) that splits hacker and challenge, and gives a reason for why that hacker is related to that challenge)
Remember, that just because two columns are named the same it doesn't mean they are alternative join options. Even columns that have participated in a join before are not alternative, if it's a left join. If it's an inner join, fair game:
a INNER JOIN b ON a.id = b.id INNER JOIN c ON b.id = c.id
a INNER JOIN b ON a.id = b.id INNER JOIN c ON a.id = c.id
                                              ^

but these can give different results:
a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id
a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON a.id = c.id

That small but critical difference in the second query of both tables left joining to a might be huge


In what cases are BLOB and TEXT stored in-line on InnoDB?

Keep in mind that there are 4 "row formats".  A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few months before DYNAMIC and COMPRESSED were introduced in the "InnoDB plugin".
So, I claim that that other Q&A is out of date!  That is the 754 Upvotes are no longer valid.
Here are some 'current' manual references:
(emphasis mine)
From https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html :

Columns such as BLOB and VARCHAR that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages.

From https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html :

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.
[DYNAMIC and COMPRESSED] When the row is too long, InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.
The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.
The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format

From https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html (even a big CHAR may be treated like TEXT):

InnoDB [all formats?] encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.
ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED handle CHAR storage in the same way as ROW_FORMAT=COMPACT.

(The links are for 5.6, but I think the text applies at least to newer versions.)
I see nothing specific that says that VARCHAR(...) and *TEXT are handled differently.


Try this query, I've reformatted it to make it easier to read.  You really should specify the type of join (INNER, LEFT OUTER, etc.) and specify how they are related.

SELECT a.title
    ,a.discription
    ,a.supportername
    ,a.reportername
    ,a.member_id
FROM ticketdata a
    INNER JOIN marked b ON b.ticketid = a.ticketid
    INNER JOIN users u on u.member_id = a.member_id
WHERE u.id = ".mysql_real_escape_string($_SESSION['id'])."
    AND b.active = '1'


How to fix "TNS:listener does not currently know of SID given in connect descriptor" with DBVisualizer on Linux?

TNS Ping does not care about service or SID, all it checks is the availability of the listener, so this does not mean your connection string is correct. Connecting through SQL*Plus successfully however confirms it.

Your tnsnames.ora uses SERVICE_NAME in CONNECT_DATA, but your JDBC URL uses the SID syntax. SID and service name are different concepts, and their value can be different. In your case, you can connect with CMSQA provided as service name, but not as SID.

In this syntax, CMSQA means SID:

jdbc:oracle:thin:@10.160.189.78:1521:CMSQA

To use it as a service name, your connection string should be:

jdbc:oracle:thin:@//10.160.189.78:1521/CMSQA


usage percentage of Mongodb internal cache

What could have caused the difference in the cache percentage usage and throughput?
MongoDB uses both file system as well as WiredTiger's cache when required
Is it safe to change explicitly this settings so Mongodb uses more of its cache?
Yes. It is totally safe to change the cache settings
Can I do this setting change from the command line ?
The setting has be done in your mongod config file under storage option

Refer the below link on how you should decide on the cache size
https://docs.mongodb.com/manual/faq/storage/

Similar to the below:

storage:
  engine: wiredTiger
  wiredTiger:
    engineConfig:
      cacheSizeGB: 7

Thank you!

-Anban


mysqld startup/initialize permission demied - can't connect

It looks like permissions for your actual data/* is correct: mysql:mysql, but if you're having it access that through a symlink in your own user's $HOME, then the permissions leading up to the symlink are probably what is blocking mysql from accessing the symlink path.

What about giving mysql its own $HOME, like /home/mysql/{etc,data} (chown mysql:mysql) and place its configuration and data files within that. You could always add your user to the mysql group if you need to access mysql's config or data as your own user.


Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

Prevalent?  No.  It's not uncommon, however, to write a procedure like so:

create or replace procedure write_to_log
  (p_entry_point in varchar,
   p_log_type    in varchar,
   p_message     in varchar)
as
pragma autonomous_transaction
begin
  insert into my_log_file
    (entry_point, log_type, message, log_timestamp)
  values
    (p_entry_point, p_log_type, p_message, systimestamp);
  commit;
end;

Then you can add calls to write_to_log where needed throughout the code.


How can primary keys in a Netezza table be indexed?

You can't have more than one primary key on a table - I assume you mean foreign keys.  There is no best way to do this, because it depends upon your workload.  In some indexes one column may come first, while another may have a completely different selection of columns, let alone order.

In the end it doesn't really matter, because Netezza doesn't have indexes in the first place, so I'm not sure what you're getting at.

That is a composite primary key made up of all those columns.  Together they are "unique" (Netezza doesn't enforce this) and uniquely identify the row.  This is typical in a dimensional design.  The FKs of a large number of dimensions uniquely identify a fact - usually a date/time for the snapshot, and something like a stock ticker or customer or whatever.


Which ports need to be opened in order to use Named Pipes

We had the same issue with legacy applications built around 1998-2007 when we ported SQL Server to a VLAN--for security purposes. Depending on which server we connected to we'd get spurious results--one would work and the other would not...because of a tighter VLAN configuration.

Our solution was to "bag" named pipes and to force TCP--as shown below. The SQL server port is denoted by XXXXX. This forced our legacy apps to use TCP no matter what. So far, it's fixed all our issues.

tcp:servername\instance,XXXXX


Ms-access defining additional Index Fields

Your question is really: What are the benefits of surrogate keys?

A surrogate key is a (generally) integer identifier for a record in a database which is used as the primary key (generally) and used as a link in foreign key relationships.

The answers are found all over the place, Wikipedia has a great summary including the advantages and disadvantages, some of which you allude to: https://en.wikipedia.org/wiki/Surrogate_key

Addressing each of your points:

"Will it help?"
Yes. From a performance perspective it will make a huge difference if your database grows to a large size because computers naturally use integers and comparing them is easy for them to do. As soon as you have to compare natural/business keys, computer processing time ramps up considerably. But this is only one advantage, immutability being probably the biggest. Read the above Wikipedia article to understand this.

"How should I handle the conversion of empl_name="Carlos" to emplID=1?"
You shouldn't. You have a couple of options:

Hide the key field from the user, they don't need to know about it.
Let the user see the field, just tell them don't touch it (preferably lock it).

Users don't need to know there is stuff going on behind the scenes. They usually don't care either. There is no need to do the conversion if you hide the column or show it. Secondly the user should not be updating details on excel sheets offline unless you have a process for automatically reintegrating these into your application. Instead, build a data entry form for them to use with a search function and tell them to make updates to the database directly.

"SELECT statements look ugly"
This is because MS Access makes SELECT statements look ugly NOT because they are ugly. A properly formatted SELECT statement looks fine:

SELECT
    tblC.ID
    ,tblA.Name
    ,tblB.Client
FROM tblC
INNER JOIN tblA ON tblA.ID = tblC.FK_A
INNER JOIN tblB ON tblB.ID = tblC.FK_B
WHERE tblA.Name="MyName"
AND tblB.Client="MyClient";

Unfortunately if you are stuck with Access, you are stuck with ugly looking SELECTs. Having worked with MS Access a lot, you are better off just using the query designer in Access and forgetting about SQL Statements unless you are doing them in VBA code. In VBA code you are in control of how it looks so can design it as you wish.

Your book appears to include a chapter about database theory. This is important and you should re-read the parts about data theory (Chapter 4) as these should have answered your questions. If not, look for another book that hopefully will.


MongoDb: acquireWaitCount and timeAcquiringMicros is missing from system.profile output

When those fields are not present in the profiling output, it means their value was 0.
That is, if the query never had to wait to acquire a lock, the acquireWaitCount field will not be present in the profiling data.
The profiling data out contains "storage": {} which could mean 2 things:

all necessary documents and indexes were already in cache so the query didn't need to request anything from storage
there was a timeout attempting to retrieve the storage data

In the second case, there should be a log entry to that effect in the mongod log.
Another possibility is too many simultaneous read operations.  The storage engine has an upper limit on the number of concurrent reads it can handle, when that limit is exceeded, operations queue waiting for a turn in the storage engine.  This is not the same as locking, and is not reported in the profiling data.  You can see this occurring in the output of the serverStatus command


How to get list of users on all instance's databases? excluding the mirrors!

sp_MSforeachdb is undocumented, unsupported, and has some serious shortcomings.  You'll need to use something else.  Either writing your own cursor over sys.databases, something like this:

declare c cursor local for
    select name
    from sys.databases d
    where name not in ('master','model','tempdb')
    and d.state=0
declare @db sysname

open c
fetch next from c into @db
while @@fetch_status = 0
begin
  begin try
     declare @sql nvarchar(max) = concat(  N'use ', quotename(@db), N';
     select db_name() db, *
     from sys.database_principals u
     where type = ''U'''
     )

     --print @sql
     exec ( @sql )

  end try
  begin catch
    print error_message()
  end catch

  fetch next from c into @db
end

close c
deallocate c

or copying someone else's, eg here.


Using wildcards in NEAR in a Full Text Search

Have you tried CONTAINSTABLE?  This includes a Rank column so will give you more visibility of the ranking process.  This worked for me:

SELECT *
FROM dbo.yourTable t
    INNER JOIN CONTAINSTABLE(dbo.yourTable, FileContent, '"minis*" NEAR "educ*"') ct ON t.yourKey = ct.[Key]
ORDER BY RANK DESC

Update: found the following paragraph in the book Pro Full-Text Search in SQL Server 2008 by Michael Coles with Hilary Cotter

  "...the NEAR operator is basically useless with the iFTS CONTAINS and
  FREETEXT predicates (it’s no different from using the AND operator).
  To get a true proximity search, you need to use CONTAINSTABLE or
  FREETEXTTABLE and restrict the results with a WHERE clause."

Ch3, p67


The problem was, as proposed by some in comments, that Database Instant File Initialization was not enabled on the instance. I enabled this by giving the user under which the SQL Server service was running the needed permissions, and then restarted the SQL Server Service. The steps to take can be found in the following link: Database Instant File Initialization

Thanks for all the suggestions, some of them pointed me to the solution.


Elasticsearch: Versioning a document on revisions

Elasticsearch stores data in on a document basis. Even if you are to put a slightly different data, each of the resulting document must be stored as a different document.

Note that the versioning in Elasticsearch is used for consistency, not for data tracking across different versions. That is, in short, only the latest version of a document is saved.

I think what you can do is to prepare a document for each separate version, and that's only thing you can do as long as you are using Elasticsearch.

{
    "Title": "A",
    "Summary": "CONTENT",
    "updated_date": "The date"
 }


Load csv file to mysql using load data local infile

Your problem is on using the variable $name on your LOAD DATA LOCAL INFILE statement, you should use the variable $file in your statement..

Your statement should change to;

$import = "LOAD DATA LOCAL INFILE '" . $file. "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());


How to grant database creation privilege to a user in MySQL

ALL privileges includes "ALL" of them except "wITH GRANT OPTION"... Thus above command will let admin user create the database too.

You might want to show the error that you're getting. Note that in above GRANT you have not specified the password for admin user too.

Update:

You should be able to do following:

login as root and create user:

$] mysql -uroot -p
mysql> grant all privileges on *.* to 'admin'@'localhost';

login as admin (without password):

$] mysql -uadmin -p
mysql> create database admin;

When you login, you can verify your privileges as follows:

mysql> show grants;

or

mysql> show grants for current_user();

mysql> select current_user() will show you, user you logged in as.


NULL is different from empty string '' and 0. Empty string as well as 0, are existing values that can be used in equations and other operations.

'' = '' is true

0 = 0 is true

NULL = NULL is not true

Quoted from Wikipedia:

  Null is a special marker used in Structured Query Language (SQL) to
  indicate that a data value does not exist in the database.

I'm not sure what kind of impact on performance would be expected with NULLs instead of ''s or 0s, but AFAIK no, there is no big difference in performance between the two. Somebody may correct me if I'm wrong. An exception to this rule is noted below.
First of all, with NULL you can have a UNIQUE INDEX on a column, and still have many NULL values. This is by definition of the SQL NULL. Read more at mysql reference

Also, if you use InnoDB (I'm only saying this because your question is tagged with mysql-5.1), then no value is stored at all, which may result in big reduction in storage and possibly faster searching (since more rows fit into one page)

On the contrary, with MyISAM, nullable column indexes may have increased size causing slightly increased storage space required.

In the end, it is neither bad, nor good practice to use NULL columns. Combined with bad design they can be a headache. But with good design they are useful and helpful. NULL columns deserve to have their position in well designed databases, they're just not appropriate for every column.


Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?

It's because the order of operations that the clauses of your query are executed in. The WHERE clause occurs before your OFFSET and FETCH clauses. That's why not only are there about 10,000 (9,000 in your first example) key lookups, but there's just as many index seeks. This is the filtering that occurs as a result of your predicate on target_user_id in your WHERE clause. Execution Plans read from right to left for order of events, so if you follow the plan starting from the index seeks and work your way left you'll see the Top operator which is what represents your OFFSET / FETCH clauses, and thus comes after your WHERE clause.
So simply put, SQL Server first needs to locate the rows based on the filters you apply via your predicates (in the WHERE, HAVING, and JOIN clauses). Then once it finds the correct rows it can apply your OFFSET / FETCH clauses. If it didn't get all the rows based on your filters first, it wouldn't know what rows it'll need to OFFSET / FETCH.
Could have the SQL Server Engine been programmed a little bit logically better in such a case? Possibly. They could've designed the Engine in such a way that realizes a Top operation is going to eventually occur on that index seeked data and apply it before the key lookups occur to at least minimize the amount of key lookups but I would imagine it would only help performance in certain cases, and make things programmatically a lot more complicated that it may not be worth it. For example, the key lookups are happening in parallel to the index seek operation (hence them being equally to the right in the plan steps). If the Top operation were to occur before the key lookups to reduce the data, then the remaining rows key lookups would have to happen serially after that Top operation, resultantly serially after the index seek operation as well, which would be worse performance-wise in some cases.

In regards to your second question on what you've experienced with the execution plan changing from index seeks to clustered index scans back to index seeks again, this is known as the Tipping Point. Basically the Query Optimizer analyzes your query and quickly calculates the cost of each operation among a series of different execution plans it can use to fetch your data, partly based on cached statistics of your data that SQL Server maintains. The sum total of those costs allows the Query Optimizer to choose the execution plan with the lowest cost (ideally the fastest execution plan).
Those cached statistics are based on how many rows exist for each value in each column out of the total rows in the table and are used to do cardinality estimates on each operation that needs to occur. In short, cardinality estimation is the number of rows SQL Server thinks a particular operation is going to return. For example, your WHERE clause in your first example returns approximately 9,000 rows and the SQL Server Engine probably estimated the cardinality to be close to that number, which resulted in the cost of an index seek operation being sufficient enough that an index seek was chosen.
When you explicitly told SQL Server to return 10,000 rows instead, it likely tripped the Tipping Point, which made it think the amount of rows it needs to return will be more efficient with an index scan operation instead, because the cardinality of rows is high enough that the SQL Engine thinks a scan will likely encounter more contiguous rows efficiently as opposed to doing 10,000 index seeks. (Long story short, the Optimizer isn't perfect, especially when dancing so close to the tipping point, and an index seek is probably still more efficient here.)
As to why when you added another unindexed column to your SELECT list causes the Query Optimizer to choose a plan not over the tipping point, I couldn't precisely tell you for sure (especially without the actual execution plan in front of me) because it's pretty complicated what's going on under the hood, but generally speaking the estimations it did, based on the cached statistics of your data at that point in time, resulted in it calculating costs that were not over the tipping point any more, and resulted in it choosing an execution plan that leverages index seeks instead.


The index columns in sys.index_columns different from what are in the index

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group).

In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that the non-clustered index is partitioned.

If you expand your query to check all columns on sys.index_columns, you will see that the partition_ordinal column is set to 1 for ColDate, indicating that is a partitioning column, and key_ordinal is set to 0, indicating that it is "Not a key column".

Here is a full repro with a dummy table:

-- Create dummy partition function and scheme
CREATE PARTITION FUNCTION pf_test (INT)
AS RANGE RIGHT
FOR VALUES (1,2)
GO
-- Create dummy partition function and scheme
CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test_transactionId
ALL TO ( [PRIMARY] )
GO

-- Create dummy partitioned table
CREATE TABLE dbo.testPartitionedTable (
    id INT NOT NULL,
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    CONSTRAINT PK_testPartitionedTable PRIMARY KEY (a, id) ON ps_test(id)
)
-- Create the non-clustered index
CREATE INDEX IX_testPartitionedTable ON dbo.testPartitionedTable (b)
GO
-- Run your query for inspecting the index columns
SELECT b.name,
       c.name,
       a.*
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.testPartitionedTable')
  AND b.name = 'IX_testPartitionedTable'
GO


Can I use hypertables in timescaledb merely to get better insert rates?

(Timescale person here.)

Yes, you should be able to get much higher insert rate in a TimescaleDB hypertable than a normal table.

The primary downside of hypertables is that there are a couple limitations they expose related to the way we do internal scaling.  In particular:

We only allow a key to be marked as UNIQUE if it includes all partitioning keys (in its prefix).  So if you partition on a time column, the time column could be unique, or you could build a unique composite key on (time, device_id).  But this means you can't build a standard auto-increment id as the primary key (note primary keys are be definition UNIQUE).  But we find that doesn't typically make sense for time-series data.
You can define a foreign-key constraint from your hypertable to a regular table, but we don't currently allow the opposite: a FK from a regular table to a hypertable.  (But same as the UNIQUE constraint limitation above, this rarely makes sense or can be designed around.)

If you have other questions, Docs (docs.timescale.com) or community Slack (slack.timescale.com) are great resources.


.NET error when trying to create snapshot for transactional Replication

The error:

  System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection'

is typically (in my experience!) indicative of a mismatch between the version of .Net on the machine initiating the connection, and the target machine.

Ensure both machines have the same version of the .Net distributable.


How to create a new user in a remote offline database without elevating roles

Use a signed stored procedure. The right to alter logins is encapsulated within the SP. The users only have rights to execute the SP, not amend logins directly. You may need several SPs to cover different aspects for administration.


Differences between "Unique Key" and "Primary Key"

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.

Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.


How to fix accented character substitutions on French text in MySQL db

After you connect to MySQL in PHP OR in HeidiSQL, use SET NAMES 'utf8'. This is effectively equivalent to:

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

Now try your insert statement containing French characters.


PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

Assuming the locale of the database is en_GB.UTF-8 both in Ubuntu (master) and FreeBSD (slave), I believe the differences in sort semantics alone may account for the fact that the index is unusable on the slave.

Here's an example of how they sort differently:

On Ubuntu 12.04:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
0102
"0102"

On FreeBSD 9.0-RELEASE:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
"0102"
0102

This shows that the same locale orders differently the two strings "0102" and 0102 (even though they do not even contain any character outside the US-ASCII set...)

Here's a test that I suggest you try on your own dataset:

On the master:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.master

On the slave:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.slave

Now compare email.master and email.slave with diff or cmp. I suspect you'll find that they are not identical. In which case it demonstrates that the index replica can't be used, since its build rules on the master differ from the scanning rules on the slave.


Replication in MongoDb is a simple process to setup and I configured it locally during an online course I completed via the 10gen website, obviously this will fail over without an issue to your other instance but depending upon what fails on your machine will it be of any use!

I'd advise working your way through the free course they have it as it will solve 90% of the queries you will have with evaluating the product (replica sets are part of this)

We had it my old company and did run it on several machines for full redundancy and automatic failover to another node, you'll need a witness too.


One reason could be somebody changing options or running sp_configure. That would be logged in your error log.

Please read this article: Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

  using sp_configure to change a configuration value causes SQL Server
  to issue a DBCC FREEPROCCACHE statement.

and

  The same behavior will occur if you use a GUI such as SQL Server
  Enterprise Manager that issues an sp_configure call that changes a
  parameter.

The Plan Caching and Recompilation in SQL Server 2012 white paper gives some other possibilities.

  Flush Entire Plan Cache The following operations flush the entire plan cache, and therefore, cause fresh compilations of batches that
  are submitted the first time afterwards:

  Detaching a database
  Upgrading a database to a later database compatibility level
  Restoring a database
  DBCC FREEPROCCACHE command
  RECONFIGURE command (many of the options to this command cause a flush)
  ALTER DATABASE … MODIFY FILEGROUP command
  Modifying a collation using ALTER DATABASE … COLLATE command

  Flush Database-Related Entries in Plan Cache The following operations flush the plan cache entries that refer to a particular
  database, and cause fresh compilations afterwards.

  DBCC FLUSHPROCINDB command
  ALTER DATABASE … MODIFY NAME = command
  ALTER DATABASE … SET ONLINE command
  ALTER DATABASE … SET OFFLINE command
  ALTER DATABASE … SET EMERGENCY command
  DROP DATABASE command
  When a database auto-closes

Lots of these events should be in your log files.


How to speed up query on table with millions of rows

On reason this can happen is that you're using local variables.

  The problem is that this query takes so much time to go, despite all
  of the indexes i've made on different columns.

Here's an example using a similar setup. In the Stack Overflow schema there's a narrow-ish table called Votes that looks like this.

With no index on CreationDate, our only option would be to scan the Clustered Index. But if we create one only on CreationDate, the optimizer can choose to use that index if it thinks doing a Key Lookup for the rest of the columns is cheaper than scanning the Clustered Index and applying a predicate.

CREATE INDEX ix_yourmom ON dbo.Votes(CreationDate)

For this query:

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate;
GO

The cardinality estimate for unknown variables using between is 16.4317%. That leads to a clustered index scan and a missing index request for an index that covers the entire query.

If you run the query with RECOMPILE, you allow for the parameter embedding optimization.

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate
OPTION ( RECOMPILE );

Which gives us a different query plan, and a more accurate estimate.

Hope this helps!