Update column with different value for multiple rows using a csv file

-- Create some working space:
CREATE TEMPORARY TABLE t (
    productname ...,
    text ...
);
-- copy all the .CSV into it:
LOAD DATA INFILE INTO t ... .csv ...;
-- do the Update en masse:
UPDATE ForgeRock
    JOIN t ON t.productname = ForgeRock.productname
    SET ForgeRock.text = t.text;

Assuming that nothing changes from run to run, exactly the same statements can be reused.

If you also need to insert "new" data, see IODKU.


When are we supposed to set default-character-set for the client?

You have the option of setting character sets/collations on the fly:

character-set-client
character-set-connection
character-set-filesystem
character-set-results
character-set-server
collation-connection
collation-database
collation-server

While trial-and-error may be necessary, don't go willy-nilly on these variables in /etc/my.cnf. You are better off setting them dynamically during any trial-and-error testing.

To make sure of any corner cases, look at the initial variables of any mysqldump and see if character sets or collations are set in the beginning and reset at the bottom.

In fact, here is a sample for a mysqldump:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

See the variables

@OLD_CHARACTER_SET_CLIENT
@OLD_CHARACTER_SET_RESULTS
@OLD_COLLATION_CONNECTION
SET NAMES is hardwired to utf8

You could perhaps set these options

when calling the mysqldump
place these options in /etc/my.cnf under [mysqldump] group section
edit these values for existing mysqldumps using perl, awk, etc.


Firstly, you can remove the DISTINCT operator from each subquery - you're grouping by UserId and aggregating all other columns, so DISTINCT isn't doing anything:- You will always get a single row per distinct UserId value because of the grouping.
Secondly, because of this 'single row per UserId' behaviour courtesy of the grouping, you should actually use an INNER JOIN. You will always return a single row per UserId in both subqueries, so you've effectively turned your many-to-many into a one-to-one by grouping on the key column used to link the two tables.
Thirdly, you can potentially remove the subqueries altogether. Test the results and performance, but the below query should return the same results and should be quicker:
SELECT
    s.[UserId]                                       AS UserId                                 ,
    SUM(s.[ProbabilityAlive])                        AS ProbabilityAlive                       ,
    AVG(s.[ExpectedMonetaryValue])                   AS ExpectedMonetaryValue                  ,
    SUM(s.[EstimatedNumberOfTransactionsShort])      AS ExpectedTransactions                   ,
    SUM(s.[CustomerLifetimeValue])                   AS CustomerLifetimeValue                  ,
    SUM(s.[ResidualLifeTimeValue])                   AS ResidualLifetimeValue                  ,
    SUM(s.[NumberOfTransactions])                    AS Transactions                           ,
    SUM(s.[TotalSpend])                              AS TotalSpend                             ,
    SUM(s.[DeltaProbabilityAlive])                   AS DeltaProbabilityAlive                  ,
    SUM(s.[ProbabilityActive])                       AS ProbabilityActive                      ,
    SUM(s.[DeltaProbabilityActive])                  AS DeltaProbabilityActive                 ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsShort]) AS DeltaEstimatedNumberOfTransactionsShort,
    SUM(s.[EstimatedNumberOfTransactionsLong])       AS EstimatedNumberOfTransactionsLong      ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsLong])  AS DeltaEstimatedNumberOfTransactionsLong ,
    SUM(s.[EstimatedNumberOfTransactionsLongLeft])   AS EstimatedNumberOfTransactionsLongLeft  ,
    SUM(s.[ResidualLifeTimeValueLeft])               AS ResidualLifeTimeValueLeft              ,
    SUM(s.[CustomerLifetimeValueLeft])               AS CustomerLifetimeValueLeft              ,
    SUM([a.PeakClv])                                 AS PeakClv
FROM [Clv] s
LEFT JOIN [AdditionalClvData] a ON a.[UserId] = s.[UserId]
WHERE
    s.[ClientId]            = '2'
    AND s.[CalculationDate] = '02/28/2021'
    AND s.[Product]         = 'Total'
    AND a.[ClientId]        = '2'
    AND a.[CalculationDate] = '02/28/2021'
GROUP BY
    s.[UserId]


SQL Server: Should we use TCP or Named Pipes or use the default?

I prefer TCP/IP over Named Pipes, even though in most situations there will be no noticeable difference. You can do this by adjusting the protocols supported by the instance in SQL Server Configuration Manager rather than hard-coding things in your connection string (this makes it easier to make changes or to troubleshoot).

Essentially the routing and other overhead involved with named pipes (unless your apps are on the same machine as SQL Server, in which case there is only a little extra overhead) make it the less efficient option, especially at scale, in a slower network environment (100MB or less), or if your workloads come in bursts.

If your apps are on the same box as SQL Server, you should also keep shared memory in mind - if you have applications on the SQL Server box directly communicating with SQL Server, this is going to be the most efficient option.

You can read about the performance advantages of TCP/IP in more detail.


Is there any significant difference between Month(date) and DatePart(month, date)?

I just ran several tests on a largish table (12,430,129 rows) in SQL Server 2008r2.

The field is smalldatetime
The field is NOT indexed
I changed the processing order after each run to eliminate page caching issues

Results:

MONTH() - Ran in 6662, 6583, 6661 and 6560 ms.  Average runtime 6616.5ms

DATEPART() - Ran in 6520, 6584, 6552, and 6608 ms. Average runtime 6566ms

So, DATEPART() does seem to be marginally faster.  However, this is 7 tenths of a percent difference, so it probably won't matter a whole lot.


You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete:

DELETE RemoteTable
  FROM OPENQUERY(...) AS RemoteTable ...


high cached_page_count - when to start to worry and what can I do about it?

The fact that these tables both cause performance problems and use lots of pages in the cache isn't at all surprising.

I would rather find it expected. You call a couple of tables that (probably) both are on the larger side, and you also do lots of activity on those tables.

Perhaps you have lots and lots of inserts, which quickly causes the clustered index to become fragmented (hence freeing up space after rebuild)? If that is the case, then perhaps the key for the clustered index might have been badly chosen.

I would start by collecting more information. Possibly running a trace to see what activity you have going on.


Do I lose data if I change from INT to TINYINT

A tiny int will save you space. Here are their storage:

tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes

From MySQL official website: http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

Keep in mind a tinyint has a range up to 255. If there is a chance you may go over, I would use smallint or int

You can alter the column in sql and not lose data:

    alter table MyTable alter column MyColumn tinyint --(null|not null)


error while converting sql to eloquent query

I have found the an alternate for this. I created a stored procedure in laravel using migrations

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared('
            CREATE PROCEDURE fetch_all_users()
            BEGIN
                select * from users;
            END'
        );
    }

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    DB::unprepared('DROP PROCEDURE IF EXISTS fetch_all_users');
}

}

Then i called it in the code:

  DB::select('call fetch_all_users()')


You could temporarily set up a sshfs mount of the target directory and use --tab=dir_name on that mounted directory.


Database design for handling 1 billion rows and counting

5000 inserts per minute are about 83 inserts per second. With 5 indexes that's 400 physical rows inserted per second. If the workload was in-memory this would not pose a problem even to the smallest of servers. Even if this was a row-by-row insert using the most inefficient way I can think of. 83 trivial queries per second are just not interesting from a CPU standpoint.

Probably, you are disk-bound. You can verify this by looking at wait stats or STATISTICS IO.

Your queries probably touch a lot of different pages so that the buffer pool does not have space for all of them. This causes frequent page reads and probably random disk writes as well.

Imagine a table where you only physically insert at the end because of an ever-increasing key. The working set would be one page: the last one. This would generate sequential IO as well wen the lazy writer or checkpoint process writes the "end" of the table to disk.

Imagine a table with randomly-placed inserts (classic example: a guid key). Here, all pages are the working set because a random page will be touched for each insert. IOs are random. This is the worst case when it comes to working set.

You're in the middle. Your indexes are of the structure (SomeValue, SequentialDateTime). The first component partially randomizes the sequentiality provided by the second. I guess there are quite a few possible values for "SomeValue" so that you have many randomly-placed insert-points in your indexes.

You say that data is split into 10GB tables per week. That's a good starting point because the working set is now bounded by 10GB (disregarding any reads you might do). With 12GB of server memory it is unlikely, though, that all relevant pages can stay in memory.

If you could reduce the size of the weekly "partitions" or increase server memory by a bit you are probably fine.

I'd expect that inserts at the beginning of the week are faster then at the end. You can test this theory on a dev server by running a benchmark with a certain data size and gradually reducing server memory until you see performance tank.

