Which TLogs are causing large block change in SAN snap?

Good news! You don't have to query Simpana, NetApp, or PowerShell to figure out where the log file sizes have been changing.

Just query the msdb.dbo.backupset table - it tracks database name, backup date/time, and backup size. You can trend this over time. I like charting this with Excel or SSRS to show volume trends, and then I can drill down into detail to see exactly which backup is causing it.

The below query displays full backup sizes in a pivot - the 0 column is current month, -1 is last month, -2 is two months ago, etc. Note that I'm filtering for file_type = D - that's full backups. You can switch out different filters for differential or log backups if you like.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @startDate DATETIME;
    SET @startDate = GETDATE();
    SELECT PVT.DatabaseName ,
        PVT.[0] ,
        PVT.[-1] ,
        PVT.[-2] ,
        PVT.[-3] ,
        PVT.[-4] ,
        PVT.[-5] ,
        PVT.[-6] ,
        PVT.[-7] ,
        PVT.[-8] ,
        PVT.[-9] ,
        PVT.[-10] ,
        PVT.[-11] ,
        PVT.[-12]
    FROM ( SELECT BS.database_name AS DatabaseName ,
                DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo ,
                CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
            FROM msdb.dbo.backupset AS BS
                INNER JOIN msdb.dbo.backupfile AS BF
                    ON BS.backup_set_id = BF.backup_set_id
            WHERE NOT BS.database_name IN ( 'master', 'msdb', 'model', 'tempdb' )
                AND BF.[file_type] = 'D'
                AND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate)
                                         AND     @startDate
            GROUP BY BS.database_name ,
                DATEDIFF(mm, @startDate, BS.backup_start_date)
         ) AS BCKSTAT PIVOT ( SUM(BCKSTAT.AvgSizeMB) FOR BCKSTAT.MonthsAgo IN ( [0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] ) ) AS PVT
    ORDER BY PVT.DatabaseName;
GO


Block certain queries from being logged in slow query log

long_query_time is a session variable and could be set to a large value in the mysqld_node code.


I would suggest two fundamental tables for this purpose, if you're creating this all from scratch. This assumes that this is being created in PostgreSQL.

An employee table containing their name and whatever other details are important for your application, with a numeric primary key. The primary key is important because you may have more than one employee with the same name, and you may have employees that change names in the future. Using a key allows the information about their time to remain consistent.

An activity log table containing:

a primary key column (type SERIAL is likely fine for this)
an employee ID column (this will reference the column in the other table)
a project column (you can make this free-text, or create another table with a primary key and reference it here), this column should be nullable to reflect activity log entries that aren't related to a project (eg tea break)
a tstzrange column to represent the period for the log entry

Now this schema doesn't contain enough information to identify a team leader. You could add this information to another table, referencing your employee (and possibly project) table.

Here's a quick example schema you can play with:

 CREATE TABLE employees (
     employee_id SERIAL NOT NULL,
     employee_name text NOT NULL,
     PRIMARY KEY (employee_id)
 );

 CREATE TABLE projects (
     project_id SERIAL NOT NULL,
     project_name text NOT NULL,
     PRIMARY KEY (project_id)
 );

 CREATE TABLE project_memberships (
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer NOT NULL REFERENCES projects,
     supervisor_employee_id integer REFERENCES employees,
     PRIMARY KEY (employee_id, project_id)
 );

 CREATE TABLE activity_logs (
     activity_log_id SERIAL NOT NULL,
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer,
     activity_time tstzrange NOT NULL,
     activity_notes text,
     PRIMARY KEY (activity_log_id)
 );
 ALTER TABLE ONLY activity_logs
     ADD CONSTRAINT activity_logs_project_member_fkey FOREIGN KEY (employee_id, project_id) REFERENCES project_memberships(employee_id, project_id);


Unless you plan on regularly updating portions of your table, I'm curious why you would set your FILLFACTOR on the table to anything other than the default of 100%?

Regarding your question about different fillfactor settings for the same table, I don't think that is possible. If you were planning to set the fillfactor per index, then that might have some benefit -- but if you are only inserting then set the fillfactor to 100% (or close to it).


How to make cdc skip the point that it is stuck at?

You can try running the cdc cleanup job.
It should have a name similar to this.

[cdc.Replication_cleanup]


Postgresql 11 SSPI authentication failing

If you are authenticated as "SrvSupportPsgr@domainname", then that is what needs to be in the 2nd column of pg_ident.conf; including the "@domainname" part.  (Or it could be a regexp which matches to that)

Also, pg_ident.conf is case sensitive.  In the third column, "SrvSupportPsgr" is not the same thing as "srvsupportpsgr".


How do we reference to a collection in MongoDB?

I wanted to know, how can I add a reference to another collection?

Relationships in MongoDB represent how various documents are logically related to each other. Relationships can be modeled via Embedded and Referenced approaches. Such relationships can be either 1:1, 1:N, N:1 or N:N.

It's possible through Modeling Referenced Relationships and DBRefs.

Here i am going to discuss about Modeling Referenced Relationships.

Let us consider the case of storing addresses for customers. So, one customer can have multiple addresses making this a 1:N relationship.

Following is the sample document structure of customer document

{
"_id":ObjectId("52ffc33cd85242f436000001"),
   "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address": "100A, Doha,Qatar"
})

Following is the sample document structure of address document −

{
"_id":ObjectId("52ffc4a5d85242602e000000"),
   "address": "100A, Doha,Qatar",
   "office address": " 200B, Doha Qatar",
   "mailing address": "abc@gmail.com"
}

Let's first i am going to write down here Modeling Embedded Relationships, for your better understanding.Even as @JJussi has already told that we can write down all documents information in one collection through modeling Embedded Relationship.

  Note: The drawback is that if the embedded document keeps on growing too much in size, it can impact the read/write performance.

Modeling Embedded Relationships

Data in MongoDB has a flexible schema. Collections do not enforce document structure. Decisions that affect how you model data can affect application performance and database capacity.

This document describes a data model that uses embedded documents to describe relationships between connected data.

If your application frequently retrieves the address data with the name information, then your application needs to issue multiple queries to resolve the references. A more optimal schema would be to embed the address data entities in the customer data, as in the following document:

{
"_id":ObjectId("52ffc33cd85242f436000001"),
    "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address": [
{
"address": "100A, Doha,Qatar",
   "office address": " 200B, Doha Qatar",
   "mailing address": "abc@gmail.com"
}]}

Then Modeling Referenced Relationships

This is the approach of designing normalized relationship. In this approach, both the customer and address documents will be maintained separately but the customer document will contain a field that will reference the address document's id field.

{
   "_id":ObjectId("52ffc33cd85242f436000001"),
   "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address_ids": [
      ObjectId("52ffc4a5d85242602e000000"),
      ObjectId("52ffc4a5d85242602e000001")
   ]
}

As shown above, the customer document contains the array field address_ids which contains ObjectIds of corresponding addresses. Using these ObjectIds, we can query the address documents and get address details from there. With this approach, we will need two queries: first to fetch the address_ids fields from customer document and second to fetch these addresses from address collection.

>var result = db.customer.findOne({"name":"Gokulnath Kumar"},{"address_ids":1})
>var addresses = db.address.find({"_id":{"$in":result["address_ids"]}})

For further your ref Here, Here and Here


What is the best way to store an email address in PostgreSQL?

Custom DOMAINs

I don't think using citext (case-insensitive) is enough[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a domain for instance over the citext type, or over text.

Using HTML5 type=email spec

Currently the most correct answer to the question what is an e-mail address is specified in RFC5322. That spec is insanely complex[2], so much so that everything breaks it. HTML5 contains a different spec for email,

  This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (allowing comments, whitespace characters, and quoted strings in manners unfamiliar to most users) to be of practical use here. [...] The following JavaScript- and Perl-compatible regular expression is an implementation of the above definition.

/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/

This is likely what you want, and if it's good enough for HTML5, it's probably good enough for you. We can make use of that directly in PostgreSQL. I also use citext here (which technically means you can simply the regex a bit visually by removing either the upper-case or lower-case).

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Now you can do...

SELECT 'asdf@foobar.com'::email;

But not

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@f@foobar.com'::email;

Because both of those return

ERROR:  value for domain email violates check constraint "email_check"

Because this is based on citext too

SELECT 'asdf@foobar.com'::email = 'ASdf@fooBAR.com';

returns true by default.

Using plperlu/Email::Valid

As an important note, there is a more correct method of doing this that is far more complex using plperlu. If you need this level of correctness you do not want citext. Email::Valid can even check if the domain has an MX record (example in docs of Email::Valid)! First, add plperlu (requires superuser).

CREATE EXTENSION plperlu;

Then create the function, notice we mark at as an IMMUTABLE:

CREATE FUNCTION valid_email(text)
  RETURNS boolean
  LANGUAGE plperlu
  IMMUTABLE LEAKPROOF STRICT AS
$$
  use Email::Valid;
  my $email = shift;
  Email::Valid->address($email) or die "Invalid email address: $email\n";
  return 'true';
$$;

Then create the domain,

CREATE DOMAIN validemail AS text NOT NULL
  CONSTRAINT validemail_check CHECK (valid_email(VALUE));

Footnotes

Using citext is technically wrong. SMTP defines local-part as being case sensitive. But, again, this is a case of the spec being stupid. It contains its own identity crises. The spec says local-part (the part before the @) "MAY be case-sensitive" ... "MUST BE treated as case sensitive" ... and yet "exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged."
The spec for an email address is so complex, it's not even self-contained. Complex is truly an understatement, those making the spec don't even understand it.. From the docs on regular-expression.info

  Neither of these regexes enforce length limits on the overall email address or the local part or the domain names. RFC 5322 does not specify any length limitations. Those stem from limitations in other protocols like the SMTP protocol for actually sending email. RFC 1035 does state that domains must be 63 characters or less, but does not include that in its syntax specification. The reason is that a true regular language cannot enforce a length limit and disallow consecutive hyphens at the same time.


