TSQL issues with view and data operations

Sounds like you have at least one row where the combination of year/month is not valid (and only with certain plans is the conversion attempted before that row is filtered away). To be certain, we should see the full definition of the view, the actual plan for the query that works, and the estimated plan for the query that fails (if you can get it).

First, I suggest that instead of breaking the string from SAP apart into separate components, you import the data into the correct data type from the start. But that's a different story. Given what you have, I would identify the bad rows this way:

SELECT [key?], period_year, period_month
  FROM dbo.[table shown as "a" in my_sales_view]
  WHERE TRY_CONVERT(datetime,CONCAT(period_year,
    RIGHT('0'+RTRIM(period_month),2),'01')) IS NULL;

And when you find the bad rows and fix them, your current view will work, but if you don't make any other changes, this will just be temporary and bad data will happen again. So your view can say the following instead of all this CONCAT nonsense:

TRY_CONVERT(datetime,CONCAT(a.period_year,
  RIGHT('0'+RTRIM(a.period_month),2),'01'))) AS period_date

Of course this will mean that no matter what predicate you apply to period_date, those bad rows won't appear in the output. Hopefully you wouldn't expect them to, but without the error, it won't be obvious those rows are missing.


Not able to login to MySQL server through MySQL workbench

If your MySQL Server and Workbench are on different machine then you need a mysql user to connect and you also need to comment the bind address in my.cnf

User creation

GRANT ALL PRIVILEGES ON *.* TO 'someUser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf or /etc/my.cnf on Unix/OSX systems.

Comment bind-address

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1 (commented)

And restart the MySQL server for the changes to take effect ,Now try this user from workbench


What is the most performant way to group by and join high cardinality columns in column based table?

I only have a little bit of experience with columnstore indexing, but my experience has been awesomely positive so far. I'm assuming when you say the cardinality of the SomeFactTable.NumberA column  is ~8 million of the ~9 million records, that you mean they share the same value. If so, then I think a columnstore index would work great for you on it, because it compresses the data of the columns it encompasses, and ~8 million / ~9 million records would be about a 90% compression ratio.
Again I'm no expert but I'd recommend testing a columnstore index on just the (NumberA) column vs one on all the fields you might group on, i.e. (Timestamp, Description, NumberA).


PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted

The required trigger is quite fancy. This is the trigger function:

CREATE FUNCTION wh_calc_ins_upd_trg_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    t text[] ;
BEGIN
    SELECT array_agg(x[1])
    FROM regexp_matches(new.calc_form, '\[bp(\d+)\]', 'gi') AS x
    INTO t ;

    new.bbg_pulls_1_id := t[1]::integer ;
    new.bbg_pulls_2_id := t[2]::integer ;
    new.bbg_pulls_3_id := t[3]::integer ;
    new.bbg_pulls_4_id := t[4]::integer ;
    new.bbg_pulls_5_id := t[5]::integer ;
    new.bbg_pulls_6_id := t[6]::integer ;
    new.bbg_pulls_7_id := t[7]::integer ;
    new.bbg_pulls_8_id := t[8]::integer ;

    SELECT array_agg(x[1])
    FROM regexp_matches(new.calc_form, '\[wc(\d+)\]', 'gi') AS x
    INTO t ;

    new.wh_calc_1_id := t[1]::integer ;
    new.wh_calc_2_id := t[2]::integer ;
    new.wh_calc_3_id := t[3]::integer ;
    new.wh_calc_4_id := t[4]::integer ;
    new.wh_calc_5_id := t[5]::integer ;
    new.wh_calc_6_id := t[6]::integer ;
    new.wh_calc_7_id := t[7]::integer ;
    new.wh_calc_8_id := t[8]::integer ;

    RETURN new ;
END ;
$BODY$ ;

And this is the association of this function to a trigger that should be fired before insert or update of column 'calc_form'. [or...]

CREATE TRIGGER wh_calc_ins_upd_trg
    BEFORE INSERT OR UPDATE OF calc_form
    ON wh_calc
    FOR EACH ROW
    EXECUTE PROCEDURE wh_calc_ins_upd_trg_func();

This trigger plays with a few concepts:

Regular Expressions to capture the integers in [bp___] and [wc___]. The regexp_matches function returns a SET of text[].
The SET of text[] is converted into an array of arrays by means of array_agg.
We convert from text to integer, by means of tx::integer.

You can try then:

INSERT INTO
    wh_calc
    (calc_form)
VALUES
    ('[bp20]/[bp3]')
RETURNING
    * ;

And see that you get what you expect:

| id |    calc_form | bbg_pulls_1_id | bbg_pulls_2_id | bbg_pulls_3_id | bbg_pulls_4_id | bbg_pulls_5_id | bbg_pulls_6_id | bbg_pulls_7_id | bbg_pulls_8_id | wh_calc_1_id | wh_calc_2_id | wh_calc_3_id | wh_calc_4_id | wh_calc_5_id | wh_calc_6_id | wh_calc_7_id | wh_calc_8_id |
|----|--------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
|  4 | [bp20]/[bp3] |             20 |              3 |         (null) |         (null) |         (null) |         (null) |         (null) |         (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |

You can check it a SQLFiddle


How to Monitor Transaction Log Usage In SQL Server?

The easiest way is to buy an off-the-shelf monitoring tool. They all give you this kind of information - Idera SQL DM, Quest Spotlight, SentryOne SQL Sentry do these kinds of things with really low impact.

The next easiest way is to build something yourself. If you're going that route, I'd start by logging sp_WhoIsActive to a table - especially with the @get_transaction_info = 1 switch.

If you try the roll-your-own approach, you need to be aware that queries aren't the only thing that will cause the transaction log to grow. For example, if you're using replication, database mirroring, or Always On Availability Groups, SQL Server needs to retain history when one of those replicas is offline. To learn more about what's causing that, check log_reuse_wait_desc:

SELECT name, log_reuse_wait_desc FROM sys.databases;


Your app requires an an infinite amount of ways to store an infinite amount of arbitrary data. At that point you're critically limiting what you can do with a schema. You may as well just store the environment, and an array of templates.

CREATE TABLE flashcards (
  id          serial  PRIMARY KEY,
  environment jsonb,
  templates   jsonb   CHECK ( jsonb_typeof(templates) = 'ARRAY' )
);

Now, in the client (browser/javascript), you call (using pugs but you could also use Handlebars or even compile in into an Angular Component)

pug.compile(templates[a])(environment)

Using this kind of schema you can meet the requirements of your application in storing and rendering flashcards, but I have no idea what kind of data querying you'll be doing on them.


MySQL Error: FUNCTION LEVENSHTEIN already exists

Check your import .sql script.
There is possibly a create function statement between the create tables.
You have dropped the tables, but the functions already exists because you didn't know it was there.

BTW if you would clear the tables, you must not drop an recreate them, you could also issue a truncate table tbl_name;


Sounds to me like Merge Replication, considering the offline aspect. You generally have a "master" which the "clients" sync to. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-2017
I would question the need to distribute the database in the first place. Makes things soooo much more complicated, so carefully weigh the gain you get from distributing the database to the headaches you get from it.


How do I properly perform a MySQL bake-off?

Here is the first thing I thought about

Setup 3 DB servers (identical HW/OS configs) each installed with

MySQL
Percona
MariaDB

Get a Fourth Server installed with MONyog (eval version lasts 30 days)
Register the 3 DB servers in MONyog
Use the Performance Metrics Charts in MONyog or Set Up Your Own Charts in MONyog
Use SysBench  against all 3 DB Servers at your discretion

This is just a basic outline. Believe me, you can use Monyog right out of the box. Check out my testimonial at MonYog's Website (Look for my name on the page). I eventaully convinced my company to purchase the Utlimate Version outright after my testimonial, which they watched with me as it happened.

Caveat : Do no get Monyog until you have the 3 DB servers and Sysbench Ready to get the best 30-day usage of MonYog.

UPDATE 2011-06-20 13:18 EDT

If you keep the InnoDB Buffer Pool Small (default for innodb_buffer_pool_size is 8M) and keep the binary logs disabled (do no include log-bin in my.cnf), then these metrics should be measured

Innodb_buffer_pool_pages_flushed
Innodb_buffer_pool_write_requests
Innodb_row_lock_time_avg
Innodb_os_log_fsyncs
Queries, Questions
SELECTs ((Com_select + Qcache_hits) / Questions)
INSERTs ((Com_insert + Com_replace) / Questions)
UPDATEs (Com_update / Questions)
DELETEs (Com_delete / Questions)

Many of these are default graphs in MONyog's Monitors/Advisors Section, and more graphs can be created to check other desired MySQL metrics. MONyog also lets you export the graphs as CSV data.

UPDATE 2011-10-12 14:17 EDT

Percona performed a bake-off among multiple releases of MySQL

MySQL 4.1
MySQL 5.0
MySQL 5.1 (with built-in InnoDB)
MySQL 5.1 with InnoDB-plugin
MySQL 5.5
MySQL 5.6

All tests were performed with MySQL unconfigured. The results?

MySQL 4.1 performs the best single-threaded
MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

What do I get out of this? Your must configure MySQL 5.5/5.6 to get multicore enhancements engaged.


Can I specify the default backup location for non-automatic backups?

No, it's not possible to specify a default location.  The best way to do this would be to write a shell script (which specifies your location of choice) to handle your backups.

You can have DB2 remove old backups (using the AUTO_DEL_REC_OBJ database configuration parameter with proper values for NUM_DB_BACKUPS and REC_HIS_RETENTN), but that will only occur after a successful backup.

So you'll need to ensure that there is enough space for the backup to complete successfully.


Database stored procedure with a "preview mode"

There are several flaws to this approach:

The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes back 15 minutes later -- after grabbing some coffee, stepping outside for a smoke, walking around the block, coming back in, and checking something on eBay -- and realizes that they didn't click the "OK" button to actually perform the operation and so finally clicks the button?

Do you have a time-limit on proceeding with the operation after the preview is generated? Or possibly a way to determine that the data is in the same state at modification time as it was at initial SELECT time?
This is a minor point as the example code could have been done hastily and not represent a true use-case, but why would there be a "Preview" for an INSERT operation? That could make sense when inserting multiple rows via something like INSERT...SELECT and there could be a variable number of rows inserted, but this doesn't make much sense for a singleton operation.

  this is undesirable because of ... a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

Where exactly does this "low degree of confidence" come from? While it is possible to update a different number of rows than show up for a SELECT when multiple tables are JOINed and there is duplication of rows in a result set, that should not be an issue here. Any rows that should be affected by an UPDATE are selectable on their own. If there is a mismatch then you are doing the query incorrectly.

And those situations where there is duplication due to a JOINed table that matches multiple rows in the table that will be updated are not situations where a "Preview" would be generated. And if there is an occasion where this is the case, then it needs to be explained to the user that they are updated a subset of the report that is repeated within the report so that it does not appear to be an error if someone is only looking at the number of affected rows.
For the sake of completeness (even though the other answers mentioned this), you are not using the TRY...CATCH construct so could easily run into issues when nesting these calls (even if not using Save Points, and even if not using Transactions). Please see my answer to the following Question, here on DBA.SE, for a template that handles transactions across nested Stored Procedure calls:

Are we required to handle Transaction in C# Code as well as in stored procedure
EVEN IF the issues noted above were accounted for, there is still a critical flaw: for the short-period of time the operation is being performed (i.e. prior to the ROLLBACK), any dirty-read queries (queries using WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) can grab data that isn't there a moment later. While anyone using dirty-read queries should already be aware of this and have accepted that possibility, operations such as this greatly increase the chances of introducing data anomalies that are very difficult to debug (meaning: how much time do you want to spend trying to find a problem that has no apparent direct cause?).
A pattern like this also degrades system performance by both increasing blocking by taking out more locks, and generating more Transaction Log activity. (I see now that @MartinSmith also mentioned these 2 issues in a comment on the Question.)

Additionally, if there are Triggers on the tables being modified, that could be quite a bit of additional processing (CPU and Physical/Logical reads) that is unnecessary. Triggers would also further increase the chances of data anomalies resulting from dirty reads.
Related to the point noted directly above -- increased locks -- the use of the Transaction increases the likelihood of running into deadlocks, especially if Triggers are involved.
A less severe issue that should relate only to the less-likely scenario of INSERT operations: the "Preview" data might not be the same as what is inserted with regards to column values determined by DEFAULT Constraints (Sequences / NEWID() / NEWSEQUENTIALID())  and IDENTITY.
There is no need for the additional overhead of writing the contents of the Table Variable into the Temporary Table. The ROLLBACK wouldn't affect the data in the Table Variable (which is why you said you were using Table Variables in the first place), so it would make more sense to simply SELECT FROM @output_to_return; at the end, and then don't even bother creating the Temporary Table.
Just in case this nuance of Save Points is not known (hard to tell from the example code as it only shows a single Stored Procedure): you need to use unique Save Point names so that the ROLLBACK {save_point_name} operation behaves as you are expecting it to. If you re-use the names, a ROLLBACK will roll-back the most recent Save Point of that name, which might not be at the same nesting level where the ROLLBACK is being called from. Please see the first example code block in the following answer to see this behavior in action: Transaction in a stored procedure

What this comes down to is:

Doing a "Preview" doesn't make a lot of sense for user-facing operations. I do this frequently for maintenance operations so that I can see what will be deleted / Garbage Collected if I proceed with the operation. I add an optional parameter called @TestMode and do an IF statement that either does a SELECT when @TestMode = 1 else it does the DELETE. I sometimes add the @TestMode parameter to Stored Procedures called by the application so that I (and others) can do simple testing without affecting the state of the data, but this parameter is never used by the application.
Just in case this was not clear from the top section of "issues":

If you do need/want a "Preview" / "Test" mode to see what should be affected if the DML statement were to be executed, then do NOT use Transactions (i.e. the BEGIN TRAN...ROLLBACK pattern) to accomplish this. It is a pattern that, at best, only really works on a single-user system, and not even a good idea in that situation.
Repeating the bulk of the query between the two branches of the IF statement does present a potential problem of needing to update both of them each time there is a change to make. However, differences between the two queries are usually easy enough to catch in a code review and easy to fix. On the other hand, problems such as state differences and dirty reads are much harder to find and fix. And the problem of decreased system performance is impossible to fix. We need to recognize and accept that SQL is not an Object-Oriented language, and encapsulation / reducing duplicated code was not a design-goal of SQL like it was with many other languages.

If the query is long / complex enough, you can encapsulate it in an Inline Table-Valued Function. Then you can do a simple SELECT * FROM dbo.MyTVF(params); for the "Preview" mode, and JOIN to the key value(s) for the "do it" mode. For example:

UPDATE tab
SET    tab.Col2 = tvf.ColB
       ...
FROM   dbo.Table tab
INNER JOIN dbo.MyTVF(params) tvf
        ON tvf.ColA = tab.Col1;

If this is a report scenario as you mentioned it could be, then running the initial report is the "Preview". If someone wants to change something they see on the report (a status perhaps), then that doesn't require an additional preview since the expectation is to change the currently displayed data.

If the operation is to perhaps change a bid amount by a certain % or business rule, then that can be handled in the presentation layer (JavaScript?).
If you really need to do a "Preview" for an end-user-facing operation, then you need to capture the state of the data first (maybe a hash of all of the fields in the result set for UPDATE operations or the key values for DELETE operations), and then, before performing the operation, compare the captured state info with the current info -- within a Transaction doing a HOLD lock on the table so that nothing changes after doing this comparison -- and if there are ANY difference, throw an error and do a ROLLBACK rather than proceed with the UPDATE or DELETE.

For detecting differences for UPDATE operations, an alternative to computing a hash on the relevant fields would be to add a column of type ROWVERSION. The value of a ROWVERSION datatype automatically changes every time there is a change to that row. If you had such a column, you would SELECT it along with the other "Preview" data, and then pass it along to the "sure, go ahead and do the update" step along with the key value(s) and value(s) to change. You would then compare those ROWVERSION values passed-in from the "Preview" with the current values (per each key), and only proceed with the UPDATE if ALL of the values matched. The benefit here is that you don't need to calculate a hash which has the potential, even if unlikely, for false-negatives, and takes some amount of time each and every time you do the SELECT. On the other hand, the ROWVERSION value is incremented automatically only when changed, so nothing you ever need to worry about. However, the ROWVERSION type is 8 bytes, which can add up when dealing with many tables and/or many rows.

There are pros and cons to each of these two methods for dealing with detecting inconsistent state related to UPDATE operations, so you would need to determine which method has more "pro" than "con" for your system. But in either case, you can avoid a delay between generating the Preview and doing the operation from causing behavior outside of the end-user's expectations.
If you are doing an end-user-facing "Preview" mode, then in addition to capturing the state of the records at select-time, passing along, and checking at modification-time, include a DATETIME for SelectTime and populate via GETDATE() or something similar. Pass that along to the app layer so that it can be passed back to the stored procedure (mostly likely as a single input parameter) so that it can be checked in the Stored Procedure. Then you can determine that IF the operation is not the "Preview" mode, then the @SelectTime value needs be no more than X minutes prior to the current value of GETDATE(). Maybe 2 minutes? 5 minutes? Most likely not more than 10 minutes. Throw an error if the DATEDIFF in MINUTES is over that threshold.


SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently

As pointed out in the comments by Aaron, the current session was being caught, because they are reused in the connection pool.

The solution is to filter it out:

AND session_id <> @@SPID


Biggest possible double/float for a column in my MySQL database

Do not use (m,n) on the end of FLOAT or DOUBLE.  That causes a rounding (at the bottom) or a truncation (at the top).  If you want (m,n), you probably should use DECIMAL(m,n).

FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38.

DOUBLE stores 53 bits (about 16 digits, 8 bytes) and has a huge range.

Your question is vague -- are you storing "money"?  If so, then you really should use DECIMAL(m,2).  m=8 lets you store up to a million dollars (or Euros, etc).  But m can be as big as you want.  (Remember to subtract the 2 before seeing how big the numbers can be.)  (14,2) would store up to a trillion dollars, with precision to the penny.  It will take 8 bytes of storage.


warning sign on the select operator - what does it mean?

If you right-click on the warning -> properties you should see some Warnings:


Too many WAL files in archive folder PostgreSQL Streaming Replication

wal_keep_segments tells it how many files to keep in the pg_xlog directory, not the archive directory.

PostgreSQL doesn't manage the archive directory for you, that is up to you.  It doesn't even know where (or what) the archive location is, that is why it asks you for an archive_command, rather than just a directory.  As far as PostgreSQL is concerned, your archive command could pass the files through od, send the output to a line printer with acid-free paper, then put that in a 3 ring binder and store them in your attic.  Although more common would be encrypt them and upload them to Amazon's S3, say.

It is kind of weird to use both wal_keep_segments and archive_command together, at least for relatively simple setups.  If you just want a streaming replica and don't need the ability to do point-in-time recovery, you could turn off archiving altogether.

If you really want to keep using an archive but have it cleaned up when the files are no longer needed, you can use the archive_cleanup_command setting.  But now that we have streaming replication and replication slots, that is almost obsolete.


MYSQL to other DB migration using MYSQL workbench

MySQL Workbench is primarily a tool to work with the MySQL database server and hence all implemented migrations ultimatively use MySQL as target. No other target is supported.


Transactions in SQL Server Emergency Mode

firstly when a database is marked as suspected

There may be other reasons but the most common one is when you have some issue with data file or log file of database and it goes through crash recovery after SQL Server is restarted, since SQL Server will not be able to recover the database due to corrupt, incorrect or missing information in one of the files(data or log) it has to mark database as suspect. Learn how to make database suspect (only for testing purpose. It goes without saying that please do not try this on Prod and even UAT).

  secondly when we use emergency mode.

In any case where you want to gain access to contents of database but are not able to because it has been marked suspect. You can then put database in emergency mode which is "inconsistent database" provided to you without fully doing crash recovery. Please note you might not always be successful.

  Are they rolled back normally?

When you use emergency mode you have lost all possibility to bring database online so you force it to "kind of come online with inconsistent data" using emergency mode. When you do so no recovery happens and you are just given read access to inconsistent database. After this when you run checkdb with repair_allow_data_loss then all kinds of actions are taken to bring database online in consistent state, to do so repair_allow_data_loss will remove any such thing which might block database to come online.

Putting database in emergency and then recovering it should not be treated as method to solve suspect database issue. Restoring from good backup should be the preferred way.

Would also suggest you to read Paul Randal blog


How to view an encrypted view or stored procedure

I have a fairly detailed description about this problem here.

In short, the object is not really encrypted, but rather obfuscated. Therefore we can retrieve the original back. The method is a little involved but it consists of these steps:

Connect to the instance using the Dedicated Admin Connection
Select the obfuscated code like this:

SELECT @secret = imageval
FROM   sys.sysobjvalues
WHERE  objid = OBJECT_ID(@object_name);

Replace the object with another one that has the same name same object_id and the same length in bytes (e.g. using ALTER PROCEDURE)
Get the newly obfuscated code the same way as above
XOR the three values together (obfuscated original, replacement and obfuscated replacement)

That will give you the original code. However, as Kin mentioned, there might be support and even legal implications with doing this so be sure to consult your lawyer first.


Does Sql Server maintain the free space defined by fillfactor?

Ni, it isn't maintained. It is there to postpone page splits. But after so many inserts, the page will split. Then it is up to you whether you fell like doing anything about it (rebuild or reorganize).


SQLCLR presents a surface area for creating fully custom data types.  In fact, that is how SQL Server supports geometric and hierarchy data types.

Since SQLCLR is based on the Microsoft.Net common-intermediate-language, a large variety of constraints are possible for a SQLCLR data type.  For instance, you could easily ensure an email address is from a valid domain by querying DNS for the MX record as part of the data validation code.

SQL Server can index a CLR UDT as long as IsByteOrdered:=True is set. There are a ton of properties like that, which you can change to affect how SQL Server uses the UDT.  For equality matching like that required by an index, SQL Server simply looks at the binary value stored in the page i.e. it doesn't need to look at the UDT code at all.

As an example of a SQLCLR User-defined-type that checks a domain-space for validity, I've written the following terrible proof-of-concept VB.Net code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, MaxByteSize:=320, ValidationMethodName:="ValidateEmailAddress")> _
Public Structure EmailType
    Implements INullable
    Implements IBinarySerialize

    Private m_Null As Boolean
    Private m_EmailAddress As String

    Public Function ValidateEmailAddress() As Boolean
        'is the email address valid?
        If Me.IsValidDomain Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function ToString() As String
        Return Me.m_EmailAddress
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
        Get
            ' Put your code here
            If Me.m_EmailAddress Is Nothing Then
                Me.m_Null = True
            Else
                Me.m_Null = False
            End If
            Return m_Null
        End Get
    End Property

    Public Shared ReadOnly Property Null As EmailType
        Get
            Dim h As New EmailType
            h.m_Null = True
            Return h
        End Get
    End Property

    'called when SQL Server passes in a SqlString value to the UDT
    Public Shared Function Parse(ByVal s As SqlString) As EmailType
        If s.IsNull Then
            Return Null
        End If

        Dim u As New EmailType

        u.m_EmailAddress = CType(s, String)
        If u.ValidateEmailAddress = False Then
            Throw New Exception("Invalid Email Address")
        End If
        Return u
    End Function

    Public Function IsValidDomain() As Boolean
        Dim iAtSign As Int32 = Microsoft.VisualBasic.Strings.InStr(Me.m_EmailAddress, "@")
        Dim iDomainLength As Int32 = Microsoft.VisualBasic.Strings.Len(Me.m_EmailAddress) - iAtSign
        Dim sDomain As String = Microsoft.VisualBasic.Strings.Right(Me.m_EmailAddress, iDomainLength)
        Dim bResolvable As Boolean = False
        Try
            Dim ip As System.Net.IPHostEntry = System.Net.Dns.GetHostEntry(sDomain)
            bResolvable = True
        Catch ex As Exception
            Throw New Exception(Me.m_EmailAddress & " is not from a resolvable domain.")
        End Try
        Return bResolvable
    End Function

    ' save the value to the database
    Public Sub Write(w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.m_EmailAddress)
    End Sub

    ' retrieve the value from the database
    Public Sub Read(r As System.IO.BinaryReader) Implements IBinarySerialize.Read
        Dim sTemp As String = r.ReadString
        Dim sTemp1 As String = ""
        For Each n As Char In sTemp.ToCharArray
            sTemp1 = sTemp1 & n.ToString
        Next
        Me.m_EmailAddress = sTemp
    End Sub

End Structure

Since the code above is not signed, you should test it only on a development machine where you can enable the TRUSTWORTHY database setting.  Once the code is compiled, you import it into SQL Server via the following:

CREATE ASSEMBLY SQLCLREmailType
AUTHORIZATION dbo
FROM 'C:\Path\Goes\Here\SQLCLREmailType.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE TYPE EmailType
EXTERNAL NAME SQLCLREmailType.[SQLCLREmailType.EmailType]

The UDT can then be used like this:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES ('mvernon@mvct.com')
    , ('us@them.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

The above code returns:

+------------------+
| (No column name) |
+------------------+
| mvernon@mvct.com |
| us@them.com      |
+------------------+

However, when attempting to insert an address belonging to a non-existent email domain, as in:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES , ('us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

You see an error:

  Msg 6522, Level 16, State 2, Line 27
  A .NET Framework error occurred during execution of user-defined routine or aggregate "EmailType":
  System.Exception:  us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com is not from a resolvable domain.
  System.Exception:
     at SQLCLREmailType.EmailType.IsValidDomain()
     at SQLCLREmailType.EmailType.ValidateEmailAddress()
     at SQLCLREmailType.EmailType.Parse(SqlString s)


Azure SQL: Azure SQL Database traffic migration to newer Gateways

I received the same email. I have received similar emails in the past. The only thing I do is add the IPs of the new gateways on the firewall rules on the Azure SQL Database logical server as shown on the image below.

Add the IP addresses of the gateways that belong to the zones where you have created your Azure SQL Database logical servers.

You do not have to do anything else. Based on my experience everything keeps working as usual.


Table person is situated in schema assigned to your apex workspace. You can go to APEX IDE to the section SQL Workshop -> Object Browser, schema name will be displayed in the top right part of the page. Or you can execute query:

select owner from all_tables
where table_name = 'PERSON';

You will get names of all users that have table with such name.
After that you need to use this name in your query:

select * from schema_name.person


Short answer: It depends on a lot of different things.

Longer answer w/ questions: If you haven't used any database management systems before, then any of the main options (SQL Server, Oracle, PostgreSQL) will suit your needs. However, do you already have a DBMS installed on a server, or is this a personal project starting from scratch? Do you require a physical server and your own copy of the software, or are you willing to explore cloud-based options?

What are your plans for backup and restoration of critical data? What level of availability do you need (for example, 5 9s)? Apart from loading data every 30 seconds, are you querying this data often or at all, or are you simply storing it?

Without knowing anything else about your application, how it's going to be sending data to this central DB, and how you're going to use the data, it's hard to make a firm recommendation (at least for me).

As for data types and minimising space used, you've got the right idea. Good indexing strategies based on a use case come into play when trying to minimise data usage. Minimising data duplication (look at normalisation forms if you're not sure about it) using properly set up Dimension and Fact tables in the appropriate layout (Star, Snowflake etc.), assuming a Data Warehouse use (that's sort of what it sounds like) will help with query performance as well as minimising space used.

TL;DR: Just spin up an instance or two of Microsoft SQL Server on Microsoft Azure to test your application/service and see how it goes. It's scalable, can be well within a small budget and if you don't need it, you can get rid of it.

http://azure.microsoft.com/en-us/services/sql-database/


I will first answer your question, what you ask, but that is probably not what you want.

If you want to dump a single table using mysqldump, you can just put its name after the database name. So, if you execute:

mysqldump [connection parameters here] a_database a_table > $DEST/a_table.sql

It will backup only the a_table table from the database a_database. If you write:

mysqldump [connection parameters here] a_database a_table another_table yet_another_table \
> $DEST/a_database.sql

It will dump a_database.a_table, a_database.another_table and a_database.yet_another_table.

I mentioned that this (exporting each table separatelly) is probably not what you want because if you execute mysqldump several times and the server is not in read_only mode, you will end up with several tables in an inconsistent state (the backup was done at different times). In many cases, that is undesirable because if you have to restore more than one table they could end up with incompatible data. Using several runs of mysqldump in parallel can be interesting, as it can speed up the backup to some point, but you must makes sure that you lock the tables for a consistent backup.

Another approach is to use mysqldump --tab: This will let you export each table on a couple of separate files (a .sql and a .txt), which you can later import with mysqlimport (an it will be faster than a .sql file). You can still select specific tables in the same way:

mysqldump [connection parameters here] --tab=$DEST \
a_database a_table another_table yet_another_table

$ ls $DEST

a_table.sql
a_table.txt
another_table.sql
another_table.txt
yet_another_table.sql
yet_another_table.txt

The idea is that you should avoid exporting the tables at different times, as you will lose the consistency that mysqldump --lock-tables (default) or --single-transaction will give you.


1062 Duplicate entry but there are no duplicates?

It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.

I suspect if you run:

SELECT *
FROM   <table>
WHERE  <keyfield> = 'Upping Your Type Game-http://jessicahische.is/talkingtype'

or if that key is a composite

SELECT *
FROM   <table>
WHERE  <keyfield1> = 'Upping Your Type Game'
AND    <keyfield2> = 'http://jessicahische.is/talkingtype'

You will find one matching row, and that your code at the point of the error is trying to insert a second.

Dealing with duplicates on insert:

If you try INSERT a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.

There is also the mysql specific ON DUPLICATE KEY UPDATE option (see https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) if you are happy to sacrifice compatibility with other RDBMSs.

As of version 9.5 Postgres supports a similar feature with slightly different syntax (ON CONFLICT DO UPDATE/NOTHING - see https://wiki.postgresql.org/wiki/UPSERT).

MS SQL Server, Oracle and some other systems support MERGE statements as defined in the SQL:2003 standard (see https://en.wikipedia.org/wiki/Merge_(SQL)) which is intended to achieve the same functionality and more. Be careful with any of these options if cross database compatibility is (or is likely to be in future) one of your goals.


Resume Logshipping after Primary database restored

When you restored the primary the log shipping has broken. The only way to resume it is to re-initiate it, with a new full backup. End of discussion.

  We do schema and data changes for new Release deployment on to D2 database.

And that is your real problem. Do all changes as code changes and deploy by running deployment scripts. When you want to migrate the changes done in development on D2 to D1 simply run the migration scripts on D1. Use Visual Studio Database Projects for instance.

Even w/o proper development process you could had applied the changes between D2 and D1 as scripts run on D1, and don't break the log shipping relation. Eg. tools like Red Gate SQL Compare and Data Compare would had done the job.


Sync MySQL schemas between different databases

MySQL Workbench did the trick.
If anyone reading this wants to do the same thing, go to File, create a new model, then go to Database (the menu is different, lol), and then go to Synchronize with Any Source.
I just selected the source and since the databases were on the same server, I selected the overwrite option to change the destination database.


Automatically partition a table in mysql (pymysql) everyday for new data

Yes REORGANIZE of a "future" partition is the right way.  See http://mysql.rjweb.org/doc.php/partitionmaint for a discussion of why, plus sample Perl code.

Do not create more partitions that you need immediately; it leads to inefficiencies.  Instead, check to see if you need a new partition more often than simply "just in time".


Are there mysql client environment variables for USER and DB on Linux?

Not that I know of, but you can make a file called my.cnf in a directory that you specify with the MYSQL_HOME environment variable. For example say you have ~/mysql_home/db/my.cnf:

[mysql]
user = username
password= ****
host = hostname
db = databasename

Then you can call the client like

MYSQL_HOME=~/mysql_home/db/ mysql

and it will use those options. Please keep in mind that your password will be readable by whomever can read that file and it isn't encrypted!


How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

I think you have to look at extensions entirely differently.  The CLR stored procedures are most closely related to the procedural language interface which is one application of the extension system.  The extension system has goals quite a bit broader than mere stored procedure languages.  Extensions can define primitives or functions as well.

However if we limit the question to the stored procedure interface, the answer is, broadly speaking, that they have a similar purpose.  There are a significant number of major differences between the two and they come from different database viewpoints.

The two most obvious differences are that functions have a lot less transactional control in PostgreSQL than MS SQL, and that MS SQL can more easily return multiple result sets from functions than PostgreSQL can.  On the other hand, you can write stored procedures in PostgreSQL in a wide range of languages including TCL, Perl, Python, Java, and more.  I suspect that PL/Mono is relatively unmaintained, but it could be polished up without too much effort allowing you to run CLR code as well.  So PostgreSQL gives you a great variety of languages, while MS SQL gives a little more control in the stored procedures over things like transactions.

This being said, in general these just force different perspectives on database development.  Both are quite programmable databases, though I personally think PostgreSQL is slightly more programmable, this may say more about the fact that I know PostgreSQL better than MS SQL than it does about the two RDBMS's.

My reason for siding with PostgreSQL here has to do with the ability to hook a lot of these functions into indexes.  This is also useful when combined with filters on indexes (which both support).  In essence I am a little happier with the way functions fit into SQL with PostgreSQL.  However, this is a really minor detail.....

In the end I think it is probably possible to do pretty much anything you can do with one with the other.  I find the "object-relational" perspective of PostgreSQL to be very empowering.  I would, however, not recommend making a purchasing decision based on such an abstract criteria.  Nitty gritty matters.


The nagios monitoring scripts for mysql should be a good start.

If you want to monitor your database with this kind of email alerts, you should really try to setup nagios or some similar software as this is exactly the purpose of a monitoring software.


Include the providerid column (and appropriate values) in the UserRating table.
Make (userid, providerid) a unique key for the UserRating table.
Make (id, providerid) a unique key of CreditRating.
Make (ratingid, providerid) a foreign key of UserRating, referencing (id, providerid) in the CreditRating table.

Also, make (providername, typename) a unique key of Provider, if you haven't already. Or better yet, normalize them into separate provider and type tables, with a many-to-many join as appropriate.


Not specifically like AWR, but there are a number of built-in tools that can be deployed.

The "mda" tables will be your best friends for identifying poorly performing queries.

Rob Verschoor's site has detailed information on how to use them along with some pre-built tools.

http://www.sypron.nl/mda.html

Official documentation can be found here:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc70002.1570/html/title.htm

sp_sysmon, monserver and historical server may be of little use. They're more suited to server configuration.


sql command utility version is not working

If you don't qualify the SQLCMD.EXE location at the command prompt, it will use the first one found in your search path. So you need to either fully qualify the path to the executable for the desired version or modify your search path so that the newer version directory is before the older one.


You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem.

Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is the way to do that. Right?

If so: wrong approach. Start with a single table. Partition if/when you need to for performance and maintenance reasons. With a DB of this scale it is very unlikely that you will ever need to, it's tiny.

  How many partitions are too many

Because the constraint exclusion code isn't super smart, try to stick to low partition counts. I prefer tens or hundreds at most.

  Is having small partitions bad (could have less than 150 records per partition)

Yes, it's very wasteful in terms of planning and execution time.

  possibly need to separate partitioned data legally (some cases but not all)

What's the difference between a partition and a single table with a composite key? I've never seen a legal or regulatory code that goes down to the level of actually specifying database structure, other than maybe PCI, and not in this way.

Details please.

  is this the best approach, if not what are some other ideas

Use one table, a composite key, and some composite indexes. If useful/necessary, use partial indexes for sub-ranges.


Measuring the need of Capacity or scaling SQL server

As others have said, there is no magic formula that will tell you how your database server will respond to a sudden "increase the volume by double the current one" (volume of what exactly, by the way).

The two things that can help you to get a general idea (neither of which you have, apparently):

Historical (over many months) data that show you the correlation between your server load, the volume of data, and the application workload (concurrent connections, user sessions, registered users, daily orders, or whatever makes sense in you case). "[P]erformance data for last 15 days" is completely useless.
Performance testing environment that's configured meaningfully similar to your production. In this day and age spinning up a virtualized test environment on one of the cloud providers and running it for a week costs peanuts, compared to the price you will pay for a production outage caused by unexpected performance problems. Performance burn-down tests should be a part of your release process.

If you start preparing both of these tomorrow, in half a year you will be able to answer your own question with some degree of confidence.


Solution to assigning unique values to rows with finite collaboration distance

This problem is about following links between items. This puts it in the realm of graphs and graph processing. Specifically, the whole dataset forms a graph and we are looking for components of that graph. This can be illustrated by a plot of the sample data from the question.

The question says we can follow GroupKey or RecordKey to find other rows that share that value. So we can treat both as vertexes in a graph. The question goes on to explain how  GroupKeys 1–3 have the same SupergroupKey. This can be seen as the cluster on the left joined by thin lines. The picture also shows the two other components (SupergroupKey) formed by the original data.

SQL Server has some graph processing ability built into T-SQL. At this time it is quite meagre, however, and not helpful with this problem. SQL Server also has the ability to call out to R and Python, and the rich & robust suite of packages available to them. One such is igraph. It is written for "fast handling of large graphs, with millions of vertices and edges(link)."

Using R and igraph I was able to process one million rows in 2 minutes 22 seconds in local testing1. This is how it compares against the current best solution:

Record Keys     Paul White  R
------------    ----------  --------
Per question    15ms        ~220ms
100             80ms        ~270ms
1,000           250ms       430ms
10,000          1.4s        1.7s
100,000         14s         14s
1M              2m29        2m22s
1M              n/a         1m40    process only, no display

The first column is the number of distinct RecordKey values. The number of rows
in the table will be 8 x this number.

When processing 1M rows, 1m40s were used to  load & process the graph, and to update the table. 42s were required to populate an SSMS result table with the output.

Observation of Task Manager while 1M rows were processed suggest about 3GB of working memory were required. This was available on this system without paging.

I can confirm Ypercube's assessment of the recursive CTE approach. With a few hundred record keys it consumed 100% of CPU and all available RAM. Eventually tempdb grew to over 80GB and the SPID crashed.

I used Paul's table with the SupergroupKey column so there's a fair comparison between the solutions.

For some reason R objected to the accent on Poincaré. Changing it to a plain "e" allowed it to run. I didn't investigate since it's not germane to the problem at hand. I'm sure there's a solution.

Here is the code

-- This captures the output from R so the base table can be updated.
drop table if exists #Results;

create table #Results
(
    Component   int         not NULL,
    Vertex      varchar(12) not NULL primary key
);

truncate table #Results;    -- facilitates re-execution

declare @Start time = sysdatetimeoffset();  -- for a 'total elapsed' calculation.

insert #Results(Component, Vertex)
exec sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'select GroupKey, RecordKey from dbo.Example',
    @script = N'
library(igraph)
df.g <- graph.data.frame(d = InputDataSet, directed = FALSE)
cpts <- components(df.g, mode = c("weak"))
OutputDataSet <- data.frame(cpts$membership)
OutputDataSet$VertexName <- V(df.g)$name
';

-- Write SuperGroupKey to the base table, as other solutions do
update e
set
    SupergroupKey = r.Component
from dbo.Example as e
inner join #Results as r
    on r.Vertex = e.RecordKey;

-- Return all rows, as other solutions do
select
    e.SupergroupKey,
    e.GroupKey,
    e.RecordKey
from dbo.Example as e;

-- Calculate the elapsed
declare @End time = sysdatetimeoffset();
select Elapse_ms = DATEDIFF(MILLISECOND, @Start, @End);

This is what the R code does

@input_data_1 is how SQL Server transfers data from a table to R code and translates it to an R dataframe called InputDataSet.
library(igraph) imports the library into the R execution environment.
df.g <- graph.data.frame(d = InputDataSet, directed = FALSE) load the data into an igraph object. This is an undirected graph since we can follow links from group to record or record to group. InputDataSet is SQL Server's default name for the dataset sent to R.
cpts <- components(df.g, mode = c("weak")) process the graph to find discrete sub-graphs (components) and other measures.
OutputDataSet <- data.frame(cpts$membership) SQL Server expects a data frame back from R. Its default name is OutputDataSet. The components are stored in a vector called "membership". This statement translates the vector to a data frame.
OutputDataSet$VertexName <- V(df.g)$name V() is a vector of vertices in the graph - a list of GroupKeys and RecordKeys. This copies them into the ouput data frame, creating a new column called VertexName. This is the key used to match to the source table for updating SupergroupKey.

I'm not an R expert. Likely this could be optimised.

Test Data

The OP's data was used for validation. For scale tests I used the following script.

drop table if exists Records;
drop table if exists Groups;

create table Groups(GroupKey int NOT NULL primary key);
create table Records(RecordKey varchar(12) NOT NULL primary key);
go

set nocount on;

-- Set @RecordCount to the number of distinct RecordKey values desired.
-- The number of rows in dbo.Example will be 8 * @RecordCount.
declare @RecordCount    int             = 1000000;

-- @Multiplier was determined by experiment.
-- It gives the OP's "8 RecordKeys per GroupKey and 4 GroupKeys per RecordKey"
-- and allows for clashes of the chosen random values.
declare @Multiplier     numeric(4, 2)   = 2.7;

-- The number of groups required to reproduce the OP's distribution.
declare @GroupCount     int             = FLOOR(@RecordCount * @Multiplier);

-- This is a poor man's numbers table.
insert Groups(GroupKey)
select top(@GroupCount)
    ROW_NUMBER() over (order by (select NULL))
from sys.objects as a
cross join sys.objects as b
--cross join sys.objects as c  -- include if needed

declare @c int = 0
while @c < @RecordCount
begin
    -- Can't use a set-based method since RAND() gives the same value for all rows.
    -- There are better ways to do this, but it works well enough.
    -- RecordKeys will be 10 letters, a-z.
    insert Records(RecordKey)
    select
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND()));

    set @c += 1;
end

-- Process each RecordKey in alphabetical order.
-- For each choose 8 GroupKeys to pair with it.
declare @RecordKey varchar(12) = '';
declare @Groups table (GroupKey int not null);

truncate table dbo.Example;

select top(1) @RecordKey = RecordKey
from Records
where RecordKey > @RecordKey
order by RecordKey;

while @@ROWCOUNT > 0
begin
    print @Recordkey;

    delete @Groups;

    insert @Groups(GroupKey)
    select distinct C
    from
    (
        -- Hard-code * from OP's statistics
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
    ) as T(C);

    insert dbo.Example(GroupKey, RecordKey)
    select
        GroupKey, @RecordKey
    from @Groups;

    select top(1) @RecordKey = RecordKey
    from Records
    where RecordKey > @RecordKey
    order by RecordKey;
end

-- Rebuild the indexes to have a consistent environment
alter index iExample on dbo.Example rebuild partition = all
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

-- Check what we ended up with:
select COUNT(*) from dbo.Example;  -- Should be @RecordCount * 8
                                   -- Often a little less due to random clashes
select
    ByGroup = AVG(C)
from
(
    select CONVERT(float, COUNT(1) over(partition by GroupKey))
    from dbo.Example
) as T(C);

select
    ByRecord = AVG(C)
from
(
    select CONVERT(float, COUNT(1) over(partition by RecordKey))
    from dbo.Example
) as T(C);

I've just now realised I've gotten the ratios the wrong way around from the OP's definition. I don't believe this will affect the timings. Records & Groups are symmetrical to this process. To the algorithm they're all just nodes in a graph.

In testing the data invariably formed a single component. I believe this is due to the uniform distribution of the data. If instead of the static 1:8 ratio hard-coded into the generation routine I had allowed the ratio to vary there would more likely have been further components.

1 Machine spec: Microsoft SQL Server 2017 (RTM-CU12), Developer Edition (64-bit), Windows 10 Home. 16GB RAM, SSD, 4 core hyperthreaded i7, 2.8GHz nominal. The tests were the only items running at the time, other than normal system activity (about 4% CPU).


Use sp_attach_single_file_db to attach the MDF file without the LDF file since sp_attach_db didn't work. This will likely fail too though as the restore had already kicked in and overwrote those files (check the SQL Server error log to see if the restore started). If it does fail, you are stuck with restoring from the August 23rd backup.

Get the backups in order after you are done with this emergency.


Can SQL Server 2000 and SQL Server 2012 CoExist?

As stated on MSDN side-by-side installation of SQL 2012 with earlier version is supported. However take note of the instance name, if your SQL 2000 instance is a default instance then your SQL 2012 install will have to be a named instance.

I would take note also that some issues may occur with SQL 2000 tools as your SQL Server 2012 installation may attempt to upgrade any shared components it finds. Although I don't know what of SQL 2000 would still exist in SQL 2012.

Now, my opinion and not the opinion of StackExchange, or my dog...I would not suggest configuring a side-by-side install with a SQL 2000 instance. Not knowing the setup, I would highly doubt the server configuration of a SQL 2000 instance is very well suited to what Microsoft suggest for a SQL Server 2012 installation. Especially considering the fact that SQL Server 2012 is only supported on Window Server 2008 SP2 and higher. This all depends on how old the server is and such but just my opinion.


Cloned production SQL Server now Windows Authentication doesn't work

If the machine SID was not changed Active Directory will only allow the most recently booted machine to authenticate users.
Cloning a server and not giving it a new SID will definitely cause it to conflict with the current PROD server. I would advise doing this sort of thing in an environment separate from production.


postgres_fdw slower then copy + scp + copy (~12 times)

postgres_fdw is certainly not as optimized for bulk transfer as copy to, copy from, and scp are.  After all, bulk transfer is the main reason for the existence of those tools.

But that doesn't mean there is nothing you can do.  If you were running 9.6 on the local server, you could try increasing the fetch_size.


When are procedural queries absolutely necessary?

In my experiences, I've run into a few times when procedural/iterative approaches were warranted.

API only allows for single-row operation

If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.

Set based doesn't scale

If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.

I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.


SQL Server 2014 SP1 CU6 Issue with Multi-Valued Parameters (No Longer Expand)

In case anyone comes across this, I spoke to someone at Microsoft and it turned out this bug was fixed in the next cumulative update although unfortunately it wasn't out until October. These releases which resolve the issue are:

SQL Server 2014 SP1 CU9
SQL Server 2014 SP2 CU2

The release date was around 17/10/2016.

We have since upgraded to the latest version which is 2014 SP2 CU2 released last month and I can confirm the issue was resolved and no longer occurs in any browser.


Dump MySQL table to csv file (utf8mb4_unicode_ci)

The CSV "de facto standard" allows for fields to contain line breaks. So, what you get is the result of some of your data in your original table containing line breaks.

When that's the case, one record uses more than one row of text.

Some libraries or some programs can handle and interpret that adequately, but not all do. For instance, you can use MS Access, and it will work; but if you try MS Excel, line breaks within a field won't be properly handled. If you work with MySQL or PostgreSQL, they can import/export CSV with line breaks in fields.

Check, for instance, Comma-separated values at WikiPedia or RFC 4180

  Fields with embedded line breaks must be quoted (however, many CSV implementations do not support embedded line breaks).


Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I had the same error, I figured that the error was happening during shutdown of old cluster, it actually caused the cluster not to shut down fully.

What worked for me was stopping the old cluster manually and only then performing an upgrade.

sudo service postgresql stop
sudo pg_upgradecluster 9.3 main


Problem in Fetching Result set in Desired Format

I think you need a third table, orders,  which connects the items and persons.

Tables

CREATE TABLE IF NOT EXISTS `persons` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`person_id`)
);

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(4) NOT NULL AUTO_INCREMENT,
  `item_type` varchar(50) NOT NULL,
  PRIMARY KEY (`item_id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT,
  `item_id` int(4) NOT NULL,
  `person_id` int(4) NOT NULL,
  `status` varchar(50) NOT NULL,
  `ordered_date` date NOT NULL,
  PRIMARY KEY (`order_id`)
);

Data

INSERT INTO `persons` (`name`) VALUES
('Walter White'),
('John Locke');

INSERT INTO `items` (`item_type`) VALUES
('A'),
('B'),
('C');

INSERT INTO `orders` (`person_id`, `item_id`, `status`, `ordered_date`) VALUES
(1, 1, 'Y', '2012-5-2'),
(1, 2, 'N', '2012-5-5'),
(1, 3, 'Y', '2012-5-7'),
(1, 1, 'Y', '2012-5-17'),
(2, 1, 'Y', '2012-6-12'),
(2, 2, 'N', '2012-6-15');

Query

SELECT orders.person_id AS PID, GROUP_CONCAT(items.item_type) AS ITYPE, GROUP_CONCAT(status), GROUP_CONCAT(ordered_date)
FROM orders
INNER JOIN items ON
items.item_id = orders.item_id
GROUP BY person_id, orders.item_id

Result

| PID | ITYPE | GROUP_CONCAT(STATUS) | GROUP_CONCAT(ORDERED_DATE) |
-------------------------------------------------------------------
|   1 |   A,A |                  Y,Y |      2012-05-02,2012-05-17 |
|   1 |     B |                    N |                 2012-05-05 |
|   1 |     C |                    Y |                 2012-05-07 |
|   2 |     A |                    Y |                 2012-06-12 |
|   2 |     B |                    N |                 2012-06-15 |

SQL Fiddle


Is it OK to blindly add missing indexes?

Many reasons.

One of the biggest that I can think of is that the missing index DMVs don't take into account existing indexes.

Example:

You have a table with ColA, ColB, ColC.

Currently you have an index on ColA.  The missing index DMV will suggest you add an index on (ColA, ColB).  This may be correct, but the smart thing to do is to add ColB as a second key on the existing index.  Otherwise you have duplicate coverage and wasted space and overhead.

Similarly, if you have an index on ColB INCLUDE (ColA), it may suggest an index on ColB INCLUDE (ColC).  Again the smart thing to do is to add ColC to the include list in the existing index.

The suggested indexes have an extremely narrow view - they only look at a single query, or a single operation within a single query.  They don't take into account what already exists or  your other query patterns.

You still need a thinking human being to analyze the overall indexing strategy and make sure that you index structure is efficient and cohesive.

If there were no issues with just adding all the suggested indexes then there would be no need to even have them be suggested - they would be implemented automatically.


Delete unassigned shards in Elasticsearch

Probably the solution is to delete shards with replica status.
curl -XPUT "localhost:9200/myindex/_settings?pretty=1" -H 'Content-Type: application/json' -d' {"index" : { "number_of_replicas": 0 } }'


DataBase Performance Issues with Indexes

Fill factor will leave free space on your index pages. If you know that updates on index keys will need aditional space, then it may be ok to leave that 10% free. But if there will be no changes on index keys, it is better to have no free space on index (remember that all pages will generate logical reads on querys, so More pages = More logical reads = slower execution).

If you change fill factor, immediatly you will be asked to do rebuild


In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such as: RPC:Completed, SP:Completed, and SQL:BatchCompleted. You also need to select the "TextData" field as the values will be in there.

The difference between my answer and @Kin's answer on this Question is that @Kin's answer (unless I am mistaken, in which case I will remove this) focuses on getting either:

your own query plan (in which case it can have the runtime parameter info in it, but not for other Sessions/SPIDs), or
plans from the DMVs (in which case they should only have the compiled parameter values, which are not runtime values).

My answer focuses on getting the parameter values for other sessions that are currently running. When relying on the DMVs, there is no way to know if the runtime parameter value is the same as the compiled parameter value. And the context of this question is tracking down the runtime value of queries being submitted via other Sessions/SPIDs (and in SQL Server 2005, whereas Extended Events were introduced in SQL Server 2008).


Extract filename without extension from the absolute location

Edit:

Even though my first solution answered the question as asked, I saw @DavidBoho 's answer and he made several good points. He suggested that if the filename is my_file.tar.gz then the return value should be my_file.tar and also that my solution would fail in the event that the file had no extension at all. All of the code here is available on this fiddle.

Given the table and data as follows:

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

populate:

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');

My original solution:

SELECT LEFT(
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1),
                       POSITION('.' IN
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1)) - 1
) AS my_file
FROM with_filename;

gives the result:

my_file
file1
file2
file3
file4
file5
file6
file       -- << should be file7
file8      -- << should be file8.tar
my_prog

After reading @DavidBoho's post, he used the SPLIT_PART function to resolve the problems with files 7 & 8 - see the fiddle. I decided to look again at my own SQL and I came up with this (perhaps more traditional?):

SELECT
      REPLACE(SUBSTRING(file_name, (LENGTH(file_name) + 2) - POSITION('/' IN REVERSE(file_name))),
      RIGHT(file_name, POSITION('.' IN LEFT(REVERSE(file_name), POSITION('/' IN REVERSE(file_name)) - 1))),
      '') AS the_files
    FROM with_filename

Result:

the_files
file1
file2
file3
file4
file5
file6
file7
file8.tar
my_prog

which is also the correct answer!

While I was grasping for a solution, I became interested in regular expressions as a means of solving this problem. Even though I (we) was/were able to solve this using "traditional" SQL, it became clear to me that regexes are extremely powerful and even though SQL is now Turing complete, it could rapidly  become extremely convoluted for relatively simple string manipulation  problems, so I decided to investigate.

I found two regex solutions - in fairness, I can't claim to have done this myself, the solutions are as a result of a question I asked on StackOverflow. So, the regex solutions are as follows:

The best one is this one -

SELECT
    file_name,
    REGEXP_REPLACE(file_name, '^.*/([^/]*?)(\.[^/.]+)?$', '\1') AS filename
FROM with_filename;

There is a second one, but IMHO (and that of the original author) it's a bit of a hack - it involves two nested REGEXP_REPLACEs

SELECT
    file_name,
    REGEXP_REPLACE(REGEXP_REPLACE(file_name, '^.*/(.*)$', '\1'), '\.[^.]+$', '') AS filename
FROM with_filename

Finally, there may be a solution possible using the UNNEST and the STRING_TO_ARRAY functions together - I came up with this code:

SELECT fn,
    LEFT(fn, POSITION('.' IN fn) - 1) AS lef
FROM with_filename w,
  UNNEST(STRING_TO_ARRAY(w.file_name, '/')) AS fn
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY lef;

which gives the result:

fn  lef
file7   file       -- << should be file7
file1.pdf   file1
file2.py    file2
file3.pdf   file3
file4.c     file4
file5.java  file5
file6.class     file6
file8.tar.gz    file8  -- << should be file8.tar
my_prog.cpp     my_prog

I tried lots of different permutations with this, but couldn't get it to work. Would be grateful for any input! :-)

Another interesting function is REGEXP_SPLIT_TO_TABLE.

SELECT
  fn,
  COUNT(fn)
FROM
(
  SELECT REGEXP_SPLIT_TO_TABLE(w.file_name, '/') AS fn
  FROM with_filename w
) AS sq
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY fn

Result:

fn  count
file1.pdf   1
file2.py    1
file3.pdf   1
file4.c     1
file5.java  1
file6.class     1
file7   1
file8.tar.gz    1
my_prog.cpp     1

Again, this might be worth pursuing - didn't have time.

Example DML/DDL

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');


Amazon RDS non superuser create function in C

The Amazon RDS is marketed as

  [...] managing time-consuming database management tasks, freeing you
  up to focus on your applications and business.

From one point of view, this is true, it relieves you from a lot of maintenance.  On the other hand, it means less flexibility, in our case the lack of access to the filesystem.

The PostgreSQL documentation states:

  The CREATE FUNCTION for a user-defined C function must therefore
  specify two pieces of information for the function: the name of the
  loadable object file, and the C name [...]

As you cannot put files onto the machine, this simply won't work.

If you have to do your work on some AWS platform, you can choose an EC2 instance, which you can basically handle like any other machine you have root access to.


Sharding Mongodb while limiting the number of documents to be migrated

For your 7 collections on the primary shard:

Enter as administrator on the primary replica (if you shard your replica) and create your collection there by inserting 1 document or creating an index there. When you create a collection via the mongos - shard client - then the collection is started on a random shard; if you create it on the shard itself first, then you know it's on the one you want.  shard1$ mongo --port 27018 localhost/mydb --eval 'db.mycol.insert({firstdocument:'hello'})'
For the 25 collections:

Standard sharding indeed as you mention. Be patient with the distribution. The first shards will start distributing after a certain amount of data, can be 100k documents if the documents are small.
For the big collection:

Have a look at shard-tags. So, you have to tag your collection on shard1 with let's say : {status:'in_use'}; shard2 is tagged {status:'freeBeer'}. Then depending on the status in the document, the shard is chosen.
For the disk issue, I think you gonna have to write a script. Get a warning when 60% of a disk is used, and another warning at 80% to take action. Then you'll need to upgrade your disk or redistribute the chunks. Certainly the 2TB collection could give you space via the tag, to send more to shard2. Or, add a shard3 ... what is actually the purpose of sharding, to grow horizontally.
Choose shardKeys well, practice configuration and setup on a small server, practice moving chunks, adding tags, ....
Have a look at the AllChunkInfo script of Adam Comerford for you scripts and testing : https://github.com/comerford/mongodb-scripts


SQL DB Permissions so anyone can modify the DB

Add the Windows Group, Everyone, to the database db_owner role, if you're really certain you want anyone and everyone to be able to do anything they like to the database. Take a backup of the database that you will then distribute to the class. After you take the backup, remove the group from the db_owner role.

The only gotcha is the students need to have their instances configured with Windows Authentication enabled.

Windows Authentication stores the SID of the Windows principal in the database; in the case of the "Everyone" group, the SID is the same on all Windows machines.


How to set default schema for PostgreSQL debugger?

you should set the 'search_path' to have all schemas needed if you don't prefix them.
The default value is: "$user", public.

You should probably have: meta, data, public.

See: 18.11.1. Statement Behavior


selecting more fields causes catastrophic failure

So you are using Microsoft OLE DB Provider for ODBC (MSDASQL) which is presumably pointing to the ODBC DSN for your Pervasive data source. Since the error is an Access Violation (AV), I'm willing to bet that the AV is raised due to something going wrong in the Pervasive ODBC driver. Since you also mention that this query works from some other machine, can you double check the:

ODBC DSN configuration between the 'bad' machine and the 'good' machine?
The ODBC driver version for Pervasive - is it the same on both 'good' and 'bad' machines?


List of all database without a specific index

CREATE TABLE #dbs(db sysname);

DECLARE @cmd nvarchar(max) = N'INSERT #dbs SELECT ''?''
  WHERE EXISTS (SELECT 1 FROM ?.sys.tables WHERE name = N''TableA'')
  AND NOT EXISTS (SELECT 1 FROM ?.sys.indexes AS i
    INNER JOIN ?.sys.tables AS t
    ON i.[object_id] = t.[object_id]
    WHERE t.name = N''TableA''
      AND i.name = N''IX_Missing_Index'');';

EXEC master.dbo.sp_foreachdb @command = @cmd, @user_only = 1;

SELECT db FROM #dbs;

DROP TABLE #dbs;

You can get sp_foreachdb here (it is also being open sourced and added to Brent Ozar Unlimited's SQL Server First Responder Kit).


Way to check where the MySQL connection was initialized?

Who has the root password?  If there are multiple admins with root permissions, change them to non "root" user names, but with ALL PRIVILEGES.  Then you can distinguish them in the general log.

"Socket" implies the same machine; nothing provides what program.

I recommend you add comments to every query, perhaps with the filename and line number (__LINE__).


Multiple Agent Jobs Running same SSIS Package

You may be referring to the MaxConcurrentExecutables property.  It controls how many parallel threads may run during the execution of the package and it can be changed on the package properties.

The default is -1 which translates to 'how many cores ya got'+2.
I haven't ever heard of anything that would stop either the SQL Agent or the Integration Server to limit the number of concurrent executions of a single package.

Check out this article on MaxConcurrentExecutables.


Moving SSRS reports from one Server to other

Just migrate the objects with the "ssrs_migration.rss" script.
Much easier than database keys and restores

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password"

I've used this many times with good results

Script sources ("ssrs_migration.rss")

https://docs.microsoft.com/en-us/sql/reporting-services/tools/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers
https://msdn.microsoft.com/en-us/library/dn531017(v=sql.120).aspx


sp_estimate_data_compression_savings for column store compressions

There is no equivalent proc so what I have been doing recently is creating a copy of the table in tempdb using SELECT...INTO, eg

SELECT TOP 10 PERCENT *
INTO tempdb.dbo.yourTable
FROM dbo.yourTable

then apply the clustered columnstore.  If your table is too large to fit in your tempdb (as I might expect for a datawarehouse table), transfer a percentage and infer from that.  You could also create the empty table in tempdb, add the columnstore then populate, although this will probably take longer - compression has quite an effect on INSERT time.  The SELECT...INTO technique has the added benefit of removing the indexes for you, can be minimally logged and can even run in parallel on SQL 2014.

Example:
I have a 1TB table, I copy 100GB ( 10% ) into a table in tempdb.  I apply the clustered columnstore index ( normal compression ) and check the table size.  It has packed down to 10GB, or 10x compression.  I rebuild the clustered columnstore with COLUMNSTORE_ARCHIVE mode, the table packs down to 5GB or 20x compression.  I can infer that my 1TB table will pack down to approximately 100GB or 50GB with archival mode.  Allow some tolerance as it's an estimate.

The documentation suggests up to 7x compression but I have been seeing larger factors in my testing, particularly with the COLUMNSTORE_ARCHIVE mode, clearly with some CPU overhead when reading back.  YMMV.


Static Data Masking does not appear in SSMS 18 preview 6

It was removed from the final version of SSMS 18. Interestingly, no explanation deemed necessary to be given as why it was taken out or will this feature be coming soon.

From SQL Server Management Studio (SSMS) 18.0 released for general availability by Brad Groux:

  Removed static data masking (preview) feature – While this feature was available in previous previews, it did not make it into the GA version of SSMS, unfortunately.


How to create some GIN index concurrently in Postgresql

To follow up on my comment, PostgreSQL absolutely can build gin indexes on different tables in parallel to each other.  If you don't see a benefit, it is because you don't have enough of some resource.  Probably random disk IO capacity, as building large gin indexes can be quite intensive on this.

If using a different table space didn't speed it up, then perhaps the IO bottleneck is in the kernel or FS code, such that it fails to take full advantage of the underlying hardware.  One way to more-definitively determine the bottleneck would be to run two separate PostgreSQL servers on the same machine (listening to different ports) and then put one table in each database server, and try to create the indexes simultaneously on that set up.


SQL Server slow response when accessed from a remote client

By default SQL Server instances don't listen for TCP/IP connections, so verify that this is enabled:

https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx
https://stackoverflow.com/questions/2388042/connect-to-sql-server-2008-with-tcp-ip

Also you need to make sure that firewalls between your client and the server are not blocking the connection. For the default instance under standard configuration TCP port 1433 needs to be open. If you have multiple instances then things get more complicated. See https://msdn.microsoft.com/en-us/library/cc646023.aspx for more details.

These are the most common cuases of complete failure to connect to a remote server. If all this is configured correctly then, as Tom V suggests, we need more information in order to be able to help.

Warning: It is strongly recommended that you do not open your SQL server ports to the Internet at large. If you allow connections in from the outside network then make sure you limit it to specific source IP addresses. If you can not do this because the source address(es) are variable (for example if you need to connect from a laptop that moves between networks a lot) then consider instead setting up a VPN to allow access through the firewall that way instead of directly. Even for a single fixed source address, I would still tend to prefer the VPN option.


Best practice for exposing record IDs to API users

Rows are created with a Surrogate, numeric key that keeps the database nice and tidy but, for each record, is there a Natural key that would be meaningful to the Users?

For example, a User might log into your Application using an email address.

Each User record would have both a Surrogate, numeric, UserId that the User never sees plus their email address (and other stuff).  When joining tables within the database, you use the internal, Surrogate key.  For anything coming from the Outside World, you use the Natural keys that make sense to Them Out There:

create table Users
( id            int            auto_increment not null
, email_address varchar( 100 ) not null
, primary key ( id )
, key ( email_address )
);

select *
from Users ;
+----+----------------------------+
| id | email_address              |
+----+----------------------------+
|  1 | fred.flintstone@rubble.com |
+----+----------------------------+

It might be that one field is not enough to do this.  The Natural, Primary Key of a record might be a handful of fields - if that what it takes for Users to get to that record, then that's what they have to enter.

As you quite rightly suspect, you should never leak those internal identifiers outside of the database.  Always take Real World value[s] and map them back into their internal equivalents.  If nothing else, it gives you an degree of input data cleansing en route.


Local SSMS connected to VPN SQL Instance - update fails when VPN disconnects

As far as I know, if the connection between the computer issuing the query batch and the server executing the batch is broken, the server will terminate the batch.

As the comments have noted, your best solutions are executing the command from a computer that won't be likely to lose its connection; executing the commands from the server itself is, unsurprisingly, about as safe as you can get.

As sp_BlitzErik recommended, executing your batch from a SQL Server job is good, when the query can safely be run as a fully unattended process works, and shouldn't consume too much of the server's resources.

If a process needs to be monitored above and beyond simple success/failure on completion, then the most common solution is probably a remote desktop connection. Depending on our organization's security restrictions, you could hopefully be allowed to remote into another computer on the same LAN as the server (if not the server itself, if it wouldn't constitute too much of a resource drain).

If your VPN connection breaks, the session on the remote server keeps running - you just lose your visibility into it. When you re-establish your VPN connection, you can reconnect to the remote server, and see what progress has happened while you were disconnected.

As noted, you want to be sure that your remote connection is not consuming resources needed by your server, and that all necessary security concerns are dealt with. You do not necessarily need to be on the server itself; as I said, any computer on the same network as the server will do, as long as it has the software you need to issue your query.

Over the course of the past few years, I have worked on a few projects that required long weekends hours every other weekend, or two out of three even, for months. Remote desktop connections from home made life much easier (and was much more appropriate than basically living in my cubicle :-) ).


Can I create a random number in an SSIS derived column

You don't actually have to alter your source data, but you can use a customised SQL command in your OLE DB Datasource.  Basically change the Data access mode from Table or view to SQL command or even SQL command from variable and create your statement to include your PIN code, something like this:

SELECT *, 1 + ABS(CHECKSUM(NEWID())) % 9999 PIN
FROM dbo.ssisTest

Test results:

No need for Derived Column, Script Task etc.  Make sense?

Another approach would be to assign some kind of arbitrary row id to your source table and a similar id to your unique account ids table and join on that, something like this:

SELECT *
FROM
    (
    SELECT
        ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rowId,
        *
    FROM dbo.ssisTest
    ) x
    INNER JOIN dbo.uniqueAccounts a ON x.rowId = a.rowId

Similar to the above approach, you can hold this SQL in your OLE DB Source without affecting your actual source data.  In order for this to work you must a) persist your unique accounts and give them an arbitrary row id and b) there must be more unique accounts than there are original records, eg generate a few million up front.


threshold replica lag for secondaryPrefered mongod

Yes, the data you read from SECONDARY might not be up-to-date. The obvious solution if you are no fine with it: read from the PRIMARY.
However, one hour sounds a lot. Did you configure a Delayed Member or did you configure storage.oplogMinRetentionHours?


Does it make sense to create index with all columns included

If

your query uses all those fields,
the sort order needs to be different than that of the clustered index, AND
you can't change the clustered index,

an additional (covering) index with a different column order may work for you. As with anything index-related, more (and wider) indexes means slower write/update performance. This is particularly true if you're effectively doubling the size of the table.

If you just index OtherTableId without including the other columns, you'll get a smaller index but your query may end up either just using the clustered index with expensive Scan or Sort operators, or using Key Lookups from the non-clustered index, which may be great if you're querying just a few rows at a time but disastrous if you're scanning the entire table.

This is general advice. For specifics, edit your question to include query, table definitions and a query plan.


Mysql: Possible to increase the priority of your query on a database?

As has been pointed out, if you are using MyISAM then SELECT HIGH_PRIORITY might give you some advantage... but if you need performance under concurrency, you shouldn't be using MyISAM since the only level of locking available is table-level locking, and table locking occurs implicitly with a significant proportion of queries (hence the rationale for the old high/low priority stuff).

Or you could obtain an explicit lock on the tables involved while you run your queries, but that's probably not going to be a solution.

The question raised by your suggestion that you are "competing with other users" is whether you know for a fact that this is the major cause of your performance issues or, alternatively, whether you are dealing with a query that isn't properly optimized.

For all practical purposes, MySQL does not have any mechanisms for prioritization like this.  Every query is executed with comparable priority, subject to the limits of disk I/O, CPU clock speed and number of cores, available memory (and how it's allocated)... which means if you're not getting a level of performance consistent with the quality of your code, your server needs more of one or more kinds of resources, or you need to scale out with a read replica, or migrate some of the databases to a different machine... or, of course, if the machine isn't dedicated exclusively to running MySQL, then that's potentially problematic as well.

See also: Is there a way to achieve Workload Management in MySQL?


SQL Server many active create procedure processes

This is not running the create procedure statements. The text you are receiving is the body of the procedure that has been called, not the actual call (I assume you are catching SP:Completed and not SP:StmtCompleted). If you are looking at for example sys.dm_exec_sql_text() you should also get start_offset and end_offset values that will allow you to pinpoint the actual text that is being executed as part of the call, and there are also ways to see the actual call but it depends largely on how you are monitoring now and how you are sending your procedure calls to SQL Server.


Cannot Connect Using SSMS to Local SQL Server 2008 R2

As I posted in the comments, a couple of possibilities exist for that error, mainly lack of memory for additional TCP connections (generally in conjunction with 32-bit OSes), or lack of TCP ports. Both issues (and possible solutions) are outlined here on the SQL Protocols blog:

Understanding the error "An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full."


Do I need to create index for the column when using it in update statement?

Indexes are meant to improve performance of predicates used in WHERE, JOIN, and HAVING clauses for any type of query. So yes having an index on userId theoretically can improve performance of your UPDATE example query.
After you create the index you don't need to specify it in your query, PostgreSQL will automatically use it if its engine thinks it'll help the query performance.


Access oracle database from control file

A control file is a metadata file and lists some system configuration settings and all the datafiles and their paths.  A typical file is quite small and does not contain any user data.

You need a datapump export or, even better, as suggested by Philᵀᴹ, an RMAN backup.  The RMAN backup is preferable as you will not have to create users and tablespaces first which you would with an export.


PostgreSQL: How does COPY FROM behave inside a transaction?

Does COPY FROM create a subtransaction?

No. It creates a single transaction if not run in a transaction; otherwise it will use the current transaction.

  Will a failure roll back the parent transaction, or leave it alone?

It will roll back the parent transaction.

If you want a subtransaction, start one explicitly before the COPY FROM.

There is no way to cause COPY FROM to ignore errors and continue. Any error causes the whole COPY to fail.


What affects the speed of a count(*) besides the number of records

Bear in mind that the query plan shows an estimate of cost which is not the same as time.  The estimated cost values are a unitless aggregation of CPU, memory, and IO, not how long each operation takes to execute.  Also bear in mind that the cost values are estimates even in an "actual" execution plan.

The biggest bottleneck for this and most other SQL Server operations is disk IO.  Your example STATISTICS looks to be from a second run since there are no physical reads, though, so you see a higher CPU which is used for the aggregation/sorting.

You may be able to speed this particular query up with a filtered index, if your WHERE predicate is consistent (i.e. always Bar = 1 AND Baz = 2).

Otherwise I'm not aware of a way to speed it up beyond something more drastic like an indexed view.


Postgresql: delete all comments from database

Erwin Brandstetter gave a very good answer on how to do this on this question Removing COMMENT ON from all objects in PostgreSQL on Stack Overflow.

It basically consists of removing the comments directly from the system catalogues like so (disclaimer - the following code is copied directly from his answer):

DELETE FROM pg_description WHERE description = 'something special';

Or you could delete the comments based on any other criteria you may choose.


I can't give you a definitive answer, but I would suspect slow authentication, both client and server being Windows-based: because of the client-server version mismatch there may be some extra authentication back-and-forth between them, and at some point either (or both) will traverse the Active Directory on the domain and possibly all trusted domains for user ID and group lookup.

Try setting the authentication method on the client to match that on the database server (SERVER would be the default), this might speed things up. For this you'll need to uncatalog and re-catalog the database in question.

Ask your sysadmins to check AD status if (when) you experience this issue again, may be there's a spike in load or some other issue.


I achieved this by doing the following:

SELECT Id,Name,Num,TypeId,
[1] Line1,[2] Line2,[3] Line3,[4] Line4,[5] Line5,[6] Line6,[7] Line7,[8] Line8,[9] Line9,[10] Line10
,Location
from
(
SELECT ast.Id,ast.Name,ast.Num,ast.TypeId,
,loc.Location
FROM MyTable ast
LEFT JOIN MyLine astline ON ast.Id = astline.Id
LEFT JOIN aim.Location loc ON ast.LocationId = loc.LocationId
where ast.ProjectId = @ProjectId
) as maintab1
pivot
( MIN(maintab1.Line)
   for maintab1.LineIndex in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as pivotT1


Your data follows a strict pattern:
If there is more than one entry like #TS%, then the first entry will be the one with #TS, the second will be the one with #TS - W. Obviously, if you sort descending, the order will be reversed.

Therefore the solution seems obvious: group by #TS, get those lines where row_number >1, when sorted descending.

SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
FROM         TransmittalSheet ts

To get only the multiples, you'd put that into a CTE:

With T1 as (SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
    FROM         TransmittalSheet ts)
select * from T1 where T1.RN>1

You could then simply inner join that CTE to your original table on TransmittalID to remove all duplicates.

If the TransmittalNumber has a variable length, you'd also have to use patindex(), in order to calculate your parameter for the left() function.


Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors

Welcome!

More RAM may make a big difference, but it's worth looking into the configuration and some specific queries to see where they're being constrained.

Here's a couple of next steps:

Compare your current settings to those recommended by PGTune and try out the recommendations if there are big differences
Use explain (ideally explain (analyze, buffers)) locally and on your remote server to see if it's memory constrained, or something else

Feel free to edit your question with more information from what you find! If you do, comment on this anwer to notify me :)


Ensure data is physically deleted from a data file

Where I work, we had this exact issue. I wrote code that read metadata tables and redacted information for people or companies on request. I also logged the id value for every person that we redact. Hence if we do bring back an old database we have the option of taking that metadata and redacting it in any backup that we restore. It would not be practical to bring back every backup, restore, redact people who want to be redacted, do a new backup and send the backup back offsite. We can think about the retention that were need for offsite backups. Ideally in the future we will separate the PII of people from the financial data that we need to keep longer. thus it would be easier to redact some data, but keep the data that we really need. You need more than a single answer. You need to build a framework on how to redact data and still know which data was redacted in case it needs to be redacted again.


Unable to disable SQL statement logging in PostgreSQL 9.1

There are a number of relevant settings missing in your list. Check the chapter "When to log" in the manual, in particular these settings:

log_min_messages (enum)
log_min_error_statement (enum)
log_min_duration_statement (integer) -- as @Jack already hinted in his comment.


What all are the recoverable points of failure in an Oracle database?

Not really sure what you are asking for here.. If your database is backed up correctly, then there is no unrecoverable points of failure. However most "failures" will result in your session being terminated or at the least being thrown an error. In most cases you wont want to deal with specific errors, but rather simply rollback the transaction ( if possible ) and log the issue for inspection.

If you are testing a 3rd party application, and assuming your are on a standalone database, I'd just test it for session termination to see if it automatically reconnects itself, correctly cleans up the work it was doing, etc. You could also lockup its session I guess and see how it handles timeouts.

Sorry for the vague response, but the question is quite vague.


If you are attempting you return all rows from the four tables where the City is the same, you should be able to use a UNION ALL to get the results:

select name, 'Company' as Source
from company
where city = 'London'
union all
select name, 'programmer' as Source
from programmer
where city = 'London'
union all
select name, 'manager' as Source
from manager
where city = 'London'
union all
select name, 'ceo' as Source
from ceo
where city = 'London'

I included a field that identifies what table the record is coming from.  This is not necessary for the query and can be removed.

Just as a side note, the error you were getting in your query is because you created an alias for the Company table but when selecting the Company.name in the SELECT list you are not using the alias.  You would need to use:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
WHERE Cm.City = 'London'

If you want to JOIN the tables then you could use something similar to this:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
INNER JOIN Manager M
   ON Cm.City = M.City
INNER JOIN CEO C
   ON Cm.City = C.City
WHERE Cm.City = 'London'