Now even if all reads and writes fit into memory you might still have random dirty page flushing IO. The only way to get rid of that is to write into co-located positions in your indexes. If you can at all convert your indexes to use (more) sequential keys that would help a lot.

As a quick solution I'd add a buffering layer between the clients and the main table. Maybe accumulate 15min of writes into a staging table and periodically flush it. That takes away the load spikes and uses a more efficient plan to write to the big table.


Why MySQL pick a different index for the same query structure but different values?

Per your testing based on my comments, it does seem like it's related to the cardinality of the data. I.e. MySQL's Engine thinks a larger date range predicate in your WHERE clause will result in enough data to be returned that the cliente_id is a better index to use when searching for that data. (It may even be doing a Full Index Scan instead of seeking through the index, but this I can't say for sure without seeing more analysis of the execution plan.)
Normally there's a cause behind cardinality issues (e.g. non-SARGable predicates, bad statistics, etc), but unfortunately I'm not an expert on MySQL and from looking at your query it appears to be pretty benevolent. Without any additional information my only guess is it's not any particular root issue, rather it's just a shortcoming of the MySQL Engine in this particular case (which can happen occasionally with larger datasets).
Normally my suggestion would be to cautiously use an index hint (as you already have tried) and to keep an eye on future performance of that query.


SQL Server evaluation period has expired

Release candidates and community technology previews (CTPs) are considered evals and they expire 180 days after installation.  You may not be able to upgrade the instance as upgrades are not supported (see Upgrading SQL Server 2019 from CTP to RTM version: Is it even possible?)

Backup up your user database files (you'll have to copy the actual .mdf, .ldf, etc. files since you can't start SQL Server to do database backups), and then uninstall this instance, install the RTM version, and then attach the databases and create the necessary logins.  Alternatively, of course, you could just install another instance with the RTM version, but then you'd have to connect to a non-default instance, etc.


Avoid Vacuum (to prevent wraparound) on read-only databases

from your description , you actually need this process or else your old data will effectively become future data that you can’t access (wraparound)

You have 2 options here:

Run the vacuum yourself in non-peak hours to better control load
Reduce the number of transactions
(E.g. if you load data hourly you’ll have 24 transactions, each one loading just 41k persons )


Is Service Broker the best choice for auditing data changes on SQL Server Express?

I am guessing that the following post is the basis of what you are currently using: Centralized Asynchronous Auditing with Service Broker.

While I really like Service Broker, I don't feel that it is the best fit to address this particular situation. The main concerns that I have with Service Broker, at least in this particular scenario, are:

It is probably over-complicated if used mainly to separate the DML event from the audit event. If not moving the data to another system, it doesn't seem to offer much benefit since you will still need to persist the INSERTED and DELETED tables in the DML trigger.
It is event-based, and events vary greatly in their size and frequency. You could have a million 1-record operations, or one or two 1 million record operations. And since each event is each individual DML operation, you don't have any opportunity to remove duplicate and/or negating entries across several DML operations.

My preference is for dumping the changes into a queue table, and then in a separate process that is scheduled to run every X minutes, read Y number of rows and process them.

Create a queue table to hold the audit data for a particular table (not for each individual DML operation). The table should have:

the LOCK_ESCALATION option set to DISABLE (via ALTER TABLE {name} SET (LOCK_ESCALATION = DISABLE) ) to avoid conflict between the logging of new data by the trigger and the removal of data from the audit processing. This option was introduced in SQL Server 2008, so it cannot be use on 2005 instances, but no reason to not use it in the 2008 and newer instances since it doesn't alter the functionality in either case.
an AuditID PK that is one of the following:

an INT IDENTITY starting at -2147483648
a BIGINT IDENTITY
an INT with its value coming from a SEQUENCE that is set to CYCLE

only the fields that you are tracking changes for, if not all of them
fields for both "old" and "new" if you need to keep track of the before and after values each time you process. Depending on how you are auditing changes, if you have an archive of prior values, then you should only need the "new" values from the INSERTED table since the values in the DELETED table should already be in your prior audit data.

Create a trigger that simply inserts into the queue table. If you need to pass in both "old" and "new" values, JOIN the INSERTED and DELETED tables here rather than trying to maintain two separate queue tables, one for each of those pseudo tables. JOINing them at this point and inserting both the "old" and "new" values into a single row is a slight performance hit, but will guarantee that each operation stays together and in chronological order (via the incrementing PK).

If you are not tracking changes on all fields, then use either UPDATE() or COLUMNS_UPDATED() to determine if the columns being audited have indeed been updated (for UPDATE operations; these functions return true for all columns in INSERT operations). Please keep in mind that the UPDATE() and COLUMNS_UPDATED() functions do not determine if a change has been made in the value of the column(s)!! They only report if the columns were present in the SET clause of the UPDATE statement. The only way to determine if the value(s) actually changed is to JOIN the INSERTED and DELETED tables. But if not tracking all columns, those functions are great for exiting the Trigger without doing any work if no tracked columns have changed. Meaning, at the beginning of the Trigger, you would do:

IF (NOT UPDATE(TrackedColumn1) AND NOT UPDATE(TrackedColumn2))
BEGIN
  RETURN; -- no changes so just exit
END;

If you are not capturing both "old" and "new" values into the queue table, then this is the only opportunity to eliminate "updated" records where no columns actually changed. When inserting into the queue table, you just filter on WHERE IntColOld <> IntColNew OR StringFieldOld <> StringFieldNew COLLATE Latin1_General_BIN2 OR ISNULL(DateFieldOld, '1900-01-01') <> ISNULL(DateFieldNew, '1900-01-01') OR .... It is important to use a _BIN2 collation on string fields to ensure that the two fields are in fact identical. And you need to use INSULL for just the nullable fields so that they can equate if both are NULL.
Create a Stored Procedure that will run for X seconds, and in that time will process sets of TOP(@BatchSize) rows, using ORDER BY AuditID ASC. You do this by a WHILE loop that checks GETDATE() against @StartTime which was set at the beginning of the stored procedure. Depending on what processing you need to do, it is sometimes easier to create a local temporary table to either INSERT INTO #TempTable SELECT... the working set (then you will have to DELETE those rows at the end of each loop) or DELETE FROM using OUTPUT INTO #TempTable.

Here you can remove duplicate modifications. If you are tracking both "old" and "new" values per each row, you should also eliminate rows where none of the columns actually changed. You can do that by testing for WHERE IntColOld = IntColNew AND StringFieldOld = StringFieldNew COLLATE Latin1_General_BIN2 AND .... It is important to use a _BIN2 collation on string fields to ensure that the two fields are in fact identical. Non-binary collations, even if case-sensitive and accent-sensitive, etc, still allow for linguistic normalizations that should compare the same for regular comparisons, but not when auditing. Don't use _BIN collations as they have been deprecated since SQL Server 2005, which is when the _BIN2 collations came out.

Everything done inside of the loop needs to be within an explicit BEGIN TRAN / COMMIT / ROLLBACK. Use a TRY / CATCH block to manage that. This will prevent either losing some records or processing some twice.
Schedule the stored procedure to run every X minutes. Typically this is done via a SQL Server Agent job, but SQL Server Agent is not available for the Express editions. In that case, you can either use the Windows Task Scheduler or get something like Quartz.NET.

Setting up a process in this fashion allows you to have a more consistent, and tuneable, process that should steadily process X records every Y minutes. So it doesn't matter if you have 1 million single-row DML operations or a single 1 million row DML operation; this process will just keep chuggin' along, doin' what it does.


Insert statement performance in SQL Server

It's hard to be sure what's happening without a sample of data, but I can throw a few ideas out there:

Have you isolated whether the problem is with the INSERT or the SELECT?  It could be that the query is running slower on the one machine, and it's not the insert per se that's the problem.
Do the two servers have the same schema?  I once spent a week tracking a poor performance issue until discovering that I had temporarily deleted an index and forgot to restore it (oops).  There are several good tools for automatically comparing schemas.  For example, if they have the same fields in their clustering index, but in a different order, that could make a big difference.
Do the two tables have similar record counts, fragmentation, and fill factors?  Those shouldn't make a huge difference, but they will affect insert speed.
To avoid the implicit insert, consider writing the result of your SELECT into a temp table, with data types and clustering fields like the target.  Then INSERT from there into your target.  This will let you separate the cost of the INSERT vs the SELECT, and will minimize the time the target table is locked.


How to changes or update local server connection in Maintenance plan job

Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename.

Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection strings. Or recreate the maintenance plans.

  Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

   use msdb

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'

   -- set the new server name to the current server name

   declare @newservername as varchar(max)
    set @newservername=@@servername

   declare @xml as varchar(max)
    declare @packagedata as varbinary(max)
    -- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor
    FOR
    SELECT    id
    FROM         sysssispackages
    WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

   OPEN PlansToFix

   declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid

   while (@@fetch_status<>-1)  -- for each plan

   begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

   declare @planname varchar(max)
    select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

   set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
    UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

   end
    fetch next from PlansToFix into @planid  -- get the next plan

   end

   close PlansToFix
    deallocate PlansToFix

  ----- This will also handle the packages that have a tag such as
    ----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
    declare @newservername as varchar(max)
    set @newservername = @@servername
    declare @xml as varchar(max)
    declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor FOR
    SELECT id
    FROM sysssispackages
    WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

   OPEN PlansToFix
    declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid
    while (@@fetch_status<>-1) -- for each plan
    begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max))
    from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
    declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
    set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
    UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
    end
    fetch next from PlansToFix into @planid -- get the next plan
    end
    close PlansToFix
    deallocate PlansToFix