Does restarting Windows restart the SQL Server?

Assuming you have a single MS SQL Server instance on a single Windows machine the answer is a very definite resounding yes, restarting Windows will mean restarting all the services running on it including SQL Server.
If you had some sort of clustering with automatic fail-over etc. configured and your apps behave with it properly (i.e. they don't somehow bypass the mechanism and connect to single host), then like other multi-node solutions (load balanced web servers and such) you should be able to drain-stop the node and restart without it affecting anything else (other than a performance drop if you have an active-active load balances setup instead of mirroring with fail-over), then do the same on other hosts in the cluster to make sure everything is updated. Even with a fail-over or load balanced solution, when the updated node comes back into service there may be some impact as there will be nothing in the buffer pool so initial queries will be slower than usual until the normal working set is loaded.


How does MySQL Workbench decide what number to put in parentheses after a datatype?

In this case, the difference is likely due to the int and tinyint being signed (default) or unsigned in their definitions. Note that the numbers in the parentheses are largely irrelevant for most applications (they define a display width).


does SELECT with implicit transaction prevent VersionStore cleanup?

I found the answer in this video:
Monitoring Snapshot Isolation with Perfmon in SQL Server
And yes SELECT statements with open transaction can block version store cleanup.


Bulk DELETEs in publisher breaking SQL Server Replication in subscriber

It sounds like the root cause of your error is that someone/thing is deleting the rows on the subscriber prior to the replication trying to delete the same rows. I would try and address this by tracking down who/what is removing the rows and then restricting their permissions.
Doing a bingle for the error number brings up this automation with Powershell to try and "repair" these errors automatically - https://www.mssqltips.com/sqlservertip/3351/auto-fix-sql-server-transactional-replication-error-20598-for-updates/
but better to address the root cause IMO


Is there any way to detect and get information on a push-subscription on subscriber's side?

You have 4 tables For transactional replication (push subscription) at the subscriber's side:

MSreplication_objects

  contains one row for each object that is associated with replication
  in the Subscriber database

MSreplication_subscriptions

  contains one row of replication information for each Distribution
  Agent servicing the local Subscriber database

MSsubscription_agents

  is used by Distribution Agent and triggers of updateable subscriptions
  to track subscription properties

MSsnapshotdeliveryprogress

  is used to track files that have been successfully delivered to the
  Subscriber when a snapshot is being applied

you can use this columns to monitor the status for your replication at the subscriber's side

SELECT login_time,
       last_sync_status,
       last_sync_summary,
       last_sync_time,
       spid
FROM dbo.MSsubscription_agents


The canonical answer is to create sample data using both schemas, and profile the queries you'll be doing to see which schema runs faster :-)

But I'm guessing you'll actually see equivalent performance. Mongo is pretty fast at querying subdocuments as long as you index the subfields (which you're doing in the first option).

So the real way to decide between the two approaches is to really understand your data and your query / access patterns. For example, one advantage of creating a join table is that you could move all the additional data (like 'UserName' and 'cards') into the join table. That shrinks your game records. This might be useful if you'll be having lots of game servers creating tons of game data in realtime, and where different users are hosted on different servers, which means updating user data as the game happens can lead to locking and contention issues if all of the user data is stored in one big game record. In that scenario, a join table makes better sense, because each user-game combination is a separate record which can be updated separately (and can even be sharded onto separate servers if your game becomes really popular :-) without needing to lock other records.

On the other hand, if you don't foresee needing to do a ton of writes and updates to user-game data in realtime, then keeping all of that data as subdocuments can make queries easier to write.

At the end of the day, it really does depend on what your access patterns will be like. The raw read performance will likely be equivalent, but that's only one factor in determining what's optimal in your scenario.


PostgreSQL upsert implications on read performance after bulk load

That's the whole point of why INSERT ... ON CONFLICT exists.

I don't think that that is the case.  I think a pretty big point was a convenience so people don't have to implement a catch and retry loop for unique constraint violations upon single-row insertions.  Not rolling back entire bulk inserts due to a few violations is also a pretty good improvement, but I don't think it is the whole point.  And if you are going to have the table offline to other uses anyway, think I think it is actually none of the point of ON CONFLICT.

ON CONFLICT pays a high price to handle concurrency at a fine grained level. If you can guarantee that only this procedure is inserting new records, then one query to do the insertion of new keys and a second query to do the update of olds ones would almost certainly be more performant than using ON CONFLICT.  If your point 3 means that the big table had all constraints and indexes dropped other than the one needed to support the ON CONFLICT, then it seems likely you are in a maintenance window, and so you can probably make the guarantee about no concurrent inserts.

  In terms of index bloat, an upsert would be worse than simple bulk copying inserts + update to a table without constraints? Is upsert bad for index maintenance?

I would not expect this to be the case in general, if you are keeping the unique constraint either way.  But it would depend on things like whether the unique key values of rows being inserted are mostly occurring in order, or random.  Also, bloat would depend on whether rows taking the UPDATE path have room for the updated row in the same block the old version originally resided in (so the table fill-factor), but this would also be true if the UPDATE were a separate statement.  You would have to mock up something similar to your actual conditions and test it to get clear answers.

  Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maintenance since the target table doesn't need to have an index to do the upserting?

If you take the table offline for normal use and drop all constraints and indexes, then it should be both more performant and lead to less bloated indexes (although perhaps only slightly less bloated) once you rebuild them.  That is the case whether you compare it to either using ON CONFLICT online, or compare it to dropping all-but-one of the constraints and running ON CONFLICT offline.


Need advise on Indexaphobia: High value missing index with High Impact. When index is already there

To clean up and simplify your question a little:

The index you already have is on [LOGID], [RECEIVE_TIME]
SQL Server is asking for an index on APT_ID, and it wants you to include LOGID, RECEIVE_TIME

That is indeed a different index than you already have.
Let's take the old example of the phone book. The white pages of the phone book were on LAST_NAME, FIRST_NAME, MIDDLE_NAME. That's great if you run a query like this:
SELECT *
FROM PHONE_BOOK
WHERE LAST_NAME = 'OZAR'

But if you don't know someone's last name, and you ask for this:
SELECT *
FROM PHONE_BOOK
WHERE FIRST_NAME = 'BRENT'

Then you're going to be scanning the entire phone book looking for me. The first column in the index is incredibly important. That's why SQL Server is asking for an index on APT_ID, plus other columns included.
So you might ask, "Why isn't an existing index on APT_ID alone enough?" The problem is that in your case, APT_ID isn't selective enough, or people are asking for ranges of APT_IDs. SQL Server's having to do tons of key lookups, and the missing index recommendations are trying to remove those.


Update main and second table with a before update trigger for message logging

First of all, if you want to add a new entry log for each insert/update operation, you should change userMessageLog's primary key. Otherwise you'll receive an error message due to duplicity.
Then, you can deal with trigger recursion by using: pg_trigger_depth(). You can get some examples in this questions:

Prevent recursive trigger in PostgreSQL.
Or at this fiddle I've set up using your table definitions.

Having said that, you can find another questions with some discussions about the use of pg_trigger_depth(), with points for or against of using it. Have a look at the answers of Evan Carrol and Erwin Brandstetter in this question:

Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

Personally, I'll use a function instead of a trigger.


I assume you are working on SQL Server, given question tags and T-SQL notation.

Nesting transactions in stored procedures as you are doing can turn into a mess. Basically, when you start a nested transaction, SQL Server does nothing but increment the transaction count and doesn't really start a "new" transaction. Every time you commit a transaction, you decrease the transaction count.

Usually I try to avoid using nested transactions and instead I check for existing transactions to "reuse".  Here you can find the stored procedure code template I use to handle nested procedure calls without nesting transactions.

Other than that, you have to handle commits and rollbacks with savepoints to obtain real transaction isolation from the "parent" transaction. If you don't do that, you get the default behavior, which is commit for the innermost transaction and rollback for the whole transaction stack.

Also, take into account that transactions need proper error handling and you have to make sure you get a rollback whenever you encounter an error. TRY/CATCH blocks can handle that for you, as I'm showing in the template I linked. Another thing worth noting is that even SET options can affect the code flow, so make sure you have XACT_ABORT set accordingly.


single or multiple databases advice needed

How much data are you dealing with? 500 schools with 300 students in each is not a lot of data for SQL Server to deal with if you design the tables well. As other users have mentioned in the comments, a few indexes on the right columns and you should be fine. SQL Server can happily run terabytes of data without working too hard.

The management of multiple databases is also much harder than it looks. A few examples:

Deploying schema changes to multiple databases when you upgrade your application
Backups for multiple databases - location, times, restore time, etc.
Running other maintenance tasks (checkdb) for multiple databases

The one point you mentioned that is harder is if restoring or deleting the data for a particular school is an important requirement. It is much harder to restore/delete data for 1 school if they are all in the same database, and that reason might be enough to mean you should split the data.

My general rule is if it's the same app then you should start designing it in 1 database until you have a reason to split it.


This unicity constraint can be enforced with this unique index:

CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2));

Demo:

test=> insert into mytable (col1,col2) values(1,2);
INSERT 0 1
test=> insert into mytable (col1,col2) values(2,1);
ERROR:  duplicate key value violates unique constraint "idxname"
DETAIL:  Key ((LEAST(col1, col2)), (GREATEST(col1, col2)))=(1, 2) already exists.


