Redesign the storage of large amounts of sensor data I have been tasked to implement/redesign a solution that will store weather data from a sensor array. The array will consist of ~40 towers, each with about ~10 sensors each that'll sample atmospheric conditions at 10 second intervals for an undetermined amount of time (years).
Some of the applications and requirements for this task are as follows:

Manage and retrieve tower/sensor configurations to make sense of data analysis.
Data visualization by sensor or time intervals for meteorological observations.
Provide customers with reliable and persistent data resources/data sets to compare model and sensor performance (may require some post-processing to deliver in the format required?).

Note: The current solution (implemented as a proof of concept, with 5 towers) stores data as flat files (one file per hour).

I was originally unsure if this would constitute a big data problem in the future, so I researched on a couple of solutions both for relational and NoSQL databases, but I feel I need a little more guidance, as I am no expert in data management.

One of the solutions I thought was to store data in a relational database indexed by tower, sensor, and timestamps and partitioning the table by date.

Another, based on future scaling, was to store it in a document-type NoSQL database, like MongoDB, and mimic the structure of the current solution.

Are any of these good approaches? If not, what would be a better/recommended solution? Also, would it even be necessary to redesign the current solution? I was told that the rationale for using flat files is that they believed a relational database would take too much overhead. Is there a way to avoid this if it was the case?

Since (a) the information you are working with appears to be, in a of itself, a very valuable organizational resource, and (b) the volume of data will be considerable, I would decidedly (c) build a relational database on one of the major SQL platforms.

That of course —from a very general perspective— requires three essential  factors:

A clearly defined conceptual schema, in which one has to identify and mark out with precision the prototypes of things, i.e., the entity types (including their properties and interrelationships) of relevance in the business environment you are working with (e.g., the Towers and Sensors you mention).

As you know, this point entails establishing continuous and productive communication with business experts.
A logical layout that reflects the conceptual level with accuracy, by means of tables (i.e., mathematical relations) holding well-delimited columns with appropriate column names and types (i.e., relation attributes) and all the corresponding constraints to ensure that the data complies with all the rules determined at the previous tier.

Therefore, it is here where the vast power of the relational model comes into play (though its advantages have positive repercussions at other levels of abstraction).
A physical arrangement that, e.g., boosts the execution speed of the —dynamic— logical data manipulation operations and guarantees the logical constraints.

Since the relational model offers physical data independence, a database management system (DBMS for brevity) can provide any kind of structure at this level, not exclusively indexes, to support the logical definitions. In the case of the leading SQL platforms, yes, this commonly implies, precisely, setting up an indexing strategy based on the database-specific query tendencies, and you brought up very interesting considerations with respect to some possible configurations but, without knowing the particular informational necessities with exactitude, offering specific advices in this regard would not be suitable.

Other elements that deserve evaluation are, e.g., upgrading network infrastructure to increase bandwidth, enabling proper server configurations (hardware- and software-wise), etc. And, if, and only if, a practitioner is qualified enough, he or she could even modify the source code of the DBMS of choice (more feasible in open source environments, naturally).

In this way, the following aspects that you highlight

  Manage and retrieve tower/sensor configurations to make sense of data analysis.
  Data visualization by sensor or time intervals for meteorological observations.
  Provide customers with reliable and persistent data resources/data sets to compare model and sensor performance (may require some post-processing to deliver in the format required?).

would be well-addressed, because you would easily be able to declare queries to, e.g., obtain information in very meaningful forms. For instance, you can get data associated with

the Sensor identified by SensorNumber 1750, installed at the Tower identified by TowerNumber 31, between the Date 1 June 2017 and the Date 27 June 2017.

Furthermore, since (1) the data in a relational database is logically managed in terms of sets with the aid of operations based on the relational algebra, and (2) the different SQL engines are physically optimized (some more than the others) for set processing, you can, e.g.,

compare set a with set b;
join set c with set d;
obtain subset f through a restriction on set e;
produce n subsets from n set intersections;
project n attributes from set f
retrieve information from set z that is the result of a union of set x with set y;
and so on.

The data manipulation possibilities are in fact huge —demonstrating the unmatched versatility of the relational paradigm— since you can work not only with base tables (the ones declared with CREATE TABLE … ( … ); statements) but also with derived ones (the ones expressed via SELECT …; operations, sometimes fixed as VIEWs). In other words, you can (i) express new data structures based on (ii) prior ones operating on (iii) the single underlying relational construct, i.e., the mathematical relation.

Evidently, the arrangement of the base tables and columns of a relational database can evolve, and (a) new base tables or columns may be incorporated into it when (b) keeping track of new entity types or entity type properties is deemed worthwhile in the pertinent business context. In other words, neither the initial structure nor the opening constraints of a relational database are expected to be static or immutable. Besides, a database that is appropriately organized from the very beginning tends to be much easier to modify when new informational requirements arise.

In agreement with the considerations above, the logical format of the applicable sets must be produced declaratively, at the database logical level. The graphical or presentational formatting of the sets (e.g., the colouring or font faces utilized) must in turn be processed by way of the code of one or more application programs (yes, mostly in a procedural manner, perhaps with the assistance of an object-oriented framework, HTML, etc.), in order to make the access and presentation of such sets user-friendly. Certainly, you could as well use reporting software that connects with your database.

The modeling of a database of relevance

Given that you will be working with Sensor data (which, among other features, typically involves information in the shape of time series), you may find of help multiple database design and overall administration principles contained in the two exceptional answers, by @PerformanceDBA, to the questions entitled:

“Opinions on sensor / reading / alert database design”, and
“Historical / auditable database”.

The Relational, Flat File and NoSQL approaches

The relational model by Dr. Edgar Frank Codd, although published in 1970, remains genuinely the most modern and elegant method (based on logic and set theory) to cope with the problem of data management. The distinct SQL DBMSs are, in turn, the most popular approximations (that, although not fully compliant, are nevertheless really powerful) to the systems proposed in the relational theory, and some of them have been heavily optimized (e.g., regarding their physical-level mechanisms) for even decades now. In addition, the main SQL platforms of course can (and will be able to) work with the most up-to-date storage (e.g., hard drives) and processing (e.g., CPUs) technologies quite efficiently.

When built on a powerful DBMS, a relational database that is properly designed at the conceptual, logical and physical levels would decidedly become a self-contained, self-descriptive and self-protective asset that (1) is trustworthy and (2) offers a fast response, two aspects that, as you know, are of great significance.

Flat files

Therefore, the claim that follows

  I was told that the rationale for using flat files is that they believed a relational database would take too much overhead.

is easily discarded, because the flat file approach would be:

pre-scientific;
far from optimal for large volumes of data;
too cumbersome;
application program-dependent (and you would have to implement by hand most of the features that proper DBMSs offer natively);
its performance will easily be undermined;
etc.

Whereas the —much more convenient— relational fashion, to say the least:

would offer large scalability (it is physical-level-independent, so you could enhance the underlying physical mechanisms as needed);
would bring a simple style to manipulate the data (via abstract operations) and
could work with multiple application programs simultaneously (e.g., one or more mobile apps, and/or one or more web apps, and/or one or more desktop apps, etc.).

If, however, you opt for utilizing flat files, you should evaluate the employment of a robust utility like Awk that, although not a DBMS (and was not designed as such), supplies handy resources to deal with files, records, fields, etc. See The GNU Awk User’s Guide for more information on this subject.

NoSQL

“Unstructured data” and associated terms

As per their propaganda, the initial justification for the usage of NoSQL DBMSs is that they are meant to be used in business domains that involve handling “unstructured data”, so that calls for the question:

What is the expression “unstructured data” supposed to mean?

In that respect, it must be said that data, by its very nature, is structured; if it had no structure then it would be something meaningless, consequently such a thing (i) could not be considered data and (ii) would not be worth administering. Hence, “unstructured data” is a contradictory and unfortunate expression.

Other phrase of those contexts is “semi-structured data”. That phrase suggests that there exists data that is structured “in part” or “in half” so, in accordance with the previous paragraph, only the “part” or “half” that is structured can be actual data, the remaining “part” or “half” is merely a shapeless thing because it lacks structure, and cannot be referred to as data.

Yet another, alas, typical term that is found in NoSQL marketing is “polymorphic data”. If said term signifies something like “data that has many different forms”, then it is in fact ordinary data, it comes about in many distinct forms as always. And since it has many different forms, then it presents many distinct structures, so there is nothing special about this “kind” of data.

Needless to say, data and data structures have always been susceptible to changes, then there is nothing unusual in this regard either.

Data volume growth

Evidently, the volumes of information managed by means of computerized systems have definitely grown over the years —and will keep growing exponentially as time passes, because new systems are being built every day—, but that is a fact that does not have to do with the structure of the information per se.

Lack of a rounded theoretical foundation

A critical limitation of NoSQL systems (there are of different classes, e.g., document- and graph- based) is that none of the current products —although heavily marketed and labeled as “modern”— possesses a sound theoretical basis (if at all) that supports each and everyone of the three most important elements of a proper DBMS, i.e., tools for data (a) definition, (b) manipulation, and (c) constriction. Thus, the NoSQL approach actually suggests a regression to an ancient era in which the handling of the data was performed in an ad hoc and unsound course of action, with all the needless complexity it entails.

Nowadays, graph systems are included within the “NoSQL” spectrum. These software products invite to manage data by virtue of operations on two distinct structures: nodes and relationships —which, once again, is in conflict with the term “unstructured data”—, and they stand out in the “NoSQL” group because they do have a mathematical foundation. However, graph products are rather similar to network platforms, which are deemed obsolete since tens of years ago (an obvious drawback is that, as suggested above, they need two structures for data representation, while a relational DBMS —as per the information principle— needs only one).