Increase Oracle buffer cache gets slower

In any system, if you make the part of the system that is not the bottleneck more efficient, there is the potential to make the system as a whole less efficient.  If, for example, your system's bottleneck is the CPU, making I/O more efficient by increasing the size of the buffer cache may end up causing many more threads to be waiting on the CPU which can make the system as a whole much slower as the operating system is now spending more time swapping between the competing thread, the CPU caches get overwhelmed, and you get the classic hockey stick graph of response time where once you get to the "knee", adding a bit more load adds a lot to the response time of the system.

If you have a system where I/O requests are highly uncorrelated, increasing the size of the cache could mean that Oracle will spend more time looking through the cache for a particular block before being forced to do a physical read after all.  That's probably relatively unlikely in an OLTP application but it's possible in a data warehousing type system where various reports are asking for blocks that no other report will want to reuse.

Of course, whether this is actually the problem that they encountered in the past is still an open question.  It is entirely possible that it was simply a matter of having a cold cache that needed to be warmed up or a perception problem where someone drew an incorrect conclusion from an earlier test or a poorly constructed test where, for example, someone inadvertently increased the size of the buffer cache at the expense of the PGA rather than at the expense of the operating system I/O cache.  I tend to share your distrust of human recollections of the past that aren't corroborated by some nice AWR reports so I'm not claiming that either of the above explanations are likely merely that they are potential explanations if the human recollections are correct.


File Group Association while Snapshop Replication

The information text in the description of the item property provides you with the solution to your question:
(emphasis mine)

Copy file group associations
Determines whether to script the associations between file groups and published objects. The file groups must already exist at the Subscriber.

If the file groups don't exist at the Subscriber and you set the option Copy file group associations to True, then the replication will fail, because it will expect the Subscriber to have identical file groups. All transferred objects will be transferred with an additional ...filegroup... parameter, depending on the object that has to be transferred.
If this option is set to false then the objects will be transferred without an additional ...filegroup... parameter, depending on the object being transferred. The replication will succeed, even if the Subscriber has different file groups, because the objects will be replicated to the default file group.
Proving It
I created a StackExchange database with the following files and file groups and added a new file group for this question:

CREATE DATABASE [StackExchange]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'StackExchange', FILENAME = N'C:\SQL\SQL_DATA\StackExchange.mdf' , SIZE = 56320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [DEFAULTRO]
( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [PUBL_SNAP_DBASE]  DEFAULT
( NAME = N'StackExchange_PUBL_SNAP', FILENAME = N'C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'StackExchange_log', FILENAME = N'C:\SQL\SQL_LOGS\StackExchange_log.ldf' , SIZE = 112384KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

This database already contains various tables and stored procedures from answering previous questions on DBA.SE. They were created in the PRIMARY file group. Here an extract:
Input

SELECT DS.name AS DataSpaceName
      ,AU.type_desc AS AllocationDesc
      ,AU.total_pages / 128 AS TotalSizeMB
      ,AU.used_pages / 128 AS UsedSizeMB
      ,AU.data_pages / 128 AS DataSizeMB
      ,SCH.name AS SchemaName
      ,OBJ.type_desc AS ObjectType
      ,OBJ.name AS ObjectName
      ---,IDX.type_desc AS IndexType
      ---,IDX.name AS IndexName
FROM sys.data_spaces AS DS
     INNER JOIN sys.allocation_units AS AU
         ON DS.data_space_id = AU.data_space_id
     INNER JOIN sys.partitions AS PA
         ON (AU.type IN (1, 3)
             AND AU.container_id = PA.hobt_id)
            OR
            (AU.type = 2
             AND AU.container_id = PA.partition_id)
     INNER JOIN sys.objects AS OBJ
         ON PA.object_id = OBJ.object_id
     INNER JOIN sys.schemas AS SCH
         ON OBJ.schema_id = SCH.schema_id
     ---LEFT JOIN sys.indexes AS IDX
         ----ON PA.object_id = IDX.object_id
            ----AND PA.index_id = IDX.index_id
WHERE OBJ.type = 'U'
AND OBJ.type_desc = 'USER_TABLE'
AND OBJ.name LIKE 'Q%'
ORDER BY DS.name
        ,SCH.name
        ,OBJ.name
        --,IDX.name

Output

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DEFAULTRO     | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Observations
As you can see all my tables related to questions on DBA.SE have been created in the PRIMARY filegroup. (Ok, nearly all of them.)
If I were to create a new table, then it would be created in the DEFAULT file group, which is no longer PRIMARY but the one named [PUBL_SNAP_DBASE].
Snapshot Replication
I have created a snapshot replication on my local machine which will replicate any changes from the StackExchange database to the StackExchangeSubscriber database.
The settings for the All Tables Properties - Copy file group associations has been set to TRUE, which should result in an error in the replication.
The Subscriber Database

CREATE DATABASE [StackExchangeSubscriber]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'StackExchangeSubscriber', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeSubscriber.mdf' , SIZE = 56320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'StackExchangeSubscriber_log', FILENAME = N'C:\SQL\SQL_LOGS\StackExchangeSubscriber_log.ldf' , SIZE = 112384KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

The subscriber database doesn't have the [DEFAULTRO] file group, nor does it contain the newly created [PUBL_SNAP_DBASE] one.
Verifying the Replication Status

Everything looks good. Let's continue with a modification...
Modifying the Published Database
Let's go ahead and create a new table in the published database StackExchange using DDL. First we'll check the objects in the target/subscriber database with the same script from above.
Output

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Looks the same except for the object that was created in the [DEFAULTRO] file group, which wasn't replicated. Hmmm. I think that is a hint of things to come.
Create Table via DDL
USE StackExchange
GO
CREATE TABLE Q221890 (Q221890_ID INT, Q221890_Text VARCHAR(20))

I didn't define the file group, so it should be created in the current DEFAULT file group, which is? Exactly.

+-----------------+----------------+-------------+------------+------------+------------+------------+------------+
|  DataSpaceName  | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType | ObjectName |
+-----------------+----------------+-------------+------------+------------+------------+------------+------------+
| PUBL_SNAP_DBASE | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q221890    |
+-----------------+----------------+-------------+------------+------------+------------+------------+------------+

I did not define the file group which means the table will be created in the newly defined [PUBL_SNAP_DBASE] file group, because it is the new default.

The replication will not replicate the new table, because of the Copy file group associations setting of True in the All Tables Properties.

Insert Data

USE [StackExchange]
GO
INSERT INTO [dbo].[Q221890]
           ([Q221890_ID]
           ,[Q221890_Text])
     VALUES
           (1
           ,'Test')
GO

Check Data

+------------+--------------+
| Q221890_ID | Q221890_Text |
+------------+--------------+
|          1 | Test         |
+------------+--------------+

Checking StackExchangeSubscriber Database in Replication Monitor

As can be seen in the monitor error message, the new object is created with the reference to the non-existing file group [PUBL_SNAP_DBASE] which causes an error at the subscriber.
Turn Off the Setting and Retry
We'll turn of the file group setting Copy file group associations and re-initialize the snapshot replication. Let's check the list of objects with the script:

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q221890                       |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Yes, the new table in the file group has been replicated.

Answering Your Question

I want to know how can I save filegroup structure during snapshot replication in SQL Server?

You can't. (See answer further down)

Does this property solve my problem?

No, which was proved with the above procedure

I want the same filegroups in publishers be created in the subscriber.

You will have to create the file groups at the target/subscriber before you create the objects in those file groups.
Turning on the option will cause errors, if the file groups do not exist.


SELECT with comparison operator, based on an order, skipping row(s)

All sorting are alphanumeric, and follow the ascii code
that means

1 decimal representation 49
N decimal representation 78
n decimal representation 110

1 comes before N and that before n and so on, so far as i can tell it is sorting correctly.

This link shows how you can sort the text "correctly"


And now I'm asking myself what this means and if I could store a locations object in there.

Yes, you can. (But there are not many great use cases for that.)

This does not do what you seem to think it does:

SELECT ROW(x)::locations FROM locations X LIMIT 1;

x is already a row type. By wrapping it into ROW(x) you create a record containing a column of type locations, which cannot be cast to the row type locations as it's something else. Use instead:

SELECT x::locations FROM locations x LIMIT 1;

... where the cast is redundant. So just:

SELECT x FROM locations x LIMIT 1;

However, if there is a column of the same name "x", this resolves to the column name. Pick a table alias that can never appear as column name or use this to be sure:

SELECT (x.*)::locations FROM locations x LIMIT 1;

Now, the cast is not redundant as Postgres would otherwise expand x.* or even (x.*) to the list of columns. Read the manual here and here.

Also just:

SELECT pg_typeof(x) FROM locations x LIMIT 1;

instead of:

SELECT pg_typeof(ROW(x)) FROM locations x LIMIT 1;

Aside: the ROW constructor does not preserve column names and always produces an anonymous record (as you found out the hard way).

SELECT ROW(x)::locations FROM locations X LIMIT 1;

Related:

Array of strings when updating a field


In case it helps anyone else.  If you open Reporting Services Configuration Manager you can specific a URL for the Web Portal. If you use the DNS alias here this automatically sets the subscription emails to have the DNS alias.  The Web Service and Web Portal address have to match and make sure you have your Kerberos/NTLM settings configured properly or you'll get an error when you try to manage subscriptions from the server itself.


Lost connection to MySQL server during query issue

There's a good chance the mysql crashed while trying to execute your query. First check the error log to see if it did. Common reason for mysql crash is memory exhaustion and tmp dir space depletion.


MySQL's source command executes the file as a separate process.  By design, it executes in a different session than the one where you assigned the variable.  You should find that executing set @2:='%scriptpath%'; source DBQuery1.sql; from within a mysql.exe session also does not work, either, although I haven't tested this myself recently.

As a workaround, you can write your script to automatically generate the SQL script file each time the file is run:

set MUSER=root
set MPASS=xprd123
set MHOST=172.33.49.55
set DB=DBName1
set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
set scriptpath=D:\STATS_COLLECTOR\
set scriptfile=D:\STATS_COLLECTOR\DBQuery1.sql

echo SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' > %scriptfile%
echo UNION >> %scriptfile%
echo SELECT >> %scriptfile%
echo MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN, >> %scriptfile%
echo MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS >> %scriptfile%
echo FROM information_schema.GLOBAL_STATUS >> %scriptfile%
echo INTO OUTFILE '%scriptpath%/stats.csv' >> %scriptfile%
echo     FIELDS TERMINATED BY ',' >> %scriptfile%
echo     ENCLOSED BY '"' >> %scriptfile%
echo     LINES TERMINATED BY '\n' >> %scriptfile%
echo ; >> %scriptfile%

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" < %scriptfile%

Another alternative is to write the query in a more versatile scripting language such as PowerShell, which would allow you to use the MySQL Connector/Net API to execute any arbitrary set of queries you wanted.


Postgres: query on huge (11gb ) index does not return

I don't know if my answer is accurate as we don't have much informations in your question, but perhaps you should consider one of this lead:

partitioning
partial indexes


Why won't some DBMS's allow rollback for certain DDL statements?

The reason why this works in PostgreSQL is that the system catalogs are regular tables.  So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on.  Since tables are transactional anyway, you'd almost have to go out of your way not to have it work that way.  (Lots of painful implementation details omitted here. ;-) )

I suppose, not knowing the source code, that other database engines use some custom internal structures to represent their system catalog information.  And so they'd have to put in extra effort, a lot of extra effort likely, to make transactional DDL work, and it's apparently not a priority for them.

The flip side of this is that this is the reason why major version upgrades of PostgreSQL are so painful.  Other products can presumably design their internal metadata structures with changes and updates in mind, and so there are no issues with upgrading to a new major version.  In PostgreSQL, there is no way to change a system catalog table to suddenly look like a newer version of a system catalog table, at least not while keeping the system online, because that would require access to the system catalogs.  Urgh.


ORA-12899 when inserting from sqlplus, but ok with jdbc

I have the same character set:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Result:

VALUE
-----------
AL32UTF8

I have NLS_LENGTH_SEMANTICS set to BYTE, but I can override this option in this way:

CREATE TABLE encoding (name VARCHAR2(5 CHAR));
INSERT INTO encoding VALUES('ééééé');

1 row created.

My NLS_LANG is: AMERICAN_AMERICA.UTF8, because my server is encoded UTF8.
But if I unset this environment variable:

unset NLS_LANG

and if i try again:

INSERT INTO encoding VALUES('ééééé');
ORA-12899; value too large for column "..."."..."."...." (actual: 10, maximum: 5)

so, yo can do a try and set your NLS_LANG variable, which is in the format

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

And it would work. You can obtain this parameters from NLS_DATABASE_PARAMETERS.


Why would SELECT * be magnitudes faster than SELECT foo?

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val.  Since id is the key it will be indexed and the order by will be a trivial amount of work.  To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows.

Change both queries to order by id and I think your execution times will be almost identical.


What does "-" mean before a column in where

It just negates the value in the column.

For example:

 with data1 as ( select 1 as col1 from dual)
 select -col1, col1
 from data1
 where -col1 = -1;

... returns -1,1.

Fiddle.


To bring a schema in 3NF, first you have to find a canonical cover of the dependencies. Then, you can apply either the “analysis” algorithm to find the Boyce-Codd Normal Form (which is more strict that the 3NF) or you can apply the “synthesis” algorithm to find the 3NF. In this case, both the algorithms gives the same results.

Here is a canonical cover of the dependencies:

date, emp_no → job
date, emp_no → salary
dep_no → d_budget
dep_no → mgr_emp_no
emp_no → phone_no
emp_no → proj_no
mgr_emp_no → dep_no
phone_no → area
phone_no → dep_no
phone_no → office_no
proj_no → dep_no
proj_no → p_buject

Let's find the 3NF with the synthesis algorithm. The first step is to collect together all the dependencies of the canonical cover with the same left part, that is:

date, emp_no → job
date, emp_no → salary

dep_no → d_budget
dep_no → mgr_emp_no

emp_no → phone_no
emp_no → proj_no

mgr_emp_no → dep_no

phone_no → area
phone_no → dep_no
phone_no → office_no

proj_no → dep_no
proj_no → p_buject

The second step creates a relation for each group, with the key given by the common left part:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       key: (dep_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R4 (mgr_em_no, dep_no)                  key: (mgr_em_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

The third step remove relations that are contained in another: in this case the relation R4 is contained inside R2, so R4 is removed.

The last step checks if any relation contains one of the keys of the original relation, and if this is not true add a new relation with one of the keys: since the original relation has (only) the key (date, emp_no), and the relation R1 contains it, no new relation must be added.

So the 3NF is the following:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       keys: (dep_no), (mgr_em_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

and reasoning from these relations you can infer the entities represented by each of them and the foreign keys towards other relations:

R1 represents the history of jobs of the employee, with key: (date, emp_no),
    and emp_no as FK
R2 represents a department, which is identified by dep_no but also by the number
    of the manager (which is a FK for employees)
R3 represents an employee, with key emp_no, with FKs phone_no and dep_no
R5 represents the “place” of an employee, with key phone_no and FK dep_no
R6 represents information about a project, with key proj_no and FK dep_no

Finally, note that the dependencies 4 and 5, that you have called “Multiple Functional Dependency to the same dependency”, are in effect particular, in the sense that they are transformed in a relation (department) with two primary keys, the number of the department and the number of the manager of the department. And this is managed by the algorithm in the third step by merging the two relations previously obtained (and for this reason the relation has two primary keys).


Design of fact table(s) and dimensions tables for data warehouse

The IBM Redbooks have a couple of good titles on modeling a data warehouse.

Data Modeling Techniques for Data
Warehousing
Dimensional Modeling: In a Business
Intelligence Environment

You might be better served with an OLTP database in 5NF, though. It's not hard to test simplified versions of both.


Where are queries against a linked server actually processed?

I don't think this is empirically deterministic. My understanding is that an entire table could be brought over and processed on the calling server instead of being processed remotely on the linked server. If both servers are SQL Server 2008+ you should see scenarios like this:

SELECT x.foo FROM linked.db.dbo.x JOIN linked.db.dbo.y ON ...

And in these cases you will see that the join, filters etc. are processed on the remote server. But this should not be construed as a blanket statement! You may see slightly different remoting decisions in the plan for a slightly different scenario:

SELECT x.foo FROM localdb.dbo.x JOIN linked.db.dbo.y ON ...

These are for SQL Server 2008+ -> SQL Server 2008+ linked servers. Other linked servers (lower versions of SQL Server, other platforms altogether) may behave differently, and it may be version-dependent, it could even be specific driver-version dependent, or query-dependent (e.g. is a function used). And it of course could depend on the permissions of the linked server account - if you can't see remote statistics / cardinality for the distributed portion of the query, the local optimizer is going to be pretty blind and may not make the decision you want.

If you want to guarantee that processing occurs on the remote server before it starts streaming data across, and you have actually tried to get it work with four-part names and the plan does not try to process remotely, you can use OPENQUERY instead of explicitly referencing four-part names. Never tried that in a view, though. :-)

If you come across a case where you expect processing to occur remotely and it's not, I'm more than happy to look at the actual plan and the stats profile to help understand why (or at the very least to pass it on to some of my connections who can help).

From my own personal experience, I've found much, much, much more reliable and predictable performance by doing whatever I can to make as many queries as possible entirely local. Linked servers are useful but they certainly don't lend themselves to real-time performance. While you may think it violates DRY in some sense, I would rather pay that cost once during a mirroring session or replication and have redundant data, than potentially pay for a sub-optimal remoting decision for every single cross-server query. And if the data can be slightly delayed there are even more options for moving data across to make it available locally (log shipping, SSIS, custom).


When do I use another type index than integer? (eg string) [integer vs string index]

There are a lot of places internally where it is possible to reference data using a user id, which is better than a string.  For example, if you had 4 tables referencing the user table, they'd each have a string column which is less efficient than 4 integer indexed columns.  This is the reason why most schemas use this layout; you're more likely to want to reference a user than to refer to the user by name.

However, you can also set username to be an index, which you might want to do if you are looking it up a lot.

To answer your edited question, a 24 character text field will require roughly 24 bytes.  Depending on whether it's a varchar or a char it will be stored differently, but the full amount will be needed for searching, so let's consider them both to be 24.

An integer field will require 4 bytes.

You can see how this will add up over time.  For comparisons, assume a linear time comparison, meaning 24 operations to compare two a string field, while only 4 operations to compare an integer field.  In practice, they will probably take roughly the same amount of time, though the string comparison cannot be faster.

If you want to search by username, create a table with the schema of username | user_id.  That'll let you search by username and join with the other data you are looking for while still being efficient.


Postgresql - return column of referenced table that is not primary key

You can just use a CTE for the insert, and then check the users table via SELECT:

WITH ins AS
(
INSERT INTO
    listings
    (posted_by, other_columns)
VALUES
    ('just_me', 'and that''s what I post')
RETURNING
    listings.id, posted_by
)
SELECT
    ins.id, users.internal_id
FROM
    ins
    JOIN users ON users.id = ins.posted_by ;

dbfiddle here


Should I revoke login from the default pgsql user?

You will need a superuser that can login for administrative purposes. So don't change the existing superuser to NOLOGIN unless you create another superuser first (but tgen you could just as well rename the existing superuser). The secure thing to do is

use a good password for the user

severely restrict access for that user in pg_hba.conf, ideally to local connections

There is one scenario where you could change the only existing superuser to NOLOGIN: create a regular user admin with NOINHERIT and
GRANT chris TO admin;

Then admin can always assume superuser privileges with
SET ROLE chris;

This setup has the advantage that you don't need a shared password for the superuser. Each administrator can have her own normal user that can assume superuser privileges when necessary.


how to output 3 column into 3 element in xml format with the same name?

It seems that when setting the directive (the 4th item in the !-delimited specification that is the field name alias) to "element", then the 3rd item in that specification is the element name that contains the value, whereas the 3rd item would otherwise be the attribute name that contains the value. In both cases the 1st item in the specification is the element that contains either the attribute or the sub-element. Hence, leaving it blank in [MyElement!1!!element] gave it no name for the child elements.

Instead, fill out the 3rd item so that the specification looks like:

[Parent!1!MyElement!element]

And so using the following query:

SELECT TAG,PARENT, col1 AS [Parent!1!MyElement!element],
                   col2 AS [Parent!1!MyElement!element],
                   col3 AS [Parent!1!MyElement!element]
FROM @table
FOR XML EXPLICIT

returns:

<Parent>
  <MyElement>Value1</MyElement>
  <MyElement>Value2</MyElement>
  <MyElement>Value3</MyElement>
</Parent>

Please note that you must fill out the 1st item in the specification, which means you cannot get this list without having a parent element.

Please also see the MSDN page for Example: Specifying the ELEMENT Directive.


Read on MDF file failed with reason 15105

As your mention error

  Read on "D:\Data\Filename.mdf" failed: 1(failed to retrieve text for
  this error. Reason: 15105)

MS SQL error 15105 is an error message which occur when a user try to create database file backup to a network shared disk or when hosting database file on a network share is not supported, and as a result such error message event take place.

How is MS SQL error 15105 caused ?

MS SQL error 15105 is caused due to following stuffs.

1)Trying to attach an .mdf file, that has not been detached properly.
2)There might not be enough free space on the destination where the user wants to keep his database backup file.
3)There might not be the permission to access the location of the backup file that is assigned in in the SQL Server service account.
4)It may also be possibility that the location of the folder in the error message is not the same instance where the user want to restore the backup. In this case it’s unlikely that the service has access to the folder.
5)If the user has copied the backup file from another computer, then the destination computer may block the file due to security reason.
6)Corruption of Windows Operating System
7)Attack of Viruses
8)Incomplete software installation
9)Excessive startup entries and registry errors.