Are there any caveats in my SQL migration plan?

Additional considerations for:

Backup Databases

WITH COPY_ONLY in order to retain the backup sequence of any third-party tool or backup maintenance plan currently running on your old SQL Server.

Restore Databases

You might have to RESTORE DATABASE ... WITH NO_RECOVERY in order to be able to apply further transaction log backups to your new database.
Or after restoring the database backup run the RESTORE LOG ... WITH NO_RECOVERY on the new SQL Server so that further transaction log backups can be applied.

Single User Mode

(placeholder)

Final Transaction Log Backup

(placeholder)

Restore Transaction Log Backup

(placeholder)

Turn Off Old Instance

(placeholder)

And possible the most important point:

Test Procedure

Test the procedure in your Development Environment


Columnstore index on multi-tenant database

Is it still a good idea to use NCCI indexes for multitenants database?

If the alternative is to have a data structure that efficiently filters by tenant, then no.

If TenantId is the leading column in your clustered index, then single-tenant queries will be pretty fast.  The NCCI row groups will not be segregated by tenant, and so all the row groups will need to be scanned to find rows for a single tenant.

As rows are inserted, they are inserted into the "middle" of the clustered index, typically at the "end" of the rows for the tenant.  But rows are always inserted into the delta store for an CCI/NCCI.  And whenever the delta store has 1 million rows in it, it's rebuilt into a columnar row group.  So each row group will end up with the last million rows inserted across all tenants.

You can fix this with partitioning by TenantID, which could give each tenant (or group of tenants) a separate physical table (or NCCI).  Each partition will have its own delta store, which will fill up with single-tenant rows.  If you do partition, each partition might end up too small for a Columnstore to be useful. You'll want a few million rows per partition at least.

In general with multi-tenant data, you want to avoid interleaving tenant data in a way that requires you to read all tenants data to retrieve a single tenant's data.


How to deal with SQL Server Full Text Log Files?

It seems your option is this undocumented stored procedure running in a job:
EXEC sp_fulltext_recycle_crawl_log @ftcat = 'FullTextCatalogName';

Check the Recycle Fulltext Catalog Log Files article by Jonathan Kehayias.
I just tested it in a lab environment and it purges the oldest fulltext log file creating a new live file (in my lab I could see 1 live file and 6 old files, so this must be the default number of files SQL Server keeps).


Storing Dynamic data in relational database (SQL Server 2014)

It sounds like you are looking for an Entity-Attribute-Value (EAV) data model design.  Aaron Bertrand has a great post on this which I suggest you look through and see if a similar approach will fit your needs.

As a DBA, I have a deep-seeded hatred for this approach, but sometimes it makes the most sense for the situation at hand.

If you can convince whoever controls the budget to upgrade to SQL Server 2016, there are some new features that take advantage of JSON data, which can give you even more flexibility.


Copy Huge Table with Transformations into Another Table

If this is a one-time job, you might just use the BCP Utility to do this task.

It seems that your hanging point is doing a massive import that exceeds the resources of your disk space.  The thing you need to do is to break the import into batches, which BCP supports, so as to avoid a transaction that cannot fit into your server.

When I have had to make a single massive data transfer, I have had good success using the BCP utility to extract the data to a file.  This does, of course, require a file location to temporarily hold the extracted data.

Use The BCP Utility examples from MSDN as samples. (Scroll to see entire commands.)

Copy Data to File Storage:

 bcp WorlWideImporters.Warehouse.StockItemTransactions OUT D:\BCP\StockItemTransactions_native.bcp -m 1 -n -e D:\BCP\Error_out.log -o D:\BCP\Output_out.log -S ServerA -T

To import the data back into a database using BCP you should define a batch size, such as -b 5000 (commit transactions ever 5000 rows) so as to avoid filling up the transaction log.

You may also need to schedule the log backups more frequently during the massive import that you describe. Or you can switch to SIMPLE mode during the insert to the database.

Copy from File Storage to Database

bcp WorlWideImporters.Warehouse.StockItemTransactions_bcp IN D:\BCP\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\BCP\Error_in.log -o D:\BCP\Output_in.log -S ServerA -T

These are reliable tools, but not the fanciest. For importing data using BULK INSERT which is, in my experience, runs faster that BCP and it also has a parameter for controlling batch size.

You should reference The BCP Utility and BULK INSERT if your needs are more complex than the simple examples I have used.

However, please notice this post with several answers: Freeing Unused Space SQL Server Table

Note that Kin included the options mentioned is BCP and BULK INSERT, but there are other good options is the posts noted above.


You can find the answer in the EXPLAIN ANALYZE outputs you've posted.

In the first one, the estimated cost of the sequential scan is 96.24, while the index scan has a cost of 360.41 in the second one.  This is why the planner decided not to use the index.

However, we see that the execution time is much better in the second case.  The main reason might be that the row estimate of the sequential scan is quite bad - 15 rows are expected instead of ~3k.  Do an ANALYZE crm_customer; and see if it changes - it is already possible that the index will be chosen.


Mirrored DB on Restoring state after removing Mirroing also

That is normal. When you setup a database mirroring the mirror database is on no recovery/restoring mode and copies and run transaction executed on primary server. Then when you remove the database mirroring you just stopped those transaction to be transferred and nothing is happened to bring it back to recovery mode. If you want your secondary (mirror) database to be active you have to execute.

      RESTORE DATABASE YourDatabaseName  WITH RECOVERY


How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it.

First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that this field is VARCHAR(2), but it doesn't allow specifying a string. However, I did get the following to work:

ALTER SERVER AUDIT [servAudit]
WHERE ([class_type] <> 20038); -- EXECUTE Scalar UDF

(more info on that investigation here: Server Audit Mystery: Filtering class_type gets Error Msg 25713)

The next most generic approach is not an option since it was stated that this is a vendor-supplied database and hence no changes can be made. So I will cover that last.

The least generic approach (but one that definitely works) is to filter out the specific function name:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name');

Or, if multiple names:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name1' AND [object_name]<>'function_name2');

While not very generic, this approach should be fine since the number of functions to filter out should be fairly small, and it won't be very often that new functions are introduced.

Finally, to others who face this situation and are not restricted from making changes: you can place functions into their own Schema and then filter out just that Schema. This is more generic than filtering out the functions individually. Assuming that you create a Schema named fn and place the function(s) into it:

ALTER SERVER AUDIT [servAudit]
WHERE ([schema_name]<>'fn');

ALSO, regarding the following two comments in the question:

  Unfortunately, SQL Server doesn't allow relational IN operator (probably because it doesn't want to query every time it has to write to the audit log).

and:

  We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause

The IN operator isn't the issue. True, it is not supported, but it's just shorthand for a list of OR conditions. The actual issue is the use of T-SQL. Only literals — strings or numbers — are allowed. So you wouldn't have been able to execute a Stored Procedure anyway. Nor can you use the built-in functions.


SQL Calculation based on 'similar' rows within table

Just an UPDATE using EXISTS will be  your best bet:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
WHERE EXISTS (SELECT 1
              FROM CakeTable C2
              WHERE c2.personid = c.personid
              AND c2.date > DATEADD(day, -2, c.date))

If you think this will run too long, you can always break it down by date range, cake type, etc to do smaller batches.

If you have a composite index on personid, date this should be pretty fast.

You could also do a self-join but I'm pretty confident the EXISTS will run a lot faster:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
INNER JOIN CakeTable C2
  ON c2.personid = c.personid
  AND c2.date > DATEADD(day, -2, c.date)


If the execution plan that you posted is for the query that is too slow for you, the answer would be to create an index on "CaseProductUnderwritingPK".


On SQL Server 2016+, you can load-balance read-only connections. (Note: this does not work on SQL 2012 or 2014 AGs.)

The full documentation is available here.

Essentially, when configuring your read-only routing list, you would use syntax like this:

ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQL16A'
    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL16B', 'SQL16C'), 'SQL16A')));

This will make your first routing option SQL to load-balance between SQL16B & SQL16C. The second routing option is to send read-only connections to SQL16A. If server B is offline, then just that one server is taken out of the rotation--so all read-only connections would continue to go to Server C.

Load balancing is done in a round-robin fashion, so connections will be routed, A,B,A,B,A,B, etc.  Load balancing does not consider existing number of connections, existing server load, or anything fancy.


unable to create schema on amazon rds for postgres

RDS PostgreSQL in this sense is no different from any other PostgreSQL installation.  You can, of course, create schemas and whatever objects you want.

The problem here is missing privileges.  By default, the owner of the DB (the role that created it) has full access (CREATE, CONNECT, TEMP) on the DB, and can grant these to other roles, too.

Connect to the DB with the role that created it, then you can try to create a schema to prove the above.  To make other users able to create schemas inside this DB, do the following:

GRANT ALL ON DATABASE your_db TO andy_k;

After this, you can log in as andy_k, and create schemas.


you should use concat to join strings.

this column:

'' + a.name + ''
AS "Assignmentlink"

in mysql should write as this:

concat('' , a.name , '')
AS "Assignmentlink"


remove the instance while uninstall a database

Actually you delete the database not unistall it.
in SQL Server for one instance there can be many database. i.e. one to many instance.
so there's no possibility to unistall sql server instance while deleting the database. (ofcourse unless you script it)

Uninstall the instance from control panel menu and there after.


Logging Postgres startup errors on Windows

There's a chicken-and-egg problem there.

PostgreSQL reads postgresql.conf to decide where to log, and how.

So it cannot really log errors in postgresql.conf to the PostgreSQL logs, unless it uses some kind of fallback/default log.