Even if the creation of the different NoSQL systems is chronologically newer in comparison with the origins of the majority of the SQL DBMSs, most of the concepts onto which the NoSQL products are based are, in effect, primitive.

A NoSQL program should be employed, mostly, in scenarios where, e.g.,

the IT personnel lacks the technical skills required to determine (or to determine opportunely) the structure of the data of interest —e.g., due to its complexity—; and/or
the organization cannot afford suitable education and training for the present staff, or cannot hire new staff that possesses the required education and training; and/or
when the integrity and consistency of the data is not particularly important; and/or
when blending the concerning data with that of mission critical systems that demand high precision is not expected.

But, even if the structure of the data at issue is not defined before the creation of the concerning systems, one ore more people will necessarily have to

discover the aforesaid structure,
discard all the surrounding “interference” and
collect and link the proper data

after the database and app(s) have entered the production stage in order to be able to get the most out of all the resources invested in a project, then data structure delineation is a task that cannot be bypassed, it has to be done sooner or later.

So, while going the NoSQL way is a possibility, all the factors previously mentioned should definitely be taken into account.

The most robust method

In contrast, approaching the informational requirements of a business environment in a relational manner —i.e., with a general paradigm behind— offers the possibilities of (1) managing the data in its natural structure from the beginning —which eases integration with other data sources— and also of (2) producing new trustworthy structures by dint of the manipulation of a single instrument —as explained in previous sections— that has a robust scientific basis.

According to your description of the scenario in question, you have already identified a particular structure in terms of the relevant organizational needs, so I suggest requesting that the business domain experts validate it. Successively, I recommend taking advantage of (i) the constructs —the relation, the constraints and the operations— provided by the relational model to handle said structure and the respective data, and of (ii) your SQL DBMS of choice which will very likely offer very efficient physical tools that can hold up the present demands and will supply future scalability.


postgres deadlock without explicit locking I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock was detected is wrapped in transaction block though. Anyway, how comes it?

Suppose session 1 does:

begin transaction;
update a set col = val where pk = 1;
update a set col = val where pk = 2;
commit;

and at the exact same time session 2 does the opposite:

begin transaction;
update a set col = val where pk = 2;
update a set col = val where pk = 1;
commit;

Then session 1 will be waiting for session 2 to commit or rollback the update on the row where pk = 2 while at the same time session 2 will be waiting for session 1 to commit or rollback the update on the row where pk = 1. Deadlock.


Prevent a user from making changes to some columns in a MS SQL Server database I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best as I can. Our ERP allows me to block certain fields/columns from users through their security tabs. Sounds great. However, if a person uses an ODBC or DSN connection they would be able to change data. As an example I don't want the user to be able to change costs or anything financial in a customer order table, but have no issue with him correcting incorrect addresses or shipping information. If I lock the table as READ ONLY then using the ERP would also block that column.
I hope i'm making sense here.
Thank you!

You can revoke update on that column from a particular user:

REVOKE UPDATE ON OBJECT::Employees.Salary FROM theUser;

See: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-object-permissions-transact-sql?view=sql-server-2017


How to fix discrepancies between publisher and subscription data? I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and the subscription database.  I've done a manual validation over the record counts and there are small differences. I know there's a latency between the two sides, so that's not to worry too much, but there is a specific table that will not synchronize no matter what (new rows appear, but some specific older rows do not appear, so they look like they were removed after synchronization).

The situation is that the subscription database is not read-only, so an application may read and also write data to this database.

Now I'd like to know what solutions would you use for synchronizing the two sides.
I can think of two right now:

use the tablediff.exe utility for each article to compare source and target tables and generate scripts
reinitialize the subscription so it will invalidate last snapshot and generate a new one.

Are there any other solutions to fix this?

I'll answer this in 2 parts.

First, if you want to get the data back in sync, then you should reinitialize. Check to see what is changing the data though. If this is a big table, reinitialization could take a while.

Secondly, If the subscriber isn't read only and you are trying to do transactional replication, you are going to run into this error a lot. People, apps, whatever can change a couple rows and boom, the emails start flying in. If you need both sides to be able to CRUD data, then I would use merge replication. This enables both sides to update data.


SQL Server Backup To a NAS Box I'm trying to backup to a NAS box but I'm getting the following error:

Cannot open backup device '\\nas02\backup\SQL\instance\Full-Backups\OtherDBs\Daily\MGMT_TEST.bak'. Operating system error 1326(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

This was working before and I have checked permissions to the location by logging onto the server as the account sql is running under and I can go to the location above. From there i can create new files and update existing ones etc.

There is no space limitation
The user running the statement is a sysAdmin

I'm thinking some permissions of some type but struggling to see how.

It means either it's permission issue or you are running out of space on target.

Permission Issue

The service account of SQL Server (which used to start SQL Server service) does not have access to the target.

START > RUN > type SERVICES.MSC.

Find SQL Server Service > Right click and go to PROPERTIES.

Go to LogOn/Security tab

If it's system account (NETWORK SERVICE, LOCAL SYSTEM etc.) then change it to any specific account (domain account like ex. dba\sqlserviceact).

Then restart SQL Server Service.

And grant the account (ex. dba\sqlserviceact) read & write privilege on the shared folder (network path).

If it's other then system account the grant the account ready/write privilege.

Thanks


Why does everyone want me to create an alert for severity 025? Brent Ozar and others suggest to listen to severity 025 SQL-Server events. I searched for the meaning of all different high severity events, looked through different articles and read the most recent msdn documentation. But the only severity that seems to be monitored by everyone but explained nowhere is 25.

What is severity 25, where is it explained and what does it mean?

Severity 25 is a 'catch-all' fatal system error.

  A severity 25 error is a fatal system error. I have heard that
  severity 25 is more or less a catch-all for miscellaneous fatal
  errors. I have only seen this error when related to failed upgrades:
  something prevents one of the upgrade scripts from running, and a
  severity 25 error is thrown

http://sqlperformance.com/2015/04/sql-performance/dealing-with-high-severity-errors-in-sql-server


SSIS Transaction Problem I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback.

I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to "Required".

But it fails to execute the package and gives DTS error . (Everything is working fine if i remove the Data Flow from Sequence Container).

i have done the changes on server as described here:
http://www.databasesupport.eu/categories/sql-server/1482-ssis-distributed-transactions

Could you please tell me if there is something i am missing.
Do i need to do DTS settings on both the servers from where i am importing the data and to which i am importing.

Please tell what i am missing. I am very new to SSIS.

Yes. You will need to configure all the servers involved with your process to be of DTC.

When you use the native SSIS transaction capability, if the connection managers are entirely self contained, then behind the scenes a standard BEGIN TRAN...COMMIT|ROLLBACK are issued. When there are multiple servers involved, that's when the distributed transaction coordinator comes into play. Even if you're only reading from a source table, SSIS will attempt to enlist that SELECT statement in a transaction.

We learned this the hard way when dealing with DB2 as they apparently have the ability to enable/deny transaction stuff. We also had issues using an antiquated MySQL instance as it didn't support the concept of a transaction. In that instance, we had two data flows. One ran without a transaction and wrote to a local file, then used a transaction controlled data flow to read that file into our target table. Not an advisable approach for very large data sets but worked fine for our problem set.


Comparing values from two tables in a nested where clause. This is a rather specific question. I'm working to create row restrictions in Business Objects and the customer wants records to fall within a certain range from another table. The problem is, you can only select one table. A work around I found was that I can select other tables in the WHERE clause.

For Example:

a.RE_UID = b.RE_UID

I have to use:

a.RE_UID IN
      (select A.RE_UID
       from
        TABLE1 A
        join
        TABLE2 B
        ON
        A.RE_UID = B.RE_UID
      )

Where I'm stumped is that I need compare off another table with a BETWEEN. Something like:

A.CL_FROM_SVC_DTE BETWEEN B.EFF_DTE AND B.END_DTE

If I just try like I did above I get errors that it expected another character after WHERE:

SELECT *
FROM
  TABLE2
WHERE IN
(select A.CL_FROM_SVC_DTE
FROM TABLE1 A
JOIN TABLE2 B ON A.RE_UID = B.RE_UID
)
BETWEEN B.EFF_DTE AND B.END_DTE

If you are selecting everything from Table2 with the condition on Table1, what you could do is this:

SELECT T2.* FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T2.RE_UID = T1.RE_UID
WHERE T1.CL_FROM_SVC_DTE BETWEEN T2.EFF_DTE AND T2.END_DTE

I hope it helps.


How to avoid lengthy CASE statement? Below is my simplified scenario. We have a AdCampaign table with the below columns:

ID   Provider     Campaign         AdFlag
 1   Instagram    Influencer123    1

I have been updating the AdFlag based on a lookup table that contained the list of Providers, like so

update t
set AdFlag = 1
from AdCampaign t
join lookup.AdFlag s on t.provider = s.provider

Now the requirements have changed and the logic is something like this:

set AdFlag = 1 according to:

when Provider = 'instagram'
       and campaign = 'InstagramCampaign'
when Provider = 'instagram'
       and keyword like '%foobar%'

when Provider = 'google'
        and keyword like '%foobar%'

when Provider = 'bing'
        and campaign like '%abc123%'
        and keyword like '%foobar%'

... and so on for about five more providers with slightly different logic

Before this requirement change, it was data-driven and clean. Now, it looks like I'll need to replace my lookup table with a lengthy and harder to read CASE statement.

My question is: is there a better approach?

You can still make it data driven - you just need more data!

First some data setup:

drop table if exists #Lookup;
drop table if exists #AdCampaign;

with LookupData as
(
    select * from
    (
        values
        ('instagram', 'InstagramCampaign', NULL, NULL),
        ('instagram', NULL, NULL, '%foobar%'),
        ('google', NULL, NULL, '%foobar%'),
        ('bing', NULL, '%abc123%', '%foobar%')
    ) as T(Provider, CampaignEqual, CampaignLike, KeywordLike)
)
select * into #Lookup from LookupData;

with CampaignData as
(
    select * from
    (
        values
        (1, 'Instagram', 'InstagramCampaign', NULL),
        (2, 'bing', 'Contains abc123 internally', 'bazfoobarbix')
    ) as T(ID, Provider, Campaign, Keyword)
)select * Into #AdCampaign from CampaignData;

There's no magic in there. It's all just a convenient (for me) way to populate some tables without worrying about datatypes, constraints, keys and whatnot all. The pertinent bit is the columns in #Lookup. I've inferred that Provider is mandatory. Campaign is split in two depending on how the values are to be compared. I'll get to that in a minute.

I use NULL in #Lookup to represent "not applicable" or "not a part of this comparison".

And here's the query that does the matching:

select
   a.*,
   '|',           -- just a visual separator for SSMS
   l.*
from #AdCampaign as a
inner join #Lookup as l
on a.Provider = l.Provider      -- I'm taking this as mandatory.
and (l.CampaignEqual is NULL or l.CampaignEqual = a.Campaign)
and (l.CampaignLike is NULL or a.Campaign like l.CampaignLike)
and (l.KeywordLike is NULL or a.Keyword like l.KeywordLike);

The idea is to have one column in #Lookup per value-operator pair. These then translate quite directly into the WHERE clause of the actual update. So Lookup.CampaignEqual compares to AdCampaign.Campaign using and "equals" operator. That's why Lookup contains both CampaignEqual and CampaignLike - so it's obvious what to compare to and how. This design can be extended indefinitely with columns such as ValueEqual, ValueLessThan, ValueStartsWith etc.

You could have fewer columns in #Lookup at the cost of more involved predicates. For example if we have just Lookup.Campaign then the predicate becomes

.. and (l.Campaign is NULL
     or a.Campaign = l.Campaign
     or a.Campaign like l.Campaign
     ... etc.
   )

The meaning's the same. I think the performance will be the same. It's a matter of style and what you're comfortable with maintaining.

It may be a little easier to get the lookup data correct with just a single column. Putting a wildcard in a ..Equal column or missing them from a ..Like would cause the matching to break.

OK, so more data and a somewhat lengthy where clause.


Has the guidance on the setting for 'cost threshold for parallelism' changed with the advent of columnstore indexes? First off, what I'm not asking.  I'm not asking what my setting should be.

Many are recommending upping the value past the default, and I certainly understand why that's the case for B-Tree based queries.  But I've been reading about the (almost) linear scalability of in-memory clustered columnstore indexes, and I'm wondering if setting the cost threshold too high could cause SQL Server to starve columnstore-based queries for CPU cores.

So the question is this:  Does SQL Server treat columnstore indexes differently when the 'cost threshold for parallelism' is concerned, and should that cause me to change my decision about what my initial setting should be?

Beyond the Cost Threshold setting, SQL Server appears to treat parallelism differently for columnstore indexes depending on your SQL Server version (2012 vs 2014) and even the datatypes in your table.

I'd start with Joe Chang's post benchmarking decimal vs float datatypes, and read the comments on that post as well. If you want to get exactly the right MAXDOP and Cost Threshold for Parallelism settings for your system, you'll need to perform the level of detailed testing that Joe does in his post, and that takes a lot of work. Because of that, I would focus on your system's primary bottleneck first - use wait stats to make sure parallelism or CPU pressure are problems for you, and then start by tuning the most CPU-intensive queries rather than making system settings changes.


Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data We are building a system with concept of Admin and Employee. so basically Admin is an employee with all powers and can view all the data created by other Employee.

CREATE TABLE `Vendor` (
  `vendor_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email_Id` varchar(40) DEFAULT NULL,
  `landline_Number` varchar(15) DEFAULT NULL,
  `mobile_Number` varchar(15) DEFAULT NULL,
  `address_Line1` varchar(65) NOT NULL,
  `address_Line2` varchar(65) DEFAULT NULL,
  `city` varchar(255) NOT NULL,
  `pincode` int(6) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`vendor_Id`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `username` varchar(40) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_username_unique` (`username`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `action_emp_id_foreign` (`emp_Id`),
  CONSTRAINT `action_emp_id_foreign` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

There are other tables Roles and EmployeeRoles which I thought were not required here.

Approach 1 : Now, when the Admin logs in to see all the Actions created by everyone

we first need to query the Employee table to find all the employees of that Vendor (we will have the Vendor_Id stored in the session when the admin/employee logs in)
Then query the Action table with where in employee_Id array from Step 1

Is this a good approach ?

Approach 2 : or in Action table, I shall store Vendor_Id for each record (mainly all this effort only so that when Admin logs in I can easily retrieve all the records for that Vendor. When Admin logs in from the session I can easily find the Vendor_Id and query the Action table.

I don't know at this moment which would be a better approach. Any suggestions ?
Like Action, there are other 3 tables where similar concept needs to be applied.

Edit 1: There can be a case where we can have  multiple vendors registered under a Single brand (future extension) and the Super-Admin would like to analyze the data across multiple branches.

Edit 2:
Employees can create ** new rows ** by using the features; but admin would be interested to see all the activities that are going on in that feature by different employees. Basically, admin should be able to see how are different employees using the system

Maybe have separate table for Vendor_id, Admin_id, employee_id. You could join on isnull(admin_id, employee_id).

That way, if the user has an admin_id, they could see all the records. If they don't, they would see only the records for their employee_id.


Replace value based on previous row / next row depending on conditions I have a price table, contain 2.9 million rows of price figures belong to a total of 941 of company_id for three months. 0.00 represents missing data. As an example:

   +------------+---------------------+------------+
   | company_id | date_time           | price_open |
   +------------+---------------------+------------+
   |      4     | 13/12/2014 16:23:00 |    56.30   |
   |      4     | 13/12/2014 16:24:00 |    56.25   |
   |      4     | 13/12/2014 16:25:00 |    56.25   | <--- last price of `company_id` = `4`
   |      5     |  22/9/2014 08:00:00 |     0.00   | <--- first price of `company_id` = `5`
   |      5     |  22/9/2014 08:01:00 |     0.00   |
   |      5     |  22/9/2014 08:02:00 |     5.45   |
   |      5     |  22/9/2014 08:03:00 |     5.25   |
   |      5     |  22/9/2014 08:04:00 |     0.00   |
   |      5     |  22/9/2014 08:05:00 |     0.00   |
   +------------+---------------------+------------+

May I know if I can achieve the below by entirely using MySQL command? If yes, any guidance/help would be much appreciated.

Loop through each company_id and sort price_open by its
date_time, then
if found any price_open = 0.00,
replace this/these 0.00 value(s) with previous known price_open
figure, but
if the price_open = 0.00 is in the very
first row or first few rows of a company_id, do not replace with
previous known price_open figure. Instead, use the next known
price_open figure - because it shouldn't mix up with other
company_id.

Expected result will be as follow:

   +------------+---------------------+------------+
   | company_id | date_time           | price_open |
   +------------+---------------------+------------+
   |      4     | 13/12/2014 16:23:00 |    56.30   |
   |      4     | 13/12/2014 16:24:00 |    56.25   |
   |      4     | 13/12/2014 16:25:00 |    56.25   | <--- last price of `company_id` = `4`
   |      5     |  22/9/2014 08:00:00 |     5.45   | <--- first price of `company_id` = `5`
   |      5     |  22/9/2014 08:01:00 |     5.45   |
   |      5     |  22/9/2014 08:02:00 |     5.45   |
   |      5     |  22/9/2014 08:03:00 |     5.25   |
   |      5     |  22/9/2014 08:04:00 |     5.25   |
   |      5     |  22/9/2014 08:05:00 |     5.25   |
   +------------+---------------------+------------+

I have tried searching about this but those weren't really close to what I am trying to achieve.

Note: It doesn't matter if it has to be done in a new table rather than original price table.

Thank you very much in advanced! :-)

Add this composite index if you don't already have it -- it should probably be the PRIMARY KEY.

INDEX(company_id, date_time)

Run this a few times (until it stops modifying any rows)

UPDATE    tbl a
    JOIN  tbl b
       ON  b.company_id = a.company_id
      AND  b.date_time  = a.date_time + INTERVAL 1 MINUTE
    SET a.price_open = b.price_open
    WHERE  a.price_open  = 0.00
      AND  b.price_open != 0.00;

In case I am wrong, suggest you copy the table over for testing:

CREATE TABLE tbl LIKE real_tbl;
ALTER TABLE tbl DROP PRIMARY KEY,
                ADD  PRIMARY KEY(company_id, date_time);  -- if needed
INSERT INTO tbl SELECT * FROM real_tbl;
run the update a few times
check the values
if satisfied, ...
RENAME TABLE real_tbl TO old, tbl TO real_tbl;
DROP TABLE old;

On the presumption that your SELECTs will be company-centric, it is better to have PRIMARY KEY(co, dt) than (dt, co).


Import Oracle .dmp with error 1659, unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM I'm trying to importing an Oracle DUMP file (v10.2) into Oracle Express 11.2.0.2.0. I was getting some schema errors and resoled them by following other posts here. Now I'm getting this error and I'm not able to understand it enough to troubleshoot it:

C:\>imp system/pass FROMUSER=DWH TOUSER=IMPORT_MAN file=e:\LRT_ARCHIVE_170911.dmp

Import: Release 11.2.0.2.0 - Production on Wed Sep 20 11:01:48 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by DWH, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing DWH's objects into IMPORT_MAN
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "LRT_ARCHIVE_170911" ("EVENTTIME" TIMESTAMP (6) WITH LOCAL TIM"
 "E ZONE, "TTR_ID" NUMBER(10, 0), "STATUS" VARCHAR2(3), "ISEMPTY" VARCHAR2(1)"
 ", "TDP_IDCURR" NUMBER(6, 0), "TNP_IDLASTPOS" NUMBER(6, 0), "STORINGREASON" "
 "VARCHAR2(20), "ISPROCESSED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 "
 "MAXTRANS 255 STORAGE(INITIAL 1824522240 FREELISTS 1 FREELIST GROUPS 1 BUFFE"
 "R_POOL DEFAULT)                   LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM
Import terminated successfully with warnings.

Would really appreciate if someone can point out why this is happening and how to resolve it.

Also, I was running the command with the full=yes command-arg but that was not running at all and reported that I cannot use full with FROMUSER

Background:
This is a freshly installed instance of Oracle Express. I got the dump and am tasked to restore it. When I tried originally, I followed the warning about a schema/user that actually created/exported the dump so I created a new user with same name DWH. I think that was a non-issue. Apart from that, I have not done much in the two hours I have spent on this.

01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE

As stated above you have two options, either increase the size of your SYSTEM tablespace, which might be easier or first extract the table DDL(CONTENT=METADATA_ONLY) from the dump file and alter the DDL script to change MINEXTENT to lower value and run the script to create tables then import the data(CONTENT=DATA_ONLY).

From comment...

  I'm afraid I don't. I do have this query in my clipboard history: ALTER TABLESPACE ADD DATAFILE but don't actually know how to use it or why is the SYSTEM schema being used here when I created the IMPORT_MAN user/schema

The SYSTEM mentioned in your error is a tablespace, not a schema. SYSTEM tablespace is used to store metadata of system and user objects such as tables under SYS schema. So, while importing the tables you need to have space on SYSTEM tablespace as well as in user's default tablespace where it stores the rows. To add datafiles, refer to this link ALTER TABLESPACE


Deleting data directly from backup file Is it possible to update the data directly inside the backup file (*.bak) in SQL Server 2014?

The scenario is that I want to automate the process of providing the "smaller" database to some endusers/developers (just deleting some rows/updating values in column) to be restored by them furthermore. Can I achieve this goal by running any command to directly update the *.bak ... or I have to prepare additional (copy of) database, then restore it from this *.bak, then provide my "cleaning" (running update command) and then prepare different *.bak file again?

Restore it to a dummy DB, purge the data that you don't want them to have, make another full backup, drop the dummy DB, and then provide them the copy of the BAK file at that point.


automatically change value on the table mysql i have table invoice in my database that contain atributes 'due date', 'date' and 'status'. What i want is data on 'status' change automatically. Example, status (unpaid change to expired) when date past the due date. Does it make sense ?

There are two ways to do this. The more traditional way is with a cron job on the server:

http://unixhelp.ed.ac.uk/CGI/man-cgi?crontab+5

Another way is to do it with MySQL events:

https://dev.mysql.com/doc/refman/5.6/en/events.html

I personally prefer to keep this kind of logic in the app via a cron job and keep mysql events dedicated to db maintenance related items (partition maintenance for instance).


How to use / as sysdba in pluggable database? I'm trying to make a export, usually I use userid=\"/ as sysdba\" syntax in Linux command line (I don't know sys password in my production environment.
How can I do this using pluggable database? I tried something like this: userid=\"/ as sysdba@mypdb_oracle_net\" but it does not work.
expdp userid=\"/ as sysdba@mypdb_oracle_net\" dumpfile=full_%U.exp logfile=full.log directory=teste full=y parallel=4 version=11.2.0

Export: Release 19.0.0.0.0 - Production on Tue Mar 10 11:18:06 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
UDE-00002: invalid username or password

Username:

First of all, to export from a PDB you shouldn't use SYS for that. It is way over-privileged. You should create a user in the PDB and use that user to connect to the database. You can use the privilege "exp_full_database" to grant the necessary privileges.

Next, you have to specify a connect string to go directly into the PDB (via the relevant service). It is "mypdb_oracle_net" in your example. However, when doing so, you must specify the password for SYS. You can't use OS authentication when doing this.

To solve your problem I would do something like this:

SQLPLUS / AS SYSDBA
ALTER SESSION SET CONTAINER=mypdb;
CREATE USER EXPORT_USER IDENTIFIED BY YOUR_PASSWORD;
GRANT CREATE SESSION TO EXPORT_USER;
GRANT exp_full_database TO EXPORT_USER;

EXPDP EXPORT_USER/YOUR_PASSWORD@mypdb_oracle_net dumpfi......


Modifying replication distributor & publisher I have a SQL Server 2008 transactional replication setup as follows:

192.168.100.1 <-- Distributor
192.168.100.2 <-- Publisher
192.168.100.3 <-- Subscriber

Our network team want to change the IP address of the distributor, for example to: 192.168.200.1

Also, we want to move the publication database to a new server. so, the replication source (publisher) will be also moved and it will have a new IP address as well.

The subscriber will remain as is.

I cannot afford having long downtime as our systems are running for 24x7 and there are so many huge tables that are already in replication and generating a new snapshot will take long time to be replicated.

What is the best way to seamlessly re-configure the replication and keep my data in sync without rebuilding it from scratch

Thanks

Changing the IP addresses is no problem.  You'll just need to force AD replication after the machine comes up with the new IP then do an IPCONFIG /flushdns on the other machines so that they can all see each other.  Depending on which machine has it's IP changed and if you are doing push or pull replication you may not even need to do that.

Moving the distribution database is a lot harder to do.  You can shutdown all the replication agents and do a backup and restore of the distribution database to the new machine, making sure to use the KEEP_REPLICATION flag.  You'll then need to recreate all the SQL Agent jobs for replication on the new machine manually.  Then you'll need to edit the replication tables manually to change the distributor to the new name, then edit all the agent jobs to give them the name of the new distributor.  Once all that is done everything should work again.

Moving the publisher requires that the same approach be taken as moving the distribution database.


MySQL: why assign NULL as default value When we define a table, multiple times we assign default value of a column as NULL. I have

How NULL is different than empty value('') as well as 0?
Is there any impact of NULL value in query performance?
How it affects the index created on that column?

Thanks...

NULL is different from empty string '' and 0. Empty string as well as 0, are existing values that can be used in equations and other operations.

'' = '' is true

0 = 0 is true

NULL = NULL is not true

Quoted from Wikipedia:

  Null is a special marker used in Structured Query Language (SQL) to
  indicate that a data value does not exist in the database.

I'm not sure what kind of impact on performance would be expected with NULLs instead of ''s or 0s, but AFAIK no, there is no big difference in performance between the two. Somebody may correct me if I'm wrong. An exception to this rule is noted below.
First of all, with NULL you can have a UNIQUE INDEX on a column, and still have many NULL values. This is by definition of the SQL NULL. Read more at mysql reference

Also, if you use InnoDB (I'm only saying this because your question is tagged with mysql-5.1), then no value is stored at all, which may result in big reduction in storage and possibly faster searching (since more rows fit into one page)

On the contrary, with MyISAM, nullable column indexes may have increased size causing slightly increased storage space required.

In the end, it is neither bad, nor good practice to use NULL columns. Combined with bad design they can be a headache. But with good design they are useful and helpful. NULL columns deserve to have their position in well designed databases, they're just not appropriate for every column.


Help about Oracle licensing I would like to know some details about oracle licensing.  What is oracle processor licences? How does it differ from the other?We are planning for a dual processor server, do we need to purchase 2 diff licences??
Thanks in advance. .

Oracle licensing is a complex topic. You can find information on the Oracle pricing page.

You can find there links to a paper called "Software Investment Guide" that has a lot of licensing samples.
Also there can be found some papers about special licensing topics and a price list.

There is also a FAQ concerning Oracle licensing (not an Oracle site) that may be interesting.

DISCLAIMER: All information is offered in good faith and in the hope that it may be of use, but is not guaranteed to be correct, up to date or suitable for any particular purpose.

Oracle has several different types of "editions" of its database prodcut:
Enterprise Editions, Standard Edition, Standarad Edition One. Enterprise Edition has additional options must be licensed if enabled.

Oracle has two types of licenses: so called "Named User Plus" licenses and "Processor" licenses. You can either license the machine where Oracle is used. Then you have to use Processor Licenses.  Or you can license the users/devices that are able use the database. Then you have to use "Named User Plus" Licenses.

If you use Enterprise Edition the number of Processor Licenses needed is the number of cores multiplied by a factor that depends of the processor type. I you use Standard Edition (Standard Edition One) the number of needed Processor Licenses equals the number of processors in the machine. You cannot use Standard Edtition (Standard Edition One) on all machines: machines that have or can have more than 4 processors cannot be licensed with Standard Edition . For Standard Edition One the limit is 2 processors.

If you use Processor Licenses you always have to license whole machines except for some special situations (look at the "partitioning" document in the "Speciality Topics" section of the Oracle pricing page) where it is possible only to license a part of a machine. If you have a cluster , failover, backup or test environment you havee  to check how to  license this environment additional machines.

If you use "Named User Plus" licenses than bear in mind that a user is not a database user but a person or device that uses the database. So for example if you have an application that is accessible by all of your employees and this application stores its data in an oracle database then all of your amployees need "Named User Plus" license. Even if the application uses only one database user.

There is also a minimum number of "Named User Plus" licenses that is needed depending on the number of Processors/Cores that are used for Oracle databases

A machine with 2 intel processors and standard edition  needs 2 processor licenses if one uses processor licensing.

If enterprise edition is used  it depends on the number of cores. The core factor is 0.5 for Intel processors. For  2 Hexacore processors  2*6*0.5=6 "processor licenses" are needed. The same number of licenses is needed for every licensable option, too.


SQL Server: Cannot resolve collation conflict between "Latin1_General_CI_AS" and "[garbage]" So I'm writing a large UDF in T-SQL to be used in reporting. The UDF contains quite a few common table expressions.

At one point I was adding another CTE:

 cteCmtCauses AS (
    SELECT ProductId = p.Id,
           Name = hz.Name,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]

    UNION ALL

    SELECT ProductId = p.Id,
           Name = c.Name,
           --C = c.Cmr_HasCarcinogenicRisk,
           --M = c.Cmr_HasMutagenicRisk,
           --R = c.Cmr_HasToxicForReproductionRisk,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]
 ),

 cteCmtCausesConcat AS (
    SELECT ProductId = p.Id,
           ComponentIds = (
                -- Here the issue happens
                SELECT CONCAT(cte.CMT, N'|', cte.Name, dbo.QueryConcatenationString())
                FROM cteCmtCauses cte
                WHERE cte.ProductId = p.Id
                FOR XML PATH(N''), TYPE
           )
    FROM [redacted]
 ),

When trying to persist the UDF mutation, I got this error:

Msg 468, Level 16, State 9, Procedure QueryProduct, Line 93
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "널㾍.鉀杫.....祉߾.䊙꛸.鈀杫..." in the concat operation.

And in fact on every attempt the message changed a little bit:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "࿠䚋.剀焩.....祉߾.䊙꛸.刀焩..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠䥆.뉀洶.....祉߾.䊙꛸.눀洶..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠洦.퉀洷.....祉߾.䊙꛸.툀洷..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "焐柘.牀䯏.....祉߾.䊙꛸.爀䯏..." in the concat operation.

I was able to work around it by using:

SELECT CONCAT(cte.CMT, N'|', cte.Name COLLATE Latin1_General_CI_AS, dbo.QueryConcatenationString())

But the weird thing is, everything in the database and tempdb has the same collation: database collation, being Latin1_General_CI_AS (except from one of the tables used in the UNION ALL which is Latin1_General_CS_AS).

Gist of full reproducible sample, make sure that the database collation is Latin1_General_CI_AS.

How do I properly resolve this issue, is this a known bug, and do I need to worry about SQL server silently corrupting my data once I start using this UDF?

Using

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Sep 23 2016 15:49:43
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

UPDATE

I finally had time to test this and was able to reproduce the problem. While not respecting collation precedence (noted in my original answer below) was a problem, it was not this problem (though both could be caused by the same underlying bug). Here is what I can now confirm:

The O.P.'s test script (via Gist) does not produce this problem as it's missing a critical piece: forcing the collation of one of the NVARCHAR "Name" columns in the Component and Statement tables to be different than the other.
The actual error is a collation mismatch between the two "Name" columns in the UNION ALL.
The collation mismatch in the UNION ALL should have ended the query before it even got to the CONCAT function, and it would have if both columns were VARCHAR, but if at least one column is NVARCHAR, then the CONCAT function prevents the query from terminating properly.
There are two ways in which the query can behave improperly when CONCAT is involved (at least in this UNION ALL scenario):

If the column from the subquery (containing the UNION ALL with the collation mismatch) is not the first parameter of the CONCAT function, then it will terminate with a misleading error message, stating that the error is in the "concat operation" instead of being in the "UNION ALL operation" (oh, and let's not forget about the garbage collation name in the error message!)
If the column from the subquery (containing the UNION ALL with the collation mismatch) is the first parameter of the CONCAT function, then it will  actually succeed, using the database's default collation for the value returned by the CONCAT function. (see final test case below)

The CONCAT built-in function was fixed as of SQL Server 2014 as neither this behavior, nor the incorrect behavior shown in the "Original Answer" (see below) is reproducible starting with that version (and I tested with 2014, 2016, 2017, and 2019).

-- DROP TABLE #Mix;
CREATE TABLE #Mix
(
  [VC1]  VARCHAR(50)  COLLATE SQL_Latin1_General_CP437_CS_AS,
  [VC2]  VARCHAR(50)  COLLATE Azeri_Cyrillic_100_CS_AS_WS,
  [NVC1] NVARCHAR(50) COLLATE Frisian_100_CS_AI_KS,
  [NVC2] NVARCHAR(50) COLLATE Sami_Sweden_Finland_100_CI_AI
);
INSERT INTO #Mix ([VC1], [VC2], [NVC1], [NVC2]) VALUES (0xB0, 0xDE, 0xDE, 0xDE);
SELECT * FROM #Mix;
/*
VC1    VC2    NVC1    NVC2

░      Ю      Þ       Þ
*/

SELECT CONCAT(N'Both VARCHAR', sub.[WhatEva])
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [VC2]
    FROM #Mix
) sub([WhatEva]);
/*
 Msg 457, Level 16, State 1, Line XXXXX
Implicit conversion of varchar value to varchar cannot be performed because the
   collation of the value is unresolved due to a collation conflict between
   "Azeri_Cyrillic_100_CS_AS_WS" and "SQL_Latin1_General_CP437_CS_AS" in
   UNION ALL operator.
*/

SELECT CONCAT(N'At least one NVARCHAR', sub.[WhatEva])
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [NVC1]
    FROM #Mix
) sub([WhatEva]);
/*
-- 2012
Msg 468, Level 16, State 9, Line XXXXX
Cannot resolve the collation conflict between "{db_default_collation}" and
   "堓.ꚤ鍛翹.堓.툀堗.툀堗.쓀姧.꺱䱷..꺱䱷......꺱䱷..툘堗.帎鍲翹.堓..錿翹..."
   in the concat operation.

-- 2014, 2016, 2017, 2019
Msg 451, Level 16, State 1, Line XXXXX
Cannot resolve collation conflict between "Frisian_100_CS_AI_KS" and
   "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator occurring in SELECT
   statement column 1.
*/

-- SUCCESS!?!?!?! Should be an error!!!
SELECT CONCAT(sub.[WhatEva], N':At least one NVARCHAR') AS [ConcatSuccessWTF?],
       SQL_VARIANT_PROPERTY(CONCAT(sub.[WhatEva], N':At least one NVARCHAR'),
                            'collation') AS [ResultingCollation]
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [NVC1]
    FROM #Mix
) sub([WhatEva]);
/*
-- 2012
ConcatSuccessWTF?          ResultingCollation

░:At least one NVARCHAR    {db_default_collation}
Þ:At least one NVARCHAR    {db_default_collation}

-- 2014, 2016, 2017, 2019
Msg 456, Level 16, State 1, Line XXXXX
Implicit conversion of nvarchar value to sql_variant cannot be performed because the
   resulting collation is unresolved due to collation conflict between
   "Frisian_100_CS_AI_KS" and "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator.
*/

  is this a known bug

Not sure if it was noted in any public forum, but it must have been noticed internally (to Microsoft) since it was fixed as of the next version (i.e. SQL Server 2014), though not in any Service Pack as I tested on SQL Server 2012, SP4 GDR (11.0.7462.6).

  How do I properly resolve this issue

If you, or anyone else, is still using SQL Server 2012 and running into this, it is best to resolve the collation conflict at the source, which is in the UNION ALL operation. Just pick the table with the column that has the collation that you do not want, and apply the COLLATE clause there such that the UNION ALL operation succeeds on its own, even if no CONCAT function is being used. This is better than specifying the COLLATE in the CONCAT function because that is after the fact of the UNION ALL, which in this case should be failing, but is allowed to succeed due to the bug in CONCAT.

ORIGINAL ANSWER

The Collation problem (separate from the error message resulting from the Collation problem) is due to the CONCAT built-in function not honoring Collation Precedence, and hence requiring all input parameters to be of the same Collation. Clearly you have one input parameter that is not of the same Collation as the rest. That parameter is cte.Name, which you have currently fixed via the COLLATE keyword.

We can simulate this scenario as follows. You can run it from any database. The default Collation for the Database that I am executing the following code in is: SQL_Latin1_General_CP1_CI_AS.

CREATE TABLE #TT (Col1 NVARCHAR(50) COLLATE SQL_EBCDIC278_CP1_CS_AS);
INSERT INTO #TT values ('something');

SELECT CONCAT('now this is ', tmp.Col1)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
*/

And the following two queries even show that the Collation is evaluated per each input parameter, with the first input parameter setting the Collation to be used:

SELECT CONCAT('now this is ', tmp.Col1, N' else' COLLATE Latin1_General_100_CI_AS)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "Latin1_General_100_CI_AS" in the concat operation.
*/

SELECT CONCAT('now this is ' COLLATE Latin1_General_100_CI_AS, tmp.Col1, N' else')
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_Latin1_General_CP1_CI_AS" in the concat operation.
*/

This conflict can be fixed in two ways:

Don't use the CONCAT built-in function. The main benefit of CONCAT is that it doesn't require each parameter to be a string type; it will handle the conversion to string internally. This is convenient if you have several non-string items to concatenate. But if you only have strings, then it doesn't provide any benefit, and probably even hurts performance to pass all of that stuff into the function. AND, by not using CONTACT, Collation Precedence will take over, and in most cases resolve the conflict automatically.

SELECT 'now this is ' + tmp.Col1
FROM #TT tmp;
-- now this is something

In this case, Collation Precedence will determine that the Collation of the tmp.Col1 column overrides the Collation of the string literal (which uses the "current" database's default Collation).
Use the COLLATE clause (as you are already doing). There is nothing wrong with this approach as this is one of the uses of the COLLATE keyword.

-- Force the Collation of the column in the temp table to match the "current" database:
SELECT CONCAT('now this is ', tmp.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS)
FROM #TT tmp;
-- now this is something

-- Force the Collation of the string literal to match the column in the temp table:
SELECT CONCAT('now this is ' COLLATE SQL_EBCDIC278_CP1_CS_AS, tmp.Col1)
FROM #TT tmp;
-- now this is something

In these two cases, the Collation to use is determined by the first input parameter, and either that one needs to be explicitly set to the Collation of the second parameter (bottom example) which in this case is taken from the columns definition. Or, the second parameter needs to be explicitly set to match the Collation of the first parameter (top example) which in this case is taken from the databases's default as it is a string literal.


How can I verify I'm using SSL to connect to mysql? I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL:

[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=~/certs/ca-cert.pem

When I log in with my client and view the status, it lists a cipher on the SSL line:

mysql> \s
--------------
SSL:            Cipher in use is DHE-RSA-AES256-SHA

Without installing something like wireshark to verify that the connection is secure, can I assume that I'm connecting via SSL based on this information?

From the client, just run status.  If this connection is using SSL, you'll get something interesting in the SSL row.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

Connection id:      12
Current database:
Current user:       replicator@domU-12-31-39-10-54-BD.compute-1.internal
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.30-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     boston.hugskeep.wstudent.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         44 min 49 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.005
--------------

mysql>

If this connection is not using SSL, you'll get:

SSL:            Not in use

You can also use:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

But I think the first is more attractive, and sure easier to type.


Advice on table processing for invoice-allocation-payment structure for invoices of zero amount Please may I have some advice/opinions on the best process to use in the following situation.

To deal with subscription payments in a small charity I use a cash matching system comprising

Table invoice PK invoice_id;

Table cash_payment PK cash_payment_id;

and linking Table payment_allocation PK allocation_id, FK invoice_id, cash_payment_id.

This gives me a many to many relationship between invoice and payment. (the invoice table is only used for admin, no real invoices are sent out)

All is good except for the following situation.

Some members are honorary and do not have to pay subscriptions. Hence there is no requirement to create a record for them in the invoice table. If they don't pay then all is OK.

However, some of these members choose to pay the subscription anyway, resulting in a record in the 'cash_payment' table that does not have a corresponding invoice record or one in Table 'payment_allocation' (as there is no invoice to allocate it to) .

I therefore have two choices. Either

Generate an invoice for everyone, including those for whom the amount will be zero and record all payments made, linking them to the appropriate invoice.
This will result simply in an over payment being recorded for those honorary members who choose to pay unnecessarily.
Do not generate an invoice for any zero amount. When an unnecessary subscription payment happens I insert a record in Table cash_payment with a new payment_type of contribution but without any corresponding record in the payment_allocation table,
This will avoid zero amount invoices but might make it more difficult to report on cash flow and things like over/ under payment.

I'm tending towards choice (1) Does anyone have any hints regarding which might be the better method?

In the end I went with option 1. That way it copes with people paying unnecessarily as there is an invoice against which to place the payment. It also allows for refunds to be made against zero amount invoices if someone pays unnecessarily but then wants a refund. The payment / refund can be put in the cash_payment table and allocated to the invoice through the payment_allocation table just like any other invoice.


How to structure T-SQL query with many left joined "attributes" I am writing a T-SQL query for a complex report, with a unique key, and have run into a bit of a performance problem.  I am not sure where it's coming from, but I thought I'd ask how it should be done.

One issue that may become apparent is that I am doing lots of left joins on the same attribute/lookup tables.

Basically, I the query is structured into a "trunk" and "leaves", like so:

select *
  -- many many fields, probably about 150
  -- I explicitly list them in the real query
  from
  -- the "trunk" of the query, all of which are necessary,
  -- and which resolve to a single row
       foo inner join bar on foo.id_foo = bar.id_foo
           inner join baz on baz.id_bar = bar.id_bar
           inner join bon on bon.id_bar = bar.id_bar

   -- the "leaves", where i do a bunch of lookups:
           left join attribute height on height.CD_NM = 'height'
                                     and height.id_foo = foo.id_foo
           left join attribute weight on weight.CD_NM = 'weight'
                                     and weight.id_foo = foo.id_foo
       --    etc, for maybe 50 different attributes, etc
  where foo.id_foo = @ReportId

Currently, I am applying the bisection method to figure out if there is some number of attributes that the database starts choking on.  I commented out half the left joins, and un-commented until it started slowing down.

Execution time appears to increase non-linearly, and quickly, in the number of lookups.  At one point, the execution time jumped from <1s to 14s, and now each additional look up appears to add a fixed amount of time.

Strangely, the "live query statistics" say that the key lookups on my core tables (which have indexes, which are getting used) are taking up the bulk of the time.  I would have presumed this would continue to take less than 1s, regardless of the number of additional lookups.

I'm not even done doing lookups for this query and I'm already closing in on 20 seconds.

I am just a lowly analyst, not a database engineer, so I'm not in a place to do much to tune the database itself, but anything I can include in an SSRS dataset is fair game.  (Theoretically, I can make a tuning change happen, but I'd need something concrete to take to the database people)

Is splitting this query up into multiple queries really my best option?

In the original Plan you have few Merge Join and then Nested Loops.

When the things slow down, you can see that plan is changed to: Hash Match (and parallel).

You can try to enforce using old plan by replacing inner join with inner MERGE join and inner LOOP join.

The documentation may help you dive into differences between them: Hints (Transact-SQL) - Join

Take note of the warning:

  If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords.

If you do not wish to force the join order for all joined tables in the query, consider using OPTION (LOOP JOIN, MERGE JOIN) instead. This will allow the optimizer freedom to reorder joins and choose between nested loops and merge join on the basis of estimated cost.


Does leaving queries open in SSMS lockup memory on the SQL Server? We have a logging database that has been locked down from developer access.  The reason it was locked down was because they say that people would run large queries and then leave the results up in Sql Server Management Studio (SSMS).

They read somewhere that doing this leaves the connection open and holds onto memory on the SQL Server.

I had never heard this, but if it is true, then I want to make sure I start closing my queries, especially when I run large ones.

Is it true that leaving a query open in SSMS will hold on to extra memory on the server?  If so, is there a way around it?

The only significant memory that would be held would be if SSMS was being run on the server itself (and then you'd still be limited to the 2GB of memory space that SSMS can allocate).

There will be a very small amount of memory allocated to maintaining the connection in SQL, but that's truly trivial.

Of course, if you leave an open transaction, that's a whole other level of pain, but it doesn't sound as if that's the trouble your developers are trying to avoid (unless this is what they are trying to avoid, but came up with a PC way of telling them that).


Pros and Cons of having additional columns in join table of many to many relationship I searched but did not find any discussions on this topic.
Are there any pros and cons on creating many extra columns on joined table?
Does it affect speed of data retrieval because we are joining 3 tables?

As far as what I think, it may slow tiny bit but it is necessary otherwise how can we save additional data about many-to-many relationships. Or is there any other way of which I am not aware of?

In general, the wider the table the slower the query. But slower doesn't necessarily mean slow.

The number of joins probably has more to do with the use of id numbers than anything else. Natural keys can speed performance dramatically when the important information is carried in the key. (This happens more often than you might think.)

The M:N table is the natural and sensible place to store information about that M:N relationship.  So adding columns to store information about that relationship makes perfectly good sense. Wider tables are usually slower, but narrow tables that don't store data are less useful.


Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram? I have a problem statement (briefed),

Database for a Fitness Club -
Client's Attributes: Name, Address, Ph. No.
Membership Plan's Attributes: Beg. date, Sub. Fee, Monthly Fee, Duration
Trainer's Attributes: ID, Name, Salary
Relationship-Types: Client is 'Enrolled In' a Mem. Plan, and Client 'Meets' Trainers
Enrolled In: Client has total participation while Mem. Plan does not.
Meets: Client & Trainer, both have total participation.

By Meets, it means, a Client entity attends a session guided by a Trainer entity.
So, Client & Mem. Plan are weak entity types & Trainer is a strong entity type.
When converted to an ER diagram, Client & Mem. Plan are related to each other, and Client is related to Trainer.
Is it a possible to have such an ER diagram or is there another way to rectify the same?
Edit: This ER diagram represents what I think it should be (without partial keys) and consider arrows in diagram as straight lines connecting two blocks/shapes.

Feel free to correct me if I'm wrong anywhere.
I also have an entity 'Workout Session', which has a relation with Trainer. A Workout Session is conducted by one Trainer only, but the problem states to keep data of only Trainers which a Client meets, not the Sessions he/she attends. But since that wasn't related to the question I needed to ask, so I didn't add that part.
I think there is no single attribute of either Client or Membership Plan which I can use to distinguish their respective entities uniquely, so I think Client and Membership Plan are weak entity types.
I have provided as much details as I can as I can't disclose the complete statement due some reasons.

Depending on how much you briefed your problem statement, it can be stated that:

Trainer is a strong entity (it has an ID)

Neither Client nor Membership Plan seem to be weak or strong entities.
They both could be considered strong entities provided that a primary key is defined on them*; e.g. Client.(Name, Phone) and Membership Plan.("Beginning Date", Duration)

In your Chen diagram, Meets entity should have both ends total participation, but there's an arrow between Trainer and Meets?
Other than that, it seems to be alright.

* A strong entity is defined as having a key that does not depend on any other key; a weak entity as as having a key that depends on a foreign key. E.g. Order has a primary key order_id which is independent, and that makes it a strong entity, whilst Order_Detail has a primary key order_detail_id and unique key order_id, line_no, which makes it a weak entity since order_detail_id does not provide a meaningful independent ID, and the unique key depends on Order table. See weak entity entry in Wikipedia.
They're independent from other entities, so they are candidates for being considered strong entities provided that they have a primary key, which is not known if they have and is not clear if they could in the definition.


Distinct column in an inner join query sql I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist).
I want to run a DISTINCT query showing the Artist's Image AND Biog.
I am getting plenty of records with the code below, but cannot get the DISTINCT to filter out duplicates. I want the DISTINCT to work only on the Artist firstname and surname, but for it to disregard the images, which are all distinct.

Here is the code:

SELECT ArchiveCatalogues.ARTIST,
   ArchiveCatalogues.IMAGE,
   ArchiveCatalogues.FIRSTNAME,
   ArchiveCatalogues.SUFFIX,
   ArchiveCatalogues.TITLE,
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues
INNER JOIN artist
ON (ArchiveCatalogues.ARTIST = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)

Any image would be acceptable at this stage. I was thinking that the main query should be the one that queries the table with the BIOGS, since each artist is distinct on this table (no artist is mentioned more than once.) Then, all I need to do is pull the image in from the other table.

this will give image only

SELECT
   max(ArchiveCatalogues.IMAGE),
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues
INNER JOIN artist
 ON (ArchiveCatalogues.ARTIST    = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
group by
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog


Is it ever a good idea to denormalize for integrity? I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.

These problems can take a variety of forms - they might be multiple choice (What's 2 + 2? A: 2. B: 3. C: 4), or they might require the user to calculate an answer of their own, in which case I need to constrain their input to be something like '440' or '1/2' or '.333'. Some of the problems might prompt the user to type in an essay. And, of course, I may need to add more types of problems later.

The tables I'm envisioning, in a simplified form, are something like this:

CREATE TABLE problems
(
  problem_id serial NOT NULL PRIMARY KEY,
  problem_type text NOT NULL, -- Refers to a lookup table
  answer_letter text, -- If not null, refers to the correct answer in the answers table below.
  response text -- If not null, represents a correct answer to be input, like '0.4'
);

CREATE TABLE answers
(
  problem_id integer, -- Foreign key
  answer_letter text,
  content text,

  CONSTRAINT answers_pkey PRIMARY KEY (problem_id, answer_letter)
)

CREATE TABLE questions
(
  user_id integer,
  created_at timestamptz,
  problem_id integer, -- Foreign key
  answer_letter text,
  response text,

  CONSTRAINT questions_pkey PRIMARY KEY (user_id, created_at)
);

So, the problems table would have a variety of constraints to ensure that:

When problem_type is 'multiple_choice', answer_letter must not be null and response must be null.
When problem_type is 'user_input', answer_letter must be null and response must not be null. Response must also consist of only a few characters.
When problem_type is 'essay', both answer_letter and response must be null, since I can't really have a correct answer for an essay question.

This is clean enough, and constrains the problems table just fine. I might use an enum instead of a lookup table for problem_type, since all of its possible values will already be baked into the schema anyway.

My difficulty is, how to constrain the questions table? Its constraints will be very similar (I don't want an answer_letter supplied for a question that references an essay problem, and so on). I can think of a few options:

Create a unique index on problems (problem_id, problem_type), add a problem_type field to questions and include it in the reference to problems, then use it in check constraints in a way similar to problems. This is the way I'm leaning right now, since it seems cleanest overall, but then I'm denormalizing to achieve the proper constraints, which feels wrong to me.
Create three problem tables, one for each type, and constrain them separately. Do the same with three question tables. This feels like the pure relational way to me, which is typically what I'd like to go for, but it also feels way too complex. I don't want to have to deal with unioning three tables (or more, later on) to get a user's question history.
Go with #2, but use Postgres' inheritance support to try to keep the logic simple. But since you can't point a foreign key at a hierarchy of tables, again it's not a very clean solution, it's a solution that would again need to be hacked around.
Use triggers to ensure that questions data fits the corresponding problem. Maybe it's because I don't have much experience with triggers, but I'm concerned that cramming that sort of imperative logic into the DB will eventually become unmanageable.
Forget the question constraints, handle it in app logic, and hope for the best. You can't constrain everything all the time. Of course, I don't really like this idea either.

I feel like there's a problem with my approach to modeling that's leading me to these difficulties, especially since I'm running into a couple of cases very similar to this elsewhere in my schema (this one was just the easiest to describe). Maybe it's just a domain that's difficult to model, but I feel like there must be a better way, and I'm just not normalizing correctly.

Help? Thanks!

You are getting at something that is missing from standard normalization discussions, namely the constraint dependencies.  In general wider tables provide greater possibilities here than narrower tables.  So my view is that the sorts of questions you are asking in fact highlight good reasons to denormalize.  I would go with your first solution (the one you are leaning towards right now).

In my view good database design generally normalizes as far as possible but ensures that all columns necessary for proper data constraints are included.  You can do some of this with composite foreign keys if you don't mind adding additional unique constraints on the parent tables.  Leveraging data constraints an important part of database design and one should not sacrifice that for the sake of normalization that looks good in theory.


When to use a TCP dynamic port and when TCP Port? I have a new server that is configured using a dynamic port as you can see below:

The application that connects to this server uses the following connection string, but I am getting the following error:

I asked the developer to change the connection port from 61844 to 53187, which is my dynamic port.

After done that and restarting their services, the application could connect to SQL Server.

Is there any specific reason it needs to be using a dynamic port?

Say that something else uses the port that you have configured for your SQL Server: What do you prefer?
The SQL Server to start but use a different port? Use dynamic.
Not start at all? Use static.

Another aspect is that your developers seem to have hard-wired the port number in the application (connection string) i.e., connect to something e.g. yourserver,45346. That is one way to do it. Another way is to connect using the instance name such as yourserver\instancename. This way the port resolution is handled for you, but it requires that the SQL Server browser service is started on the server machine and you can reach it using UDP port 1434 for the name->portnumber "query".


Ms Access queries equivalent in Sql Server Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do

SELECT * from allProducts;

My questions is, what is the equivalent in Sql Server? I'm currently working on a project where we are converting all our ms access databases over to ms sql server. Tables get converted over to tables, but what about queries knowing very well our programmers used access queries as exampled above.

A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you.

Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.


Service Broker: Queue Monitor is dropped after poison message I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer.

When I setup event notification on a queue, it creates a queue monitor and if receiving a message from that queue causes a poison message the queue monitor disappears. It's not in "RECEIVE" or "INACTIVE" it's just drops. And after enabling the queue with ALTER QUEUE somequeue WITH STATUS = ON; the queue monitor does not re-appear and and I need to drop and recreate event notification in order to have event notification working again.

So the question is, is that by design or not? And after I re-enable the queue after poison message is it a common practice to recreate event notifications.

Thanks!

When a transaction rolls back five times, Service Broker disables all queues that the transaction received messages from, even if you have poison message handling turned off. This would mean recreating the event notifications.  So, adopt try .. catch and handle your poison messages.  You get four tries before SB will handle it for you.


How to "join" two tables without PRIMARY KEYs I have two tables in which there are no primary nor foreign keys.

Table 1 (Persons):
name, region, department
Table 2 (Access):
name, access_level

How can I answer the following question:

  How many people in each department have data access?

If we're to assume that these tables are actually used to determine who has access, then the query from blobbles' answer is the answer, even if there are multiple people with the same name in Persons.

If there are 4 "John Smith"s in Persons, and Access indicates "John Smith" has data access, then every "John Smith" would have data access.

If this is merely a record of access granted (rather than what defines the access granted), and Persons.name is not unique, then you can't know for certain. Perhaps two "John Smith"s have been granted access, perhaps only 1, perhaps none. The record is incomplete, because we don't know how access ties back to different Persons with the same name.


Query rows with common value over result set partition I'm on Postgres 11 and I have these tables

 foo              foobar

| id |      | foo_id | bar_id |
------      -------------------
|  a |      |      a |      1 |
|  b |      |      a |      2 |
|  c |      |      b |      2 |
|  d |      |      b |      3 |

And I need all bar_id which are common to all foos. So I have this query:

select b.bar_id from foo f join foobar b on b.foo_id = f.id

which gives me

1
2
2
3

while I'd need just 2, i.e. the intersection of the result set partitioned by f.id. Then, I might apply a where clause, e.g. where f.id in (...). How can I achieve this?

It seems that your desired result do not use all foo values, there is no ('c',2) and ('d',2), then you can remove it from the equation and use only foobar table.

select
    barid
from
    foobar
group by
    barid
having
    count(fooid) = (select count(distinct fooid) from foobar);

| barid |
| ----: |
|     2 |

db<>fiddle here


is it recommend to set "--setParameter ttlMonitorEnabled=false" for better performance if you don't use TTL Indexes We run a few sharded clusters and many replicasets. Our customers don't use TTL Indexes.

Will there be a better performance if we disable background thread that is responsible for deleting documents from collections with TTL indexes?

mongod --setParameter ttlMonitorEnabled=false

I found an old (2013) Time to live (TTL) slows down mongod post on mailing list. I don't know if this is a edge case or general optimizion.

Quote from SERVER-10033

  May be used to stop TTL deletes if the system is under extreme load or
  during an outage/corruption investigation. A single global (or per-DB)
  setting would be easier than having to update or drop all TTL indexes.

Except under edge case conditions I can't see this making any noticeable impact if you are not using the feature, it is intended as an emergency measure if you are using the feature and experiencing heavy load (stop deletes until load subsides basically).

Since you are not using TTL anywhere all MongoDB will be doing is scanning your indexes for one that contains the expireAfterSeconds field. The example you linked was an edge case itself, not many people have a quarter million indexes in a database, but even in that case a read query on such a small collection as system.indexes is unlikely to use enough resources to cause you issues. It might show up in the slow query logs, but since it is basically a no-op and only takes a read lock, there would be minimal impact elsewhere.

That's the theory out of the way, if you want to judge impact for your particular use case, profile your system so you know what current performance looks like, then profile it after you turn it off. If it does have an impact, then I am sure the engineers at MongoDB would like to know about it (and your use case) so they can evaluate how the feature operates and see if the behavior you see is intended or not.


Throw and RaiseError of level 16 do not cause alert to occur The next alert has been created:

USE [msdb]
GO

/****** Object:  Alert [Notify]    Script Date: 09/09/2014 11:38:31 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Notify',
        @message_id=0,
        @severity=16,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

I expect that the next code will cause it to occur:

THROW 51000, 'The record does not exist.', 1;

Or the next code:

 RAISERROR ('Error raised.', 16,  1);

System Messages does not contain the error messages:

SELECT *
FROM sys.messages
WHERE language_id = 1033
and severity>=16
ORDER BY severity asc, message_id asc

I also have no events in History tab:

What am I missing?

From reading the BOL article on sp_add_alert

  [ @severity = ] severity - The severity level (from 1 through 25) that defines the alert. Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to define the alert, severity must be 0.

If you want to add alerts for user-defined error messages, you need to add your error message to the sys.messages table using sp_addmessage

   EXEC sp_addmessage @msgnum= 90210, @severity=16, @msgtext='Error raised.',  @lang= 1033;

To actually raise the alert for the error, there are a few caveats you must be aware of (seriously...read the online guide...that's all I'm doing here...)

  These are the circumstances under which errors/messages generated by
  SQL Server and SQL Server applications are sent to the Windows
  application log and can therefore raise alerts:
  • Severity 19 or
  higher sys.messages errors

  • Any RAISERROR statement invoked with WITH LOG syntax

  • Any sys.messages error modified or created using sp_altermessage

  • Any event logged using xp_logevent

So a RAISERROR for a severity 16 error will not just alert you by default, you would need to add WITH LOG after it.

RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG


Database design for varying amount of relations I am currently trying to design a database model to store all the possible nutrition data, but for many products there isn't as much data available as for the others, as a result many field could end up as NULL values (I do not want to use any placeholder/default values as I will need to be able to separate which ones are actually missing)

I found that PostgreSql might be a good solution for me, as I do not want to build a EAV (Entity-Attribute-Value) tables, because I have had my experience with it, it doesn't scale very well. (If "products" could have anywhere from 0 to 200 attributes, it would quickly get into tens of millions rows).

PostgreSql has composite types and you can also store them as an array, which allows me to store key value pairs easily within a single column, as a result it is easy to lookup everything related to the "product", I can easily do a join between tables and have the data available.

Now the question is:

Is it a good solution, what kind of obstacles could I face, is there a better solution to my problem?

Would having separate tables for example:

CREATE TABLE products (
    proteins_id FOREIGN KEY,
    lipids_id FOREIGN KEY,
    minerals_id FOREIGN KEY,
    vitamins_id FOREIGN KEY)

each foreign key referencing a table with possibly up to 50 null values in a row

OR

CREATE TYPE custom_type (
   field_1 INTEGER,
   field_2 INTEGER
)

CREATE TABLE products (
    proteins custom_type[],
    lipids custom_type[],
    minerals custom_type[],
    vitamins custom_type[]
)

I understand that the optimal solution might be somewhere in-between those two solution by finding a balance of some sort, but I really need some pointers on that, thank you guys! :)

EDIT: Plus another things to consider: The tables might need to be expanded quite often and it would be great if the process were least painful.

I also face the same kind of problem with other tables such as (food) "recipes" as the number of ingredients can be very varying plus I need to store the measures/quantity of the components.

What you are describing is called a subtype. A subtype is a common data structure used for things that are very similar but have some differences. For example, and object Named Clothing can be used to describe many types of personal coverings but T-Shirts and Shorts have very different properties. With a Subtype, you store all the common properties in one table and have a related table of the properties that are different. That main table will have a FK to a Type table that will denote which of the subtype tables this object is.

Entitty: Clothing
ClothingID
ClothingTypeID -> ClothingType
Name
Size
Color

Type Entitty: ClothingType
ClothingTypeID
Name

Now for the subtype tables. You will have one for each value in the Type table.

Entity: TShirt
ClothingID (Subtypes always have the same primary key value as the parent object)
SleeveLength
ColorType

Entity: Shorts
ClothingID
HasPockets
LegLength
IsSwimSuit


Extract names from another table I have data in two tables. One has order details, the other has product details. The order_details table references the product table via the product_id column. The product table has columns product_id and product_name.

I count sales per product ID like this:

SELECT product_id, COUNT(*)
FROM order_details
GROUP BY product_id
ORDER BY COUNT(*) DESC

and get output like this:

product_id
123         2500
421         1700
111         1040

Now I want to have the names from the product table instead of the IDs:

apples  2500
banana  1700
pear    1040

How can I do that?

The (possibly) most efficient way, first group, then join:

SELECT
    product_name, c
FROM
    (
      SELECT product_id, COUNT(*) AS c
      FROM order_details
      GROUP BY product_id
    ) AS counts
    /* The trick is now to JOIN this virtual table with your product table */
    JOIN product p ON p.product_id = counts.product_id
ORDER BY
    c DESC ;

You can check that it works in this SQL Fiddle (scaled by a 10 factor).

This SQL is very close to standard, so it should work not only with MySQL, but with most SQL databases (I've tested it with MySQL, PostgreSQL and SQLite).

Most contemporary databases will probably execute the following query with the same efficiency, provided that NOT NULL and REFERENCES are in place (and you type a bit less, and probably make it easier to understand):

SELECT
    p.product_name, COUNT(*) AS c
FROM
    order_details od
    JOIN product p ON p.product_id = od.product_id
GROUP BY
    p.product_id
ORDER BY
    count(*) DESC ;

Or, without using aliases:

SELECT
    product.product_name, COUNT(*) AS c
FROM
    order_details
    JOIN product ON product.product_id = order_details.product_id
GROUP BY
    product.product_id
ORDER BY
    count(*) DESC ;


Very slow update with subquery (different tables) I run this query to update "domains" table based on "log" table:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` >= DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) AND
`time` <= DATE_SUB( CURDATE( ) , INTERVAL 15 DAY )
group BY domain_name
HAVING count(*) > 200)

