Why should I use Visual Studio 2010 over SSMS for my database development? Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to do my database development without issue.

Why should I bother with VS2010 when SSMS works for me?  What, specifically, does it do better than SSMS?
But perhaps my premise is incorrect and SSMS still trumps VS for database development. If so, in what specific ways is that true?

Actually I was a bit underwhelmed with VS2010, to be honest.  I think an old-school create table script and files for stored procedures are easier to work with.  If you need schema management then you can get Redgate SQL Compare Pro for a few hundred dollars.

If you really need a database modelling tool then Powerdesigner or even Erwin does a much better job, although they're not particularly cheap.

Although I prefer SSMS I've used both.  Some pros and cons:

SSMS has a user interface that 'just works' well for SQL development.  Just building and using creation scripts is much more convenient than the hoops VS2010 forces you to jump through.  Much, much more flexible (+ SSMS).
VS2010 has basic schema management (i.e. difference/patch script generation) (+ VS2010).  However it's not all that good and has some flaws.  For example it matches constraints on name.   If you have check or default constraints on a column without naming them, SQL Server generates a random name behind the scenes.  This will confuse VS2010 if you install the script on a different machine as the constraints may have different names.  Redgate is cheaper and better. (+ VS2010, but flawed).
VS2010 is really clumsy - you need to have one file for each table or other DB object.  Essentially you have to do things the VS2010 way, which is quite cumbersome.   (- VS2010)
VS2010 Is also somewhat fragile and source control integration is flaky.  Even on a simple database every table, constraint, stored procedure, index and other database object is its own file.  Files get added to the project all the time, much faster than a typical programming project with (say) C#.  With optimistic concurrency it has a tendency to silently drop files from the project as check-ins get out of sync.  Even with good team discipline the feedback from the UI about status is very poor.  This would be a disaster on a complex model.  (-VS2010 - I'd almost consider that a show-stopping flaw for a large project).
SSMS comes with SQL Server - can't beat the price (+ SSMS).
VS2010 still does not have a proper repository like (say) PowerDesigner or Oracle Designer.  You can't easily query the data model without installing it into a database. (- VS2010).

On the whole, I would rate VS2010 about a B-.  It was clumsy on a relatively simple database project with two fact tables and around 15 dimensions.

The largest data model I ever did was for a court case management system, which had around 560 tables.  I would not recommend VS2010 for a project that size (I did that on Oracle Designer).  Essentially it's trying to be clever and the paradigm doesn't really work all that well.  You're better off with a best-of-breed modelling tool like PowerDesigner or just using create table scripts by hand.

SSMS is simple and reliable but manual.  You have pretty much infinite control over how you want to manage the model.  Combine it with a schema manager such as Redgate SQL Compare and maybe a decent modelling tool such as PowerDesigner and you have a much better package than VS2010.

Summary
I'm not sure I could quote any killer features or benefits apart from (perhaps) integration with VS solutions containing other projects.  If you already have VS2010 premium or ultimate then you get a somewhat flawed database development tool thrown in with your .Net tool chain.  It will integrate DB projects into your VS solution, so you can use it to make sproc deployment scripts at least.

However, VS has no modelling tool to speak of, so PowerDesigner or even Erwin is better on that count.  Redgate's schema management is much better and SQL Compare Pro is quite cheap (about £400 IIRC).  IMHO SSMS works much better for T-SQL development but you can certainly do it with VS2010.

VS2010 premium isn't much cheaper than a best-of-breed database modelling tool and VS2010 ultimate is at least as expensive.  At the expense of tight integration with your VS project you could probably do better with third party tools.

An alternative

I guess one shouldn't slag off VS2010 too much without suggesting at least one alternative and outlining its pros and cons.  For the purposes of this I'll assume a large project.  Although I mainly do A/P work these days I have been involved in a 100+ staff year project where I did the data model (and some development work) and a couple of others in the 10 staff-year range, where I mainly worked as an analyst or a developer.  Mostly I work on data warehouse systems these days but the larger projects were mainly applications.  Based on my experiences with various tools, here are some suggestions for an alternative tool chain:

VS2010 professional or higher.  You may or may not want to use project management features of premium or ultimate.
Subversion, AnkhSVN and TortoiseSVN - Better than TFS any day and plays nicely with VS.  It's also quite amenable to farming off local repositories for concurrent development workstreams.
SSMS for T-SQL development - Project management and SC integration not so good but works well for DB development work.
VS2010 DB project for tracking sproc files - slightly clumsy if you're using SSMS but works OK.  It will also generate deployment scripts.
PowerDesigner - Way better at modelling a database and managing DB schema items.  It also does UML if you want to get heavily into MDA.  If you want to drive your DB design from an object model you might consider Sparx EA instead.  It does the best job of Meta CASE (extensible meta model) of any CASE tool I've seen, although its database modelling leaves something to be desired.
SQL Compare pro - Use this to generate DB patch scripts or to test manual patch scripts (see 1 below).
Framemaker - Much more stable and better groupware features than Word if you have multiple analysts working on a spec.  It also supports conditional inclusion, so you can have versioned releases of a spec with WIP changes hidden off.  MIF and MML make it fairly easy to integrate API docs and data dictionaries into the spec documents.  It's quite useful to do this as you can then cross-reference them in the spec.  Textual label anchors make cross-references stable across re-imports.  You can also use TCS to single-source the document to PDF, HTML and CHM output.
Open-source issue tracker - Many good open-source ones (e.g. TRAC, Bugzilla to name a couple I've used).  Open-source ones are easier to modify or integrate into a custom workflow and you can't beat the price.
NUnit or other testing tools - whatever automated testing tools are most appropriate to your requirements.
Anything but MS project - Considered harmful.  MS project is very inward looking and forces project plans into a model that does not effectively represent uncertainty, risk or dependencies on stakeholders or other third parties (see 2 below).

Pros: Better database modelling and schema management than VS2010, better version control system, easier to customise the build and project work flow, better management of specs and documentation.

Cons: More effort to integrate tools, limited DB integration into build process.

Assumptions: Assumes that control of change/release process in more important than automated or tightly integrated release management for DB schemas.  Also assumes that automated DB schema management is not 100% reliable.

Not terribly high tech or slickly integrated, but for a complex project you're probably more interested in control than cute automated features.  I'd argue that you're better off with a set of best of breed tools and whatever home brew build and test scripting is necessary to integrate them.  Just try to keep the build (a) relatively simple to understand and (b) fully automated.

QA on DB patches.  On a large schema you may want to have a manual patching process for live systems, particularly if the patches involve data migration.  For example, it may be desirable to have roll-forward and roll-back scripts to support backing out a change if necessary.  In this case you will want to have a facility to test that the patch actually works properly.  If you manage the database schema in a repository you can test the scripts by setting up before databases and generating a reference database from the repository.  Running the patch script on the before database should synchronise it with the repositiry model.  A schema compare tool can be used to test this.
I've done a lot more data warehouse and integration work than bespoke application development lately, so I encounter this more often than a development team will in most cases.  However, project management tools and methodologies do a very poor job of managing external stakeholders.  In an integration project such as a data warehouse I really want to see a project management tool that really pushes external dependencies (i.e. ones I don't control) in the face of programme management.  On any non-trivial integration project the external dependencies are by far the biggest drivers of wasted time.


Oracle 12c export and import schema with different data tablespace and index tablespace Suppose that I exported the schema objects from my Oracle 12c database using the original export utility as below:

exp userid=<export_schema_name>/<export_schema_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log

The export_schema_name uses different tablespaces for data and indexes, let's called data_tbs_1 and idx_tbs_1. All tables in the export_schema_name are created as below:

create table <table_name>
(
    -- columns definition
)
tablespace <data_tbs_1>
/
--
alter table <table_name>
add constraint <constraint_name> primary key(<key_name>)
using index tablespace <idx_tbs_1>

Now I want to import the exported dump file into a new schema that I created as below (I already created a new data tablespace data_tbs_2):

create user <import_schema_name> identified by <import_schema_password>
default tablespace <data_tbs_2>

The imporst command I am going to use is:

imp userid=SYSTEM/<import_system_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log fromuser=<export_schema_name> touser=<import_schema_name>

My questions are:

If I do this, where are the data and indexes in  stored? Are they still in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.
If the indexes of the  are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?

I wonder why would you use EXP when you have EXPDP-more efficient and performant tool- on your hand?

Using IMPDP

  If I do this, where are the data and indexes in stored? Are they still
  in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.

Yes. Since the import job executes the DDL to create those tables and the DDL includes TABLESPACE clause to define where to create the table not the default tablespace of the user. The objects will be created in their original tablespace regardless of the default tablespace of the new user.

This can be confirmed by exporting the dump file to SQL file.
IMPDP Command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay_ddl.sql

DDL-

Here is an excerpt from the SQL file.

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ; -- Here you can see it has defined the tablespace to create the table.

  If the indexes of the are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?

You can use REMAP_TABLESPACE parameter while importing the dump.

Here is the DDL for the same table imported with the REMAP_TABLESPACE clause.

IMPDP command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay2_ddl.sql remap_tablespace=USERS:DATA

DDL-

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

REMAP_TABLESPACE

Update:

  The reason I still use the original exp and imp utilities is because my company still have that legacy data on previous version Oracle database.

If that is the case, I would import the objects in the same tablespace and later on I would migrate to another tablespace using ALTER INDEX REBUILD TABLESPACE <new_tbs> command.

Using IMP

If you have to use the original import tool then-

First of all, you need to extract the DDL from the dump file using
indexfile
parameter along with fromuser and touser parameter(Same schema name for fromuser and touser).