Instead, it uses the Windows Event Log, which is what Windows applications are supposed to do anyway. (You can have PostgreSQL write all logs to the Windows Event Log if you prefer, by changing its logging configuration).

So you should look in the windows event log - see the Event Viewer - for startup issues with PostgreSQL.


Yes, you are correct. I wrote about this before: How can I optimize this mySQL table that will need to hold 5+ million rows?

WHen it comes to bulk loading InnoDB tables, one of the most frequently ignored settings to disable is innodb_doublewrite.

InnoDB Architecture

Please note the double write buffer inside the system tablespace file ibdata1. Every time a 16K page is written to an InnoDB table or transaction log, the page is written to the double wrote buffer first. All you need to do is disable it, load your data, and reenable it.

STEP 01 : Disable Fast Shutdown

mysql -uroot -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"

STEP 02 : Restart mysql without the double write buffer

service mysql restart --innodb-doublewrite=0

STEP 03 : Load the InnoDB data

STEP 04 : Restart mysql

service mysql restart

CAVEAT

You can disable the foreign key checks and the unique checks as well. Any standard mysqldump will do that for you already.


Select data from one table with id from another

How about something more like:

SELECT COUNT(users.id)
FROM users
    INNER JOIN online
        ON online.ID = users.ID
WHERE users.id = 'myuser';


MySQL configuration to connect to multiple local applications

Not really an answer, but too big for a comment. This is quite a specialised question and would probably (IMHO) be better off asked on the FreeRadius mailing lists.

I looked here and found this

  FreeRADIUS is a high-performance and highly configurable RADIUS
  server. It supports many database back-ends such as flat-text files,
  SQL, LDAP, Perl, Python, etc.

The problem with many systems which support multiple back-ends is that they end up not being optimised for any of them - it's possible that something in their code conflicts with MySQL's way of doing things. And I'm not even sure what a "Perl, Python, etc." back-end is - drivers?

From here, it appears to me that the default database is PostgreSQL and it's possible that scenarios which work with PostgreSQL either don't work or work poorly with MySQL. So, my second suggestion is to try that RDBMS (which, in any case, is superior to MySQL IMHO).

[EDIT] in response to the OP's queries.

Just out of curiosity - what are the response times of those queries when run separately? Second, the issue appears to be a lock on the radacct table. This is a problem with MyISAM tables which your system uses.

Could you take a dump of your schema using mysqldump, change ENGINE=MyISAM to ENGINE=InnoDB,

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql

load the edited dump file and rerun the system to the point where you are having the problem? There is very little to be said these days for running a system using MyISAM tables.


It looks like you are looking for SEQUENCE, simple usage:

CREATE SEQUENCE ip_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

and then in select:

where id = ip_seq.nextval % ip_table_size


Can I restore database A to database B but keep B's permissions?

A SQL Server database backup is the whole database, users included.  You can script out the permissions prior and reapply them as part of your process while blowing out the other permissions.  You could make your db schema work on both environments.  You could also just do data exports and load up data.  Tools like RedGate Data Compare could also help keep the data in sync.

If you want to give more info about your environment we can help guide you to the best option but you have several.


Can I revert a transaction after a certain number of days

There isn't anything built into SQL Server that will undo statements. Your options are:

Restore from backup or from database snapshot
Buy a third party development tool like Red Gate's SQL Compare & Data Compare - which may require creating another database, making your changes, and then comparing the differences between them
Buy a log reader tool like Quest Litespeed - which can read Litespeed's transaction log backups and generate undo scripts for specific transactions
Writing your own undo script - whatever changes you plan to make, write the undo scripts yourself to undo your changes


This is just formatting to try and understand

So far that OUTER APPLY is what I would look at

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
       Coalesce(Error1, '')
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
       + Coalesce(Error2, '')
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
       + Coalesce(Error3, '') as ErrorList
From ( select [Date], ClientName,
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3
              From
             ( Select s.[Date],
                      s.clientID,
                      c.DisplayName   [ClientName],
                      si.PayorID      [SchedPayor],
                      ce.PayorID      [EligPayor],
                      stf.DisplayName [StaffName]
                 From Schedule s
                 JOIN ScheduleItem si
                   ON s.ScheduleID = si.ScheduleID
                  and s.AccountID  = si.AccountID
                  and s.accountID  = 1
                  and si.Owner = 1
                 JOIN Client c
                   ON s.ClientID  = c.ClientID
                  and s.AccountID = c.AccountID
                OUTER APPLY
                      ( Select ClientID, PayorID
                          From ClientEligibility ce
                         Where s.ClientID = ce.ClientID
                           and s.[Date] BETWEEN ce.StartDate and ce.EndDate
                      ) as ce
                 LEFT JOIN Staff stf
                   ON s.StaffID   = stf.StaffID
                  and s.AccountID = stf.AccountID
             ) as a
     ) as b

My guess is this may get you what you need

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
       Coalesce(Error1, '')
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
       + Coalesce(Error2, '')
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
       + Coalesce(Error3, '') as ErrorList
From ( select [Date], ClientName,
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3
              From
             ( Select s.[Date],
                      s.clientID,
                      c.DisplayName   [ClientName],
                      si.PayorID      [SchedPayor],
                      ce.PayorID      [EligPayor],
                      stf.DisplayName [StaffName]
                 From Schedule s
                 JOIN ScheduleItem si
                   ON s.ScheduleID = si.ScheduleID
                  and s.AccountID  = si.AccountID
                  and s.accountID  = 1
                  and si.Owner     = 1
                 JOIN Client c
                   ON s.ClientID  = c.ClientID
                  and s.AccountID = c.AccountID
                 left join ClientEligibility ce
                   on s.ClientID = ce.ClientID
                  and s.[Date] BETWEEN ce.StartDate and ce.EndDate
                 LEFT JOIN Staff stf
                   ON s.StaffID   = stf.StaffID
                  and s.AccountID = stf.AccountID
             ) as a
     ) as b


table_open_cache won't increase above 431 - MariaDB 5.5

Based on Open_tables being 431, I assume that you have only 431 tables in all the databases?  Or at least only that many are in use?

What is the value of table_open_cache?

What is the value of Table_open_cache_misses / Uptime?  If that is less than, say, 1/sec, then the table_open_cache is "big enough".


Starting and Stopping the Database with Oracle Administration Assistant for Windows

  To start or stop the database:

  From the Start menu, select All Programs, then Oracle - HOME_NAME,
  then Configuration and Migration Tools, and then Administrative
  Assistant for Windows.

  In the console window, expand the Oracle Administration Assistant for
  Windows tree structure.

  Under Databases, right-click the name of the database, and from the
  menu, select from the following options:

  Connect Database

  Start Service

  Disconnect Database

  Stop Service

  Startup/Shutdown Options

  Process Information

At Startup/Shutdown Options, you can set whether the instance should start when the Windows service belonging to that database instance starts.

Another method is to use the command line, disabling the automatic startup of the instance of database ORCL:

oradim -edit -sid ORCL -STARTMODE manual


Add INCLUDE term to primary key index in Postgres 11

Without messing with the catalogs (which is not commendable), the only option I can think of would require you to do without foreign keys for a while:

You can define a second UNIQUE index on the table that contains the appropriate INCLUDE clause.

If you use the CONCURRENTLY clause of CREATE INDEX, that shouldn't be disruptive.

Then you can delete the original primary key constraint and all dependent foreign keys using DROP INDEX ... CASCADE.

Then use ALTER TABLE ... ADD CONSTRAINT ... USING INDEX to turn the unique index into a primary key constraint.

Now you can re-create the foreign key constraints.


Oracle 11g R2 installation on Ubuntu 14.04 LTS

try these commands on terminal :

sudo mkdir -p /var/lock/subsys

sudo chmod -R 777 /var/lock/subsys


Here is one solution, there may be more efficient ways of doing it but this is one way that avoids more complicated solutions like using regular expressions.

update YourTable SET [ColumnValue] = REPLACE(REPLACE([ColumnValue],'- ', '-'), '-', '- ')

So what this is doing is setting all instances of '- ' to '-' first, in order to make the spacing between hyphens/words consistent throughout your whole string. It then takes all instances of '-' and transfers them to '- '.

The reason behind doing this double replace is to avoid adding extra spaces between the hyphen and the word.

If you just did:

update YourTable SET [ColumnValue] = REPLACE([ColumnValue], '-', '- ')

You would end up with multiple spaces between the hyphen and the words (in the case of "- is" and "- a").


OK - if you can't dump + restore, and don't want to understand your system well enough to set up replication then you've got only one option left.

You will need to run identical setups at each end (version of PostgreSQL, 32/64-bit, compilation options, file paths etc).

Then, scp the entire data directory - including all the WAL files (pg_xlog) and all other sub-directories.

During a quiet period, halt your primary server and use rsync to bring your copy up-to-date with respect to the original. Then, start the copied server and off you go.

Whether you can do this in less than 4 hours, I don't know. It will depend on how efficient rsync really is, the latency between both systems, how many updates you have had etc.

It might be that running rsync before halting the primary server will make the real run go faster. Difficult to say.


MongoDB - Does WiredTiger benefit from virtual cpus cores the same way
  it does with physical cores?

As per my understanding the MongoDB documents defines based on the physical cores.

As blog notes by @UmaPanda here this topic is always a perpetual debate to define the proper ratio of physical CPU to virtual CPU in virtualized environment.

First you understand the basic calculation:

The number of physical cores i.e. pCPU available on a host:
(# of processor Sockets) X (# of Cores) = # of Physical Processor (i.e pCPU)
The number of logical cores if hyper-threading is enabled on the host:
(# of Physical Processors i.e pCPU) x (2 cores) = # Virtual Processors (vCPU)
Total CPU resorces required for virtual machines at peak:
(# total number of virtual machines X average peak CPU utilization per system)

kindly note that hyper-threading does  not actually double the available of Physical CPU. It works by providing a second execution thread to a processor core. So, a processor with 4 physical cores with hyper threading will appear as 8 logical cores for scheduling purpose only.

  The WiredTiger storage engine is multithreaded and can take advantage
  of additional CPU cores.

The MongoDB document also states that here Specifically, the total number of active threads (i.e. concurrent operations) relative to the number of available CPUs can impact performance:

Throughput increases as the number of concurrent active operations
increases up to the number of CPUs.
Throughput decreases as the number of concurrent active operations
exceeds the number of CPUs by some threshold amount.

The threshold depends on your application. You can determine the optimum number of concurrent active operations for your application by experimenting and measuring throughput. The output from  mongostat provides statistics on the number of active reads/writes in the (ar|aw) column.

And also from MongoDB blog notes here MongoDB will deliver better performance on faster CPUs. So, one of the MongoDB performance best practices is that the core speed is more important than the number of cores.

For further your ref here , here , here and here


Oracle "Import from Export Files" screen refusing both SYSADMIN and Normal users

The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even granting an additional role, unless specifically told to by Oracle.

The first message you got says that import isn't supported when logged in with the SYSDBA role. That doesn't mean you can't import as SYS, just that you have to log in as SYS with the NORMAL role rather than as SYSDBA.

This assumes you want to do a full import though; for partial (e.g. schema-level) imports you might want to create the user first and perform the import as that user, but I'm not sure that's what you're looking for at the moment.


Custom Deletion for Ola Hallengren Scripts

If this is your required backup retention policy, then could you not have an extra backup job that runs once a month, and another running at once a year. These two new jobs would point to their own directory and have their own retention policy.

The downside is once a year you'll do 3  at once, daily, monthly and yearly one. But this is a rare event.

I would question if all this backups are needed, would the data be stale and no good by then? Could you lose a year's worth of data if you needed to restore it. If you want it for looking back at the data, is a DWH more suited?

However I don't no the reasons why this is required, there is probably a good reason for your approach. Such as cost saving.


Will PostgreSQL optimize query when the same aggregate usage?

The reason you can't use the aliases is that they don't exist during aggregation, renaming is performed after aggregation. I don't know the answer to your question, I guess the only way to find out for sure is to look at the plan. However, have you considered nesting your query?

SELECT employe, s, d, s-d as total
FROM (
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
) as t;

another option is to use a CTE:

WITH t as (
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
)
SELECT employe, s, d, s-d as total
FROM t;


MariaDB is the open source community driven version that has the  endorsement of all of the unaffiliated MySQL developers.
MySQL is a trademark owned by Oracle and which has continued developed funded by Oracle. Some of this development is pretty awesome. I wouldn't discard it. I believe the Oracle developers are likely more skilled than the community working on MySQL.
PostgreSQL is the best and most advanced open source database. Though it has nothing at all to do with MySQL, I highly suggest you use it instead. It's faster, more spec compliant, and better documented.

The Maria vs MySQL is a mess and all feature comparisons are pretty bad. So read them all! Good luck with it.

MariaDB official comparison: https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/


Check if two databases have same structure

PostgreSQL has a comprehensive set of system catalogs. These hold the metadata for a database i.e. table names, columns, index definitions and so forth. By comparing the content of the original database's catalog with that of the DB generated from the model you can check completeness. Turns out, there's an app for that.


How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column

In addition to other answers - and to make it clear that subqueries or CTEs are not needed here. We can have the same result without them. Use whatever is more clear to you:

select
     s.store_id,
     max(s.rating) - min(s.rating) as difference,
     min(s.rating) as min_rating,
     max(s.rating) as max_rating
from
     storeRatings as s
group by
     s.store_id
  ;


Does PostgreSQL support multi-threaded replication?

The standard streaming replication on PostgreSQL is single threaded and there is no way to change this.  However the question is why would you want to?

PostgreSQL's streaming replication works through the write ahead log which is kinda of like a set of instructions "change block 3525 to this", "change block 2424 to this", etc.  This makes the replication process very fast as it not re-executing SQL, its simply doing the same data file updates that the master has done.  Typically the slowness with replication on PostgreSQL is IO bound rather than CPU bound so executing the recovery in parallel will actually make it slower not faster.  This is because the effect of interleaving the IO requests will potentially make sequential IO random.  Of course modern SAS drives should be able to reorder the requests for data back into sequential order.

I suspect the question came about because of the statement shipping type of replication MySQL and MariaDB can do, in this case yes of course it makes perfect sense to interleave the statements as there will be a much higher CPU cost and running this on multi-core hardware will offer a great performance improvement.


Getting error while running dbca command as oracle user on linux, please help me on this

Start with (use the appropriate install syntax for your distro)

sudo apt-get install libaio1

and if that doesn't do the trick, do

sudo apt-get install libaio libaio-dev

Paul...


convert multiple columns as single row with appreciated data

Hope following expected result script help in your case, but you have prepare it for maximum number of fields.

Test environment (1 time execution only) as per your scenario:

Create table #Temp
(formid int, FieldName varchar(50), value_ varchar (50) )
go

Insert into #Temp
values
(101, 'FirstName', 'Name 1'),
(101, 'LastName', 'x'),
(101, 'Email', 'Name@s.com'),
(102, 'FirstName', 'Name 1'),
(102, 'Gender', 'M'),
(102, 'Mobile', '9xxxxxx'),
(103, 'DOB', '19900101')
go

Expected Result

Declare @FormID int = 103

Select  max(Case when F_Rn = 1 Then formid end) as formid,
        max(Case when F_Rn = 1 Then FieldName end) as Field1,
        max(Case when F_Rn = 2 Then FieldName end) as Field2,
        max(Case when F_Rn = 3 Then FieldName end) as Field3,
        max(Case when F_Rn = 4 Then FieldName end) as Field4,
        max(Case when F_Rn = 5 Then FieldName end) as Field5,
        max(Case when F_Rn = 6 Then FieldName end) as Field6
from            (
                select formid,
                       ROW_NUMBER() OVER (Order by FieldName) as F_Rn,
                       FieldName,
                       value_
                from #Temp
                where formid = @FormID
                ) as t
go

Drop Test environment

Drop Table #Temp
go


Extracting the maximum of several subsets and join all maximums in a new table

First step:

SELECT testname, entry, MAX(`value`) as max_value
FROM your_table
GROUP BY testname, entry

Second step:

SELECT MAX(max_value), MIN(max_value), AVG(max_value)
FROM ( /*here we insert the query from the first step*/
    SELECT testname, entry, MAX(`value`) as max_value /*rename the column so we can conveniently access it from the outer query*/
    FROM your_table
    GROUP BY testname, entry
) sq /*sq is an alias for the subquery, it's needed*/


Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE email_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.


How to use parent table column inside subquery JOIN condition?

You may want to use EXISTS operator with Sub-query, but that serve in different scenarios, in your case moving NotificationQueue table down to the sub-query can make you to do join that your looking for, following is the example of same:

SELECT  op.OpportunityID,
        op.Name AS [OpportunityName],
        ua.Email AS OwnerEmail,
        nt.NoteID, nt.Note,
        tks.TaskID,
        tks.ActivityType,
        ua.FirstName,
        ua.LastName,
        FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
        lead.LeadID, lead.LeadName
FROM NotificationQueue nq
    INNER JOIN  Opportunity op WITH (NOLOCK) ON nq.RecordID = op.OpportunityID
    INNER JOIN  UserAccount ua WITH (NOLOCK) on op.owner = ua.UserID
    LEFT JOIN   Note nt WITH (NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
    LEFT JOIN   (
                SELECT  t.TaskID, t.TaskTypeID,
                        t.OpportunityID,
                        tt.Description AS [ActivityType]
                FROM    Task t WITH(NOLOCK)
                        INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
                ) tks ON op.OpportunityID = tks.OpportunityID

    LEFT JOIN   (
                SELECT  l.LeadID,
                        l.Topic AS [LeadName],
                        lo.AssociatedOpportunityId
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1
                ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

WHERE op.Active = 1 and nq.Processed = 'N'

As per comment, following is the example of last LEFT JOIN:

    OUTER APPLY (
                SELECT  l.LeadID,
                        l.Topic AS [LeadName],
                        lo.AssociatedOpportunityId
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1 AND nq.RecordID = lo.AssociatedOpportunityId
                ) lead --ON op.OpportunityID = lead.AssociatedOpportunityId


Optimize duplicated but negated subquery

Maybe this query would be better?
We would check stuffThatIs for every row, and then validate if

ToBe=1 and there is a.Being in stuffThatIs  OR
ToBe<>1 and there isn't a.Being in stuffThatIs

SELECT *
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
  SELECT 1 [is_inside]
  WHERE EXISTS(SELECT *
               FROM stuffThatIs x
               WHERE x.Name = b.Name
                 AND x.Being = b.Being) -- instead of "...a.Being in (select Being..."
) x
WHERE ((a.ToBe = 1 AND x.is_inside IS NOT NULL)
    OR (a.ToBe <> 1 AND x.is_inside IS NULL))


Why does coalescing make this query quicker?

The timings could be bogus because of caching.  You probably have the Query cache on.  Try again, but with SELECT SQL_NO_CACHE ...;

Recommend you add a new possibility to the ENUM behind this:

`attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting')
        OR `attempt_1_result` IS NULL

That way you could avoid the NULL test and the OR.

AND `table2`.`date_assigned` >= '2015-06-21'
AND `table2`.`date_completed` IS NULL

would benefit from INDEX(date_assigned, date_completed) (in either order).

Why do you have LEFT JOIN?  It seems like JOIN would give you the same result?

Why do you even mention table2?  You are not fetching anything from it.


Dealing with out-of-range values on a PARTITION BY RANGE

Is there a way to get the min/max values I could store within the existing partitions?

And you also ask in a comment:

  Do you know of a way to get the lower and upper bounds of the range by query?

I wouldn't know of any dedicated system catalog information function for this particular purpose. But:

Range partitioning is based on inheritance internally:

  Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;

Inheritance trees are stored in pg_inherits:

  one entry for each direct child table

Partition bounds are stored in pg_class.relpartbound in internal format (pg_node_tree).
The system catalog information functions pg_get_expr(pg_node_tree, relation_oid) can:

  decompile internal form of an expression

We can build a query from this set of clues. Based on the example for range partitioning in the manual:

SELECT i.inhrelid::regclass
     , partition_bound
     , split_part(partition_bound, '''', 2) AS lower_bound
     , split_part(partition_bound, '''', 4) AS upper_bound
FROM   pg_inherits i
JOIN   pg_class    c ON c.oid = i.inhrelid
     , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
WHERE  inhparent = 'measurement'::regclass;

inhrelid             | partition_bound                                  | lower_bound | upper_bound
:------------------- | :----------------------------------------------- | :---------- | :----------
measurement_y2006m02 | FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') | 2006-02-01  | 2006-03-01
measurement_y2006m03 | FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') | 2006-03-01  | 2006-04-01

db<>fiddle here

Limitations:

Extracting lower and upper bound from the string based on single quotes is cheap & dirty. There is probably a cleaner way to extract the value from relpartbound directly.
Only including the first level of inheritance. You have to walk the graph in pg_inherits recursively to cover sub-partitioning.
This builds on several implementation details for declarative partitioning, which is a new feature of Postgres 10. While I do not expect this query to break due to changes in one of the next major versions, there is a chance it might.


SQL server is there recursive reserve word?

Ms Sql recursive CTE syntax has no RECURSIVE keyword See
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Ie. CTE of the form

WITH cte_name ( column_name [,...n] )
AS
(
–- Anchor member
UNION ALL
–- Recursive member
)

is recursive when Recursive member references  cte_name. For example

WITH node_rec AS (
    SELECT TOP(10) 1 AS depth, node AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    UNION ALL
    SELECT r.depth + 1, r.path +'/'+ n.node, n.*
    FROM   node_rec r
    JOIN   nodes n ON n.parent = r.node
    WHERE  r.depth < 4
)


I prefer to take advantage of the 'string' format of dates:
LEFT(datestamp, 4) is the same as YEAR(datestamp), etc:

mysql> SELECT LEFT(NOW(), 4), LEFT(NOW(), 7), LEFT(NOW(), 10);
+----------------+----------------+-----------------+
| LEFT(NOW(), 4) | LEFT(NOW(), 7) | LEFT(NOW(), 10) |
+----------------+----------------+-----------------+
| 2015           | 2015-02        | 2015-02-15      |
+----------------+----------------+-----------------+

So, your complex CASE boils down to deciding between 4, 7, and 10, thereby shortening the code significantly.  (This trick works for DATE, DATETIME, TIMESTAMP, but not timestamps stored as INT.)

As a side note: It would be better to use country CHAR(2) CHARSET ascii, which takes only 2 bytes.


SSIS:How retrieve and insert an XML Column

This can be done by converting the XML column when initially selecting it then casting it back to an XML data type for the insert.  This is outlined in further detail below.

In the first Execute SQL Task, select the XML column by casting it to an NVARCHAR data type.  Keep the Form_XML_Text variable as a string data type.
In the Execute SQL Task within the Foreach Loop, set this variable to use the NVARCHAR data type on the Parameter Mapping pane.
Then in the SQL statement on the Execute SQL Task inside the loop cast it back to XML as done below.  This example assumes that the XML column is the second one in the destination table.
I would also recommend always explicitly listing the columns when inserting into a table as follows.  This will make debugging easier and prevent errors that can occur if additional columns are added to the table in future.

Insert Example:

INSERT INTO TEST (Column1, XMLColumn)
VALUES (?, CAST(? AS XML))


Transactional versus Dimensional model for file upload web application -- preferred approach?

It depends on your data, and how you want to store it and access it later to report off it. Your database itself doesn't need to be geared specifically to one or the other, rather you can have tables that are transactional, dimensional, or simultaneously both depending on your use case.
In general, if you're doing some sort of reporting off the data, then normalizing your data with dimensions is helpful for performance, flexibility, and maintenance. But a transactional table can still store references to dimensions to represent the "facts" / qualitative portion of the data to maintain normalization. Or they can even directly store the dimensions inside them themselves, albeit being a denormalized representation of the data from the former.
See the following for two different schemas representing a transactional table that stores dimensions vs being normalized and storing references to those dimensions (T-SQL syntax):
Denormalized
CREATE TABLE DenormalizedTransactions AS
(
    TransactionId BIGINT,
    ItemName VARCHAR(100),
    ItemType VARCHAR(25)
    Quantity INT
);

Normalized
CREATE TABLE NormalizedTransactions AS
(
    TransactionId BIGINT,
    ItemId INT,
    ItemTypeId INT
    Quantity INT
);

CREATE TABLE Items AS
(
    ItemId INT,
    ItemName VARCHAR(100),
    Description VARCHAR(1000)
);

CREATE TABLE ItemTypes AS
(
    ItemTypeId INT,
    ItemType VARCHAR(25)
);


What may be the consequences of deleting the system keyspace?

The system keyspace is where a Cassandra node maintains information about itself and other nodes.  So things like network addresses, responsible token ranges, status from gossip, hints, schema, and other local information.  The system keyspace has its own, special replication strategy known as "LocalStrategy:"

cqlsh:system> desc KEYSPACE;

CREATE KEYSPACE system WITH replication = {
  'class': 'LocalStrategy'
};

This should tell you that data in the system keyspace isn't replicated to other nodes, and is largely specific to itself.

Sometimes you can fix gossip-related issues by blowing-away the system keyspace (or even just all entries in system.peers).  Removing it via the filesystem (as you did) has the same effect.  The reason this works, is because it forces the node to rewrite all of that data, most of which it already has (system.local) or it can learn from gossip.

While I wouldn't make a habit out of it, deleting the system keyspace via the filesystem is ok...as long as you have another node in the cluster for it to communicate with.


Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

@klin already addressed the bug you had been asking about. I am going to address your general approach.

Your DO statement would run a separate UPDATE for every column - which is almost n times as expensive as updating n columns at once.
Due to PostgreSQL's MVCC model this also produces n dead tuples in the table, leading to excessive table bloat and a lot of work for VACUUM.

What's worse, it updates every row n times even if nothing changes at all.

I suggest a much more efficient approach:

Update every row once.
Only update if anything actually changes.

CREATE OR REPLACE FUNCTION f_trim_cols(IN _tbl regclass, OUT row_ct integer
                                                       , OUT col_ct integer)
  RETURNS record AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT format('
      UPDATE %s SET (%2$s) = (trim(%3$s))
      WHERE (%2$s) IS DISTINCT FROM (trim(%3$s))' -- test if anything changes
                , _tbl
                , string_agg (quote_ident(attname), ', ')
                , string_agg (quote_ident(attname), '), trim(')
                )
        , count(*)::int  -- count columns
   INTO   _sql, col_ct
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    atttypid IN ('text'::regtype, 'varchar'::regtype) -- basic string types
   AND    NOT attisdropped
   AND    attnum > 0;

   -- RAISE NOTICE '%', _sql;  --  debug
   EXECUTE _sql;  GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_trim_cols(regclass) IS 'Trim all columns of table _tbl ($1).
Return count of applicable columns (col_ct) and rows actually changed (row_ct) .
Call:
   SELECT * FROM f_trim_cols($$city$$)   --';

Produces SQL code like:

 UPDATE city SET (city, country) = (trim(city), trim(country))
 WHERE (city, country) IS DISTINCT FROM (trim(city), trim(country));

I also target text as well as varchar columns.
Everything is sanitized and safe against SQL injection.
More explanation, links and a fiddle in this related answer on SO:

Update multiple columns that start with a specific string


Visual Studio 2010 deployment script creates objects in wrong order - trigger after stored procedure

You may have run into a known issue with Visual Studio 2010:

Connect request
SO Question
MSDN forum thread

That said, Microsoft recommends you help Visual Studio detect the object dependencies it seems to be ignoring in its deployment script by:

  schema-qualify[ing] the names of objects that are involved in dependent relationships.

So if everything you are developing sits in the dbo schema, make sure to prepend the proc and view names giving you trouble with their schema name.  For example: dbo.MyView instead of just MyView. Visual Studio has a "Fully Qualify Name" feature under the Refactor menu when you right-click on an object that'll help you do that very quickly.

If that doesn't help, give Google a spin and take a look at some of the workarounds suggested on the pages linked above. Let us know what ended up working for you.


What perfmon counters should I use to get the fastest restore possible on an idle system?

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx).  This can also be less resource-intensive than a full restore.

The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases.  Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.


Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

As you can see on https://www.w3schools.com/sql/func_mysql_cast.asp

SIGNED    Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED  Converts value to UNSIGNED (an unsigned 64-bit integer)

And from the official MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

So looks like the CAST output will actually be a 64 bits integer when you use SIGNED or UNSIGNED datatypes.


Can we directly upgrade Oracle 12.1 to 19 c or do we need to upgrade 12.1 to 12.2 first and then 19c

The term used by Oracle is "direct upgrade". You have to take into consideration if your database is a multitenant (CDB) database.

From https://mikedietrichde.com/2018/12/14/premier-support-extension-for-oracle-19c-and-more/ :

  Oracle 12.2.0.1, Oracle 18c and Oracle 19c all together sail under the cover of the “Oracle 12.2 Release Family” and hence have a common support frame

So direct upgrade should be available with the following versions: 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1

I'm not adding the MOS support hyperlinks since if you are posting the question here I'm guessing you dont have a MOS account.


With a single node i don't think you have other option than mongodump and LVM snapshot. In the case you run on a replica set which is recommended for production you can just stop one secondary and copy the data directory.


Adding a default constraint in Oracle while altering a column

A default value is not a constraint in Oracle. You simply alter the column as:

SQL> create table tab1 (col1 number, col2 number);

Table created.

SQL> alter table tab1 modify (col2 default 1);

Table altered.

SQL> select * from user_constraints;

no rows selected

SQL> insert into tab1 (col1) values (0);

1 row created.

SQL> select * from tab1;

      COL1       COL2
---------- ----------
         0          1


Does msmdpump.dll pass the authentication info to SSAS?

Yes, msmdpump will pass the authentication through to SSAS.
Here is an article on how to get it all set up for SSAS 2005.
Here is one for IIS7 to SSAS 2008.


N-Tier may be a disingenuous term

I think the term 'N-tier' is disingenouos whe used in the context of a business intelligence system.  In transactional systems 'N-Tier' describes a distributed system with an application server, ESB or some other networked middle tier.  Data warehouse systems don't work in a way analogous to this, so the term is likely to breed confusion.

Separating data and functional logic

You could build a system in terms of data that's as raw as possible and then put a transformation layer on it, which is then consumed by a reporting layer.  The transformation layer could take the form of:

A series of database views.
A metadata layer from the reporting tool (e.g. a Report Model or a Business Objects Universe).  A category of tools known as 'Enterprise Information Integration' extend the notion of the metadata layer into something like an in-memory ETL tool, allowing the reporting or functional logic layer to implement complex transformations.  However, this approach is fiddly to implement and not widely used unless something precludes implementing a more conventional ETL architecture.
A suite of data marts with their own ETL.

This would give you 'Data', 'Functional Logic' and 'Presentation' layers as per your example.  Some data warehouse systems are implemented a bit like this, although it (IMHO at least) would be something of an anti-pattern.  Some issues with pushing business logic downstream of your ETL include:

Depending on the capabilities of your functional logic medium, the possible transformative capabilities may be limited, resulting in leaky abstractions that limit the value of any ad-hoc reporting facility placed above them.  Ad-hoc tools need clean data that behaves consistently, and the data really has to be in a format that plays nicely with the tool.If the data is not consistent, clean and in the right format you are effectively limited to bespoke SQL reports of the data or building supplementary data marts to support ad-hoc facilities.  Relying on data marts has a tendency to proliferate a large volume of ad-hoc tertiary ETL processes with overlapping but subtly inconsitent functionality.  This tends to generate reconciliation and data quality issues and fails to provide a 'single source of the truth.'Generally this situation will generate a large maintenance workload and erode confidence amongst the user community.
A database schema that is not optimised for reporting may perform poorly.
'Raw data' implies that the data is kept in a form that is fairly isomorphic to the source, which is mutually exclusive with any notion of conformed data.
'Raw data' is also typically available from staging and archival areas if needed for auditing.  You will get a class of power users who 'just want the data', and these people may have a lot of political influence.  Allowing this to continue alongside a data warehouse project fails the 'single source of the truth' goal.If the warehouse has a proper conformed data mechanism (e.g. an operational data store) then this is supposed to provide the single source.  Dealing with conflicting definitions and user communities who insist on their own stovepiped systems is a whole topic in itself.

Nonetheless, this is more common than might be expected.  I think the main reason one encounters data warehouse projects implemented in this manner is that ETL tools are quite clumsy to work with if your requirements for tranformation logic are complex.  ETL tools often have the effect of dumbing down the architecture and fobbing off logic into the reporting layer, which significantly degrades the effectiveness of the data warehouse initiative.  The volume of work and presence of a central database gives the illusion of a data warehouse while providing little of the benefit.

Another N-tier view of a data warehouse

One could interpret 'Data', 'Functional Logic' and 'Presentation' as an ETL and reporting process in a more well organised data warehouse system.  In this case, 'Data' could be interpreted as the staging layer, 'Functional Logic' implemented in the ETL, presenting a dimensional data store and/or suite of data marts, and 'Reporting' implemented through a reporting and ad-hoc query suite.

Considered harmful

For this reason, I think the concept of 'N-tier' is unhelpful and even a bit disingenuous.  It sounds a lot like something a middleware company or consultancy might describe in a white paper - a flawed and even somewhat misleading theoretical notion that sounds good on paper.


MySQL CREATE table with > 4000 fields (with large sized colums)

4096 is the limit.

Innodb has a 1017 limit.

Reconsider your design.


Create tablespace "error in creating database file, access denied (OS 5)"

Do you have permission on this directory?
Under which user are you running SQL*PLUS? Administrator? –
if no than got permission first

if yes

CREATE TABLESPACE lmtbsb DATAFILE 'file_path/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


Can we restrict the number of connections to a database in SQL Server?

Seems like we can do it :

To configure the user connections option

In Object Explorer, right-click a server and click Properties.
Click the Connections node.
Under Connections, in the Max number of    concurrent connections
box, type or select a value from 0 through    32767 to set the
maximum number of users that are allowed to connect    simultaneously
to the instance of SQL Server.
Restart SQL Server.

http://technet.microsoft.com/en-us/library/ms187030.aspx


How can I skip reboot check and remove command line appearing during install of SQL Server?

To skip the reboot check:

SkipRules=RebootRequiredCheck

To hide the console window:

HIDECONSOLE=True

Full command line/.ini file options can be found here.  The SkipRules option is not documented there and appears to be part of the general installer options.


Inconsistent query execution times using trigram indexes?

That is, the name column actually has ~20x more distinct values, but returns values much faster.

Exactly so. An index on name is more selective than an index on grp, so index use is more effective.

This is confirmed by the fact that your first query, on the name field, the bitmap index scan onresidences_grp_trgm_gin gets only 21 results as shown by actual( ... rows=21 ...) in the explain analyze output. On grp you get 5968 results.

In a bitmap index scan every heap read and re-check has a significant cost, so it makes perfect sense that finding 21 rows is going to be a lot faster than finding 5968.

BTW, please always show select version() and your server configuration, as well as anything else that's relevant from the wiki's guide to reporting problems.


FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

Your statement just TRIGGERS the rebuild job, not DOING it.
So - you triggered the job by ALTER FULLTEXT statement, now you can wait until it completes like that:

WHILE (SELECT COUNT(*) FROM sys.fulltext_catalogs WHERE FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') IN (1, 6, 7, 9)) > 0
    WAITFOR DELAY '0:00:00.1'


#1170 - BLOB/TEXT column 'variable' used in key specification without a key length

The column is OK, but the UNIQUE constraint requires an index that is too big.
Some other things are wrong:  Surely there can be more than one place in a state?  Yet the UNIQUE constraint says that there can be only one row for each state.
Remove UNIQUE from any column definition that is not truly unique.
Also, I strongly recommend you use InnoDB, not MyISAM.
PRIMARY KEY is a Unique Index.
UNIQUE is an index and a uniqueness constraint.
INDEX is a way to access data faster.  But until you have the SELECTs, you may not know which columns (or combination of columns) should be indexed.


Querying a view based on a table on which I don't have SELECT permission

Perhaps the schemas have different owners?
Take a look at this: SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?


Scripting the copy of SQL Server production database to test environment

But it seems it's problematic because it solely generates a .bak file
  although this file is still "linked" with .mdf/.ldf files. So when
  restoring it in our test environment these .mdf/.ldf files are not
  found. Then I went on specifying a 'WITH MOVE' options in the restore
  command in order to specify the location of the .mdf/.ldf file over a
  network share but I think the production server locks these file and
  prevent any read on them from the test server.

This whole statement does not really make sense. A backup file is not "linked" to the mdf/ldf files, it contains the files of that database in order to recreate them when you restore it. When you go to restore them on another server you add WITH MOVE and specify the location you want the mdf/ldf files to be stored at on that server. They do not link back to the original location where the backup was taken from, at all.

Using backups is the most common method for refreshing a development environment. You would generally base current you can keep the data in development (if needed) on how your backups are done in production (e.g. full/diff/log or just full/diff).


A UNIQUE constraint (or a unique index, mysql does not distinguish much between the two concepts of constraint and index) can be composed from more than one columns (2, 3, ...), as needed. Example for your case with a constraint made from 3 columns:

ALTER TABLE default_messages_users
  ADD CONSTRAINT Unique__message__owner_user__to_user   --- you choose a sensible name
                                                        --- for the constraint
    UNIQUE message_id_owner_user_id_to_user_id_UQ       --- and the index
      (message_id, owner_user_id, to_user_id) ;


Does mongodb support non readable files?

BSON has a Binary data type in which you can store anything you want. MongoDB sees it as a binary blob and does not care what type of data it is, so of course ot is possible to store PDF files. MongoDB documents are limited to 16 MB in size, so if you need to store large data, try GridFS.

http://blog.mongodb.org/post/183689081/storing-large-objects-and-files-in-mongodb


How to retrieve the query I used to create a view?

View definitions are stored in the information schema:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'yourviewname'


According to the MySQL Documentation on myisam_repair_threads

If this value is greater than 1, MyISAM table indexes are created in
parallel (each index in its own thread) during the Repair by sorting
process. The default value is 1.
Note Multi-threaded repair is still beta-quality code.

As it  says, it is in beta. Allowing for setting the value is evidently not implemented.
UPDATE 2013-05-13 22:24  EDT
Given the table you mentioned in the comment, here is what you could do to see what just what MySQL does to handles the situation. Let's say the table is mydb.mytable and uses the MyISAM storage engine. This is what mechanically has to happen:
use mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable_new RENAME mytable_old, mytable_new RENAME mytable;
DROP TABLE mytable_old;

This is as granular as you can get as far as the repair threads go. Each unique index and the PRIMARY KEY is loaded during the INSERT INTO mytable_new SELECT * FROM mytable phase. All the remaining nonunique indexes are built during the ALTER TABLE mytable_new ENABLE KEYS; phase.
Since Oracle is concentrating on the development of InnoDB, I double if they will get around to implementing myisam_repair_threads as a number beyond 1. Why? Think about. Index pages are written sequentially with myisam_repair_threads set to 1. If myisam_repair_threads was greater 1, each set of index pages per index would be written in an interleaved fashion.
This would make the index fragmented. Of course, the solution would be to write each index's set of sequential pages in eight(8) different places. They would eventually have to be written contiguously into one common index space (in the case of the MyISAM table, that would be the .MYI file). Until Oracle comes up with a way to create nonunique indexes in parallel, you will have to settle for one thread.


As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not.
At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows.
I guess your query will at least require an index on latest_renewal (but, again, check with EXPLAIN).

This is my understanding of your query, on which I based my simplification:

SELECT sessions.member_id
    ,sessions.session_token
    ,sessions.username
    ,sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN ( /* */
    SELECT stats.member_id
        ,stats.session_token
        ,stats.username
        ,stats.ip_address
    FROM bot_sessions_statistics AS stats
    WHERE date_active >= DATE (NOW()) /* a. I think this gives all of 'today' statistics */
    ) AS stats ON sessions.member_id = stats.member_id /* b. you're joining on 'member_id' which is NOT NULL in sessions, so any row from this subquery will have a value for (stats.)member_id */
WHERE latest_renewal >= DATE (NOW())
    AND stats.member_id IS NULL /* c. this will filter out all the rows from the JOIN, since no stats.member_id can be NULL */

Next, at first sight (if I'm not mistaken) I think your SELECT query can be rewritten without the LEFT JOIN, like this:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE
    NOT EXISTS (
        SELECT 1 FROM bot_sessions_statistics
        WHERE date_active >= date(NOW())
    )
AND latest_renewal >= date(NOW())

If the above is really correct, compare the results of EXPLAIN on both.


Can I tell when and who updated my database from v11 to v12 on Azure?

I believe this question was answered in the comments:

  It is indeed MS Azure (since you did not do it). They send you an email that on this day, the Azure SQL database servers will be upgraded. - Kin

  v12 has been GA since when? - Aaron Bertrand

  since July 2015 I think. You're probably right that we just ran out of time. I found this article, which seems to confirm your suspicion: – Erik

The article linked in Erik's contribution is Stopping or Postponing an upgrade to SQL Database V12.


Where can I download older versions of Oracle Instantclient?

Here's a link that lets you download 10.1.0.5:

http://www.oracle.com/technetwork/topics/winsoft-085727.html

This particular link is for 32-bit Windows.  Hope this helps.


CPU Time Slower in SQL Server 2014 than in SQL Server 2008 R2

Differences in CPU time in this scenario can be affected by many things and it could be just that SQL 2014 is a bit slower but scales better. However, I think more interestingly, the elapsed time is more than double the CPU time on the new server whereas it is less on the old. This could point to IO (as well as many others I concede) issues. Have you run perfmon on both servers to see what the underlying OS is reporting?

Other than that, any chance you can load 2014 on the same VMWare stack as the old server to remove the virtualisation layer from the equation?


A question about multisite Always On design

In traditional Always On Availability Groups, all replication traffic comes from the primary replica. If you have two replicas in a remote data center, they will both be updated from the primary replica, period. You can't do primary-to-secondary-to-secondary.

SQL Server 2016 introduced Distributed Availability Groups, which might satisfy your design goal. However, this is going to be much more complex, much less documented, and I wouldn't recommend doing this for your first AG.


SQL-Server: sp_spaceused gives zero rows but big dataSize for cleaned table

DELETE does not reclaim space, it deletes rows.

Space can remain allocated for several reasons, 3 of which are:

ghost clean up is still running
the table has no clustered index
the system tables are not updated yet

Try

EXEC sp_spaceused 'MyTable', 'true' to force a space used update
TRUNCATE  TABLE which deallocates space, rather then deleting rows
DELETE  myTABLE WITH (TABLOCKX) if no clustered index and can't use TRUNCATE

Don't run DBCC shrinkdatabase because it adds no value and it will simply grow again


The first is table specific. The second allows deletion from any table


How to configure port for listening to traffic meant for PostgreSQL

This is basic TCP/IP, rather than PostgreSQL-specific.

If the only things that will talk directly to the database server are on the same host as the database server, you only need listen_addresses = 127.0.0.1, i.e. listen only for local connections. For example, if you have pgbouncer proxying connections and accepting connections on a different port. Or if all user interaction with the DB happens via an application server and webapp.

The address 127.0.0.1 is by convention reserved for the local host. It's for talking to yourself.

There is no practical reason to allow (or block) connections to 127.0.0.1 from outside hosts, because no router will ever send packets to your host with that destination address, and your machine would never respond to ARP requests for that address. Most operating systems would ignore packets destined for 127.0.0.1 coming from external network interfaces as obviously bogus.

So you want to open connections from other hosts. 0.0.0.0/0 on port 5432 is "let anything connect to port 5432". That's an option if you don't want to be more restrictive about who can talk to the server.

Note that you may have to adjust both firewall rules and PostgreSQL's listen_addresses directive. Also possibly pg_hba.conf.

listen_addresses controls which local network interfaces PostgreSQL will accept connection attempts from.

pg_hba.conf controls which remote hosts PostgreSQL will accept connection hosts from, assuming it's even listening.

Firewall rules will stop those connection attempts even reaching PostgreSQL by filtering based on source address, destination port, and other criteria.


Grouping results with left and inner join with zero counts

Build a table with n=1..5 and the comments (positive, etc); lets call
it labels
Get the counts (5 or fewer)
Put together:

SELECT labels.n, labels.comment, IFNULL(x.ct, 0) AS "count"
    FROM labels
    LEFT JOIN ( your big query to get the counts ) x
        ON x.n = labels.n
    ORDER BY labels.n

The principle is "FROM (stuff you want all of) LEFT JOIN (stuff that may be missing some)"


Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

MariaDB 10.3+

Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors.

SELECT 1, t.7
FROM (VALUES (7),(42)) AS t;
 1 |  7
--------
 1 |  7
 1 |  42

You'll notice that there is no support for column_alias in the FROM clause, instead requiring you to use the table_alias and the name of the first row's value for that column. But column_alias are allowed in a CTE's WITH clause,

WITH t(a) AS ( VALUES (1),(2) )
SELECT t.a, t.a AS b
FROM t;
 a |  b
--------
 1 |  1
 2 |  2

Additionally you may able to skirt this by giving the first row a definitive alias.

SELECT 1 AS x ,2 AS y
UNION VALUES (3,4),(5,6);
 x | y
-------
 1 | 2
 3 | 4
 5 | 6

also see examples in https://jira.mariadb.org/browse/MDEV-12172

PostgreSQL permits column aliases in FROM clauses, as show above with t(x), this syntax is also not supported in MariaDB

MariaDB <10.3; MySQL 5.x & 8.x (Workaround)

Prior to MariaDB 10.3, and all versions of MySQL do not support the VALUES expression.

SELECT * FROM ( VALUES (1) ) AS t(x);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (1) ) AS t(x)' at line 1

However, it does support sub-selects with literals.

SELECT * FROM (SELECT (1) AS x) AS t;
-- more simply
-- SELECT * FROM (SELECT 1 AS x) AS t;
+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

PostgreSQL also supports this syntax.

SELECT * FROM ( SELECT 1 AS x ) AS t;
 x
---
 1
(1 row)

However, things get a lot more verbose with this syntax if you're doing multiple rows. Below from MariaDB, also works in PostgreSQL

SELECT * FROM ( SELECT 1 AS x UNION ALL SELECT 2 ) AS t;
+---+
| x |
+---+
| 1 |
| 2 |
+---+

Whereas with VALUES, it's simply VALUES (1),(2)


Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)

If you start your instance using a server parameter file (a binary version of initialization parameter file, spfile), you can extract the initialization parameters to plain-text initialization parameter file (pfile), alter them, and then start your instance with modified memory parameters.

sql> create pfile='myinit.ora' from spfile='spfileORCL.ora';

Modify your memory-related parameters in myinit.ora and start your instance using this new configuration:

sql> startup pfile='myinit.ora';

You can then save your settings back to an spfile with the following command:

sql> create spfile='spfileORCL.ora' from pfile='myinit.ora';

And start your database as usual:

sql> startup;


Specify windows account for SSRS data source in SSDT project

There is no option to set a data source within the client tool to use a different Windows account. This is by design and is consistent with how Microsoft designed most all client tools (e.g. works the same way in BIDS, SSMS, etc.).

Your only option I know of would be to simply not redeploy the shared data source.