While the subquery itself runs very fast (0.6 sec) with a result set of only 6 items the update never ends (not after hours). But if I run the update-query with a item list instead of a subquery it successfully ends after 0.02 seconds:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
("0815.ru","123.com","123mail.org","126.com","139.com","13hotmail.com")

Question is: why?

The subquery will be executed for each row from the outer query, and no index will be used. In addition, 0.6 sec is "relatively" long. For example, if you have 1000 rows in domain table, then expect more them 600 sec (10 minutes).

In the second query, MySQL is able to use the index on 'domain'. This is a feature of using "IN" instead of subquery.


Data storage for analytics I have to store some amount of data for analytical purposes.

The data source produces 2TB data per month.
Data is collected on a monthly basis (not real-time).
Data is fully structured.
There are 100+ different columns of data.
Availability of SQL is important.
Engineer/developer resources are limited.

I planned to use Postgres (probably with column-oriented extension), however, it would not be feasible for such data amounts (more than 20TB per year). I also made a research on Hadoop/Spark, however, it looks like a bit massive solution (considering, that the data is fully structured). I don't consider cloud-based solutions, as well as expensive ones (preferably, free-licence) .
Would you be so kind to suggest, which data storage to use for big amounts of structured data for analytical purposes?

Aside from the recommendations made by Danielle and J.D., I suggest you examine how the data is represented and the frequency.
For example, if the data is transmitted in a JSON or XML format, which is handy for transmission and processing, the size will be large (e.g. a numeric string vice a float or an int).  Also, string identifiers (e.g. serial number, credit card number) can be made into PK/FK relationships that could save space.
Your data sampling frequency may be higher than what is realistically usable. For example, if you get weather data at 10 samples/sec but your analysis only has 1 hour resolution, you have a lot more weather data than you can use. Perhaps resampling the data to a lower frequency (e.g average and standard deviation, low-pass filter) and storing that might be a better solution. You can always archive the raw data--archival storage for a years worth of data is not very expensive.