I question whether it should be described as "object-relational", really.

That statement refers to PostgreSQL's inheritance features. They are useful, but limited. You can't have foreign keys pointing to a parent table and all children, nor can you have a unique constraint that constrains a parent table and all children. Triggers must be defined on each child, not just the parent. Etc.

Another issue is that Pg's inheritance features don't play well with ORMs. Most ORMs want to map inheritance as decomposition by reference, so that if "A" extends "B", then you'll have a table "A" with all A's fields, and a table "B" with the extra fields and a foreign key back to "A".

PostgreSQL's inheritance doesn't have that foreign key back to A. Instead, each row of "B" has all fields of both "A" and "B". When you query "A", you see the common columns from "B" as well. This confuses ORMs, becaue it means there's no relationship they can see between "A" and "B", so they don't tend to cope with mapping their understanding of inheritance onto PostgreSQL's understanding of inheritance.

That tends to limit use of PostgreSQL inheritance to areas where you're not using an ORM, but areas where inheritance is useful is just where you're most likely to use an ORM.

The inheritance features are quite handy for certain kinds of designs, you just have to accept the limitations. They're a lot faster to use than the typical ORM-style approach of separate tables with foreign keys, for one thing.

Your situation, where you're coding in PL/PgSQL, is one where the inheritance features might prove very useful to you.


Are there reasons to not use SCHEMABINDING on database objects?

I don't see any reasons of NOT using SCHEMABINDING on database objects. Its like safe guarding your objects.

Obviously there are pros and cons associated to it.

If you are not going to create Index Views, then you dont need SCHEMABINDING for views.

Basically, SCHEMABINDING

prevents the views from becoming invalid due to the change in table structure e.g. dropping columns from table.
makes UDF's deterministic - as described at Improving query plans with the SCHEMABINDING option on T-SQL UDFs

The only downside that I see is, it will make changing the table schema a bit difficult, even if it is just a small change e.g changing a NOT NULL column to allow nulls.


As Martin Smith and dezso commented, you can add parentheses to your query to be explicit in your intent since they can change how the where clause is evaluated:

SELECT DISTINCT ID AS IDs
FROM DBTABLE
WHERE ((ID = 1423) AND (Tags LIKE '%Warm%'))
OR ((ID = 2743) AND (Tags LIKE '%Dry%'))
OR ((ID = 4832) AND (Tags LIKE '%Cool%'))

This returns a 3-record result set containing 1423, 2743 & 4832 from your example data.

Parentheses can also make your SQL queries more readable and maintainable.


Cannot start SQL Server 2014 service. Memory Error (resource pool 'internal')

This machine is hosted in a VPS, and technical support says the
  "Driver locked" RAM is assigned on demand, so I can't find why SQL
  Server throws this memory error. Any ideas??

Yes.  Your hoster has "borrowed" your VMs memory and given it to another VM.  The "Driver Locked" memory is a "Baloon Driver" that both VMWare and Hyper-V use to enable dynamic memory allocation to VMs.  That memory appears to belong to the VM, but is actually held by the hypervisor, and may be currently being used by other VMs.

Not all operating systems support actually changing the amount of memory they use without a reboot.  In Windows this capability only appeared in Windows 10/Windows Server 2016.  So the way hypervisors handle this is they install something inside the VM to allocate memory.  Then this memory is actually removed from the VM and is available for other VMs.  Typically they use a driver (as opposed to a user process), commonly called a "Baloon Driver".

So the almost 12 GB are "Driver Locked" by the VPS manager is not actually available.  You need to ask your hoster to give your VM more memory.

It may be the case that the hypervisor is supposed to respond to memory pressure inside the VM adding memory to the VM and reducing the amount locked away in the Baloon Driver.  But if so, this is just not working well enough to enable your SQL Server to start, and they need to bump up the startup memory for your VM a bit.

On the other hand, if you paid for 16GB of ram, you might want to look for a new hoster who doesn't engage in such shenanagans.


Monitoring Log-Shipping for Multiple Databases

After spending considerable amount of time in setting up log-shipping for multiple database and trying to figure out why only one alert job gets created - had no clue on why however one thing became clearer as we kept on configuring databases and jobs were getting over-written that even though there is only one job being retained at SQL Server Agent Job level, it does its job for all databases and alerts us in case any of the underlying database replication fails or has some issue in back-up, copy or restoration.

I am not sure why MSSQL doesn't support having multiple jobs created however its doing its intended job internally. I also have been checking "Transaction Log Shipping Status" report can monitor the status of all databases.

I also checked two tables from msdb database at monitoring server and ensured that log-shipping is working as expected.

Thank you for your support. This could be helpful to somebody who is setting up log-shipping for the very first time and wants to use monitoring server.


If I understand your specifications well, and although I haven't tested this query in the server yet, it should work:

SELECT CO.conDat,
       PA.pesNome                         AS paciente,
       ME.pesNome                         AS medico,
       COALESCE(CO.conDiag, 'indefinido') AS condiag
    FROM  consultas    CO
    INNER JOIN pessoas PA
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;

Since this query JOINS the table consultas with pessoas twice in order to retrieve the pessoas.pesNome column values related to medicos and pacientes respectively, it is important that you look with special attention to the aliases given to the PA.pesNome and ME.pesNome columns (by way of the AS keyword in the SELECT statement), because such aliases are used for the purpose of displaying different column headers in the obtained result set, and thus, ambiguities are avoided, since the meaning of each column, paciente or medico, is clear.

For handling the cases when consultas.conDiag contains NULL, the above query takes advantage of the COALESCE() function which, in this particular case, substitutes NULL values in said column with your custom defined string ‘indefinido’.

Alternately, you can try this other query, which shows a different method for replacing the consultas.conDiag column value when it holds NULL, via the IFNULL() function:

SELECT CO.conDat,
       PA.pesNome                       AS paciente,
       ME.pesNome                       AS medico,
       IFNULL(CO.conDiag, 'indefinido') AS conDiag
    FROM  consultas    CO
    INNER JOIN pessoas PA
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;


What is the most efficient way to exclude records with a particular ID in a MySQL query for random records?

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large?

As for "doesn't work", I say that is false.  RAND() does not repeat after 64K, like some random number generators.

As far as "how large", time the first query below -- if it is "too slow" then the table is "too large".  Then skip to the link at the bottom.

Case 1 Done all at once, this gives you $N random, not repeated, rows:

$array = SELECT id FROM ... WHERE ... ORDER BY RAND() LIMIT $N;

Case 2 If you need to fetch the rows individually, such as with separate requests and minimal "memory" between requests:

$rnd = UNIX_TIMESTAMP();
$offs = $offs + 1;
$array = SELECT id FROM ... WHERE ... ORDER BY RAND($rnd) LIMIT $N OFFSET $offs;

By remembering $rnd and $x (eg passing via a URL or SESSION), you can get the "next" one in a predictable sequence.

Caveat 1:  If rows are inserted/deleted, this sequence can mess up.
Caveat 2:  If the table is 'large', the SELECT could be costly.

8 Faster ways to get random rows.  None is 'perfect', and each depends on something not in evidence, such as whether there is an auto_increment id and whether that has gaps.


What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration?

There is a one worker per table limitation, so the maximum number depends on the number of tables you are migrating.

Notes:

A decent maximum is the number of cores on your system
If you specify a large number but have fewer tables, then only the needed threads will be launched
If the network saturates, like maybe 20 tables with 20 workers, then the copy may actually be slower

So, there isn't a simple answer but hopefully this helps. For additional details, see the related wbcopytables documentation.


For all engines, a UNIQUE index permits multiple NULL values for
  columns that can contain NULL.

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

It is easily testable, just try to insert multiple NULL values in dummy table.


Gun-against-the-head

Require more heads. This solution scales linearly. Every gun you need to protect against requires one more disinterested head. Consider methods of discrete head positioning. The more work you spend positioning the heads, the more work others have to spend positioning guns. Hide heads for extra obfuscation.

Security is O(n) with heads: MySQL is equally secure with a rock, which is equally as secure as PostgreSQL. That's the best position in a security analogy MySQL has ever been in.

Software Stack Security

Network Encryption, use SSL with an AES cipher. Consider layering DB authentication with network authentication using openswan/strongswan.
Disk Encryption, use eCryptfs
Authentication, use GSSAPI

Database Security

PostgreSQL supports row-level security. I definitely suggest reviewing the DDL security docs. This protects the database against unbridled app-level access. Perhaps all of your data doesn't need to be read by every app? You can even REVOKE or GRANT access to specific columns on the table, see the docs for more info (search for column).

Special notes

You can store encrypted data in the database too. This gets the DBA admin out of the problem entirely. Then the gun-against-the-head switches points of failure to the end-user. This may or may not be a good thing. Certainly, I prefer them to risk the gun-against-the-head scenarios. I just run it.


Under what circumstances would these 3 queries return different number of results?

The first query may return rows from batches where no payment-lines exist, ie. an outer join.


What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?

As you're going to have new id values, it's not going to to be easy to do a straight copy.