For ref Here & Here


How about trying to solve this problem with Point-In-Time-Recovery? A dump has the problem that you got an old set of data anyway. I highly suggest to use a backup method giving you ways more recent version of the data.

You can also write yourself a simple change-log trigger storing all the information which has changed in a simple table. Here is how it works: http://www.cybertec.at/tracking-changes-in-postgresql/
The code in the site should give you a rough prototype and help you to fix things.


Oracle: Viewing settings for DBMS_NETWORK_ACL_ADMIN ACL?

In order to see also implicit privileges you can use this query:

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);


Table doesn't exists issue with Oracle stored procedure

The table does not exist at compile time, so with static SQL, the procedure remains invalid.

Run the INSERT from dynamic SQL as well (execute immediate).

Or even better, do not create tables on the fly.


Resume log shipping from a specific transaction

I have manually reset the log shipping by updating the last_restored_file column in the log_shipping_monitor_secondary table on the standby server to the last transaction log that was successfully restored.

Something like this:

UPDATE lsms
SET lsms.last_restored_file = '{transaction log file}'
FROM dbo.log_shipping_monitor_secondary lsms
WHERE lsms.secondary_database = '{DB Name}';


What is the appropriate SQL method for "Safe to SUM"?

The challenge with relying on ISNUMERIC() for this is that it can look at a lot of data that can't be summed and give it a pass. It isn't IS_ELIGIBLE_FOR_SUM() - it is merely returning true if the input could be converted to any of the numeric types. Commas are allowed because conversion to MONEY, for example, works successfully:

SELECT CONVERT(MONEY,'11,2');

So this also works:

SELECT SUM(CONVERT(MONEY,'11,2'));

Which probably isn't what you want. I think it would be safer to replace commas with periods before attempting to perform operations, or just exclude them and filter out rows this way:

DECLARE @t TABLE(n NVARCHAR(32));

INSERT @t VALUES(N'11,2'),(N'32'),(N'-32.4'),(N'32323'),(N'^'),(N'.'),(N'-');

SELECT SUM(CASE WHEN ISNUMERIC(n) = 1
    AND n NOT LIKE '%[^0-9.-]%' THEN CONVERT(MONEY,n) END)
  FROM @t
  WHERE ISNUMERIC(n) = 1 AND n NOT LIKE '%[^0-9.-]%';

The reason you have to perform a CASE expression in addition to the filter is that you can't control how the optimizer will process the statement - without CASE it might try to perform the conversion before the filter.