Example: imp system@orclpdb file=dumpfile.dmp fromuser=jay touser=jay indexfile=DDL.sql
Note: It takes some efforts to edit the SQL file which contains REM keywords as well as output for the number of rows.

Sample:

[oracle@oracdbuat fra]$ cat DDL.sql

REM  CREATE TABLE "JAY"."EMP2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 TABLESPACE "DATALRG" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  CREATE TABLE "JAY"."EMPLOYEE" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows

Edit the SQL file generated by the import tool to change the
tablespace for the objects.
Run the SQL script using SQLPLUS.
Disable all the constraints
Import the dump file with the parameter IGNORE=Y to ignore the
warning about the already existed objects.
Enable the constraints.


I am trying to understand the use of composite primary key or two primary keys in a table Today someone asked me about composite primary key two make two column as primary key but i unable to understand the logic behind it because we have unique index option to make a column unique, and if I think about foreign key relationship then again its not required.

If anyone have good explanation with example then please clarify that.

CREATE TABLE `sample` (
  `pri_1` int(11) NOT NULL AUTO_INCREMENT,
  `pri_2` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `value` varchar(25) NOT NULL,
  PRIMARY KEY (`pri_1`,`pri_2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

It comes in handy especially when combining with foreign keys ...

imagine having 3 tables

T1
ID, data1, data2
T2
ID, data1, data2
T3
T1id, T2id, data3, data4

the ID column for T1 and T2 is obviously primary key - but what about table T3 - we could define another ID column to make a primary key .. OR just define the combo of T1id and T2id as the primary key ... but this depends on what data you hold in your tables and what relationship they contain
but especially when T3 was used to store a M:N (many : many) relation it makes sense to use a composite primary key consisting of T1id and T2id.


Validate XML using XSD inside TSQL function in SQL Server 2012? I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function.

To make validation I have to set value to XML and if it is not valid I will receive an error.

My problem is that inside the TSQL function I can not use TRY / CATCH. Is this the propper way and if it is even possible to do this?

One option (possibly your only option if doing this within the context of a scalar function is a hard requirement) is to use SQLCLR. SQLCLR scalar functions can do try / catch / finally structures within the C# or VB.NET code. So, you could pass in both the XML document and the XSD, both as the SqlXml type, and do the validation there. Just return a SqlBoolean for whether or not it validated.

Be sure to do the following so that the SQLCLR function can participate in parallel plans:

specify IsDeterministic = true in the SqlFunction attribute.
make sure that the function is in an Assembly that is marked as WITH PERMISSION_SET = SAFE.


Return unique records based on shared column in 2 tables I'm very new to SQL and currently using SQL Server 2008 R2. I'm fairly certain that I need to use a join but not sure if that's possible. The goal is to return just the customer's email address based on their last visit to my store. Poking around, this is the query I started off with:

SELECT DISTINCT account
FROM   [main].[dbo].[visits]
WHERE  time IS NOT NULL
AND    date = '2017-05-25'

This correctly returned a list of customer account numbers for a particular day from table visits. Now I need to find the customer's email, but that is stored in a different table, custlist.

This is where I got lost. I tried some examples found on StackExchange but couldn't quite get the join logic down.

Any help is much appreciated, thank you!

Assuming Account is on CustList, the following example should get you going:

--Set up demo data
SET NOCOUNT ON

DECLARE @Visits TABLE (Id INT,Account INT,DateOfVisit DATE,TimeOfVisit TIME)
DECLARE @CustList TABLE (Id INT,Account INT,EmailAddress VARCHAR(100))

INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','08:00:00')
INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','10:00:00')

INSERT INTO @Custlist (Id,Account,EmailAddress)
VALUES (1,1,'MyEmail@Test.com');

--Common Table Expression for distince account numbers
WITH DistinctAccountsVisits
AS (
    SELECT DISTINCT Account
    FROM @visits
    WHERE TimeOfVisit IS NOT NULL
        AND DateOfVisit = '2017-05-25'
    )
--Join Visits with CustList on Account
SELECT v.Account
    ,c.EmailAddress
FROM DistinctAccountsvisits v
JOIN @CustList c ON c.Account = v.Account


MySQL Master - Slave replication stops after 20min with no errors I'm using MariaDB 10.4 in a standard master slave setup.
Everything works fine for about 20min, I can see changes replicating across but then after about 20min replication just stops.
However, if I restart the slave MySQL instance, replication begins again and catches up.
Restarting the master has no effect on the replication it stays broken.
When in the broken non-replicating state this is the output from the slave and master.

So it looks like it thinks its caught up, but it hasn't, no matter how long I leave it.
I've used netcat to test the connection and I know it can see the master still, plus when I restart MySQL it works right away.
The my.cnf config has not been changed apart from adding the server ID and this is how I configured the slave initally CHANGE MASTER TO MASTER_HOST='dynamicip.xxxxxxx.org', MASTER_PORT = 4302, MASTER_USER='remote_replication', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=328;
The error logs contain no errors also.
Where would I begin to troubleshoot this?
EDIT / UPDATE
So it appears the data does replicate over eventually but it takes a least 50 minutes, and its tiny bits of data I'm testing it with.
I've switched the hostname dymanic.****.org to an IP address in case it was Cloudflare doing something weird but that hasn't fixed it. I also tried skip-host-cache before that.
I've also switched innodb_flush_log_at_trx_commit from 0 to 1.
Traceroute looks fine.
I can see the statements in the binlog file on the master.
Interestingly using STOP SLAVE and START SALVE kicks it back into action for a while before it begins having the issue again.
SHOW PROCESSLIST on the Master shows that the I/O thread is sometimes present and sometimes not, not sure what that means..

The first thing I would do is check the network between the Master and Slave. Why ???
Almost 8 years ago, I wrote this post : Slave I/O thread dies very often
I once had an occasion where I saw a Slave with SHOW SLAVE STATUS\G looking exactly as you have it, but it was not replicating. This is what actually happened:

The firewall on the Slave would become intermittent
The Master could not talk back to the Slave through the I/O thread
SHOW PROCESSLIST on the Master revealed that the I/O thread was no longer there
Slave assumed the Master was OK

What I recommend is this

Start troubleshooting the traceroute on dynamic....org
See if DNS is acting up. If DNS is involved, add skip-name-resolve and skip-host-cache to my.cnf on Master and Slave and restart mysqld

Please let us know what you found in your troubleshooting


.bak file not visible for restoration There is a database on a server of which I would like a copy on my local machine for testing purposes, so I created a backup in the default disk location (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup). The backup dialog says it completes successfully, but when I go to restore the .bak, it's not visible to the file selection dialog (significant file names blurred, obviously).

There are other viable backups in the same location, but the one I supposedly created does not appear. When attempting to recreate the backup, the one I created appears as a selectable option, while the others in the folder do not.

What is the issue here? I don't think it's a permissions problem because I'm able to access the other backups in the same folder.

Edit: The backup was on the server which has a similar file structure to my machine. I remoted in to the server and copied the file. Thanks for the help!

You did not save the files where you think you did.

In the first image there are 9 folders below 'Microsoft SQL Server' and above 'MSSQL11.MSSQLSERVER'  in the second screen shot there are only 5.

This is possibly an issues with your local machine VS a seperate server. Assuming the address you are following are identical (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup) the only solution remaining is that the C drives are on different machines.


Query with limit and order by runs too slow I have the following table:

CREATE TABLE dpg2
(
  account_id integer NOT NULL,
  tank_id integer NOT NULL,
  battles integer,
  dmg integer,
  frags integer,
  wins integer,
  recent_battles integer[],
  recent_dmg integer[],
  recent_frags integer[],
  recent_wins integer[],
  dpg real,
  recent_ts timestamp with time zone[],
  recent_dpg real,
  CONSTRAINT dpg2_pkey PRIMARY KEY (account_id, tank_id)
)

with this index:

CREATE INDEX dpg_tank_id_idx
  ON dpg2
  USING btree
  (tank_id, dpg DESC NULLS LAST);

I ran the following query:

explain analyze
    select dpg2.account_id, tank_id, (select nickname from players2 where players2.account_id = dpg2.account_id), dpg2.battles, dpg,
                frags*1.0/dpg2.battles, wins*100.0/dpg2.battles, dpg2.recent_dpg
    from dpg2
    where tank_id=545 and battles >= 150
    order by dpg desc
    limit 50;

with the following output:

"Limit  (cost=1523898.99..1523899.12 rows=50 width=28) (actual time=23950.831..23950.838 rows=50 loops=1)"
"  ->  Sort  (cost=1523898.99..1524200.69 rows=120678 width=28) (actual time=23950.831..23950.833 rows=50 loops=1)"
"        Sort Key: dpg2.dpg"
"        Sort Method: top-N heapsort  Memory: 32kB"
"        ->  Bitmap Heap Scan on dpg2  (cost=13918.06..1519890.16 rows=120678 width=28) (actual time=434.791..23922.872 rows=21963 loops=1)"
"              Recheck Cond: (tank_id = 545)"
"              Filter: (battles >= 150)"
"              Rows Removed by Filter: 1060576"
"              Heap Blocks: exact=458918"
"              ->  Bitmap Index Scan on dpg_tank_id_idx  (cost=0.00..13887.89 rows=967310 width=0) (actual time=299.796..299.796 rows=1082539 loops=1)"
"                    Index Cond: (tank_id = 545)"
"              SubPlan 1"
"                ->  Index Scan using players2_pkey on players2  (cost=0.43..5.45 rows=1 width=10) (actual time=0.105..0.105 rows=1 loops=21963)"
"                      Index Cond: (account_id = dpg2.account_id)"
"Planning time: 0.212 ms"
"Execution time: 23953.952 ms"

What's really confusing me is that the query planner is trying to resort along the dpg column, when all it needs to do is walk down the index corresponding to the given tank_id and pick the first 50 entries that satisfy the "battles >= 150" condition.

In fact, simply removing the 'order by' clause, I get the same result in 2s because it ends up using the index, which has things sorted in the order I want.

I know I've already solved my problem but why is Postgres doing this and what are the possible solutions?

Edit: I already ran analyze on the table and autovacuum is on.

PostgreSQL thinks it cannot use the index defined on (tank_id, dpg DESC NULLS LAST) to satisfy this query without the sort.

If it is just DESC, that is fine.  Or if it was just on (tank_id, dpg), that too would work (it would scan the relevant part of the index backwards).

If you can't change the definition of the index, then making the query match the existing index would probably also work, so:

where tank_id=545 and battles >= 150
order by dpg desc nulls last
limit 50

(which is presumably what you would want anyway?)

To execute your query as originally written with the index you originally had, it would have to execute the query in two parts.  First it would have to visit the NULLs at the end of the tank_id=545 region (because ORDER BY...DESC without further qualification implicitly means NULLS FIRST), and then if it hadn't yet reached the LIMIT would have jump to the beginning of the tank_id=545 region to fulfill the ORDER BY DESC part.

So it would have to be the executor, not just the planner, which was involved.  It would take a lot of annoying code to make that happen, and no one was volunteered to write it.  (Also, it would probably be a rich source of hard-to-find bugs, so even if someone wrote the necessary code, it might not be accepted into the code-base)


SQL Server Mirroring can't connect to mirror database I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine.

The error that keeps coming up is:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://machineabc:7023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Which occurs when I go through the GUI or run

ALTER DATABASE TESTING SET PARTNER = 'TCP://machineabc:7023'

I restored the database first with the .bak and with no recovery then the .trn with no recovery. The local database has (Restoring...) next to it...

I have run select * from sys.database_mirroring_endpoints and it all looks good (Partner, started, both have rc4 encryption).

Any suggestions?
Edit: So I tried again and I did GRANT CONNECT on the access point then when I tried setting the partner I did a net stat and this was the result:

Here are the results of the connectivity tests in Craig Efrein's post:

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue.

If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an endpoint based on the certificate, then export each certificate to the opposite end.  The public key in the certificate is thus used to authenticate with the private master key on the remote database.

Create a master encryption key on each server.  This is only done once per server instance.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

Create a certificate for each instance with an arbitrary subject.  I will provide T-SQL for endpoint 1, but you will need to do this on the other endpoint as well.

CREATE CERTIFICATE Endpoint1_Cert WITH SUBJECT = 'CERT for endpoint 1'

Create the endpoint using the certificate:

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT=7023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE Endpoint1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

Back up the certificate from each end for export:

BACKUP CERTIFICATE Endpoint1_cert TO FILE = 'C:\BACKUP\Endpoint1.cer';

Now move each file to the opposite server using your favored method.  Here I assume you use the C:\backup directory as both source and target on each server.
Create server logins, based on the moved files.  Note the full credential consists of a server level login and a user credential on the master database.  (Kind of strange but that's the way it works...).  Here's the code you'd run on endpoint 2 to import the certificate from endpoint 1.  The password is totally arbitrary and is not used later.  The logins must be granted mirroring rights.

 --note, run on endpoint 2
 USE master;

 CREATE LOGIN Endpoint1_login WITH PASSWORD = 'userpassword';

 CREATE USER Endpoint1_user FOR LOGIN Endpoint1_login;

 CREATE CERTIFICATE Endpoint1_cert AUTHORIZATION Endpoint1_user
 FROM FILE = 'C:\backup\Endpoint1.cer'

 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Endpoint1_login];

Run a converse process for creating a certificate on endpoint 2 and using it to create a credential on endpoint 1.
Now, it's time to fire up the mirroring.  You start with the server whose database is not in recovery mode.

ALTER DATABASE TESTING
SET PARTNER = 'TCP://endpoint1host:7023';

Next run the converse command on the alternate server. (If you get a database is not configured for mirroring at this point, there may be an environment-specific security problem that can be worked around with a sysadmin rights grant to the endpoint -- see comment by AllenKing below.  This did not occur in our lab.)

I ran this process successfully on 2012 SP1 on 21 March.  After I corrected an error I made on the account creation -- I did not use master but instead used the mirror database -- everything worked fine.

In troubleshooting configuration, I ended up using the following dynamic views:

-- To check on endpoints status -- is the port right, etc?
SELECT * FROM SyS.endpoints
SELECT * FROM SYS.tcp_endpoints
SELECT * FROM sys.database_mirroring_endpoints

-- This shows the endpoint rights
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;


What would I look for in SQL Server to determine if a user connected with Microsoft Access? Is there a way I can check SQL Server to see who is accessing it using Microsoft Access (as opposed to a website)?  I'm using SQL Server 2008R2 with the default logging settings.

The sp_who2 stored procedure lists all active connections and includes the ProgramName.

You could also select from sysprocesses (joining in whatever other information you require such as sysusers) but it's just easier to use sp_who2.

(caveat, this will only work for applications which set the name, some applications may not)


Syntax of INNER JOIN nested inside OUTER JOIN vs. query results TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

Following up on my previous question where we found query performance difference between different join styles (ie. nested vs. traditional), I have realized that the nested syntax also modifies the behavior of the query. Consider the following 2 queries.

SELECT  a.*, m.*, n.*
FROM    dbo.Autos a
LEFT JOIN dbo.Models m
  JOIN dbo.Manufacturers n  -- <-- Nested INNER JOIN
  ON n.ManufacturerID = m.ManufacturerID
ON m.ModelID = a.ModelID

This does not have to make the manufactures join in order to include an auto row with a ModelID that is NOT in the Models table.

Using the traditional syntax, we have to change the join to Manufactures to an outer join, like so... but this changes the query plan.

SELECT a.*, m.*, n.*
FROM dbo.Autos a
LEFT JOIN dbo.Models m
ON m.ModelID = a.ModelID
LEFT JOIN dbo.Manufacturers n -- <-- Now LEFT OUTER JOIN
ON n.ManufacturerID = m.ManufacturerID

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

The second plan has a lower estimated cost, so in that limited sense it is 'better'.

The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of the query happens to find a plan using hash join and a table spool early on. The estimated cost of that plan is so low that the optimizer does not bother looking for anything better.

The second form of the query happens to find a plan using only nested loops outer joins early in the search process, and again the optimizer decides that plan is good enough. It so happens that this plan is estimated to be cheaper.

That said (as mentioned in the question comments) the two queries are not semantically identical. This may not be important to you if you can guarantee that the results will always be the same for all possible future states of your database, but the optimizer cannot make that assumption. It only ever produces plans that are guaranteed to produce the same results specified by the SQL, in all circumstances.

  I have realized that the nested syntax also modifies the behaviour of the query.

The 'nested syntax' is just one aspect of the whole ANSI join syntax specification. To enable a full logical specification for more complex join patterns, the specification allows (optional) parentheses, and FROM clause subqueries.

The query can be written using the same ANSI syntax using parentheses:

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Autos AS A
LEFT JOIN
(
    dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) ON M.ModelID = A.ModelID;

This form clearly shows that the logical requirement is to left join from Autos to the result of inner joining Manufacturers to Models. Omitting the optional parentheses gives the form you call 'nested':

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Autos AS A
LEFT JOIN dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
    ON M.ModelID = A.ModelID;

This is not a different syntax - it is just omitting optional parentheses and reformatting a bit.

As Martin mentioned, it is also possible in this case to express the logical requirement using inner joins followed by a right outer join:

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
RIGHT JOIN dbo.Autos AS A
    ON A.ModelID = M.ModelID;

All three query forms above use the same ANSI join syntax. All three also happen to produce the same physical execution plan with the data set provided:

As I mentioned in my answer to your previous question, queries that express exactly the same logical requirement will not always produce the same execution plan. Which logical query form you prefer to use is largely a question of style. There is no correlation between one particular style and 'better' query plans in general. I would generally advise against rewriting a query to get a particular plan if the new query is not genuinely logically identical to the original.

The SQL standard also allows FROM clause subqueries, so yet another way to write the same query specification is:

SELECT *
FROM dbo.Autos AS A
LEFT JOIN
(
    SELECT
        N.ManufacturerID,
        ManufacturerName = N.Name,
        M.ModelID,
        ModelName = M.Name
    FROM dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) AS R1
    ON R1.ModelID = A.ModelID;

  Using the traditional syntax, we have to change the join to `Manufacturers to an outer join, like so... but this changes the query plan.

This probably changes the meaning of the query, in which case it is technically not a valid alternative (but see ypercube's  comment on your question).

The (optional) parentheses in the ANSI join syntax are there precisely for more complex join requirements like this, so you should not be afraid to use them where necessary.


Move MySQL database to another server I need to move 20 Gb MySQL database from on Debian server to another. The problem is that there is no way to create its backup since there are almost no free HDD space on the source server. What would you suggest?

Percona XtraBackup can stream the backup over the network directly to the second server, and use no disk space on the source server.

See examples here:

http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_stream.html


ORACLE TABLESPACE USERS GETS 100% USED I am facing a problem with oracles users tablespace. It automatically grows really fast. What kind of data is stored there? And what happens when it hits 100% of space used? Is there a solution to reduce the amount of data in this tablespace? At the moment we have 11 MB free of total 33 GB. Is it a good idea to delete it and how to do it? Thanks.

I would say that some or all of your application data is likely stored in the USERS tablespace. Don't do anything rash.

IIRC databases created by the dbca utility have USERS set as the default tablespace. This means that any new users created will store their tables etc. in USERS by default.

select * from dba_segments where tablespace_name = 'USERS' will give you a starting-point for seeing what is in the tablespace. (It could be a long list.)

select * from dba_users where default_tablespace = 'USERS' will show you the schema owners which will store their data in USERS by default.

I suggest that you don't see it as a problem to be solved, but accept that USERS is where the data is stored, and plan for controlled future growth of the tablespace. I have seen a couple of applications which had been installed like this.

HTH


log to table vs log to output file I am using ola-hallengren solutions for database maintenance.
A question about the log, I see in the documentation website:

LogToTable: Log commands to the table dbo.CommandLog.

Value   Description
Y       Log commands to the table.
N       Do not log commands to the table. This is the default.

Does N which is default means to log to output file?
I also see the recommendation is to log to output file, then what is the CommandLog table for?

Which stored procedure has code to log into output file and can set location of output file?

Thanks

Does N which is default means to log to output file?

No, it just wouldn't output to the CommandLog table. It would still output to file.

  I also see the recommendation is to log to output file, then what is
  the CommandLog table for?

The CommandLog table makes it easy to read the output log instead of having to read a flat file from your drive. Many people will add follow on jobs to query this table and email / report out errors, etc.

  Which stored procedure has code to log into output file and can set
  location of output file?

The @OutputFileDirectory parameter in the MaintenanceSolution.sql script is where you should set this. If no ouput is specified, the SQL Server error log directory is used. This is referenced in the Advanced page on the job step. Depending on your SQL Version, you will see SQLLOGDIR or the actual output path. SQLLOGDIR is an agent token added in 2014.

  (SQLLOGDIR)   Replacement token for the SQL Server error log folder path
  - for example, $(ESCAPE_SQUOTE(SQLLOGDIR)). This token is only available on SQL Server 2014 and above.

Another way, if you are using Windows Scheduler or wanted to edit the Agent Job directly is by specifying your log location for sqlcmd via the output option.

  The output option (-o) specifies the file in which sqlcmd is to put
  its output.

Thus, something like -o C:\Log\YourLog.txt

In that FAQ, Ola provides an example for a DB Backup:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt


Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production.  Neither has SSIS installed.

I have to similarly maintain (migrate, restore) the same maintenance plans on all of dozens servers

Any way to import-export-migrate-save-restore maintenance plans without having SQL Server Integration Service (SSIS) and Data Tools (BIDS) installed (and running)? Though I can stage a separate server machine for them

Saw the answer to "Running an SSIS package without the SSIS service installed" but still uncertain

If you have a working maintenance plan, which is really just an SSIS package, you can export that .dtsx file out of the database and redeploy to the next one.

For 2008/2012, those packages are stored in msdb.dbo.sysssispackages which you can verify with a query

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    F.FolderPath
,   P.PackageName
,   F.FolderPath + '\' + P.PackageName AS PackagePath
,   P.packageFormat
,   P.packageType
,   P.vermajor
,   P.verminor
,   P.verbuild
,   P.ownername
,   P.PackageId
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the
-- native Data Collector packages
WHERE
     F.FolderPath <> '\Data Collector';

You will then want to pipe that information to repeated dtutil calls much as I describe on this SSIS Package Extract From msdb. Instead of the /file output though, you'd want to make it /destserver thatdatabase /copy SQL;PkgName See the answer over on
https://stackoverflow.com/questions/7603021/updating-deployed-ssis-package/7605044#7605044

As noted in the comments, if you used package level encryption, you will need to add a parameter for that as well /encrypt file;"foo.dtsx";3;<somepass> This is presented as is because I don't advocate package passwords, nor have I tested it.


How to make PG query faster even when it is using indices I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question Although my query is using indices, how can I make the query faster?

mydb=> explain analyze select count(scores), sum(scores) from mytable
       where classes in ('1st','2nd','3rd') and school_year in ('2018');

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1150864.84..1150864.85 rows=1 width=40) (actual time=185454.773..185454.773 rows=1 loops=1)
   ->  Bitmap Heap Scan on mytable  (cost=16998.63..1146247.22 rows=923525 width=6) (actual time=934.546..184704.246 rows=967663 loops=1)
         Recheck Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
         Filter: (school_year = 2018)
         Heap Blocks: exact=448386
         ->  Bitmap Index Scan on mytable_classes_idx  (cost=0.00..16767.75 rows=923525 width=0) (actual time=736.690..736.690 rows=967663 loops=1)
               Index Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
 Planning time: 1.252 ms
 Execution time: 185463.153 ms
(9 rows)

Additional Details

                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

One odd thing is that the filter Filter: (school_year = 2018) doesn't remove any rows.  So either the entire table has school_year = 2018, or all the rows which meet the criteria classes in ('1st','2nd','3rd') have school_year = 2018. Either way, it seems a bit odd.  Is this how your real problem is, or is it an artefact of creating a smaller demonstration problem?

The slowness probably comes from the IO needed to read a large amount of the table.  Doing the EXPLAIN (ANALYZE, BUFFERS), especially with "track_io_timing" turned on, would be especially helpful to figure that out.

If so, creating an index that includes all of the columns, like:

create index on mytable (classes, school_year, score);

should make this much faster.  It will allow an index-only scan, instead of an scan which still needs to visit the table for each row.  But for this to be effective, the table must be kept well-vacuumed.  The default settings of autovac will probably not be enough.


Problems when add another shard I want to create a simple mongodb cluster but while I am trying to add another shard it is throwing an error.

rs0:PRIMARY> rs.add("node75:27017")

{
"ok" : 0,

"errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node73:27017; the following nodes did not respond affirmatively: node75:27017 failed with Received heartbeat from member with the same member ID as ourself: 0",

"code" : 74,

"codeName" : "NodeNotFound"
}

First.. You are not adding another shard, just new replica set member. Error means that this (primary) could not connect that "node75" at port 27017 to check that it is live.

Problem is probably that, that this name node75 cannot be resolved. There is no IP address for that name.

Try to add line to /etc/resolv.conf

xxx.xxx.xxx.xxx  node75

where that xxx is IP address of that machine.

Or use IP-address at that rs.add("xxx.xxx.xxx.xxx:27017")

Of course you need to check that firewall (at node75) is open for that port 27017.


How to repair mysql tables using myisam storage engine from multiple repairing threads? I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) repairing the indexes by sorting.
FYI, I have kept the indexing to enabled as it was by defult.

Although I am setting myisam_repair_threads to 2 I think mysql is not taking this into count as show processlist shows only one entry in the process list. Also, it takes a little bit of time interval before starting repairing, why?

Please correct my understanding if I am wrong! Why mysql is repairing the tables with only one thread although I have set it to 2? Is there anything I am getting wrong about repairing strategy? TIA!

Read carefully:

MySQL 8.0 Reference Manual  /  ...  /  Server System Variables

  If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process.

If your table have 2 indices - then 2 threads will (must) be used.

PS. I'm not sure that you will see this in processlist... this has to be checked.


Package DBMS_REDEFINITION missing from Oracle 11g? I'm trying to partition an existing table in Oracle and the research I've done seems to say that using the DBMS_REDEFINITION package seems to be the best way to do this.

The problem is I doesn't seem like my DB has this package.  I'm using Oracle 11g, but the online docs suggest it's been supported since 10.2.

Does anyone know how I can download or otherwise gain access to this package?

Thanks!

Privileges Required for the DBMS_REDEFINITION Package 11.2

You do not need that role, that is just an example. I would not use that role, as it grants execute privileges on a lot of other packages as well.

You need the following privileges:

grant execute on dbms_redefiniton to your_user;
grant create table to your_user;
grant create materialized view to your_user;
grant create trigger to your_user;

The above is enough, if you want to redefine tables in your own schema. If you want to redefine tables in other schemas, you will need the "ANY" privileges listed below:

CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
CREATE ANY TRIGGER
CREATE ANY INDEX


To Join two tables without using Primary key Table1: Order;
 Column Name: Id  (PK), SecurutyId, UserId. ** But no foreign key in this table.

Table2: Security;
Column Name: Id (PK), SecurutyId (FK), Symbol.

Can I do That?

Select o.SecurityId, o.AccountId, s.Symbol
From Order as o, dbo.Security as s
Where s.Id= o.SecurityId ;

It is Giving me the result i want. Can anybody explain me why it is giving me the result? Because as far i know to join two tables we need one PK references to another table FK. Am i doing the right thing?

You can join on any column combination you like. You can even join on a condition that does not reference a column at all:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.TblA(a_id INT, c1 INT);
GO
INSERT INTO dbo.TblA(a_id,c1)
VALUES(1,345),(2,132);
GO
CREATE TABLE dbo.TblB(b_id INT, c2 INT);
GO
INSERT INTO dbo.TblB(b_id,c2)
VALUES(1,245),(2,456);
GO

Query 1:

DECLARE @some_value INT = 0;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

[no rows returned]

Query 2:

DECLARE @some_value INT = 2;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

| A_ID |  C1 | B_ID |  C2 |
|------|-----|------|-----|
|    1 | 345 |    1 | 245 |
|    1 | 345 |    2 | 456 |
|    2 | 132 |    1 | 245 |
|    2 | 132 |    2 | 456 |

In this example you get either the cartesian product of all rows in both tables, or no row at all, depending on the condition being met.

Now, if you have a foreign key declared, joining on those linked columns is called a natural join an that is the most common scenario for a join. But as you have seen, it is not the only possibility.

Independent of declared relational integrity, you want to make sure that your tables are indexed appropriately to support the (join) queries your application is made of. Adding an index on columns used in a join is usually a good idea (but there are exceptions).

For more information about SQL Server joins, check out my blog series from last december: A Join A Day


psql: fatal database does not exist error I have a linux user account, postgresql role, and associated database, all called foo.

from the linux user foo, I ran the command

dropdb foo

Now when I try to run psql from the linux user foo, I get the error...

psql: FATAL:  database "foo" does not exist

So does this mean that when running psql from a linux user account, there must be a database associated with that linux user account?

psql assumes that you want to connect to a database, you can either provide one (just after the command) or it will assume you want to connect to a database whose name is the same as your username (or the account name of the process that started psql):

So, you could write:

psql my_db

or

psql -d my_db

or yet

psql --dbname=my_db

... and psql will connect to the (local) database named my_db.

You have more connection options that you can check in the documentation.

If you don't have yet a database created, check createdb.


Efficient sensor data storage So, I have a task to produce design to store data retrieved from sensors, like temperature, pressure, roll etc.

First prototype was quite simple, I created TimeSync table consisting of 2 columns: ID increment, and Time.
Then for each value I would create table of 2 columns: foreign key to timesync id, and value as float.

Reading back data was quite easy, I would filter time sync table from date ranges I'm interested in, and join other tables which I want to read back. The issue was disk space usage. We are quite limited on disk space, and storing 12 parameters for a year at 1Hz logging used ~100gb on Sqlite.

So we made a decision to move to postgresql and apply a little more complicated logic. Thing is, that there is quite a lot of duplicated data, let's say temperature. It doesn't change every 1s, it might chance once a minute, so there is no need to store it so often. The perfect solution would be to store first value received, then on next value check if it has changed, and if so, only then write to database.

So, that means there can be different frequencies at which values are stored, let's say a roll changes every 1s, and temperature changes every 60s.
Now my issue is how to combined that data into single query.

My design so far is to store when device was online, and when it was offline. This will provide clues later on how to do proper filtering.

Next, to store each value in different table, consisting of 2 columns: Time and value itself.

So, some examples, activity table looks like:

ID  DevID   Online              Offline
1   1       2017-01-16 16:13:46 2017-01-16 16:24:38
13  1       2017-01-16 16:32:51 2017-01-16 16:42:16

and data tables:
Data Table1:

Time                Value
2017-01-16 16:13:59 18.9
2017-01-16 16:14:20 17.9
2017-01-16 16:15:08 19.9

Data Table2:

Time                Value
2017-01-16 16:13:57 348
2017-01-16 16:14:05 350
2017-01-16 16:14:17 353

I'm using generate_series from postgresql, and it looks like it is what I need:

select *
--This will generate a series for specified range at specified interval
from generate_series(
                 '2017-01-16 16:10' :: timestamp,
                 '2017-01-16 19:00' :: timestamp,
                 '1 second' :: interval) as date
    --Joining one data table
    left outer join
    (select
         date_trunc('second', data.device_2_p_3.timestamp) as val1time,
         avg(value)                                        as val1avg
     from data.device_2_p_3
     group by val1time) results on (date = results.val1time)
    --Joining other data table
    left outer join
    (select
         date_trunc('second', data.device_1_p_1.timestamp) as val2time,
         avg(value)                                        as val2avg
     from data.device_1_p_1
     group by val2time) results2 on (date = results2.val2time)

order by date asc;

And the readback is:

date    val1time    val1avg val2time    val2avg
14:00.0 null        null    null        null
14:01.0 null        null    14:01.0     349
14:02.0 14:02.0     18.8    null        null
14:03.0 null        null    14:03.0     349.5
14:04.0 null        null    null        null

The issue is that I'm not able to interpolate or repeat data from previous value if device was active at that point. Any clues how to solve this, or suggestions how to improve design would be highly appreciated.

This is one possible approach, although it does not produce exactly what you're asking for, it may be a proxy to get there:

-- Let's assume you have your data in "table_1" and "table_2"
-- They should have an index on column "time"
WITH table_1 (time, value_1) AS
(
VALUES
    ('2017-01-16 16:13:59'::timestamp, 18.9),
    ('2017-01-16 16:14:20', 17.9),
    ('2017-01-16 16:15:08', 19.9)
)
, table_2 (time, value_2) AS
(
VALUES
    ('2017-01-16 16:13:57'::timestamp, 348),
    ('2017-01-16 16:14:05', 350),
    ('2017-01-16 16:14:17', 353)
)

(I've changed "value" to "value_1" and "value_2" for ease)

At this point, we do two things: a UNION of table_1 and table_2 to get all the existing "time" values, then, for each time, make a subquery looking for the value_1 and value_2 closest in time...

-- We first make a UNION to have all present times, then, for each time a SUBQUERY, finding
-- the closest value ORDERing BY time
SELECT
    time,
    (SELECT value_1
       FROM table_1
      WHERE table_1.time <= times.time
   ORDER BY time DESC
      LIMIT 1
    ) AS value_1,
    (SELECT value_2
       FROM table_2
      WHERE table_2.time <= times.time
   ORDER BY time DESC
      LIMIT 1
    ) AS value_2
FROM
    (
    SELECT "time" FROM table_1
    UNION DISTINCT
    SELECT "time" FROM table_2
    ) AS times
ORDER BY
    time ;

The result that you would get is:

|---------------------+---------+---------|
|        time         | value_1 | value_2 |
|---------------------+---------+---------|
| 2017-01-16 16:13:57 |         |   348   |
|---------------------+---------+---------|
| 2017-01-16 16:13:59 |  18.9   |   348   |
|---------------------+---------+---------|
| 2017-01-16 16:14:05 |  18.9   |   350   |
|---------------------+---------+---------|
| 2017-01-16 16:14:17 |  18.9   |   353   |
|---------------------+---------+---------|
| 2017-01-16 16:14:20 |  17.9   |   353   |
|---------------------+---------+---------|
| 2017-01-16 16:15:08 |  19.9   |   353   |
|---------------------+---------+---------|

This is probably not very efficient, but it does (more or less) what you're looking for. It requires that you have an index (DESC) on the "time" column; or the efficiency will be absolutely horrible.

If you want, you can change the "times" query by a generate_series call. It will also work.

NOTE: value_1 on first column doesn't have a value... because we have not yet any reading. For this to work, you would need a whole set of readings at "start time".


Which databases does `mysqlpump` ignore by default? If I run the following command:

mysqlpump > mysqlpump.sql

Is there any documentation, a SQL query that can be run, or perhaps a config file that contains a complete list of databases that mysqlpump ignores by default?

According to this Percona blog post, the following databases are ignored by default:

INFORMATION_SCHEMA
ndbinfo
performance_schema
sys

However, this list was written in 2017 and seems incomplete. I've seen other documentation which indicates that mysqlpump also ignores tables in the mysql database.

The easiest way to know is to test, on Percona Server for MySQL 5.7.28 (latest as of this writing) it shows that mysqlpump includes the mysql schema in the backup:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mytest` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;


Format value to configured number of decimal places I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (hardcoded here) The following statement works fine except that for values like 3.50000 which returns 3.5 even when the configured value for 'NumberOfDecimalPlaces' is 2 or 3. I get that it is the desired behavior for ROUND()

ROUND([ActualValue], COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)) [FormattedActualValue]

As an alternative, I tried CONVERT AND CAST.

SELECT CAST ([ActualValue] AS NUMERIC(16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2))

SELECT CONVERT(DECIMAL (16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)), [ActualValue])

Both of which err to Incorrect syntax near the keyword 'COALESCE'. I understand that the second parameter to the datatype definition is not nullable and hence the error. What are my options? How can I achieve this with minimum performance overhead?

The STR function seems to be what you're looking for:

DECLARE @NumberOfDecimalPlaces tinyint = 3;
DECLARE @ActualValue decimal(16,5) =  3.50

SELECT FormattedActualValue =
    STR(
        @ActualValue,
        CAST(@ActualValue / 10 AS int) + @NumberOfDecimalPlaces + 2,
        COALESCE(@NumberOfDecimalPlaces,2)
    )


Cannot locally import UTF-8 encoded SQL database on Mac I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through:

  An error occurred when reading the file, as it could not be read in
  the encoding you selected (Autodetect - Unicode (UTF-8)).

  Only 1273 queries were executed.

Is this because the database file was originally from a windows machine and it's linebreaks stuffed things up?

I imagine I will get this error even if I tried to send the same database over file transfer to a server so how can I fix it's encoding?

-- MySQL dump 10.13  Distrib 5.5.40-36.1, for Linux (x86_64)
--
-- Host: localhost    Database: *****
-- ------------------------------------------------------
-- Server version   5.5.40-36.1-log

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

--
-- Table structure for table `******`
--

Based on this blog post

http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/

When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.


Calculate inventory out row unit price Currently I have data like this,

CREATE TABLE foo (id, qty, unit_price, date, action) AS
    VALUES
    (1,  2000, 4.01235, '2015-10-10'::timestamp, 'in'),
    (2,   -30, NULL   , '2015-10-11'::timestamp, 'out'),
    (3,  1800, 4.9    , '2015-10-25'::timestamp, 'in'),
    (4, -1000, NULL   , '2015-11-12'::timestamp, 'out'),
    (5,  -980, NULL   , '2015-11-20'::timestamp, 'out');

I need to get calculated avg price for outgoing rows so that result look like this.

id |  qty  | unit_price |    date    | action
----------------------------------------------
1  |  2000 |    4.01235 | 2015-10-10 | in
2  |   -30 |    4.01235 | 2015-10-11 | out
3  |  1800 |        4.9 | 2015-10-25 | in
4  | -1000 |    4.01235 | 2015-11-12 | out
5  |  -980 |    4.02141 | 2015-11-20 | out

unit_price is calculated for outgoing in this way:

for id=2, since there's 2000 unused from only one income the result unit_price is the same as first income row id=1
for id=4, there's still 1970 unused and it's bigger than needed (-1000) - so result unit_price is still the same as first income row id=1
for id=5, there's left only 970 with the first in action, but theres more with in action id=3 1800. So basically i could use -970 with price 4.01235 and -10 with price 4.9. So as result i get the avg price for this row like this ((-970*4.01235)+(-10*4.9)) / -980 = 4.02141 when rounding to 5 decimals.

All the price calculations have to consider date column. Basically it is FIFO calculation logic.

Does anyone have idea how to do this kind of calculation?

There appear to be two key issues here.

One is finding a way to match an 'out' quantity with all relevant 'in' quantities. In your case, row 5 needs to be matched with rows 1 and 3 because it uses both rows' quantities, as follows from the order of ins and outs.

You could try using a method like this. Take the two subsets of the table, ins and outs, and calculate two running totals for each, one including the current value (call it qty_from) and the other excluding it (i.e. the running total of all preceding values). Let us call the former qty_to and the latter qty_from. You will get these results for each subset:

for ins:

qty_from  qty_to
--------  ------
0         2000
2000      3800

for outs:

qty_from  qty_to
--------  ------
0         30
30        1030
1030      2010

Now join the two subsets using a well-known range matching method: A.from < B.to AND B.from < A.to. That will give you intersecting ranges – or, translating into our problem, matching ins and outs. For the example given, the resulting set will look like this:

ins.qty_from  ins.qty_to  outs.qty_from  outs.qty_to
------------  ----------  -------------  -----------
0             2000        0              30
0             2000        30             1030
0             2000        1030           2010
2000          3800        1030           2010

You can see that the first 'in' range repeats three times. That is because the first 'in' row lends its quantity to three 'out' rows. You can also see that the last 'out' range is repeated too, meaning it borrows from both 'in' rows.

Now that ins and outs are successfully matched, the big issue is to correctly determine the quantity that an 'out' row borrows from an 'in' row in case it matches more than one 'in'. There may be variations on the logic to use, here is one:

if ins.qty_to < outs.qty_to,

if ins.qty_from > outs.qty_from,

take the current ins.qty

otherwise,

take ins.qty_to - outs.qty_from;

otherwise,

if outs.qty_from > ins.qty_from,

take the current outs.qty

otherwise,

take outs.qty_to - ins.qty_from.

It only remains to apply your formula to the obtained values, which is essentially SUM(ins.unit_price * borrowed_qty) / outs.qty.

Translating all the above into SQL, we can get a query like this:

SELECT
  outs.id,
  outs.qty,
  ROUND(SUM(x.borrowed_qty * ins.unit_price) / outs.qty, 5) AS unit_price,
  outs.date,
  outs.action
FROM
  (
    SELECT
      *,
      COALESCE(SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'in'
  ) AS ins
  INNER JOIN
  (
    SELECT
      *,
      COALESCE(SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'out'
  ) AS outs
  ON  ins.qty_from < outs.qty_to
  AND outs.qty_from < ins.qty_to
  CROSS JOIN LATERAL
  (
    SELECT
      CASE
        WHEN ins.qty_to < outs.qty_to THEN
          CASE
            WHEN ins.qty_from > outs.qty_from THEN -ins.qty
            ELSE -(ins.qty_to - outs.qty_from)
          END
        ELSE
          CASE
            WHEN outs.qty_from > ins.qty_from THEN outs.qty
            ELSE -(outs.qty_to - ins.qty_from)
          END
      END
  ) AS x (borrowed_qty)
GROUP BY
  outs.id,
  outs.qty,
  outs.date,
  outs.action
;

A demo of this solution can be found at Rextester.


Schema design - Reference slightly different data in different tables I'm trying to design a schema for a MySQL database and got stuck on the following.
My database stores documents which should have relations with blocks of content.

create table document (
  `id`        int unsigned auto_increment,
  `title`     varchar(128) not null,

  primary key(id)
);

create table block_html (
  `id`        int unsigned auto_increment,
  `type`      varchar(32) not null,
  `contents`  TEXT not null,

  primary key(id)
);

create table block_image (
  `id`        int unsigned auto_increment,
  `type`      varchar(32) not null,
  `url`       varchar(255) not null,

  primary key(id)
);

As you can see the block data differs slightly between different types and I'm not sure how to design the relation. Obviously having a simple reference table will not work.

create table document_block (
  `document_id`     int unsigned,
  `block_id`        int unsigned,

  // foreign key??
);

What is a good solution to this problem?

is this what business process of yours?

that must be

document table :

create table document (
  `id`        int unsigned auto_increment,
  `title`     varchar(128) not null,

  primary key(id)
);

block table

create table block (
  `id`             int unsigned auto_increment,
  `id_document`    int // foreign key to document
  primary key(id)
);

block_html (one-to-one relation to block table)

create table block_html (
  `id`             int unsigned auto_increment, // foreign key to block table and make it primary
  `type`      varchar(32) not null,
  `contents`  TEXT not null,

  primary key(id)
);

block image (one-to-one relation to block table)

create table block_image (
  `id`        int unsigned auto_increment,// foreign key to block table and make it primary
  `type`      varchar(32) not null,
  `url`       varchar(255) not null,
  primary key(id)
);


Databases lost after Postgres upgrade from 11 to 12 This is not my first upgrade. In the past I upgraded from 9 to 10 and 10 to  11. I thought that I wrote everything down the last time but apparently not. In verion 11 my databases are in nl_BE.utf-8:
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |
-----------+----------+----------+-------------+-------------+
 glewlwyd  | glewlwyd | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 postgres  | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 prda      | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 template0 | postgres | UTF8     | nl_BE.UTF-8 | nl_BE.UTF-8 |
 template1 | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |

(5 rows)

After the installation (apt-get install postgresql-12) I modify the /etc/postgresql/12/main/postgresql.conf and change the en_US.UTF-8 into nl_BE.UTF-8. In the previous upgrades I copied this file to my data directory but 12 does not seem to like that. The port number of the 11 and 12 version differ.
When I create the database with /usr/lib/postgresql/12/bin/initdb --locale=nl_BE.UTF-8 --pgdata=/srv/postgres/data/12 the new instance is created but the databases are in en_US.UTF-8. The /usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/12/bin -d /srv/postgres/data/11 -D /srv/postgres/data/12 ends without an error.
When I connect to the instance my own databases are not there. Must be because the locale is not correct. I also changed the locale (export LANG=nl_BE.UTF-8) of my shell but this did not help. What could I have done wrong?

I followed the comment from @Colin 't Hart and this solved the problem. With pg_upgradecluster -v 12 11 main /srv/postgres/data/12 the upgrade was done. I changed the port to 5432 and restarted it. Now my applications are using the version 12 database.


Combine indexes to optimize "OR" queries I know that since version 8.1 Postgres can use multiple indexes for queries with or at query time.

I wonder if it is possible to combine multiple columns into one index column at index time. That is if I have a query:

select *
from contacts
where firstname = 'Cassidy'
or lastname = 'Cassidy'
or nickname = 'Cassidy'

Can I create an index that unions all the values from firstname, lastname and nickname so that this search (possibly with an adapted query) becomes a single index lookup?

Query

Note this equivalent sorter syntax for your query:

SELECT *
FROM   contacts
WHERE  'Cassidy' IN (firstname, lastname, nickname);

Resolves to the same expressions in the query tree:

  (('Cassidy'::text = contacts.firstname)
OR ('Cassidy'::text = contacts.lastname)
OR ('Cassidy'::text = contacts.nickname))

The three columns can even have different data types. The single given string literal 'Cassidy' is cast to different data types for this. Exactly like in your original query.

Index

You could use a single bloom index (after installing the additional module, once per database):

CREATE INDEX contacts_bloom_idx ON contacts USING bloom (firstname, lastname, nickname);

Especially if you have more candidate columns like this and query random combinations. Also if you are short on disk / cache memory or the index is rarely needed (so not in cache). Then size matters more and a bloom index is typically small.

But it's better for AND-ed conditions, less so for OR-ed conditions. And the manual advises:

  This type of index is most useful when a table has many attributes and
  queries test arbitrary combinations of them. A traditional btree index
  is faster than a bloom index, but it can require many btree indexes to
  support all possible queries where one needs only a single bloom
  index. Note however that bloom indexes only support equality queries,
  whereas btree indexes can also perform inequality and range searches.

And:

  Only operator classes for int4 and text are included with the module.

For just three columns, three simple btree indexes should be faster and more versatile:

CREATE INDEX ON tbl (firstname);
CREATE INDEX ON tbl (lastname);
CREATE INDEX ON tbl (nickname);

Postgres can rather efficiently combine multiple indexes in a bitmap index scan. Related:

What is a “bitmap index”?
PostgreSQL FTS and Trigram-similarity Query Optimization

A multicolumn index like:

CREATE INDEX ON tbl (firstname, lastname, nickname);

is not very usefull for this. It happens to cover firstname as first column moderately well (not as well as a smaller index on just (firstname)). Additional columns are covered poorly. You are not looking for combinations of columns, so it's the wrong tool.


TimeZone parameter in postgresql.conf postgresql documentation says:

  All timezone-aware dates and times are stored internally in UTC. They
  are converted to local time in the zone specified by the timezone
  configuration parameter before being displayed to the client.

timezone parameter is a session parameter. When all TIMESTAMP WITH TIME ZONEs will be stored as UTC and the client connection will specify the session timezone, then what is the benefit of setting TimeZone parameter in postgresql.conf?

Like with any other parameter that can be defined for every session, the setting in postgresql.conf is the default in the absence of an explicit setting for the session.


How do I get back some deleted records? I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table.  The server is not granting me access to the backup files on the server side.

Is there any way to get back these records?

Is your database in full recovery mode?

If yes, are you doing transaction log backups?

If yes, do you have a backup utility like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLSafe?

If yes, those utilities can undelete objects from backup files (including the fulls and transaction logs) - but explaining how to use those is beyond the scope of what I can do here.  Contact the vendor for instructions.
If no, restore the full backup and transaction logs as a database with a different name.  (Don't overwrite the existing database.)  You'll be able to get an up-to-the-second copy of the object before the delete happened, but you'll need to use the stopat part of the restore command to specify when to stop restoring the commands.  You need to stop before the delete happened.

If no, go grab a copy of a log reader utility like Quest LiteSpeed or Apex SQL Log.  These utilities can connect to the database server, examine the log file, and help you undo transactions.  I'm not sure if the demo versions will work, but the paid ones definitely will.

If no (not in full recovery mode), restore the last full backup as a database with a different name.  (Don't overwrite the database you've already got.)  From there, you'll be able to restore whatever records were online at the time of the backup, but you'll lose all of the changes since.


Suitability of mysql on a raid 5 server with raid card I would like to ask is mysql ok to run a on a raid 5 server. The raid is done using  hardware raid and not software. I saw some older links like this Is raid 5 suitable for a mysql installation? which gives negative opinion on settings raid 5? As time pass I guess hardware have improve and I guess it should be better now ? Any opinion ?

When you're comparing, RAID-10 is still a better architecture than RAID-5. My opinion is that regardless of hardware improvement the underlying functionality still remains the same for RAID-5.

(Even though Ronado's answer has "If you do not use innodb_file_per_table...", he'd still recommend the same.)


#1093 - You can't specify target table 'R' for update in FROM clause As an attempt to answer Difference data between adjacent rows

UPDATE Visits R
SET Duration = TIMEDIFF(
        ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID LIMIT 1),
        R.ReqTime
    )
WHERE R.Duration IS NULL

leads to error #1093 - You can't specify target table 'R' for update in FROM clause.

What to do?

MySQL 5. Here is the Visits table:

CREATE TABLE `Visits` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Session` int(10) unsigned NOT NULL COMMENT 'VisitsSessions',
  `EventKind` enum('search','subsectionList','show','seance','like','superprice','toursList','abonementsList','abonement','popular','sales','tab','mainpage','other') NOT NULL COMMENT 'Also ''purchase'' in Bravo::Visits::Query',
  `ReqTime` datetime NOT NULL,
  `Duration` int(11) DEFAULT NULL COMMENT 'считается ПОТОМ, не при INSERT',
  PRIMARY KEY (`ID`),
  KEY `User` (`Session`),
  KEY `EventKind` (`EventKind`),
  KEY `LoadedTime` (`ReqTime`),
  KEY `Duration` (`Duration`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 COMMENT='статистика для каждого отдельного юзера';

Based on the previous question and this, the following should do what you want.  As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;


How to determine the size of a SQL Server database for a specific date in the past? Microsoft SQL Server 2008 R2 Standard

What I would like to find out is what the size of the DB was at specific dates in the past.  I don't have backups going back that far. The current log may or may not go back far enough.

Is there any way at all to retrieve that type of information?

Unless you capture that information on your own SQL Server does not record it anywhere. Most folks go by the backup size.

If you use 3rd party software to backup the server or where ever you store your database backups you might see if it has a history as well. If you have something like Netbackup Enterprise or other similar software it should keep at most the file name and size unless your backup admins purge that information as well, but most don't that I have worked with.


Escape a quote in SSIS Flat File Conn Manager I am trying to import a simple text file, that has a few "extra" quotation marks in it, and I am trying to see if there is a simple way to accommodate this.

To reproduce the issue, I have a simple three line file I want to import:

"FirstName","LastName","ZIPCODE"
"Tim","Harkin","11111"
"Tim","Harkin"","11111"
"George","Washington","12345"

The extra quote in the third line is throwing off the import.  Looking at the file connection manager, I have the following settings:

However, looking at the preview, the extra quote then ends up "hiding" the column delimiter:

Any suggestions to get the connection manager to ignore that extra quote, and interpret each line as they exist in the flat file?

First, I'll assume you can't simply ask for a correctly formatted csv file. If the last name should really be Harkin", then the entry in the csv file should have escaped the double quote character by doubling it (i.e., "Harkin"""). If this is a recurring process, then the best solution will definitely be to require a correctly formatted file.

I don't know of a good way to force SSIS to read in an incorrectly formatted file and figure out how it should be formatted.

If I were in your shoes, I'd try one of two approaches:

Repair the file manually: Open the file up with a text editor, preferably one that can handle regular expressions. Replace the string "," in the file with a unique token (say, --COLBREAK-- - anything that won't be in your data will work [check first if in doubt]). Then search for double quotes between two characters (i.e., any remaining double quotes not at the start and end of the line). Check these, if appropriate replacing " with "". you might also want to confirm that all lines have the expected number of column breaks, to make sure that you don't have the ","embedded in an existing string, or rows that are missing columns altogether. When done, change your token back to "," and try importing the file again.
Load the data in two phases: If there too much data to manipulate in an editor, rewrite your initial load process to import the entire row as one column. You can process this data in SSIS or load it to a SQL table. Next, check for valid rows (start with ", have "," twice, end with "; and, does not have seven or more instances of " in it), and write those rows to a "valid" file. Write the rest of the rows to an "invalid" file. Process the "valid" file as you originally planned; again, manually resolve the rows in the "invalid" file so they are valid, then process them as well.


MySQL Logical grouping of tables I am coming from MSSQL world and learning MySQL now. I see that schema and database is same in MySQL, however these are different in MSSQL.

I want to logically group my tables as can be done using schema in MSSQL. How can I achieve this?

Edit 1

As per comments, I can't use schema to logically group my tables. Is there any other system which I can use? Maybe using a prefix in table names?

Edit 2

My use case is, I am designing a big software which would have multiple modules. Each of the modules would have multiple tables. I want these tables segregated from each other. Although all the tables would be used for this software only, but they should be kept apart. E.g. Employee module tables should be separate from Inventory module tables.

i like to tell you in mysql , its also schema and database are different things.

schema is present under database.

means in two different database, same named schema may present.

i.e; in any one database this may possible -

create new schema sch1;
CREATE TABLE sch1.pet (name VARCHAR(20), owner VARCHAR(20),

create new schema sch2;
CREATE TABLE sch2.pet (name VARCHAR(20), owner VARCHAR(20),

in any 2nd database which having different connection ip have

above same schema  and  may possibly present..


Joining Across Partitions SQL Server documentation for partitioning states:

Where possible, minimize requirements for referential integrity across vertical and functional partitions.
If cross-partition joins are necessary, run parallel queries over the
partitions and join the data within the application.

https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
I do understand that lack of referential integrity will make writes much faster since there won’t be a need to check other partitions for referential constraints. But how will handling joins in the application be beneficial.The number of requests to the database server and the processing needed will be the same if it was done by an RDBMS join, wouldn't it? (And in addition, for application-side joins, there will be the burden of network round-trips between the application server and the database server.)

Be very careful not to conflate table partitioning with (and?) database sharding. This specific article doesn't do itself any favors by having such a generic and overloaded title, and you're not the first person to start referring to sharding as partitioning.

In fact, there is already a pull request to change the title to Horizontal, vertical, and functional data partitioning.

The minimize requirements for referential integrity bit implies that you shouldn't be sharding data that you commonly join. If your question is "when would handling joins in the application be beneficial?" it would be a narrow use case, probably where latency between the application and a shard is better than the latency between shards (think distributed app and geo-replicated data), and when all of the data is being cached closer to the application anyway (e.g. Redis), as most good applications will plan for at the outset these days. Make sure you have done proper research to determine that (a) you need sharding at all, and (b) that you should be separating data you plan to join a lot. There are almost certainly going to be separation tactics that minimize the need to join across shards or in the application.

Sharding works best when each node that you're writing to contains data that is largely independent and can live without any of the other nodes. When they depend on each other and you're joining between them, you've largely defeated the purpose of separating them in the first place. A good use case is when each customer/tenant has its own shard (or its own database on one of the shards). Most activity for any given tenant is focused on that shard. When central data is needed, it is often replicated to each of the shards, rather than joined across shards. This is the one of the models APS and Azure SQL Data Warehouse use.

  would like to get to the bottom of why MS encourages application-side join instead of a db join

They are talking about aggregate queries there, and I think they are implying that you are concatenating the results, not joining them. For example if customer A is on shard 1 and customer B is on shard 2, and you want to show total sales by customer, the app can (even asynchronously) run an aggregate query on shard 1 for customer A, and an aggregate query on shard 2 for customer B, do that in parallel, and then just union those results together. No joining across shards. I think you are reading way too much into it by saying they are encouraging it or suggesting it is necessary.

And if you want an answer to why Microsoft says something in a very specific way, you're going to have to ask Microsoft. We can help you interpret what they probably mean, and even suggest scenarios where it makes sense, but we can't "get to the bottom" of anything.


Is PostgreSQL 9.2 autovacuum sufficient for a busy table? I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:

CREATE TABLE ch02.markers
(
  ff_id SERIAL PRIMARY KEY,
  col1 boolean,
  col2 smallint,
  created_at timestamp,
  lat double precision,
  lon double precision,
  geog geography(POINT,4326)
);

CREATE INDEX idx_markers_geog ON ch02.markers USING gist(geog);

This table is having records, probably thousands but potentially tens of thousands per hour, inserted and deleted 24/7.

I am wondering if the default autovacuum settings will sufficiently manage the table?

Otherwise I presume I write a SP function that is called by cron. However, what should I use? VACUUM ANALYZE? I am wary of using any anything that locks the table (CLUSTER, REINDEX). Do these even work for an index on a geography point column?

I am not used to such active tables and I am completely new to Postgres, so I have absolutely no feel for this. Any help appreciated.

Before you fiddle with cron jobs and other curiosities you can adjust the autovacuum default settings just inside PostgreSQL. This can be done globally or individually for each table.


SQL server - data successfully inserted in a table but not returned in Select Statement SQL select query not returning records, even though the data insert operation is successful.

We have a table [dbo].[Login] in our database where data is inserted using the below stored procedure. The data is inserted by a windows service written in dot net. It has been working until last month and the records in the table were growing several 100 thousands. Recently, customer complained that the data in not reflected in the reports. Hence, we have investigated and found that, there is no insertion error from the stored procedure, but when we select the table no new records have been returned. We then tried setting the SQL transaction isolation level to Read un-Committed then we are able to see the newly inserted records from the table. We have reviewed the insertion stored procedure but didn't find anything suspicious.

CREATE PROCEDURE [dbo].[gp_Login_Insert]
(
      @ID int OUTPUT,   /*-- the automatically generated value will be returned as an output parameter*/

      @ReaderHardWareID int,
      @CardID int,
      @EmpID int,
      @UEmpID varchar(50),
      @Type varchar(15),
      @DateTime datetime,
      @CreatedByEmployeeId int,
      @CreatedDate datetime,
      @ModifiedByEmployeeId int,
      @ModifiedDate datetime,
      @version_ts timestamp
      )
      AS  /*  ** Add a row to the Login table  */

      if not exists(select 1 from Login where EmpID=@EmpID and DateTime=@DateTime)

      begin

         declare @eventCount int

            select @eventCount=count(*) from [login]
            where EmpID=@EmpID and [DateTime] between  DATEADD(dd, 0, DATEDIFF(dd, 0, @DateTime)) and @DateTime

        if(@eventCount<500)
        Begin

        BEGIN TRANSACTION;

        BEGIN TRY
            INSERT INTO [Login]
          ( [ReaderHardWareID], [CardID], [EmpID],[UEmpID],[Type], [DateTime], [CreatedByEmployeeId], [CreatedDate], [ModifiedByEmployeeId], [ModifiedDate]  )
          VALUES
          ( @ReaderHardWareID, @CardID, @EmpID,@UEmpID,@Type, @DateTime, @CreatedByEmployeeId, GETUTCDATE(), @ModifiedByEmployeeId, GETUTCDATE()  )

          SET @ID = (SELECT SCOPE_IDENTITY())

        END TRY
        BEGIN CATCH
            SELECT
                ERROR_NUMBER()

            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
        END CATCH;

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

        End

    END

Select Query on table returns the following results:

select transaction isolation level read uncommitted
select COUNT(*) from Login where CalFlag is null

returns 59341

select transaction isolation level read committed
select COUNT(*) from Login where CalFlag is null

returns 0

Expected Result: Since SQL server default transaction isolation is Read Committed it should return the correct row count 59341 in Read Committed mode. But it is not doing so.

  SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 >2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) >on Windows 10 Pro 10.0 (Build 18362: )

Thanks in advance,
Ujjal Sarmah

If read uncommitted returns rows but read committed does not that rather suggests that you have a number of open transactions. You can look in sys.dm_tran_active_transactions, especially column transaction_begin_time. If there's anything suspicious there I think you have your culprit.

The procedure itself seems innocent. That would suggest another transaction is opened further up the stack perhaps in a parent procedure, through the application or a server setting. SQL Server support multiple nested BEGIN TRANSACTION / COMMIT statements. A single commit will not persist all the work done by all nested BEGIN TRANSACTION statements. Quote:

  When @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

If you have open, uncommited transactions I can't think what the solution could be. If the connection were severed those transaction would roll back and the data lost. If the instance were restarted those transactions would roll back and the data lost. The uncommitted values will not be in a backup file. Hopefully you have a source external to SQL Server from which they can be salvaged.


How can I *actually* defragment my InnoDB tables? After a recommendation by mysqltuner I've run mysqlcheck --optimize --all-databases successfully (all of the tables reported "note : Table does not support optimize, doing recreate + analyze instead" and "status   : OK"), but my tables are still fragmented. I ran this query:

SELECT engine,
       table_name,
       ROUND(data_length/1024/1024) AS data_length,
       ROUND(index_length/1024/1024) AS index_length,
       ROUND(data_free/1024/1024) AS data_free
  FROM information_schema.tables;

and found that about half of my tables are still fragmented. Several of them are even more than 100% fragmented (i.e., data_free > data_length + index_length). Why would they still be fragmented, and how can I ensure defragmentation without recreating the entire database?

ibdata1 is just 82 MB (the entire DB is > 100 GB), and I've got innodb_file_per_table = on set. I also tried ALTER TABLE schema_name.table_name ENGINE=InnoDB; on one table to check whether mysqlcheck was at fault, but this didn't change the file size noticeably.

Running MariaDB 5.5.

You can either recreate your table or use "optimize table" command to rebuild your table.  Highly recommend making a backup just in case.

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-storage-layout.html
https://mariadb.com/kb/en/mariadb/optimize-table/


Help with Recursive CTE query with INSERT, ON CONFLICT and RETURNING I'm trying to write a query to insert filenames to the following table table path

CREATE SEQUENCE path_id_seq;
CREATE TABLE path (
    id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'),
    parent_id INTEGER,
    name TEXT NOT NULL);

CREATE UNIQUE INDEX path_parent_id_name_ix
    ON path (COALESCE(parent_id, 0), name);

ALTER TABLE path
    ADD CONSTRAINT ios_path_parent_id_fk
    FOREIGN KEY (parent_id) REFERENCES path (id)
    ON DELETE CASCADE
    DEFERRABLE INITIALLY DEFERRED;

For example, given filenames Assets/images/a.png and Assets/images/b.png I'd like to insert the following rows.

id | parent_id |       name
----+-----------+-------------------
  1 |         ¤ | Assets
  2 |         1 | images
  3 |         2 | a.png
  4 |         2 | b.png

Inserting the parent is easy;

INSERT INTO path (name)
VALUES ('Assets')
ON CONFLICT (COALESCE(parent_id, 0), name)
DO UPDATE SET name = EXCLUDED.name -- this strange update is so that we get rows back
RETURNING id, parent_id, name;

which yields;

 id | parent_id |  name
----+-----------+--------
  1 |         ¤ | Assets

But I can't seem to put together a recursive CTE to use the parent's id as the parent_id in the next INSERT statement.

It will probably look somethings like this invalid query below.

WITH RECURSIVE names AS (
    SELECT name, lag(name, 1) OVER () as previous_name
    FROM (SELECT unnest(string_to_array('Assets/images/232.png', '/')) as name) names),

paths(id, name) AS (
    INSERT INTO path (name)
    SELECT name
    FROM names
    WHERE previous_name IS NULL
    ON CONFLICT (COALESCE(parent_id, 0), name)
    DO UPDATE SET name = EXCLUDED.name
    RETURNING id, name
    UNION
    INSERT INTO path (parent_id, name)
    SELECT paths.id, names.name
    FROM paths
    JOIN names ON names.previous_name = paths.name
    ON CONFLICT (COALESCE(parent_id, 0), name)
    DO UPDATE SET name = EXCLUDED.name
    RETURNING id, name)

SELECT *
FROM paths;

But I just can't seem to get it to work. If anyone sees a simpler way to do this it would be much appreciated.

You don't need a recursive CTE, you can use nextval() in the result from the unnest() directly and then use lag() to get the value from the previous row. To deal with existing rows, I would do this using a join between the result of the unnest() and the path table, detecting new and existing rows.

For the new rows, a new ID will be generated and only those will be inserted:

insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case
           when is_new
             then lag(id) over (order by level)
             else existing_parent
         end as parent_id
  from (
    SELECT case
              when p.id is null then nextval('path_id_seq')
              else p.id
           end as id,
           x.name, x.level, p.parent_id as existing_parent,
           p.id is null as is_new
    FROM unnest(string_to_array('Assets/images/232.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new;

Live example: http://rextester.com/YGMT19565

The only "drawback" if you will is that the sequence values are not necessarily assigned in the "order" you'd expect. But as the only purpose of a generated primary key is to be unique (and nothing else) this shouldn't be a problem.


Should I divide my table into simpler ones or should I leave it the way it is? I have a table which must store data related to an examination
The structure of the table is as follows

Table name: Exam_Routine

| Year |  Date   |Stream | Paper_Code

Since I have noticed that there will be only 4 values foryearin the entire table, I decide d to decompose my table into four different tables featuring different years as

Table name: Exam_RoutineYear_1

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_2

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_3

| Date   |Stream | Paper_Code

Table name: Exam_RoutineYear_4

| Date   |Stream | Paper_Code

Is this a better option? or the first one is better? which one will be more helpful.

No, breaking the table up that way is generally not a good idea unless you are talking about table partitioning in which case you have just one table as far as the overall design is concerned and the partitioning is a performance/administration difference.

With a separate distinct table per year you have the following problems, amongst others:

To query based on more than on year you have to UNION the tables back together (or take some other extra steps) which will make the resulting queries less efficient and less easy to maintain.
If extra years are needed later you will have to add new tables and update any views/procedures/queries/other that refer to multiple years as in the above point.
You will need to enforce (by adding database constraints if supported by the DB you are using or in your business logic) that the right rows go in the right tables which adds complexity.