I might suggest copying the data to a set of staging tables with no foreign keys defined. Then, in the staging tables, increase the existing ID values for all rows in a given table by a fixed amount that will guarantee they are higher than the max value in the destination tables (or you could change them to negative values if you haven't already used those) - you'll need to apply the same update to the foreign key fields in any child table at the same time.

Then you can use SET IDENTITY_INSERT {TableName} ON; to allow you to insert the updated records to the destination table. You should turn that off again once you are done.

SQL Server will automatically take account of the new rows you have inserted to re-seed the Identity values for new records moving forward so once you've inserted your records all is good.


How to combine 2 SQL statements into one?

A quick and dirty way would be to have a script for the next X years.
Something like:

IF db_id('ctx_2015_log') is not null
EXECUTE('
BEGIN
    USE select quotename(db_name(db_id(''ctx_2015_log'')))
    DBCC SHRINKFILE(ctx_2015_log)
    BACKUP LOG ctx_2015 TO DISK=''NUL:''
    DBCC SHRINKFILE(ctx_2015_log)
END
');

IF db_id('ctx_2016_log') is not null
EXECUTE('
BEGIN
    USE select quotename(db_name(db_id(''ctx_2016_log'')))
    DBCC SHRINKFILE(ctx_2016_log)
    BACKUP LOG ctx_2016 TO DISK=''NUL:''
    DBCC SHRINKFILE(ctx_2016_log)
END
');

You can build this statement with:

DECLARE @i int =2015
DECLARE @ti varchar (4)
DECLARE @command varchar (max)

WHILE @i<2020
BEGIN
    SET @ti=CONVERT(varchar(4), (@i))
    SET @command=
        'IF db_id(''ctx_'+@ti+'_log'') is not null
        EXECUTE(''
        BEGIN
            USE select quotename(db_name(db_id(''''ctx_'+@ti+'_log'''')))
            DBCC SHRINKFILE(ctx_'+@ti+'_log)
            BACKUP LOG ctx_'+@ti+' TO DISK=''''NUL:''''
            DBCC SHRINKFILE(ctx_'+@ti+'_log)
        END
        '');
        '
    PRINT @command
    SET @i+=1
END


Simple insert in stored procedure runs twice

I created a test DB and table, I adjusted the schema to make it a bit easier and also commented out the set in the proc.

USE [Test]
GO

/****** Object:  Table [dbo].[news]    Script Date: 2/4/2017 12:21:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[news](
    [id_news] [int] NULL,
    [news_owner_id] [int] NULL,
    [news_owner_type] [bit] NULL,
    [news_cat_id] [int] NULL,
    [news_producer] [nvarchar](1000) NULL,
    [news_title] [nvarchar](1000) NULL,
    [news_sutitr] [nvarchar](1000) NULL,
    [news_text] [nvarchar](1000) NULL,
    [news_date] [nvarchar](128) NULL,
    [news_time] [nvarchar](16) NULL,
    [news_link] [nvarchar](512) NULL,
    [img_uri_type] [tinyint] NULL,
    [img_uri] [xml] NULL,
    [newsContentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I'm going to answer 2 first. I ran the proc once, it inserted one time. I looked through the execution plan and it shows a straight forward insert. You can see it here: Standard run. I then set your error handling to 0, just to see the plan with that being triggered. Also straight forward. Error handling triggered. I would run SQL Profiler when you see it insert two records or set up sp_whoisactive to catch the plan when it does multiple inserts. My guess is that it's being executed twice.

For question 1, it looks fine to me. You could put in more exact error handling,  but overall I would not say it's bad. I really like this template by spaghetti dba.


Time it and see. Which is fastest will depend on the balance of CPU to disk I/O on your system. If CPU is abundant and the disk is slow, a -Fc dump will be faster. If CPU is very limited and the disk is fast, an SQL dump might be faster.

I strongly recommend using -Fc custom format dumps anyway. They're much more flexible, and while you can convert a custom format dump into an SQL format dump you can't do the reverse easily.


How can I synchronize data between two RDS MySQL databases

RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available.  That would have been nice. :(

If your dump download took 3 hours and your upload took 4+ hours, the good news is, that you only need the 4+ hours, because you can do them simultaneously... and depending on whether you did that testing from EC2 or from your own network, possibly faster.

What I suggest is tying mysqldump to mysql using a pipeline, so that you are simultaneously reading from the old server and writing to the new server.  To make this work properly, though, we need to add a couple of twists.  And then a couple of extra twists make it almost fun.

I would think your ideal scenario would be to spin up a disposable linux machine in EC2 in the same region, of you don't already have one available... and preferably the same availability zone as your servers, because this is going to give you the maximum potential bandwidth among the 3 components involved and that's one less thing to slow you down.

Next, on the source server where the data is coming from, you need to issue SET GLOBAL NET_WRITE_TIMEOUT = 600;.  Of course, you can hardly SET GLOBAL anything on RDS, so you have to change this via the RDS Parameter Group.  NET_WRITE_TIMEOUT is the amount of time MySQL Server will wait before aborting an attempt to write to a client that is blocking.  When you're piping the output from a dump directly into another server, the destination server is going to periodically block, and sometimes for longer than the default 60 seconds.  There's a companion variable, NET_READ_TIMEOUT, that you might want to set to a similar value, although I don't think this variable has any relevance in a dump environment.  This will prevent the source server from timing out when the destination server takes too long to do one of the massive inserts that mysqldump builds.  If you have MyISAM tables, you need to set it even higher, because the ALTER TABLE ... ENABLE KEYS can take forever to run after a table is fully restored on the target... but hopefully, you don't.

The value "600" (10 minutes) is somewhat arbitary, but it's what I use for operations like this.

As mysqldump is reading from a remote server and writing to a pipe into the mysql command line client, when mysql sends a big insert to the destination, it stops reading from the pipe, which blocks mysqldump from writing to the pipe, which in turn blocks mysqldump from reading from the socket until the destination server is ready for more data.  In addition to the timeouts this causes, it also wastes a lot of time for several different reasons... mysqldump spends a lot of time discovering table structures and starting its SELECT * queries and we want to avoid all of that wasted time.  The ticket to this is setting up a double buffer using the unix dd utility.

If we invoke dd specifying a large output block size without specifying an input block size, it will read from its input with a small block size (default 512 bytes) until it has read the amount of data specified by its output block size, then block its input and flush that data out its output in a single massive write.

dd obs=16384K

This will cause dd to accept 16 MB of data as fast as you can feed it, and then, once it can flush that block to its output, it will then accept more.

If we stack two of them, we get a double buffer. :)

mysqldump [options] | dd obs=16384K | dd obs=16384K | mysql [options]

Using this construct, we can always keep between 16 and 32 megabytes of data in RAM, in flight, at all times, between the origin server and the destination server... so we are essentially guaranteed that as soon as the destination server is ready for data, we have data immediately ready for it, with no waiting for the origin... and the origin can keep itself busy keeping our buffers full.

16 MB isn't really a large mount of data, so even a larger buffer might be desirable, but we don't want to make these too big, because when they're both full, the origin has to wait until the destination can read the entire 16 MB from the 2nd buffer before the 1st buffer can flush into the 2nd buffer which causes the 1st buffer to unblock and start accepting more data from the origin... However, we can keep more data in flight if desired by chaining even more copies of dd.  They will use 100% of the specified memory but they use very little CPU.  I usually chain at least 4 of them.

Incidentally, this same hack will also get you a faster mysqldump-pipe-to-gzip (bzip, xz, etc.) if you stick some dd in between there, because it significantly increases the likelihood that you'll always have data available for your compression program to crunch on, keeping the processor utilization of the compression program maxed, which means a faster completion because the crunching gets done in less wall-clock time due to full processor utilization.

You should also probably use the --compress option on both mysqldump and mysql so that the data is compressed over the wire in both directions.  Compression isn't free, but this compress is just zlib inflate/deflate so it's not a very significant expense in CPU itself.  The slowest part of an operation like this, as you noted, is on the destination, and all the destination has to do is the less-costly decompression.

Add the --verbose option to mysqldump so that you can watch the data flowing.

And, for a little extra sweetness, a couple of throughput meters:

$ sudo apt-get install pv

This will allow you to keep an eye on the proceedings with output on your terminal resembling this example:

 inbound:  128MB 0:07:02 [    0B/s] [ 364kB/s]
outbound:   96MB 0:06:00 [ 154kB/s] [ 334kB/s]

If you know approximately how big your dump is, you can use additional options to give pv a hint of that size and it will calculate progress in percent.

Your final command looks like this (as a single line, formatted here as multiple lines for clarity):

mysqldump --compress --verbose [other options] |
pv -pterabc -N inbound |
dd obs=16384K |
dd obs=16384K |
dd obs=16384K |
dd obs=16384K |
pv -pterabc -N outbound |
mysql --compress [other options]

The [other options] of course are the usual authentication credentials and other options you used with mysqldump and with the mysql client when you did your previous restore.

If you have multiple schemas or want to otherwise go to the trouble of running parallel copies of mysqldump | mysql it might be worth a shot to decreasing your total time, as well... restoration time is a product of CPU and IO capacity on the destination server, and with RDS it seems like you might see the combined throughput of two parallel threads be faster than 1 thread.

I think RDS also has an official, supported option to temporarily disable binary logging while restoring a dump file.  You might explore that as well, as it might improve restoration time... so long as you turn it back on when you're done.


UPDATE from subquery updates rows that shouldn't be updated

Use a FROM clause to join to more tables:

UPDATE table_1 t1    -- alias optional
SET    column_x = t2.rename
FROM   table_2 t2
WHERE  t2.rename IS NOT NULL  -- assuming you actually meant t1
AND    t2.id = t1.id;

This joins rows from table_2 in a CROSS JOIN - filtered by the condition t2.id = t1.id which makes it an INNER JOIN effectively.

This way, rows without a match in the other table are not touched at all - unlike your query with a correlated subquery.

SQL Fiddle with extended demo.

You must also understand the difference between NULL, empty string and "no row" (which results in NULL in your correlated subquery).

Details in the manual.


I would use a server level trigger on CREATE DATABASE.

Something like this:

CREATE TRIGGER [CreateDatabase_OwnerMapper]
    ON ALL SERVER
    WITH EXECUTE AS 'sa'
    FOR CREATE_DATABASE
    AS
    BEGIN

    SET NOCOUNT ON;

    DECLARE @DatabaseName NVARCHAR(128) =
    (SELECT CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVARCHAR(128)));

    DECLARE @SQL NVARCHAR(MAX) = '';
    SET @SQL = 'ALTER AUTHORIZATION ON DATABASE::' + @DatabaseName + ' TO sa';

    EXEC @SQL;

END

This will fire off a trigger to automatically change the owner whenever the CREATE DATABASE command is issued.

You could grant the db_owner yourself with the trigger potentially. I do this on our debt environments.


I think the inner query is right.

The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state.

Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP BY, but that will give the wrong result. It is necessary to do this in two steps. In the outer query just deal with states:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Then join this back to the inner query on the MAX() value:

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

In other words, here are the most numerous invoices in each state. Which vendors have these many invoices?

If two vendors have the same number of invoices in a state there will be two rows output.

Since the inner query is repeated verbatim it would be better presented as a non-recursive CTE.

SQL Server 2012 has a number of windowing functions built in. The most useful one here is RANK (). It does just what you think it should do.

Select
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

This eliminates all of the second-order dataset alignment on derived aggregate values required above. It is much easier to read and understand, and hence to maintain.


No it wont backfire. If there is confusion, you can read the manual to see the effect of caching. It means first time you select from sequence, it will cache next 999999 values in sga. It also means, for next 999999 sequence nextval calls you will not be updating seq$ table.

The downside of this is, if your instance or db crashes, those cached (and unused) values are lost. So if you need to have sequential values with no sequence loss, this will not be helpful. Along with caching, you perhaps want to use ORDER option to ensure sequences will be distributed in a ORDERED manner.


Mongo sharding issue with chunk split and Data transfer

You really only have 2 choices when a primary is still processing deletes from previous migrations (which is why you are getting the failure to engage error):

Wait for the deletes to finish
Step down the primary of that shard (assuming it is a replica set)

The first action may take a long time if the shard in question is under heavy load, but it is the safest way forward.  The second option will cause the primary to step down to secondary and terminate the background threads for the deletes - if you have only a single node then your only option is a restart, and downtime.  Either way, this will allow the new primary (or restarted node) to accept migrations, but it will create orphans which you will need to manually clean up later.

If you are in any doubt, then the best thing to do is wait for the deletes to finish.


In MySQL, does the order of the columns in a WHERE clause affect query performance,why?

Maybe because the results from the first "form" of the query are already cached ?
As lyxing said, post the execution plan. It will be interesting to see how the optimizer chooses the execution plan in both cases.


MySQL DB structure with Main category and several nested sub categories

I would generalize the design, I.e. instead of a motorcycle_brand table, You would want a category_brand table, and instead of a motorcycle_model table, you would have a category_brand_model table with an fk on the category_brand pk. This allows for several advantages:

Adding new product categories in the future requires only adding records, not the addition of new tables.
If formalizes the relationship hierarchy of category-brand-model instead of relying on shaky and maintenance-intensive query design to do so.
The queries would be very, very simple to write.

To do this, you would combine each of the three category brand tables you have now and create a new table categoy_brand with an fk on catid.


The fact that you are finding it difficult to explain indicates that there is an issue with the design, the expectations, or the perceived requirements.

Are Subscriptions automatically paid for, such as through a credit card?  Is that why an Invoice seems unnecessary?  Or some other reason.

Your current state is:

Customers can purchase your products in one of two ways:

Orders require Invoices. Perhaps you need to bill them so as to be paid.
Subscriptions have no Invoices. Perhaps subscriptions are automatically paid through a credit card, PayPal, etc.

Since a Customer decides how to interact with you, that Customer may have several Subscriptions and also may place Orders. And I would think that the customer could cancel the Subscription whenever he wanted to.

Therefore, both you and your customer have reason to keep a complete trail of orders and fulfillments (whether Invoice or Subscription).  That means that you do potentially want an Invoice of some sort even for Subscriptions.

Here are two ways to handle this, namely treat them as the same thing or as two different things.

Subscriptions create an Invoice for the fulfillment of a subscription instance.  The subscription Invoice could have a different scheme (e.g. SUB-Number-Date) for identifying the subscription Invoice than you would use for an Invoice for an Order (e.g INV:InvoiceNumber).  [I would guess that the subscription Invoice would be marked as already paid.]
Create a Subscriptions log with a header for the subscription and a log to track each fulfillment.  That allows you to track the details of each time Customers have their Subscriptions fulfilled. In the case that a subscription is cancelled early, it could be used to refund the customer the unused sum.

How your organization views your Customers (person or businesses) will help you determine what is the best approach for your business.

What would I do?  I would tend would separate the Order Invoice from the more complex Subscription Invoice.  When needed a summary of the totality of Orders, Subscriptions, and (returns, reshipments, etc) can be produced for your company and for your customer.


Designing a dimensional DB off a normalized source that already implements SCD's

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a table in the DSV and immediately move to hierarchy design, cube relationships etc.

I'd recommend moving to the DDS with the caveat that you phase out the NDS. For the simple reason that you mention with the SCD Type II - there's no reason to duplicate all of that data, ETL, and code base. Keeping both leads to an overly complex solution with a lot of maintenance and risk - this is the primary Big Mess™ you need to avoid.

Here's the justification:

The DDS sounds like a Kimball dimensional design that will be easily
understood and maintained by any new hires and contractors
There are lots of proven designs for schema and ETL that go along with this, making it easier to design and maintain
Easier to deal with large data quantities via options like fact partitioning and column store indexes
Easier DW queries in the future for yourself and power users
Simpler overall design

The alternative design I'd suggest only if you have small quantities of data, no significant expectation of increases, and no capacity to switch to the data warehouse. This suggestion ultimately includes a lot of the design work that would otherwise go into the DW and as such is similar to keeping the NDS and creating the DDS:

You can create your cubes with queries manifested as Views or hard-coded into the DSV.

It sounds like you overcame a lot of hurdles and created a great functional solution with the NDS. Unfortunately what it doesn't do are two important things that dimensional modeling provides: simple query patterns and easy translation into multidimensional analytics. Thankfully, a lot of the ETL design is likely useful as a template or starting point for loading a different table structure.


I'm going to make a wild guess and say that your database is set up to use FULL recovery.  You have never taken a log back up (let alone a full one) based on the image above.  If this is the case then you log file is growing and growing and can't clear out because you haven't taken a backup.

If you have the room take a full backup, then a log backup and try your shrink again.  Should work fine.

Worst case (and I do mean worst, particularly if this is a production database) is to set the database to SIMPLE recovery, shrink and then take a backup.  This should work even though you don't have enough space for a shrink normally.

At this point make sure you set up regular backups for your database.

You can go here for more detail.

EDIT:

Can you tell us what % free the log is vs the data file?
Also you might look at the log_reuse_wait_desc in sys.databases.

SELECT name, log_reuse_wait_desc FROM sys.databases

And last but certainly not least have you tried the TRUNCATEONLY option of shrink?

DBCC SHRINKFILE (Adventureworks2008R2_Log, TRUNCATEONLY)


MySQL server taking multiple seconds to run COUNT(*) query using an index on a MyISAM table

The problem appears to have been that the cardinality of my indices was NULL. I am unsure why the indices would revert to this state when they were apparently fine yesterday.

I discovered this problem by running SHOW INDEX FROM tickets.

I ran ANALYZE TABLE tickets. Now the EXPLAIN looks subtly different-- the rows have dropped to 1:

mysql> explain select count(*) from tickets WHERE id = 214;
|  1 | SIMPLE      | tickets | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index |


Creating range for special fields in SQL Server

Assuming the digits to the left of the + are measured in units of 100, you could perhaps create constraints like this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * 100 + StartPostPlus) > 100*100+50.3)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * 100 + StartPostPlus) < 150*100+20.65)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * 100 + EndPostPlus) > 150*100+60.3)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * 100 + EndPostPlus) < 500*100+20.75)
);

However, this doesn't allow dynamic constraints, all rows in the table will be constrained to those measurements, which may in fact be exactly what you need.

If you need dynamically adjusting constraints for each independent row, you could do this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * PostUnits + StartPostPlus) > MinStartPost * PostUnits + MinStartPostPlus)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * PostUnits + StartPostPlus) < MaxStartPost * PostUnits + MaxStartPostPlus)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * PostUnits + EndPostPlus) > MinEndPost * PostUnits + MinEndPostPlus)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * PostUnits + EndPostPlus) < MaxEndPost * PostUnits + MaxEndPostPlus)
    , MinStartPost int NOT NULL
    , MinStartPostPlus decimal(38,3) NOT NULL
    , MaxStartPost int NOT NULL
    , MaxStartPostPlus decimal(38,3) NOT NULL
    , MinEndPost int NOT NULL
    , MinEndPostPlus decimal(38,3) NOT NULL
    , MaxEndPost int NOT NULL
    , MaxEndPostPlus decimal(38,3) NOT NULL
    , PostUnits int NOT NULL
);

INSERT INTO dbo.SurveyData (StartPostStation, StartPostPlus, EndPostStation, EndPostPlus
    , MinStartPost, MinStartPostPlus, MaxStartPost, MaxStartPostPlus
    , MinEndPost, MinEndPostPlus, MaxEndPost, MaxEndPostPlus
    , PostUnits)
VALUES (120, 49.2, 175, 80.5  --measurements
    , 100, 50.3, 150, 20.65   --valid start post range
    , 150, 60.3, 500, 20.75   --valid end post range
    , 100); --units per post

Selecting data would look like this:

SELECT StartPost = CONVERT(varchar(10), sd.StartPostStation) + '+' + CONVERT(varchar(50), sd.StartPostPlus)
    , EndPost = CONVERT(varchar(10), sd.EndPostStation) + '+' + CONVERT(varchar(50), sd.EndPostPlus)
FROM dbo.SurveyData sd;

Results, like this:

+------------+------------+
| StartPost  |  EndPost   |
+------------+------------+
| 120+49.200 | 175+80.500 |
+------------+------------+

The post columns have been split into two to enable us to use the correct data types to store numeric data.  If we try to store 175+80.50 in a single field, we end up using a varchar(x) column, which allows all kinds of possibilities for bad data, such as tee+27.-1, which are very difficult to comprehensively prevent.  So, we store the station in one column, and the offset from that station in the next column.  When presenting this data on screen or on reports, to humans, we'd use the concatenated version shown above.


Why do I have to change Media Sets to create a Full Backup to a new file and what does that mean?

It's quite a long topic and for the most part it relates to technology that is irrelevant in this day and age (more specifically: tape storage). However here is a quick summary:

media set
An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.
media family
Backups created on a single nonmirrored device or a set of mirrored devices in a media set
backup set
The backup content that is added to a media set by a successful backup operation.

In short, if you create a new media set on a device, then yes, you will lose all of your previous backups:

Creating a new media set
To create a new media set, you must format the backup media (one or more tapes or disk files). The formatting process changes the backup media as follows:

Deletes the old header (if any), effectively deleting the previous contents of the backup media.

Formatting a tape device deletes all previous contents of the currently mounted tape. Formatting a disk affects only the file that you specify for the backup operation

Writes a new media header on the backup media (tape or disk file) on each of the backup devices.

For more information, checkout the Media sets, media families & backup sets documentation over at Microsoft Docs.


"Site Offline" MySQL server failing to start and stop

OMG !!! Look at the output of df -h

Size: 20G Used: 19G Available 0 Use Percent: 100%

YOU HAVE NO DISK SPACE !!!

Get more diskspace freed up or have linode increase diskspace.

According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

If you have an error file like /var/log/mysql/log, clear its contents like this:

echo -n > /var/log/mysqld.log

If the error file in /var/lib/mysql/my-machine.err then do this:

echo -n > /var/lib/mysql/my-machine.err

Give it a Try !!!


PostgreSQL 9.2 number of days in a tstzrange?

There are probably better ways to do this, but here are a few functions to return days or intervals from a tstzrange/tsrange input parameter.

CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_days(TSTZRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION extract_days(TSRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

WITH tzr AS (SELECT tstzrange('2013-10-01 10:00-07', '2013-10-03 05:15-07') AS dttz, tstzrange('2013-10-01 10:00', '2013-10-03 05:15') AS dt)
SELECT  extract_interval(dttz) as interval_with_tz,
        extract_interval(dt) as interval_no_tz,
        extract_days(dttz) as days_with_tz,
        extract_days(dt) as days_no_tz
FROM tzr;

                      tstzrange                      | interval_with_tz | interval_no_tz | days_with_tz | days_no_tz
-----------------------------------------------------+------------------+----------------+--------------+------------
 ["2013-10-01 07:00:00-07","2013-10-01 07:15:00-07") | 00:15:00         | 00:15:00       |            1 |          1

Edit: I have put up these functions on the Postgresql Wiki so that others can use or edit them if more efficient versions are developed.
https://wiki.postgresql.org/wiki/Extract_days_from_range_type


BULK INSERT into VARCHAR(MAX) in my opinion, then you shouldn't have errors and from there you can manipulate the data as needed and run standard SQL to make sure it fits within your requirements before actually placing it in the destination table. Just a suggestion but it's how I've done it in the past.


Ports necessary for Oracle 11g Replication across a firewall?

I don't think any other ports than the listener ports are required.
I suspect that the ports need to be open on your source AND target database/firewall, allowing both incoming and outgoing connections. Also, the host running RMAN has to be able to reach the listener ports, but that's probably default.

If tnsping works to both servers from the host you are running RMAN from, i think you should be all set.


Using one supertable Tag will give you less tables in your schema, but you will basically lose guarantees about referential integrity. If one column in another table must have one 'colour' value, you add a referential constraint to the table 'colours' (one of your dictionaries). If you have a big one, you can have one column that should have one colour to have a value 'wide', because this value is also in your dictionary, even if it is not a colour.

With one super-table, you move the responsibility of guaranteeing referential integrity from the database to your application (or middleware). If you do so, you should make sure there is no other way to change values in your database than through your application.

I don't see any advantage on having an inheritance hierarchy. Even if your structures look all the same, their meanings are not at all the same. You could gain some operational advantage somewhere (like having only one procedure to do the 'maintenance' of the Tag table), but I guess you could do the same using just table names as parameters to a function call or method somewhere in your application.

I'd not really care much about performance. Having a number of small 'dictionary' tables (lookup tables) won't harm you.


I solved it. I found the RPM's for GDAL, GEOS, and Proj4. They are located here: http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/

Since they are not packaged with PostgreSQL 8.4, you have to install them one by one. I hope this helps someone in future.


How to insert and update bulk data From one DB to another

It's this part that returns the error (inside your IF NOT EXISTS):

Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)

There is no correlation between the inner and outer query. A possible rewrite is along these lines:

Select emp_id  From DB1.dbo.Emp_Master where emp_code IN (Select pk_emp_code From DB2)

Moreover, the check you're doing at the very beginning of your code will skip inserting all the rows if any of them matches.

I suppose that what you want to do here is an upsert (if matched updated, if not matched insert). You can do it with the MERGE statement or with something like this:

IF OBJECT_ID('tempdb..#updatedRows') IS NOT NULL
    DROP TABLE #updatedRows;

CREATE TABLE #updatedRows (
    emp_code int PRIMARY KEY CLUSTERED
);

UPDATE DB1.dbo.Emp_Master
SET emp_name = first_name+ '' + last_name,
    emp_dept = dept.dept_desc,
    emp_desg = desg.desig_desc,
    email_id = email,
    emp_mob = office_phone,
    emp_dob = REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj = REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
OUTPUT INSERTED.emp_code INTO #updatedRows
FROM [DB2]
WHERE emp_code = pk_emp_code;

INSERT INTO DB1.dbo.Emp_Master(
    emp_code,
    emp_name,
    emp_dept,
    emp_desg,
    emp_mob,
    email_id,
    emp_dob,
    emp_doj
)
SELECT
    pk_emp_code,
    first_name + '' + last_name,
    dept.dept_desc,
    desg.desig_desc,
    office_phone,
    email,
    REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
FROM DB2
WHERE pk_emp_code NOT IN (
    SELECT emp_code
    FROM #updatedRows
);


Changing master for channel gives syntax error

You're looking at the documentation for MySQL 5.7, but you're running MariaDB 10.1, and replication channels happen to be a feature that is implemented differently in MariaDB.

From the MariaDB documentation on multi-source replication:

  You specify which master connection you want to work with by either specifying the connection name in the command or setting default_master_connection to the connection you want to work with.

So in your case you could try:

CHANGE MASTER 'master-203' TO MASTER_HOST='192.168.0.203',
MASTER_USER='rep',
MASTER_PORT=3306,
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=107;

(Disclaimer: I've not tried this in practice.)


Why SQL Server replication related jobs do not show up under the jobs folder in SSMS?

In replication their are 3 Database/Servers you have:

Publisher  : where the data comes from
Distribution  : handles tracking and packaging of data to be sent to the subscriber
Subscriber  : receives the data

Depending on the type of replication you have setup, the replication jobs may be running at a different location than you expect, ie:

Under Pull replication, there will be jobs running on the Subscriber to pull the data from the distribution database vs Push replication, the job will be running on the Distribution server.

For more info see MSDN: Replication Agents Overview


How to copy a Postgres database within the same DB cluster?

The fastest way to copy a complete database within the same DB cluster is CREATE DATABASE with a TEMPLATE. This just copies a whole DB on the file level.
CREATE DATABASE master_db_copy TEMPLATE master_db;

You must be superuser or owner of the template (master_db in the example) - unless it's marked as dedicated template in pg_database.datistemplate.
And the template cannot have concurrent connections to it. Dedicated template databases make sure of this by setting pg_database.datallowconn. You might use pg_terminate_backend() to close connections. See:

How to clone a test database from a production one in one single action?

The manual:

Two useful flags exist in pg_database for each database: the columns
datistemplate and datallowconn. datistemplate can be set to
indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database can be cloned by any user
with CREATEDB privileges; if it is not set, only superusers and the
owner of the database can clone it. If datallowconn is false, then
no new connections to that database will be allowed (but existing
sessions are not terminated simply by setting the flag false).
datistemplate = true. [...]


The documentation explains it very well:

  An Oracle database server consists of an Oracle database and an Oracle
  instance. Every time a database is started, a system global area (SGA)
  is allocated and Oracle background processes are started. The
  combination of the background processes and memory buffers is called
  an Oracle instance.

An oracle database is the physical files that make up the database itself (control files, data files etc) - Documentation link.

A database is associated with one or more instances, with multiple instances making a RAC setup - Documentation link. As far as your second question is concerned, multiple instances of a single database is a RAC setup.


Database Tuning Advisor error: ...% of consumed workload has syntax errors

The reason for the issues above was that I selected the "Showplan Statistics Profile" option under the "Performance" category on the Event Selection tab in SQL Server Profiler. Once I unchecked it and generated a new trace file workload, all the issues listed above had been resolved.


how to find out the log backups that I need to restore in order to bring log shipping from standby to online?

this script is to be run on server1 and gives me the latest log backup that is available

--------------------------------------------------------
--the latest backup for each database
--filtered by backup type
--------------------------------------------------------
declare @backup_type CHAR(1) = 'L' --'D' full, 'L' log

                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name,
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B

                    LEFT OUTER JOIN msdb.dbo.backupset A
                                 ON A.database_name = B.name
                                AND A.is_copy_only = 0
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name

and this script is to run on server2 and gives me the latest transaction log backup that has been restored:

-----------------------------------------------------------------
-- get the latest transaction log backup restored
-- modified from
--http://www.sqlservercentral.com/scripts/Backup/127480/
-----------------------------------------------------------------
SET NOCOUNT ON
SELECT
     rh.*,
    destination_database_name
    ,bmf.physical_device_name
    ,restore_date
FROM        msdb.dbo.restorehistory rh
INNER JOIN  msdb.dbo.backupset          as bs   ON bs.backup_set_id = rh.backup_set_id
INNER JOIN  msdb.dbo.backupmediafamily  as bmf  ON bs.media_set_id  = bmf.media_set_id
WHERE restore_history_id IN
    (
    SELECT MAX(restore_history_id)
    FROM msdb.dbo.restorehistory
    WHERE restore_type = 'L'
    AND destination_database_name IN
        (
        SELECT DISTINCT destination_database_name
        FROM msdb.dbo.restorehistory
        )
    GROUP BY destination_database_name
    )
ORDER BY rh.restore_date DESC


How to Backup Remote Database to FTP server

You have two processes here that are discrete,

Dumping the database.
Moving the dump to an FTP server.

There are numerous ways to accomplish both of these. You'll have to ask a more specific question about either one.

FTP is an old antiquated transfer protocol that's highly insecure and you should never use it unless you absolutely must. Instead, consider using something like rsync over SSH. Microsoft has jumped on SSH with the latest version of Windows 10 providing an install option from the Windows Store, and it's likely the most secure method of doing what you're after: if you have control over both the server and the client, I would be using that method for transfer.


This is pretty easy to test.
USE tempdb;

CREATE TABLE dbo.d
(
    col varchar(1)
);

INSERT INTO dbo.d (col)
VALUES ('a')
    , ('b');
GO

Enable "actual" execution plans, and run this:
SELECT *
FROM dbo.d
WHERE d.col IN ('a', 'b', 'a', 'b', 'c');

The results pretty clearly show that SQL Server eliminates duplicate items from the IN (...) clause before executing the query.

Be aware, if the IN (...) contains a sufficiently large number of duplicated items, the query may perform more poorly than if those duplicates weren't present.  Causes for the poor performance might include:

The client-end might have difficulty compiling the list of items for the IN clause.
The time spent by the query optimizer will obviously increase somewhat with more items in the IN clause.
If the query is being sent over the network, a long list of items in the IN clause may result in higher latency if multiple TCP packets need to be compiled, sent, and received.

The items listed above will be compounded if the query is being sent to the server very frequently.
If the list is big enough, the size of the text alone can cause performance issues in other ways, such as compilation time (just parsing the thing) and sending the query over the wire.  So just because the execution plan doesn't show it, doesn't mean those additional values can't have an effect on performance.


Increasing the number of tempdb files question about initial size

If you are going to bounce the server then nothing to do.

Just define the files and the initial size and they will be built with the initial size when you restart the service.

It does not shrink as nothing is retained in a log file.


SQL query to aggregate data from three tables

You need to get the maximum address_id per user_id

SELECT user_id,MAX(address_id) address_id
FROM users_addresses GROUP BY user_id;

Make that query a subquery and join back to the other tables

SELECT u.first_name
 , u.last_name
 , u.username
 , u.password
 , a.user_id
 , a.street
 , a.email
 , c.name
FROM
(SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id) ua
INNER JOIN users u           ON ua.user_id    = ua.user_id
INNER JOIN users_addresses a ON ua.address_id = a.address_id
INNER JOIN countries c       ON a.country_id  = c.country_id
ORDER BY u.last_name,u.firstname
;

Give it a Try !!!


Need to group by column while ignoring some text format

The expression

regexp_replace(the_column, ' +.*$', '')

will remove everything starting with the first space character up until the end of the string. So you just need to group by that expression:

with test_data (sport) as
(
  values
    ('Baseball'),
    ('Football'),
    ('Baseball [a]'),
    ('Football [a] [b]'),
    ('Basketball')
)
select regexp_replace(sport, ' .*$', ''), count(*)
from test_data
group by regexp_replace(sport, ' .*$', '')

This will return:

regexp_replace | count
---------------+------
Baseball       |     2
Basketball     |     1
Football       |     2

SQLFiddle: http://sqlfiddle.com/#!15/d41d8/3379

Edit

To deal with legitimate multi-word sports there are two options:

remove everything after the first [: regexp_replace(sport, ' \[.*$', '')
remove all characters that occur between [..] and then trim the result: trim(regexp_replace(sport, '\[.*\]', '', 'g'))


How to search Query Store for queries with multiple plans?

You can search the query store plan.  The query below returns all the query_ids with multiple plans.  You can then use the Tracked Queries report to look at it with the GUI.  You will probably want to use the configure option to extend the time interval.

Select query_id
, COUNT (plan_id) as 'Plan_Count'
from sys.query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2

If you have exported the Query store data to another database as described here removing the leading sys. and the query runs on your exported data.

Select query_id
, COUNT (plan_id) as 'Plan_Count'
from query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2

Exporting the data and adding indexes, can have a huge impact on query cost.  In the example below my exported data is in seperate database that also has Query Store enabled.  The top query with from sys.query_store_plan returns results from the databases Query Store, while the second query with from query_store_plan returns results from the table that was created when I exported the data to it.


I read that this is not good practice because function is called 'zilion' times and it have bad impact on performance.

While CROSS APPLY can be useful in some cases, I don't expect any difference in performance between calling the function in WHERE or CROSS APPLY in the specific case. If the table has a million rows (and columns C and D possibly a million different values), a million times the function will be called. How can it be otherwise?

  I tried to rewrite it with CROSS APPLY.

Here's how:

SELECT
    t.A,
    t.B,
    ca.Fc,
    ca.Fd,
    dbo.Func(t.E) AS Fe
    t.F,
FROM abcdef AS t
  CROSS APPLY
    ( SELECT
          dbo.Func(t.C) AS Fc,
          dbo.Func(t.D) AS Fd
    ) AS ca
WHERE 0 = ca.Fc + ca.Fd ;

or:

SELECT
...
FROM abcdef AS t
  CROSS APPLY
    ( SELECT
          dbo.Func(t.C) AS Fc,
          dbo.Func(t.D) AS Fd
      FROM (SELECT NULL) AS dummy
      WHERE 0 = dbo.Func(t.C) + dbo.Func(t.d)
    ) AS ca ;

Again, I don't think this will have any effects on efficiency.


I am not sure of the exact number, but SQL Server will hang on to the previous 6 errorlog files.

Try the following at least 7 times:

EXEC sp_cycle_errorlog;
GO

Source: sp_cycle_errorlog (Transact-SQL)


How to see tables and column names in exact case which is created and not by default uppercase?

That has nothing to do with TOAD.

As documented in the manual Oracle converts all non-quoted identifiers to uppercase.

If you really, really want to make your life a lot harder then it has to be, and really want to create mixed case identifiers, you have to quote them during creation:

create table "SomeTable"
(
   "Id" integer not null primary key,
   "SomeColumn" varchar(100)
);

But I strongly recommend to not use quoted identifiers. They will create much more problems in the long run than they are worth it.


For most databases including Data Warehouses Read Committed is a sufficient isolation level.  I wouldn't use Read Uncommitted due to the possibility of incorrect answers.  On SQL Server we can also add Read Committed Snapshot Isolation.  This versions rows and avoids queries being blocked by updates.  Don't know if you have something similar on DB2.


How does the Distributor know you have seeded/initialized your Subscriber using a backup?

The link posted as a comment to your question explains the technical steps but may not have made clear how the setup really works.

When you create the subscription you inform the publisher that the subscriber has been restored from a backup (specifying the backup file used).  The backup you use  has to be taken after the publication was created.  When you setup replication you tell the distributor how long to keep transactions around (Transaction Retention Period).  The distributor then keeps these commands until they are delivered to all subscribers, or until the retention period has been reached.  So when the subscription is created, the LSN of the backup is checked to see if it was taken from within the retention period.  If so, then the backup and therefore the subscriber is valid.  Only the undistributed transactions created after the backup will need to be sent to the subscriber.

In your case you would:

setup the publication
take a backup
copy that to the subscriber before the retention period expires
restore the backup to the subscriber
create the subscription (using the initialize from backup option)
watch as the undistributed commands then get sent to the subscriber
finally see that the entire thing works!

Initializing from backup makes the entire thing much easier than snapshots, because you generally have backups running anyhow and, more often than not, can use compression.  This does mean that the entire DB is on the subscriber though, which may be desired or may not.


Design DB for users with different information fields?

You did not mention whether this was an OLTP or Decision Support solution; lets assume OLTP.

Basically, you are asking about superclass (the set of common attributes) and subclass (the set of different attributes) by some classification.

In your example you have indicated that the sub-class can have many values. When the number of sub-classes are known and static, you could model it as follows:

The DDL for the User table is as follows:

CREATE TABLE User (
UserId INTEGER DEFAULT nextval('UserId_seq')  NOT NULL,
UserName CHARACTER VARYING(40)  NOT NULL,
UserLname CHARACTER VARYING(256),
UserType CHARACTER VARYING(10),
CONSTRAINT PK_User PRIMARY KEY (UserId));

The UserType attribute is new and is used to identify which subclass you are storing for a given user.

For your CS Subclass example

insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS');

-- > Assume that the assigned UserId is = 1 for the purposes of this example

insert into cs_user_projects (user_id, projectName)
       values  (1, 'p1');
insert into cs_user_projects (user_id, projectName)
       values  (1, 'p3');
insert into cs_user_projects (user_id, projectName)
       values  (1, 'p6');
       ...

Now the application would know that it had to go to the CS_USER tables to get the information for CS users as follows:

select UserName
     , UserLname
     , ProjectName
  from user u
  join cs_user_projects p
    on u.userid = p.userid
 where u.UserType = 'CS';

If you want multiple sub-class information (User Programming Skills), you have to join to that sub-class table as well.

This is a straight forward, simple design; however a potential drawback is that it is a  static design. If you wish to represent another sub-class (Engineers, Nurses, ...); then the sub-class tables must be added and the application changed to handle the new sub-class use cases.

In order to allow the model to take on new sub-classes without a change in the data model; you must think of the sub-class as a data object and model the relationship in the following way:

The User table (super-class) stays the same and you still have a classification described by UserType; but now you have an Attrib table to hold the values for each sub-class of user.

As an attribute can belong to many users and a user can have many attributes, the relationship between users and attributes is called a many-to-many relationship. This is resolved by addition of the UserAttrib table with primary keys from each parent table. The Attribute Role is provided for context of the relationship. Let's see how this would load for your CS example again:

    -- identical to before
    insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS');

    -- Load up the different projects
    insert into Attrib ('p1');
    insert into Attrib ('p2');
    insert into Attrib ('p3');
    insert into Attrib ('p4');
    insert into Attrib ('p5');
    insert into Attrib ('p6');

    -- Assign only the appropriate projects to Ali
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p1',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p3',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p6',1,'PROJECT');

To query our little data model as before:

select UserName
     , UserLname
     , AttribValue Project
  from user1 u
  join UserAttrib a
    on u.UserId = a.UserId
 where a.AttribRole = 'PROJECT'
   and u.UserType   = 'CS';

Note that to return multiple attributes (e.g. also return User Programming Skills say), you would have to join to the UserAttrib table again. The benefit in this design is that if a new sub-class comes along (Engineers who specialize in Electrical, Chemical, Mechanical, ... sub-classifications). Then you would simply add data for these classifications and assignments as above without having to change the data-model to do so.

These are the classical, general solutions to super-class/sub-class design for ALL Databases that you mention in your question.

In the Postgres Database case, you should investigate the INHERITS clause of CREATE TABLE.

It allows a table (sub-class) to inherit the parent (super-class) table definition. In addition, you can alter the child (sub-class) tables to add additional attributes to achieve your desired effect of tables which would match on a sub-class by sub-class basis. Note that this implementation method is very similar to the first method we discussed (static, known sub-classes) and would not apply in all design criteria (e.g. dynamic and/or swiftly changing sub-classes).


SQL Server Named instances by default are running on a dynamic TCP/IP port (so it won't be default 1433). You can try to do a few things to enable access to your instance (assuming that Firewall is configured properly).

SQL Server Browser

You need to ensure that the SQL Server Browser service is running.

Open SQL Server Configuration Manager
Go to SQL Server Services
Right-click on SQL Server Browser service
Click Start

Try to connect to your instance providing <hostname>\<instancename> or <IP>\<instancename> as Server Name. If that doesn't help go to the next step.

TCP\IP Protocol

To enable TCP\IP protocol for your instance:

Open SQL Server Configuration Manager
Go to SQL Server Network Configuration
Go to Protocols for
Right-click on TCP/IP
Click Enable
Restart your SQL Server instance

If you want to use a static port for your instance (instead of dynamic that changes after every restart) you can change it here.

Open Properties for TCP/IP protocol
Go to IP Addresses tab
Scroll down to IPAll section
Remove 0 value from TCP Dynamic Ports
Specify your port in TCP Port

You can use this port to connect to your instance by providing <servername>,<port> or <IP>,<port> as Server Name (yes, there is a comma, not a colon).


PostgreSQL/ Linux, way to have DNS forward to a port/cluster?

The PostgreSQL protocol cannot support hostname based virtual servers, because the client doesn't send the hostname of the server in the initial connection packet like HTTP does in the Host header.

Your only option is to add additional Elastic IP addresses. You will need to be able to tell which traffic came from which IP. Because EC2 instances don't have their public IPs directly associated with their network interfaces you can't do this the normal Linux way of just having multiple IP addresses for an interface. Instead you must create multiple interfaces and bind one IP to each interface.

You can then have PostgreSQL instances listen on port 5432 on each interface by setting listen_addresses to the interface's private internal IP address, not the public address.

You must also change the unix_socket_directories setting of each server so they put their listening sockets in a different directory, or set it to empty to disable unix socket connections. Otherwise they'll all try to grab the same socket filename in /tmp or /var/run/pgsql (depending on how Pg was compiled) and all but one will fail to start with an error.

Personally I would be using Amazon VPC for this task. It gives you much finer control over interface and address management than EC2 Classic does. It's also quite easy to script the addition/removal of interfaces via the EC2 APIs.

A handy advantage of using IPs for this is that you can discard the IP when you discard the cluster, and add a new different IP when you bring up a new cluster. So you shouldn't have to deal with the port-reuse confusion problem you outline in your question.

It's also worth noting that you could use a proxy like PgBouncer to listen on the different IPs and forward connections to Pg running on a single node on different ports, if you needed to do that for some reason.


Need help in designing a database with a need of adding new columns or tables dynamically

You have the wrong table structure planned.

Tip: Naming a series of columns on a table with a sequential number (foobar1, foobar2, foobar3) is often a sign of incorrect database design.

Many-To-Many

You have children and you have vaccines as entities. Each should be represented by a table.

Furthermore, you have a relationship between those two entities. Each child can get a dose of zero, one, or more vaccines. Each vaccine may be given to zero, one, or more children. That means these two entities have a Many-To-Many relationship.

A Many-To-Many relationship is always a problem. The solution is always a third table that bridges the other two tables, representing the relationship between them. The bridging table always has at least two fields; as a child table, it has a foreign key to each of the parent tables.

In your case, the bridging table would be named something like dose, tracking which kid got a dose of which vaccine. And in your case, the table would have a third field, the date when the dose was given.

To present this data together, you perform a database join to extract various columns from the various tables where rows match up the foreign key on the child table (dosage) to the primary key of each the parent tables (patient & vaccine).

For more discussion of Many-To-Many, search for the canonical example: Books, Authors, and a bridging table named something like Authorship.  Another example is employees being assigned to teams/projects. Yet another example is music albums where an album is categorized one or more genres, and each genre can have many albums assigned to it, as discussed in this Question.

Also search to learn more about how to separate such data into tables, a process known as database normalization.

  Each child will have his own schedule and history of administered dates.

This Answer addresses only the “history of administered dates” portion of your problem, not the scheduling.

  This is my first project using databases.

Your problem of vaccination records is not simple. As a beginner, I suggest working on a simpler problem.


What are the minimal permissions required for the "Users and Privileges" tab in MySQL Workbench to function in MySQL 5.6

A partial answer: for just resetting Passwords, I was able to get it to function with CREATE USER, RELOAD, SELECT, and UPDATE.  For actually granting schema privileges to the user, it continues to give an error up to DBA.  For example, it successfully grants the privilege, but then throws the error about revoking (as mentioned above), then if you close the "Users and Privileges" window and reopen it, you can see the schema grant actually applied and saved, but you can't delete it (same revoke error and it doesn't stick like when adding).  I still don't understand what privilege is driving the error; much trial and error and process of elimination was done.


Is there a good "rule of thumb" for translating EXPLAIN cost to (wall-clock) runtime?

I suspect that this is not possible the way you imagine it.  One important reason is that the actual run time is very hardware dependent, and many of the optimization decisions that the database engine does are effectively about balancing the use of the different hardware components (e.g., disk, memory, CPU).

I suggest that you run a bunch of queries relevant to your application, record the cost estimations and the run times, and try to make sense of that data.  You might get a nice linear relationship, or you might learn that the cost numbers are worthless for this purpose.


You should use Export data and choose data source as SQL server native client (Version) as source and select your source SQL server instance and You should select the same for the destination with your Destination server name. On the next window select table, you want to copy and Run the wizard. This will copy all the data from your source table to destination table.

Also the copy database wizard is used to create a copy of your database not for copying data from table to table. Also if you can't see this option most probably you are using express edition here

  The Copy Database Wizard is not available in the Express edition.


A table that consists of multiple FK or creating separate tables for each FK

Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines:

CREATE TABLE Category (
    Category_ID           INT NOT NULL AUTO_INCREMENT primary key,
    Parent_Category_ID    INT NOT NULL REFERENCES Category(Category_ID),
    Description           varchar(255) not null
)

This type of structure is not as well supported by MySQL as some other RDBMS's, but these issues can all be worked through.

You will also need a revised BookCategory mapping table such as this:

CREATE TABLE BookCategory (
    Book_ID        INT NOT NULL references Book(Book_ID),
    Category_ID    INT NOT NULL references Category(Category_ID)
)

My apologies if the Foreign Key syntax above is not quite correct for MySQL; I usually work with SQL Server.

Update
With this INSERT:

insert Category(Parent_Category_ID,Description)
values (1,'Mystery')
      ,(2,'SciFi')
      ,(2,'Aliens')
      ,(3,'Humanoid')
      ,(3,'Reptilian')
      ,(3,'Other')
;

the following SELECT:

select
    root.Category_ID,
    cast(
      coalesce(grandparent.Description + '-',' ') +
      coalesce(parent.Description + '-',' ') +
            root.Description
    as varchar(255)) as Description
from Category                           root
left join Category                      parent
   on parent.Category_ID            = root.Parent_Category_ID
  and root.Parent_Category_ID      <> root.Category_ID
left join Category                      grandparent
   on grandparent.Category_ID       = parent.Parent_Category_ID
  and parent.Parent_Category_ID    <> parent.Category_ID

yields this:

Category_ID Description
-----------
1             Mystery
2             SciFi
3            SciFi-Aliens
4           SciFi-Aliens-Humanoid
5           SciFi-Aliens-Reptilian
6           SciFi-Aliens-Other

When Common Table Expressions are supported (not yet in MySQL I understand) the following will provide automatic nesting of the hierarchy levels:

with data as (
    select
        Category_ID,
        cast(Description as varchar(255)) as Description
    from Category
    where Parent_Category_ID = Category_ID

    union all

    select
        child.Category_ID,
        cast(parent.Description + '-' + child.Description as varchar(255)) as Description
    from data       parent
    join Category   child
       on child.Parent_Category_ID  = parent.Category_ID
    and child.Parent_Category_ID <> child.Category_ID
)
select
     Category_ID,
     Description
from data


How to Dynamically change the database using TSQL

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.
If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy enough:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME();';

EXEC @exec @sql;

If you need to pass parameters, no problem:
DECLARE @db sysname = N'db1', @i int = 1;

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME(), @i;';

EXEC @exec @sql, N'@i int', @i;

If the goal is to execute some static SQL inside the chosen database, maybe you should consider storing that static SQL in a stored procedure in each database, and calling it dynamically like this:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'EXEC dbo.procedurename;';

EXEC @exec @sql;

And hopefully the ultimate goal is not to run all this code in SSMS just so that SSMS is now in the context of @db... Daniel would really like it if I state explicitly that this is not possible, as @Lothar's comment also stated.


Get names of games that have the highest number of guilds

One obvious solution is to get all the counts, sort them in the descending order and retrieve just the topmost row:

SELECT
  gName,
  COUNT(Name) AS GuildCount
FROM
  Guild
GROUP BY
  gName
ORDER BY
  GuildCount DESC
LIMIT
  0, 1
;

That would work only in cases where only one game can have the maximum number, but you probably cannot guarantee that. So the above solution is not very good, because in cases where two or more games have the same highest number you say you want to get all those names.

So, you could try this approach instead:

SELECT
  gName,
  COUNT(Name) AS GuildCount
FROM
  Guild
GROUP BY
  gName
HAVING
  COUNT(Name) =
  (
    SELECT
      COUNT(Name)
    FROM
      Guild
    GROUP BY
      gName
    ORDER BY
      GuildCount DESC
    LIMIT
      0, 1
  )
;

The subquery will get just the number, the highest guild count, and the outer query will get the names of those games that have the matching number of guilds.

The problem with the above is obviously that you have to scan the table twice, each time performing the same grouping, and the grouping itself, moreover, has to be repeated in the code as well. Question is, how to avoid that?

Now, where other SQL platforms offer powerful instruments like ranking functions, MySQL is trying to make up by allowing you to use variables to achieve better performance. In this case, too, it is possible to use variables to get the results in one go and avoid repetition of logic. Here is one way:

SELECT
  gName,
  GuildCount
FROM
  (
    SELECT
      IF(@LastCount <> 0 AND @LastCount <> grp.GuildCount, @flag := 0, @flag) AS flag,
      grp.gName,
      @LastCount := grp.GuildCount AS GuildCount
    FROM
      (
        SELECT
          @LastCount := 0,
          @flag := 1
      ) AS init,
      (
        SELECT
          gName,
          COUNT(Name) AS GuildCount
        FROM
          Guild
        GROUP BY
          gName
        ORDER BY
          GuildCount DESC
      ) AS grp
  ) AS derived
WHERE
  flag = 1
;

The init derived table is just for variable initialisation, and grp provides the initial set of aggregated values sorted in the descending order to use our variables on.

The main part of the logic is located at the second nesting level where the variables are actually used. The @flag variable is used to generated the flag column for further filtering, marking rows that need to be included in the output with 1 and the rest with 0. It is initially set to 1 because the first row obviously need to be included. It is reset to 0 as soon as the number less than the maximum is encountered. To help with that, the other variable, @LastCount, is used, which stores the previous row's count value.

The outermost query merely filters the nested set on the flag column.

This solution can be tested at Rextester.


Can SQL Server 2012 FCI have multiple names?

Consider using Sql Client Aliases to abstract the data source name. The aliases are set on client computers by either adding keys to registry or by running c:\windows\system32\cliconfg.exe (for x86, x64 systems) and c:\windows\syswow64\cliconfg.exe (for 32-bit applications in x64 system). A Sql Client Alias is quite a bit like the additional DNS record you described. What's more, aliases support instance names too, a feature that DNS record won't do.

After an alias has been created, configure the client to use it instead of real Sql Server instance. Should you ever need to change the server, just modify the alias.

As a caveat, there seem to be some applications that won't work well with aliases. These often are legacy ones, like those using ODBC or proprietary database drivers. For modern applications, aliases will work just fine.


Is it possible to backup stored routines and functions with Innobackupex?

Stored procedures are stored in database mysql. Innobackupex copies it by default. Make sure database mysql is restored with other databases.


Those "Starting up database" messages occur when the database starts up (is opened / accessed by a user) for the first time.  Normally you'll only see this message once (when the instance starts up), or if something happens to cause the database to shut down / close (like an availability group failover).
If you are seeing that message a lot, one likely reason is that the database AUTO_CLOSE option is set to ON.  This means the database is shut down / closed very quickly once there are no connections to that database.
Your service probably uses best practice patterns of opening a connection, doing some work, and then closing the connection.  This would lead to the database shutting down / starting up a lot if that's the only thing accessing the database.
You can, and probably should, turn this off to make sure the database remains open and online even when it's not being actively used:
USE master;
GO
ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;
GO

The parallel redo messages are also normal, and generally only occur when the database is starting up.  See this Q&A for more information on that one: Messages about parallel redo
You mentioned this problem is intermittent.  It's possible some other person or process is changing this setting periodically.  Or that the service application you mentioned has somewhat variable performance that might lead to more or less DB shutdowns (depending on how quickly it opens and closes connections).


Indexing for query containing xml column

If you are updating the xml that often, then consider property promotion, ie converting the xml to relational tables, as also suggested by others.

Re the design, are you storing Balance and Transaction in more than one place?  This sounds dangerous.  If the xml is a kind of history table then it might be better as that.  You could also make sure the history table is populated at the same time as the main table within a transaction so it's safe.  Your current design doesn't feel safe to me.  The fact your two UPDATEs above aren't done in a transaction is a bit worrying.  Have you had mismatches between the main table and xml?

Having said all that, I did a simple test rig to try and reproduce this problem and the UPDATEs run in under a second for me on my laptop on a 1 million row table.  Can you see anything different between this rig and your queries?

USE tempdb
GO

IF OBJECT_ID('[dbo].[account]') IS NOT NULL DROP TABLE [dbo].[account]
GO
CREATE TABLE [dbo].[account](
    [ID] [int] NULL,
    [Type] [char](10) NULL,
    [Date] [date] NULL,
    [Balance] [decimal](15, 2) NULL,
    [TRansaction] [decimal](15, 2) NULL,
    [mybal] [decimal](15, 2) NULL,
    [dailybalance] [xml] NULL,
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED
(
    [AutoIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Generate some dummy rows
;WITH cte AS
(
SELECT TOP 1000000
    ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) AS rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.account ( ID, [TYPE], [Date], Balance, [Transaction], mybal, dailybalance )
SELECT
      rn AS ID
    , CHAR( 65 + ( (rn -1) % 6 ) ) AS [TYPE]
    , DATEADD( day, (rn -1) % 90, '1 Jan 2013' ) AS [Date]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [Balance]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [Transaction]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [mybal]

    , ( SELECT CONVERT( CHAR(10), DATEADD( month, x.y, '1 Jan 2013' ), 120 ) AS "date", x.y AS "Balance", 1 AS "Transaction" FROM ( SELECT DISTINCT TOP 12 column_id y FROM master.sys.columns ) x FOR XML PATH('Row'), ROOT('Root'), TYPE ) [dailybalance]

FROM cte
GO

-- Create data for our condition
UPDATE a
SET [Date] = '28-Feb-2013',
    dailybalance.modify('insert
<Row>
  <date>2013-02-04</date>
  <Balance>-1</Balance>
  <Transaction>-1</Transaction>
</Row>

  as last into Root[1]
  ')
FROM dbo.account a
WHERE [ID] = 257
GO

-- Covering index for the query
-- CREATE INDEX _idx ON dbo.account ( ID, [Date] )

SELECT 'before' s, * FROM dbo.account
WHERE [ID] = 257

UPDATE account
SET [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Balance/text())[1]
with (/Root/Row[date=''2013-02-04'']/Balance)[1] +280')
WHERE [ID] = 257
  AND [Date] = '28-Feb-2013'
  AND [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;

UPDATE account
set [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Transaction/text())[1]
with (/Root/Row[date=''2013-02-04'']/Transaction)[1] +280')
where [ID] = 257
  and [Date] = '28-Feb-2013'
  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;

SELECT 'after' s, * FROM dbo.account
WHERE [ID] = 257

How many rows in your account table?  If your xml is actually very large, then that might explain things.  Could there be other issues such as blocking, triggers, or is the server busy?  Looking at the query, it's unlikely full-text indexing would help you much.  XML Indexing might help, but at a significant storage penalty.  The table can end up between 2-5 its original size and you don't have that much room to play with in SQL Express 2008 R2 (10GB), so again probably not recommended.

One other thing that might help is a covering index to support the UPDATE, eg

CREATE INDEX _idx ON dbo.account ( ID, [Date] )

Try that and see if that helps your UPDATE.  If not, try and provide the additional information requested above and I'll have another look.


You can make a procedure/job that cleans up the tables.  It would:

Check if the user is active
If so, ignore and move on to the next user or complete
If not, truncate/delete the table (after moving the records to a historical table if you desire)

Rinse/repeat every xx minutes.

We have used some variation of this in production. I usually do the coding in a stored procedure, and the "repeat" part comes from a SQL Server Agent job, which executes the procedure on a schedule.  I try to keep it as modular as I can.


BULK INSERT permission on a single database

Ok folks,

I've asked this on Brazilian msdn forum as well and I got the answer (Forum discussion: BULK INSERT por DATABASE). The user Roberson Ferreira _ helped me. I imagine you can't read Portuguese so I will describe the answer here.

Answer:
It doesn't matter if the ADMINISTER BULK OPERATIONS is server-wide, the user won't be able to BULK INSERT into a certain database if they don't have the INSERT permission as well. The INSERT permission is a database/table-level permission.

Additionally, the ADMINISTER BULK OPERATIONS is not going to grant the user any other permissions over the server or database, all the user will be able to do is BULK INSERT in the database where they have INSERT permission as well.


Should I care about physical columns in SQL Server?

Actually, this is a great article. Life would be a way easier if every developer would ask similar questions. Nowadays almost nobody cares about that but to tell the truth the correct answer is: It depends on.

If you have a relatively small software with several million of records, you will be fine with that. Maybe I could recommend you to create a weekly job that does an online rebuild on each table every Sunday to take care of your tables and indexes (of course, please use to Online=On hint or you will have some surprise :D)

If you have a big software with several million of users and plenty of rows, you must ask yourself similar questions.

If you store a row in 400 bytes instead of 100, it does not affect your performance directly. But indirectly, let imagine: you have larger rows, the pages are filled sooner, you will have more page split that takes time and IO. And, of course, your server has to read more pages to get the same amount of rows because each row is larger. So indirectly, it affects your performance.

I think if you keep some rules in your mind, you will avoid the major problems:

every fixed length column takes a place. If you have a nullable int column, it will take 4 bytes. If you have a char(10) column, it will take 10 bytes and so on.
variable length columns take a same amount of bytes as their length multiple by the storage size of one unit. A varchar(10) that stores 'apple' takes 5 bytes (plus some additional info). An nvarchar(10) that stores 'apple' takes 10 bytes.
Very big rows are stored out of the row data (check the Inside SQL Server books).

So my advice is simple:

plan your columns well
avoid to delete or alter a column
maintain your tables


What the best Configurationfor mysql using 4GB

Terrible:  innodb_buffer_pool_size = 4G and 4GB of RAM.  You must not over-allocate RAM; performance suffers terribly.  Change that to 1500M.  (Even 80% is dangerously high for a mere 4GB of RAM.)

key_buffer_size = 256M -- If you are not using MyISAM tables, change this to 20M.

table_open_cache = 5000 -- If you don't have thousands of tables, change this to, say, 1500.

Yes 45GB of data will work with only 1.5G of buffer_pool.  But it may be slow.  Let's see some of the slow queries.  The solution may be as simple as adding a 'composite' index to help a query.

In general, one should not modify my.cnf settings other than the buffer_pool.  Raising values, in any cases, hurts performance.


MongoDB Insufficient free space for journal files

I got the solution by typing the
mongod --smallfiles


SQL Server 2012 In-Place Upgrade and Timing of Database Version Change

The database version is updated automatically, when the database is first brought online, during the recovery process.

The database compatibility level is never automatically modified.  If your database is at 100 now, that won't change.

Refer to https://msdn.microsoft.com/en-us/library/bb510680.aspx for database compatibility.

See https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/04/26/whats-the-difference-between-database-version-and-database-compatibility-level/ for the differences between version and compatibility level.


Tracking down a stored procedure's parameters after execution

I often use the following query taken from http://www.sommarskog.se/query-plan-mysteries.html#otherreasons to return the sniffed values. Another way would be to open up the plan in SQL Sentry Plan Explorer and select the parameters tab. Both the XML method and plan explorer method will show the runtime values assuming that you are viewing the actual execution plan.

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)

SELECT @dbname  = '',
       @procname = '' -- enter proc name

; WITH basedata AS (
   SELECT qs.plan_handle,
            qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT plan_handle,stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)

SELECT set_options AS [SET]
        , n.stmt_start AS Pos
        , n.Statement
       , CR.c.value('@Column', 'nvarchar(128)') AS Parameter
       , CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value]
       , CAST (query_plan AS xml) AS [Query plan]
       , n.plan_handle
FROM   next_level n
CROSS  APPLY
        n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter


You should look into resetting the root password.
Normally the root user shouldn't be used for any enterprise package, however "shouldn't" isn't much of a guarantee.

Your first action SHOULD be to ask around for the root password or look in whatever password storage your company might be using.

If all else fails and you have to reset the passwored, you can find out how here.
Alternatively you could use --skip-grant-tables, and create a new admin user, however this is very insecure, and would be best done during a maintenance window with restriction of the network access.


Recommended Postgresql index type to be used with not equal () where condition

Indexes are good to reduce many rows to a few rows based on a condition in your query. Unless you have a very unequal distribution of values, a "not equals" condition will most probably not be considered for an index lookup.

So, it all depends on how selective this index would be. Unless it eliminates much more than 50% of rows (usually 90%+ is desirable) it's probably no better than a sequential scan.

See Use The Index, Luke


SSDT Publish Window: What does checkbox ENABLE mean?

The checkbox applies to each option "positively" so that e.g. ticking "script database collation" will generate the appropriate statements to alter collation settings. If not ticked, the deployment will ignore those differences by not scripting the changes.


Application service down due to SQL Server error

Before generating this message, the server would have previously received other structurally incorrect packets that corrupted components within the application stack.  This is typically how denial of service or penetration attacks are formed.  If you have this system on the Internet, I'd get it off the Internet, or use filtering to restrict access to specific IP's.  If it's not on the Internet, I'd check the users for software that has not been tested and qualified by an entity such a vendor or open source community.


A CTE can't be used as a subquery. One workaround would be:

IF EXISTS
(
  SELECT 1 FROM
  (
    SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
    FROM table
  ) AS DATA
  WHERE rn = 2
)
BEGIN
  ...
END

Another would be:

IF EXISTS (SELECT 1 FROM dbo.table GROUP BY column HAVING COUNT(*) > 1)
BEGIN
  ...
END

Even if your proposed syntax were valid, the EXISTS wouldn't short circuit in that case anyway, I would think (and I suspect that is why you'd want to use it), since the window function must materialize over the full set before rn could be filtered.


How to improve performance of frequently updated table in mysql?

You have two "ranges" filtering the data.  MySQL has no good way to do that.

First, let's check some numbers.

How many rows in the result set?
How many rows without the IN clause?
How many rows without the time range?
Is this IN list and this time range typical?

With those, we may be able to decide to focus on filtering on the time range in preference over the IN range.

Meanwhile, let's get rid of the "midnight" bug... Your time range seems to be from the morning of May 1 through midnight of the morning of Oct 31.  Perhaps you meant to include all of Oct 31, not just one millisecond of that day?  Did you mean to ask for a full 6 months?  Suggest this coding style; it's less error-prone:

AND data_received_time >= 1000 *  UNIX_TIMESTAMP('2015-05-01')
AND data_received_time <  1000 * (UNIX_TIMESTAMP('2015-05-01') + INTERVAL 6 MONTH)

Back to the performance question...

If you are looking at a smallish fraction of the total time, then PARTITIONing may be useful for simulating a 2D index.  (I mention this as Case #2 in http://mysql.rjweb.org/doc.php/partitionmaint )

If you break the table into, say, 20-40 partitions PARTITION BY RANGE(data_received_time), then "partition pruning" will first filter on the time range.  Then your PRIMARY KEY, with imei_no first, filters on the other dimension.

I cannot say how much (if any) this partitioning will speed things up.  I would expect small time ranges to benefit (percentage-wise) the most.  A time range that hits all partitions will actually be a little slower than without partitioning.  (Hence, my "check some numbers" questions.)

Schema notes...  (Smaller is faster)

BIGINT is 8 bytes.  Can odom go beyond 16M miles, the limit for `MEDIUMINT UNSIGNED (3 bytes)?
INT(4) -- the (n) means nothing; suggest TINYINT (1 byte) or SMALLINT (2 bytes).
Some of the VARCHARs could be 'normalized' to say a 2-byte SMALLINT UNSIGNED.