Of course, the smartest course of action would be to stop using the wrong data type in the first place. If you continue storing dirty data in ill-advised data types, you don't really get to complain about "optimal"...


Oracle query against SQL Server database giving inconsistent results

I'd suspect some for of characterset conversion issue.
When you do a TO_CHAR, you are getting back "┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐" (18 characters). That suggests that there is some confusion about whether the data contained in the column is single-byte or multi-byte.

"The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value"
This should ideally come across to Oracle as a RAW datatype not a VARCHAR2 or CHAR.

If you do a

CREATE VIEW temp_view as select  "id", "folder_id" from dbo.mediasite_presentation@ms

what shows up when you do a DESC temp_view


I would say yes, create the surrogate key of TransactionID. It would create a narrow, unique identifier across the table.

One of the reasons why a surrogate key would be best is because of relationships with other tables. If you need to relate the Transaction table with another table (Line_Item_Detail for instance) the entire primary key of the Transaction table would need to be in the related table as a foreign key. If you decided to use your candidate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code these columns would need to be in every related table. If you would need to update any of these fields they would need to be updated in the related tables too. This gets messy very quickly. With a surrogate key of TransactionID you would only need to add the TransactionID column to your related table. Since this will, should be, and IDENTITY column we should never have to update it.

Another thing you should be thinking about when creating SQL Server tables is the clustered index. A clustered index is how SQL Server physically stores the data for a table. You can have a table without a clustered index called a heap. In most cases you really should create a clustered index (read more about heaps vs. clustered indexes) Michelle Ufford has an excellent post on creating Effective Clustered Indexes. In short, your clustered indexes should be:

Narrow – as narrow as possible, in terms of the number of bytes it
stores
Unique – to avoid the need for SQL Server to add a
"uniqueifier" to duplicate key values
Static – ideally, never updated
Ever-increasing – to avoid fragmentation and improve write
performance

A clustered index on TransactionID would fill this criteria nicely.

Since you'll be adding a surrogate key to the Transaction table you should also consider adding an alternate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code or some other candidate key. This will guarantee there will be no accidental duplicates added to the table. If you don't add the alternate key there is a strong probability that duplicates will creep into the table.

One last thing, you may want to consider normalizing the table further. With the table design your provided if more than one item is added to a transaction you will be adding duplicate data to every row (Store Number, POS Terminal Number, etc). Querying will also become more difficult due to the duplication of data.


Add Login NT AUTHORITY\NETWORK SERVICE to SQL Server

The account will show up as just NETWORK SERVICE when you are looking at the local accounts via the UI (I recall). I'm not sure why you see one with the hostname but that would not be a system account on the local machine.

You can always just use T-SQL to add the login:

CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS;

Then assign it whatever permissions you would like.


MULTIMASTER REPLICATION
If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.
If you have EC2, you can also use the PXC Setup Guide in EC2.
MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7
If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

Setup the slave to replicate from one master. We'll call this the current master.
let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client
responsible for handling the switch just sleeps.
Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
Wait until the relay log is empty.
Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE
STATUS output.
Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup
replication.
Restart the slave threads using START SLAVE.
Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python
MySQL REPLICATION
Please do not put the options for connecting up replication in my.cnf.
They have been obsolete since MySQL 5.5
It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options
The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).
The options affected are shown in this list:
--master-host
--master-user
--master-password
--master-port
--master-connect-retry
--master-ssl
--master-ssl-ca
--master-ssl-capath
--master-ssl-cert
--master-ssl-cipher
--master-ssl-key


Why are event sourcing (CQRS) databases not popular?

Arguably the write-ahead-log that pretty much every SQL (and many no-sql) product uses is an event stream, allowing rebuild of objects (tables) in the event an instance fault. Granted most of them are opaque binary formats unsuited for application interrogation.

For that the ANSI standard now defines temporal features for SQL. This records the interval during which a particular value was valid. Recreating history is a matter of including an "as of" date-time value in the predicate. There are products specifically for storing time series data efficiently at volume.

The net result of all change is exactly what each table/ document/ columnstore provides to applications.

To answer the actual question - the outcomes offered by CQRS stores are mostly available from other, better known and supported products. The business risk of adopting one may outweigh the functional advantages so the products are not widespread.

CQRS can be considered a design pattern as much as it can be considered a product. As such any mainstraim store could implement a CQRS.

Accounting, banking and finance systems in general will store full information about each transaction i.e. each state change. Think of your bank statement - it has a complete list of all the credits and debits which occured within the reporting period. Chances are your bank uses a relational database to achieve this. I know Xero uses SQL Server. It is a tenet of double entry book keeping (accountancy to you an me) that no change can happen to an account without a transaction (ie an "event") and every transaction, once recorded, is immutable.


Any merit to MSSQLSERVER Trace from logman as opposed to XEvent Package 0

You can take a look at Jonathan's post here: An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target

The provider you see in logman is for using the Event Tracing for Windows. I have never used it but from what I understand it is more for tracking events from your application or the Windows OS back into SQL Server. I do not think it is something that can be used to just track general performance of SQL Server like you would with an XEvent session you create in SQL Server.


PostgreSQL + Citus - multi master - replication of data

Answer compiled from part-answers originally left as comments

Citus is not a multi-master solution. It's a sharding solution. It does not replicate data. It distributes data between PostgreSQL instances. Read How Citus Works article for further information. - Sahap Asci

From Multimaster in the PostgreSQL Wiki (contributed by dezso):

  2ndQuadrant provides Bidirectional Replication for PostgreSQL (BDR). In this case updates can be performed at any node of the cluster and then propagated to other nodes. BDR is essentially asynchronous: changes are applied at nodes some time later after committing transaction at master and various ways of resolving conflicts are proposed. BDR is really fast (provides almost the same speed as hot standby), but certainly there is no global consistency in such model.


One only needs to ensure that the SP and objects that the SP accesses all have the same owner (ownership chaining rules)--then SQL Server doesn't look at permissions of chained objects.  From the online SQL Server documentation:

  Stored procedures take advantage of ownership chaining to provide
  access to data so that users do not need to have explicit permission
  to access database objects. An ownership chain exists when objects
  that access each other sequentially are owned by the same user. For
  example, a stored procedure can call other stored procedures, or a
  stored procedure can access multiple tables. If all objects in the
  chain of execution have the same owner, then SQL Server only checks
  the EXECUTE permission for the caller, not the caller's permissions on
  other objects. Therefore you need to grant only EXECUTE permissions on
  stored procedures; you can revoke or deny all permissions on the
  underlying tables.


SQL: How to delete Millions of Records from a Table without disturbing the server

You need a way to estimate the amount of time that your process will take. I also recommend a simple mechanism for logging how much progress that your code makes. That way you can avoid surprises when it runs 3 weeks when you weren't expecting it to. So start by getting that estimate. You say "millions/billions" of rows in the question but there's a big difference between the two. Which is it? You'll also want to design your looping code in a way such that the last batch takes about as long as the first batch. That way you'll get predictable performance. I don't know what kind of criteria you're using to identify bad rows so I can't give any advice for that.

The basic strategy that you want to use for the delete depends on the structure of the table and the percentage of bad rows in it:

If you're deleting the majority of rows from a heap then realistically you can't design a looping paradigm that runs in constant time. The best I know if is to delete the top N rows in a loop and to stop when you delete less than N rows. Be sure to use the TABLOCK hint when doing the DELETE. Otherwise SQL Server will not free up pages from deleted rows which means that the final batch will essentially do a full scan of all of the previous rows in the table.
If you're deleting the majority of rows from a clustered table then loop over the clustered index. Avoid looping over the same row more than once.
If you're deleting a minority of rows from a heap or clustered table, consider creating a nonclustered index that will help identify the next batch of rows to delete. If possible you could consider a filtered index so you don't do more work than necessary when creating it. Avoid looping over the same row more than once.

There are a few things that might improve the speed of your process:

The delete part of the query will run single threaded so increasing CPU single core speed and getting at least 2 dedicated CPUs for your VM will help. If your VM is oversubscribed for resources that can lead to unpredictable performance. Here I'm assuming that the query to identify rows to delete is simple enough that it will be run entirely in serial. If not the number of CPUs available to SQL Server may become important.
Increasing the disk speed of your data and log files can help. As a general rule of thumb, switching to simple recovery model will not reduce the amount of data logged to the transaction log except if minimal logging is used. Minimal logging cannot be used for DELETE statements. All you gained by switching was not having to do transaction log backups.
Disabling all nonclustered indexes before doing the delete will help. Rebuild them after all of the bad rows are deleted.

The fastest delete is the one that you don't have to do. You said that you don't want to have to copy the data. That's fine, I assume you have good reasons for that. But know that you might be passing on a solution that runs in hours as opposed to days or weeks.


DBeaver 5.3.2 reconnect after db change (CREATE TABLE call)

Did you notice the "Refresh" button, usually at the bottom right of DBeaver screen?

Also accessible with F5 or from right-click menus from the database navigator:


Simple XML .query SELECT statement times out on 1 SQL SERVER instance

I'm not sure if it answers your question, but I had a similar problem. The resulution was limiting SQL Server to execute the query using one processor only.

If you have the same problem, this should work:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]')
OPTION(MAXDOP 1)

The thing is that SQL Server Query Optimizer decides that the query is costly enough to split its execution and merge those results. And sometimes this creates problems, so lowering degree of parallelism (MAXDOP) can solve it.

EDIT

Looking at the execution plan for this query, I find it hard to believe that somewhere in the middle of execution of each branch for @RequestXML.query('...'), estimated number of rows was 180,000. However, when I told it that I promise I will need only the first Foo, he reduced it to 900. So, try this...

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
    <Node4>Tomorrow</Node4>
</Foo>'

SELECT
    @RequestXML.query('/Foo[1]/Node1[1]')
    ,@RequestXML.query('/Foo[1]/Node2[1]')
    ,@RequestXML.query('/Foo[1]/Node3[1]')
    ,@RequestXML.query('/Foo[1]/Node4[1]')


Very simple read-only SELECT not run in parallel in postrgresql

The query leader is the only process that communicates with the front end.  All the SELECTed data for the non-aggregated version of the query has to be marshalled up from the parallel workers to the query leader.  That is expensive, so using parallel query here would almost certainly be counter-productive.  You might be able to get one anyway by setting parallel_tuple_cost and parallel_setup_cost to zero.

When doing the count(*) aggregate, each parallel worker only needs to send up one piece of information, a partial count, to the leader.  This is more obviously worthwhile.


Where should I start with this?

This is a really tough question. You're basically tasked with designing, administering, and migrating to a net-new database using new technologies without much support. This isn't exactly a recipe for success at the enterprise level. In my opinion, just getting your data loaded and accessible in a mySQL database will be the first and most important hurdle. Here's a response on how to convert well formatted CSV dumps to table using HeidiSQL:
https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table

In terms of database design, I can't really help. However getting a "copy" of what current databases look like is a great starting point. This allows you to "select into new_table query_from_source_tables".

  How should I store the tables?

I would store them as a MySQL database called something along the lines of "source" or "legacy". You can grant other databases access as needed to these tables.

  What ETL processes, tools should I use?

Entirely up to you. I like, generally, doing 1-off queries and then turning them into views if they need to persist/update through time. ETL is entirely up to the demand of the things which access the database. In my opinion, kick the can on this until you have things working in MySQL with your legacy dump and have a decent new design.

  What else do I need to think about? I have been reading a lot about indexing etc, but is there anything specific to this problem that I may miss?

I wouldn't worry about indexing until you're worried about performance tuning. That usually comes after proof-of-concept. I would specifically think about sustainable database design and/or migration plans to a new database structure on the applications that leverage the old database.

In terms of specific problems, I think you've just got your work cut out for you to redesign a database system. Focus on one "major" thing at a time, so you can focus more on what you're doing.


How to grant execute permissions to a stored procedure but not to the underlying databases

This is easy to accomplish with Module Signing.

The concept is to create a certificate in the database that the user, BI_User, has access to (and contains the stored procedure), [ORMAN], which will then be used to sign the stored procedure, BI_DataDownload,  that has the functionality the are currently restricted from accessing.

The certificate is then copied (public key only) into the restricted database, [PSMAN], for the purpose of creating a local user from it. That local certificate-based user is then assigned whatever permission(s) are needed in order for the BI_DataDownload stored procedure to complete successfully.

The BI_User account still only has permission to connect to [ORMAN] and to execute BI_DataDownload. No permissions are being granted to that account (Login or User) at all. The permissions are granted only to the certificate-based user that exists in the [PSMAN] database. The connection between the BI_DataDownload stored procedure and the certificate-based user is (effectively) the public key of the certificate (which is why we only needed to copy that part to the [PSMAN] database). And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in the ADD SIGNATURE statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.

The following illustrates this concept:

Main Setup

USE [master];

CREATE DATABASE [ORMAN];
ALTER DATABASE [ORMAN] SET RECOVERY SIMPLE;

CREATE DATABASE [PSMAN];
ALTER DATABASE [PSMAN] SET RECOVERY SIMPLE;

CREATE LOGIN [BI_User] WITH PASSWORD = 'DBA.SE_239700';
GO

USE [PSMAN];
CREATE TABLE dbo.RemoteTable
(
  [RemoteTableID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_RemoteTable] PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NULL
);

INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'one');
INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'two');
GO

USE [ORMAN];
CREATE USER [BI_User] FOR LOGIN [BI_User];

GO
CREATE PROCEDURE dbo.[BI_DataDownload]
AS
SET NOCOUNT ON;

SELECT rt.[RemoteTableID], rt.[SomeValue]
FROM   [PSMAN].[dbo].[RemoteTable] rt;

GO

GRANT EXECUTE ON dbo.[BI_DataDownload] TO [BI_User];
GO

Test Current Situation (no extra permissions)

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];
/*
Msg 916, Level 14, State 1, Procedure dbo.BI_DataDownload,
            Line XXXXX [Batch Start Line YYYYY]
The server principal "BI_User" is not able to access the database
       "PSMAN" under the current security context.
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$PSMAN]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing PSMAN',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO dbo.[BI_DataDownload]
    BY CERTIFICATE [Permission$PSMAN]
    WITH PASSWORD = 'SomePassword';

---
-- Copy Certificate to [PSMAN] DB:
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'Permission$PSMAN')), 1);

SELECT @CertificatePublicKey AS [Cert / PublicKey]; -- debug

EXEC (N'
USE [PSMAN];
CREATE CERTIFICATE [Permission$PSMAN] FROM BINARY = ' + @CertificatePublicKey + N';');
---

-- Create User in [PSMAN] DB to allow for extending permssions there:
EXEC (N'
USE [PSMAN];
CREATE USER [Permission$PSMAN] FROM CERTIFICATE [Permission$PSMAN];

GRANT SELECT ON dbo.[RemoteTable] TO [Permission$PSMAN];
');

GO

Test with Module Signing

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];  -- SUCCESS!!!!!!!!
/*
RemoteTableID    SomeValue
1                one
2                two
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];


Website is too slow when number of records increases?

The basic problem you have is that everything is recursive and you are attempting to lock pages of data in InnoDB, creating lots of MVCC info. In some case, you may have attempted to lock the same InnoDB page repeatedly. That explains the deadlocks. I have commented on this situation deeply with someone in three separate posts on this same issue:

Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?
Reasons for occasionally slow queries?

You need a workaround. You may want to try the following algorithm:

For this example

id = 12
amount = 22.75

Step 01) Make a table to collect all father_ids to be updated

DROP TABLE IF EXISTS fathers_to_update;
CREATE TABLE fathers_to_update
(
    father_id INT NOT NULL,
    PRIMARY KEY (father_id)
);

Step 02) Making a table that will be a FIFO queue. Put the value of 12 into the queue:

DROP TABLE IF EXISTS fathers_to_queue;
CREATE TABLE fathers_to_queue
(
    father_id INT,
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);
INSERT INTO fathers_to_queue (father_id) VALUES (12);

Step 03) Get the Count of fathers_to_queue

SELECT COUNT(1) FROM fathers_to_queue;

If the count is 0, goto Step 07)

Step 04) Remove the front number in fathers_to_queue

SELECT MIN(id),father_id INTO @frontndx,@dad FROM fathers_to_queue;
DELETE FROM fathers_to_queue WHERE id = @frontndx;

Step 05) Collect all father_ids from 161_ft_individual whose id is @dad into the queue

INSERT INTO fathers_to_update SELECT father_id FROM 161_ft_individual WHERE id = @dad;
INSERT INTO fathers_to_queue (father_id) SELECT father_id FROM 161_ft_individual WHERE id = @dad;

Step 06) Go back to Step 03

Step 07) Perform a single UPDATE of all records in 161_ft_individual you collected in fathers_to_update

UPDATE fathers_to_update A
LEFT JOIN 161_ft_individual B
USING (father_id)
SET B.total_leg=B.total_leg+@amount;

That's it. I basically performed a preoder tree traversal back up the hierarchy to all records until father_id was NULL, which makes Step 03 stop collecting father_ids.

Give it a Try !!!

UPDATE 2011-12-16 12:18 EDT

Back on October 24, 2011, I actually wrote an algorithm in the MySQL Stored Procedure language to perform preorder tree traversal: Find highest level of a hierarchical field: with vs without CTEs . Hope it helps !!!


SQL Server Agent cannot log on to "MyServer"

Here are some thoughts to help investigate.

Have you tried just removing the alias local host information?

What is the value for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\VERSION.INSTANCE_NAME\SQLServerAgent,

What are MyServer's SQL Server Logs saying?

Did you make sure you don't have any aliases in your hosts file.

C:\Windows\System32\drivers\etc\hosts

# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost

Any named aliases in SQL Server Configuration Manager?


Modelling document sharing permissions with NoSQL

I guess I'll answer my own question until someone gives a better answer.

When the relationships between entities are known and important to preserve, these relationships are costly to implement and enforce with a NoSQL database.  Looking at the relationships between entities is easy to do with a single SQL statement that is comprised of more sub queries and joins.  Doing something similar with lookups or other javascript/json programmatic instructions is cumbersome and likely to be substantially more computationally intensive when compared with a relational database/SQL approach.

Hence, using a NoSQL database in a situation where the relationships between many entities are known, important to preserve and important to enforce, is like using a steak knife to chop down a tree.  It can be done, but there are better tools for the job.


Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.

Otherwise, you're looking at:

ALTER TABLE (mytablename) REBUILD - which takes it offline
Doing deletes with TABLOCK hints
TRUNCATE TABLE (mytablename)

I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have to deal with deletes (which cause the empty space problem) and updates (which cause the forwarded fetches problem.)


Cannot restore database with transaction log

To restore to 03:07 AM you would require the following backup files:

SW_SAM_GEMAAL_backup_2017_09_21_030159_6588486 (FULL)
SW_SAM_GEMAAL_backup_2017_09_21_030501_0515333 (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001 (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030701_....... (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030801_....... (TLOG; possibly)

In your screenshot you are only listing the files up until the SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001 TLOG backup.
However, this file does not contain any modifications past 03:06:01 AM and this is possibly why you are unable to restore to the point-in-time at 03:07:00 AM.

The FULL backup is always consistent as is stated by the official Microsoft (Docs) documentation (emphasis mine):

  DATABASE

  Specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up. During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.

  When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. Only a log backup can be restored to a specific time or transaction within the backup.

Reference: BACKUP (Transact-SQL)

Depending on your error message, you might find that because you ommitted one TLOG backup file, that the restore is failing.

Reproducing Issue

Inserting data / creating backups

I reproduced your situation on my local SQL Server with the following steps and was able to restore to a point-in-time with a transaction still open during the FULL and TLOG backups:

-- 11:23 BEGIN TRANSACTION WITH INSERT
BEGIN TRAN TESTBACKUP
INSERT INTO dbo.Good_Morning
(
    Test
)
VALUES
(
    'A Text '
)
-- 11:24 TLOG Backup
-- 11:25 FULL Backup
-- 11:26 NEXT INSERT
INSERT INTO dbo.Good_Morning
(
    Test
)
VALUES
(
    'A Text 2'
)
-- 11:27 TLOG BACKUP
-- 11:28 COMMIT
COMMIT TRAN TESTBACKUP
-- 11:29 TLOG BACKUP

Backup History

My backup history looked like this:

database_name backup_start_date       backup_finish_date      backup_type physical_device_name                                                          first_lsn         last_lsn          checkpoint_lsn    database_backup_lsn
AdminDB2      2017-09-21 11:24:08.000 2017-09-21 11:24:08.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112408.trn   35000001910900001 35000001911200001 35000001908000042   35000001908000042
AdminDB2      2017-09-21 11:25:15.000 2017-09-21 11:25:15.000 Full        C:\SQL\Backup\NB31710\AdminDB2\FULL\NB31710_AdminDB2_FULL_20170921_112515.bak 35000001911100002 35000001913500001 35000001911700042   35000001908000042
AdminDB2      2017-09-21 11:27:03.000 2017-09-21 11:27:03.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112703.trn   35000001911200001 35000001914300001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 11:29:06.000 2017-09-21 11:29:06.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112906.trn   35000001914300001 35000001914600001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 11:31:59.000 2017-09-21 11:31:59.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_113159.trn   35000001914600001 35000001914900001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 12:15:00.000 2017-09-21 12:15:00.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_121500.trn   35000001914900001 35000001915200001 35000001911700042   35000001911700042

Restoring Database

I then restored my database to 11:28:00 AM with the following statements:

RESTORE DATABASE [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\FULL\NB31710_AdminDB2_FULL_20170921_112515.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112703.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112906.trn'
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2017-09-21T11:28:00'

Solution

Add your last TLOG backup of your database to the restore statement and you should be able to restore to your point-in-time at 03:07:00 AM.


Here are some thoughts on this:

Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc?
If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is due to it being in System.dll which is a supported library. You can test this out by using the new constructor that allows for a CompressionLevel. Just change CompressionMode.Compress to be CompressionLevel.Optimal. SQL Server is bound to a specific version of the CLR, not to a specific version of the .NET Framework. Meaning, any new functionality in any of the supported libraries is usable, so long as any server you deploy your code to has had its .NET updated.

This does not mean you will get better compression. I tested this code and it came up with the same 31 bytes for "Hello World" as PHP and Fiddler generated, as noted in one of the questions you linked to: https://stackoverflow.com/questions/11435200/why-does-my-c-sharp-gzip-produce-a-larger-file-than-fiddler-or-php .

I just tested again with a string of 3405 random characters (i.e. "fsdkjf skdj f..."). I declared the variable as NVARCHAR(4000) and ran it through your code, after making the changes that I suggest here. The length of the compressed binary was 211 bytes. I then copied and pasted that same string into Notepad++, made sure that the encoding was set to "UCS-2 Little Endian" and saved. I checked the file in Windows Explorer and it was 6812 bytes (6810 in data, as also reported by DATALENGTH of the variable, plus 2 for the byte order mark). I ftped that to a Linux server in binary mode. File size was still 6812 on the Linux server. I then ran gzip -9 on it (i.e. max compression; default is -6). Compressed size? 231 bytes. So the .NET GZipStream actually did slightly better.
CompressionMode.Compress and CompressionLevel.Optimal are functionally equivalent. Each one is the assumed default when specifying the other.
Don't use string for the input param; use SqlString.
Get rid of the byte[] bytes line
Change new MemoryStream( bytes ) in the first using block to be:
new MemoryStream(str.GetUnicodeBytes())
You can get rid of the if( str == null ){return new SqlBinary();} line. Instead of handling this in the .NET code, just add WITH RETURNS NULL ON NULL INPUT to the CREATE FUNCTION. This way SQL Server won't even invoke your code if the input is NULL :). Just keep in mind for when you have multiple input parameters, this option will return NULL if any of them are NULL. If naturally at least one of them should be able to pass in a NULL, then you do have to handle this situation in your code.
Replace this line if( String.IsNullOrEmpty( str ) ){str = " ";} -- which actually returns a compressed space which is not an empty string -- with:

if (str.Value.Length == 0)
{
    return SqlBinary.Null;
}

I haven't tried "zlib" yet, but unfortunately both "SharpZipLib" and "DotNotZip" are buggy and have not been updated in several years and no indication that they will be updated. However, the bugs in "DotNetZip" seem to be mostly around zip file archives and not the GZip functionality (which has been working quite well in SQL# :-) ).


INSERT / UPDATE to merge data, when empty data is an empty string

To avoid putting values two times in your INSERT statement use VALUES(<column_name>) function.

  INSERT ... ON DUPLICATE KEY UPDATE Syntax
  You can use the VALUES(col_name) function in the UPDATE clause to
  refer to column values from the INSERT portion of the INSERT ... ON
  DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in
  the ON DUPLICATE KEY UPDATE clause refers to the value of col_name
  that would be inserted, had no duplicate-key conflict occurred. This
  function is especially useful in multiple-row inserts. The VALUES()
  function is meaningful only in INSERT ... UPDATE statements and
  returns NULL otherwise.

Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL or empty strings you can do

INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
  f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
  l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
  work   = COALESCE(NULLIF(work, ''), VALUES(work));

Here is SQLFiddle demo

If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update