Best way to linking different entities to a single table I'm running for office and have created a web app for tracking my door knocks to voters at their homes. The database contains a table called voters that contains all the necessary information about voters in my community. I'm using mysql.

I'd like to add a new feature to track donors to my campaign. Not all of these donors live in the community and do not vote in the district where I'm running for office. I do not need to track the same kind of info for these individuals as I do for the voters so I'm going to place these individuals into a table called nonvoters.

Now, the individuals in my voters table can also make donations, and I want to track those as well.

Finally, I'd also like to track donations from organizations, so I also need an organizations table for storing information about the organizations.

To track the donations from both voters, nonvoters and organizations, I want to set up a new table called donations. This table would contain the appropriate details about each donation.

But I'm uncertain as to what the best structure is for linking the donations table to the voters, nonvoters, and organizations table should be. If I create a column called donor_id in the table to key it to the donors information, there's no way to know which table that ID refers to. So do I set up three columns, nonvoter_id, voter_id and org_id and insert the ID into the applicable column depending on whether the donor is a voter? This seems weird.

Or maybe I create a new column in each of the three tables called donor_id that I can use to link my data to the donations table. If I went this route, it seems like I'd have to do a some behind the scenes work to ensure the donor_id was unique and was keyed to the data inside the donations table.

Or maybe there are other approaches I'm not familiar with. Any guidance is appreciated.

Good question, you have provided a quite engaging context and analysis.

According to my understanding of your scenario description, some important aspects can be handled by means of (Super)Type-Subtype relationships. In this regard, this post provides relevant information.

Assumed business rules

As per your current specifications, the following assertions appear valid:

A Party[1] is either a Person or an Organization.
A Party grants zero-one-or-many Donations.
A Person can be a Voter and a NonVoter.

Preliminary logical data model

Therefore, I have derived the IDEF1X[2] logical data model presented in Figure 1.

Party and Donation

As you can see, the incorporation of the Party entity as an exclusive supertype provides the possibility for a Person or an Organization to be able to grant Donations, since the PartyId attribute migrates[3] from Party to Donation, but I have assigned a role name[4] (i.e., DonorId) to such attribute in order to make it more meaningful in the context of the Donation entity.

Person and Organization

In the same way, PartyId migrates to Person as PersonId, and to Organization as OrganizationId, and these two entities, in turn, can hold their specific attributes.

Voter and NonVoter

I consider that, in your business context, a given Person can be a Voter and a NonVoter over time, depending on the particular District in which said Person is located, thus I have temporarily depicted this circumstances in a nonexclusive supertype cluster, but I consider that we need to clarify some points in this respect.

Aspects that require clarification

The goal of this data modeling excersise is to capture the things of interest with respect to your business context requirements, and one can serve that purpose by determining the entities involved along with their respective attributes (including, with especial attention, the ones that uniquely identify each entity ocurrence) and the way in which such entities are interconected. In this manner, the semantic value that said aspects hold for you, and your data users, is paramount. The better these factors are comprehended, the better they can be depicted in a data model.

Having said that, I have created three more models (also preliminary) in order to have a vehicule to exemplify some ideas about your scenario. They are contained in this PDF, which you can download from Dropbox.

Then, here are the concrete aspects that need clarification:

How do you plan to inscribe people in your system? (a) Do you have access to the electoral register (or electoral roll)? (b) Will you enter this data once you have door knocked their house?
Which attributes do you want to store about a given DoorKnock?
It seems reasonable to state that a Person may receive the status of Voter or NonVoter in the context of —as mentioned above— (a) the District in which such Person resides and (b) a determined Election instance. What do you think about it?

Maybe, a person must live within a certain period in a given district in order to be able to vote in a determined election.

What are the attributes of interest in relation to a Person?
Which are the particular attributes that you want to retain regarding a Voter?
What are the specific attributes that you wish to store about a NonVoter?

Perhaps, they are personal attributes that belong in the Person entity.

It also appears fair to say that a Donation can be granted only in the context of an Election occurrence, is this correct?

This possibility is expressed in all the three models.

With respect to Donations granted by Organizations, (a) are organizations that reside outside your election district allowed to make donations, or (b) is this only permitted for organizations located in your specific district?

If this is allowed only to local Organizations, it would be necessary to move the District PK from Person to Party. I am thinking that, actually, that could be the right place for this attribute, but let me know your ideas about it. I have illustrated this option in Model C.

How much Donations are allowed by each Party? (a) One, (b)  a fixed number, or (c) an undefined quantity?

Option (c) is also currently presented in the three models.

Are there restrictions related to (a) a MinimumAmount and (b) a MaximumAmount per individual Donation?
Do you have restrictions associated to (a) a MinimumAmount and (b) a MaximumAmount per the total set of Donations granted by a certain Party?
How do you identify an Election instance?

I think that a given election can be uniquely identified by means of the date in which it is celebrated (as depicted in Model A) and, perhaps, by a combination of the date and the district where it is held (as depicted in Model B).

What attribute (or group or attributes) uniquely identify a District occurrence?

As you know, I have used DistrictNumber for this purpose in all the models, but it may well be a different attribute.

Response to your comments

  I have never heard of type-subtype relationships in a database. This is very interesting.

Yes —as far as I know— Type-Subtype relationships were introduced, as such, in the relational paradigm back in 1979[5] and, unfortunately, they are not widely used in common implementations. But now that we know them, we can take advantage of this interesting and powerful structure in your scenario.

  I am not all that practiced with databases so not sure how to begin implementing the columns needed. All of my INSERTS are straightforward single table inserts and it looks like this approach will require some fancier queries. I'm definitely willing to learn, though.

Do not worry, it is completely understandable that you are familiar with other computer science branches but not that much with relational databases, and I consider that this is a good oportunity for you to know more about this fascinating discipline, we all learn new things every day.

Yes, INSERTing a row (or set of rows) in a database table implies different considerations than, say, creating a record or file in a programming language.

We need first to focus in obtaining a stable logical data model, then we will easily derive a physical model (DDL statements or “CREATE Table code”). Once we have a clear target (all the aforementioned) defined, I will be glad to offer some ideas and guidance in relation to your INSERTs implementation and other pertaining elements.

Notes

1. Party is a term used in legal contexts to refer to a single person or group of persons that compose an individual entity.

2. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST).

3. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity (or supertype) in its child or category entity (or subtype) as a foreign key”.

4. The use of role names is recommended since 1970 by Dr. E. F. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.

5. By Dr. Codd (as one would expect) in his work entitled “Extending the Database Relational Model to Capture More Meaning”.


What is the difference between int(8) and int(5) in mysql? I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5)

in both cases the maximum value is

−2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1

or do i miss something?

I found this Question:
https://dba.stackexchange.com/a/370/12923

  The (5) represents the display width of the field. From the
  manual, it states:

    The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column
    display width from being displayed correctly. For example, a column
    specified as SMALLINT(3) has the usual SMALLINT range of -32768 to
    32767, and values outside the range permitted by three digits are
    displayed in full using more than three digits.

  The display width, from what I can tell, can be used to left-pad
  numbers that are less than the defined width. So 00322, instead of
  322. TBH, I've never used it.

  But it doesn't affect the storage size of the column. An int will take
  up more space than a smallint.

so there seems to be no difference then.

See 12.2 Numeric Types (MySQL Reference Manual)

Essentially what you listed is the max value for INT and not BIGINT.

INT(8) is the equivalent of typing INT with a display width of 8 digits
INT(4) is the equivalent of typing just INT with 4 display lengths.

The range of BIGINT is -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615.


Restoring an old backup to latest MySQL release Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new version release says that there are many performance benefits.

IMHO, when it comes to MySQL 5.5, you will definitely see much better performance from InnoDB than in past releases because dirty pages in the InnoDB Buffer Pool do not linger as long. Those dirty pages are flushed more robustly.

You also have the luxury of creating multiple InnoDB Buffer Pools (See innodb_buffer_pool_instances and innodb_buffer_pool_size). To reduce thread locking amongst the buffer pools, make sure set innodb_thread_concurrency to 0 to let InnoDB storage engine decide best how to handle thread allocation.

CAVEAT ON MIGRATION TO MySQL 5.5

Make sure you mysqldump all databases EXCEPT the mysql schema. There is a cleaner, crisper way to migrate User Grants out of the MySQL Schema. In fact, there are two options for accomplishing this:

OPTION 1 : Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql


Large ibdata file in MySQL I am having MySQL database with all my tables as InnoDB with file_per_table config on. But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have created by taking dump and loading. What is a reason for this.

You probably have lots of large transactions or one really big transaction.
Take a look at Vadim Tkachenko's Pictorial Representation of InnoDB

If you look inside the system table (ibdata1), you see rollback segments and undo logs.
When a rollback segment rolls back a transaction, it has to use the MVCC information it stockpiled in the undo space. That's where most of the growth occurs. Percona mentioned this back on Jun 10, 2010 (Reasons for run-away main Innodb Tablespace)
I have discussed this before

Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full

UPDATE 2015-03-22 15:00 EDT
If you are convinced the data dictionary is the cause, there is only one sure way you can verify this.

STEP 01) Get another DB Server (Dev or Test)
STEP 02) Install MySQL on it
STEP 03) mysqldump the schema-only (using --no-data)
STEP 04) load the schema-only dump into the empty database
STEP 05) run ls -l /var/lib/mysql/ibdata1 | awk '{print $5}'

The number that comes out will be the size will be the size of ibdata1 with just data dictionary entries for the current list of tables, but with no data in them.
In your last comment, you said

So open transaction doesnot seem to be right reason. We use to create some 200-300 table on daily basis (by our ETL tool) and drop them after some processing periodically ( drop some 2k-5k tables once in 2-3weeks), could this attribute to the big file ?

You could be right for same reason: Just like rollback segments and the undo space have no provision for reclaiming unused diskspace for the OS, dropping tables and creating new ones would likewise have no provision for reclaiming unused diskspace for the OS.
To prove that, you would have to use that new server with all tables but no data. Then, perform the same dropping and creating of tables every week. If the ibdata1 grows just for doing that, then you have proven undeniably that dropping and creating tables alone can grow ibdata1 with innodb_file_per_table enabled.
If you want to cleanup ibdata1, please see my posts

Sep 26, 2012 : How do I shrink the innodb file ibdata1 without dumping all databases?
(on why you have to dump all data, remove ibdatat1, and create a new ibdata1)
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (on how to actually do this)

GIVE IT A TRY!!!


MySQL Replication monitoring Im looking for a tool that could help me and my team monitor our MySQL Replication.

I would love for a tool that could alert me if

Seconds_Behind_Master passes a threshold
Slave_SQL_running is false
Slave_IO_running is false

If not alerting, then maybe just get a quick view of these parameters.

I am also ready for paid solutions.

Any ideas on such service/tool?

You could quite easily write a simply Perl or PHP script, running on a cron, to periodically check the MySQL server and alert you to any problems.

For example, I have the following Perl code in my setup:

$sql="show slave status";
$sth=$dbhread->prepare($sql);
$sth->execute();
($result)=$sth->fetchrow_hashref();
$sth->finish();

if($result->{'Slave_SQL_Running'} ne "Yes") {
   &SendAlert("Slave SQL not running");
}

if($result->{'Slave_IO_Running'} ne "Yes") {
   &SendAlert("Slave IO not running");
}

if($result->{'Seconds_Behind_Master'} > $threshold) {
   &SendAlert("Seconds Behind Master is too high");
}

Obviously &SendAlert can be set up to do what ever you like (email/SMS alerting)


Is it a good idea to index timestamp in postgresql? Here is my table definition

-- Table: public.my_table

-- DROP TABLE public.my_table;

CREATE TABLE public.my_table
(
    my_row_id text COLLATE pg_catalog."default" NOT NULL,
    my_item_id text COLLATE pg_catalog."default" NOT NULL,
    my_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    --  ...... some other data
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

The cardinality of the table is about 10-20 million rows

Here is the query I want to speed up

select my_row_id, my_item_id
from my_table
where my_timestamp > timestamp '2018-09-13 00:00:00'
order by my_timestamp desc

I am tempted to just index the timestamp, but I worry that since the precision of timestamp is millisecond, this means the the cardinality of the index itself will be almost as large as the cardinality of the table (eg. the number of rows will be equal to the number of index entry)

Should I have custom index that index with the precision of a day?

Like store an extra column text 2018-08-17 from timestamp with timezone 2018-08-17 07:06:43.508778

And index the text instead?

For the query you give, the optimal general index would be on (my_timestamp, my_row_id, my_item_id), with the order of the last two columns possibly switched.  This will allow an index-only scan to be used which possibly doesn't visit the table at all, since all columns you reference would be available in the index.  How much of a performance improvement this would give you depends on how much of your table meets the timestamp WHERE condition, and how well-vacuumed you keep the table.  You might do even better with a partial index if the timestamp hardcoded into your query never changes, or changes in a very rote manner.

  Like store an extra column text 2018-08-17 from timestamp with timezone 2018-08-17 07:06:43.508778

There is little reason to think that this would be helpful in general, and certainly wouldn't help with the query you show (you would have to rewrite the query before it could use such an index, and then what happens if the timestamp is exactly '2018-09-13 00:00:00' ?)


Are problems with parallelism more common today? What issues can arise from parallelism? This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and positive.

I seem to come across more and more systems that have one or more stored procedure that have issues with too much parallelism. Is there a good way to find out if that really is the problem or if it is something else lurking about?

Usually the sql is a bit complex with maybe 5 or more joins and also some udf and even more complexity. So far I found these issues by examining everything from usual performance issues but also from more extreme situations with error messages like "Could not create thread..." and also deadlocks from multiple simultaneous copies of the same store procedure.

I am really curious how you guys do to find issues like these and also how you go about solving them. Recoding? Playing with MAXDOP (max degree of parallelism)? And so on...

I wrote about six potential issues with parallelism here:

https://sqlblog.org/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism

That was three years ago, and I'm sure other equally dangerous bugs have come up since I wrote that. I haven't gone through and validated any that have been reported as fixed, but the workaround is almost always to use MAXDOP 1.

And there's another one that is still an issue in SQL Server 2012, and that hasn't been fixed in CU1:

https://stackoverflow.com/questions/9523213/possible-bug-with-range-option-of-window-aggregates-and-parallel-plans-in-sql-se
http://connect.microsoft.com/SQLServer/feedback/details/728932/parallelism-bug-with-sum-over-and-range

The workaround in that case is, again, to use MAXDOP 1.

There are other issues that can be blamed on parallelism that aren't really parallelism's fault. For example if your statistics are way off you can observe behavior where parallelism is employed but a single thread is doing all the work. This is the case where people sound the alarms because they're seeing CXPACKET waits and they think that parallelism is causing the problem, when in fact it is the victim. The knee-jerk reaction in those cases is typically to employ MAXDOP instead of investigating further and resolving the statistics issue or other root cause.

It's not clear what you mean by "too much parallelism" or whether your deadlocks and other issues are in fact related in any way to parallelism. In general I agree that parallelism is a positive thing but it is important to be aware of the potential issues above and also to be able to discern symptom from cause.


MySQL - Migrating from Master-Slave replication to Master-Master We currently run 2 large mysql master-slave replication trees.

Each tree is built with one master, several 'middle' slaves replicating from the master, and 'end' slaves replicating from the 'middle' slaves. For example:

                              - end slave
            - middle slave  - - end slave

-  master - - middle slave  - - end slave

            - middle slave  - - end slave
                              - end slave

I would like, if possible to get rid of the master, and promote the 'middle' slaves to be the masters in master-master replication. Example:

                        - end slave
              master  - - end slave

              master  - - end slave

              master  - - end slave
                        - end slave

Is this possible? What would be the process I should follow? What sort of downtime should I expect?

Thank you very much in advance!

We had wanted to make this change in order to minimize the necessary changes to infrastructure, keeping us from needing to raise a galera cluster and attempting an online switch to the new infrastructure.

However, we have decided to implement the necessary resources to raise a mysql cluster and leave the old replication structure. It seems like there is no real alternative.

Thank you for your comments.


Table and Primary Key sizes in Oracle I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table.

To be clear, my statistics are the following (approx.):

NAME        | TYPE  | SIZE
----------------------------
MYTABLE     | TABLE | 21 GiB
PK_MYTABLE  | INDEX | 20 GiB

MYTABLE has four columns, and three of them composes the primary key.  So the sizes should be correct.

But why do Oracle stores this so inefficiently?  (a separated structure for the primary key)
Do Oracle has something similar to MS SQL's clustered index, that makes the index part of the related table?
Is there something I can do to avoid this?

But why do Oracle stores this so inefficiently? (a separated structure
  for the primary key)

It is inefficient for this use case.

  Do Oracle has something similar to MS SQL's clustered index, that
  makes the index part of the related table?

It is called IOT (Index-organized Table) in Oracle.

Overview of Index-Organized Tables

For example:

CREATE TABLE mytable
(
 c1 number,
 c2 number,
 c3 number,
 c4 number,
 CONSTRAINT pk_mytable PRIMARY KEY (c1,c2,c3)
)
ORGANIZATION INDEX
;

With this, you will have only one segment caled PK_MYTABLE.

  Is there something I can do to avoid this?

See above.


Why can't I connect to SQL Server 2008? I initially installed SQL Server 2008 R2 with Windows authentication mode and have successfully changed it to mixed mode (per http://msdn.microsoft.com/en-us/library/ms188670.aspx).

After this I am able to log in to my SQL Server with SQL Server authentication. But from another computer which is on my network, I am not able to connect my server.

For security reasons SQL Server 2008 no longer opens TCP port 1433 upon installation.  You will need to create a firewall rule that allows communication over TCP port 1433 (and UDP port 1434 if you wish to use the browser service) to allow applications to communicate with SQL Server.


How to reload a linked server? I am using Microsoft SQL Server 2014 Enterprise Edition. A problem occurs with the linked servers where it is necessary to restart the server, or to stop the MSSQLSERVER service. When the server is running again, the linked servers (to DB2) don't work properly and the SQL Server shows this error:

  Msg 7302, Level 16, State 1, Line 10
  Cannot create an instance of OLE DB provider "DB2OLEDB" for linked server "Airspe".

Only after restarting the server several times does the linked server start working.

Why is necessary to restart the server several times to get up linked servers?
Are there other solutions?

This is the script to create one of the linked servers:

EXEC master.dbo.sp_addlinkedserver
@server = N'AIRS',
@srvproduct=N'Microsoft OLE DB Provider for DB2',
@provider=N'DB2OLEDB',
@datasrc=N'###.###.###.##',@provstr=N'Provider=DB2OLEDB;
    Data Source=#####;Persist Security Info=True;Password=**********;
    User ID=######;Initial Catalog=######;
    Network Address=###.###.###;Package Collection=AICOLDP;DBMS Platform=DB2/AS400',
@catalog=N'#####'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AIRS',@useself=N'False',@locallogin=NULL,@rmtuser=N'#####',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'remote proc transaction promotion', @optvalue=N'true'

This is the provider configuration:

I believe something is missing in the configuration done for DB2 Link.

the connection string should looks like this First.

Provider=DB2OLEDB; User ID=db2user; Password=mypassword; Initial Catalog=mydatabase;
Network Transport Library=TCP; Host CCSID=37;PC Code Page=1208;
Network Address=111.111.111.333; Network Port=446; Package Collection=MSDB2COL;
Process Binary as Character=False; Connect Timeout=15; Units of Work=RUW;
DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0; Max Pool Size=100;
Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

Please add missing parameters, so system will not set Automatic/rando parameters, that should solve the issue.


Will transactional replication push a CREATE INDEX immediately? Or do I need a snapshot? I have transactional replication set up on SQL 2008, and the article in question has "Copy nonclustered Indexes" set to TRUE.

If I add a non-clustered index on the publisher (using CREATE INDEX), will that push to the subscriber immediately?

Or do I need to run a snapshot?

You'll have to push it to the subscriber yourself.  "Copy..." only applies to the snapshot.

From MSDN :  Frequently Asked Questions for Replication Administrators

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations
for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER
INDEX are not replicated, so if you add or change an index at, for example, the Publisher,
you must make the same addition or change at the Subscriber if you want it reflected there.


How to implement a 'default' flag that can only be set on a single row For example, with a table similar to this:

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that it can only be set on a single row.

SQL Server 2008 - Filtered unique index

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'


Check constraint age 18< Hello I need to create a constraint that chekc the user is 18< before listenning to certain songs but idk how... I tried this :

ALTER TABLE Historique_ecoute
DROP CONSTRAINT IF EXISTS majorite_utilisateur
GO

ALTER TABLE Historique_ecoute
ADD CONSTRAINT majorite_utilisateur
CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)
       AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES') )

But I get thoses messages :
Msg 1046, Level 15, State 1, Line 244
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 4104, Level 16, State 1, Line 255
The multi-part identifier "Catalogue.annee" could not be bound.

There are apparently 2 problems with you constraint:

Msg 1046, Level 15, State 1, Line 244 Subqueries are not allowed in this context. Only scalar expressions are allowed.

What this means is that you can not use a subquery inside a check constraint, you can only validate such constraint row by row. Examples of valid check constraints would be:

CHECK ( x BETWEEN 1 AND 3 )
CHECK ( x > y )

I think sub-queries are allowed according to SQL-standard, but few if any vendors allow it. Apparently SQL-server is not among those that do.

so the culprit for this problem is:

AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES' )

Let's remove that part and try:

ALTER TABLE Historique_ecoute ADD CONSTRAINT majorite_utilisateur
   CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)

My guess is that this is not allowed either, because GETDATE() is not deterministic, so a row may be valid during insert but invalid a little bit later. A constraint is never allowed to evaluate to False. During a modification of a row, the constraints can be validated, but in your case, the truth value of the constraint can change without any modification being made. What action would be taken when the constraint all the sudden evaluates to False? There is no transaction that can be rolled backed, no user that can be notified.

As a side-note, I don't understand what Utilisateurs.Date_naissance refers to?

Msg 4104, Level 16, State 1, Line 255 The multi-part identifier "Catalogue.annee" could not be bound.

This error I don't understand. I can't locate annee anywhere in the constraint.

I assume that the choice of song is written to some table ( Historique_ecoute ). What you can do is create a trigger that validates the condition

CREATE TRIGGER validate_age ON Historique_ecoute
AFTER INSERT
AS
    IF (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)
         RAISERROR ('You are not allowed to ...',
         ROLLBACK TRANSACTION;
    END;

I don't know the exact syntax, but you should get the idea. I did copy the trigger stuff from CREATE TRIGGER


Help Changing Collate of ALL databases I have approximately 80 Databases with various collations.

I would like to modify the collation of all 80 databases.

I have the following code:

USE [COLLATE_ADM]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PR_COLLATE3] AS
BEGIN
    DECLARE @collate SYSNAME;
    SELECT @collate = 'Latin1_General_CI_AS';
    DECLARE @cmd AS NVARCHAR(4000);
    DECLARE @banco AS VARCHAR(100);
    -- Bancos que serão consultados
    SELECT name INTO #tmp
    FROM master.sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
    --while
    -- Loop pelos bancos
    While (Select count(1) from #tmp) > 0
    BEGIN
        SELECT @banco = min(name)
        FROM #tmp;
        SET @cmd = '    use [' + @banco + ' ]

        insert into collate_adm.dbo.tblCollateScript;

        SELECT ''?'' as Banco,
            ''['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name + ''] -> '' + c.name
            , '' USE [' + @banco + ' ]; ALTER DATABASE '+@banco+' SET SINGLE_USER;
            ALTER DATABASE  '+@banco+ '  COLLATE Latin1_General_CI_AS;
            ALTER DATABASE '+@banco+' SET MULTI_USER;
            ALTER TABLE     ['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name + '']
            ALTER COLUMN ['' + c.name + ''] '' +
            UPPER(t.name) +
            CASE WHEN t.name NOT IN (''ntext'', ''text'')
            THEN ''('' +
                CASE
                WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length != -1
                    THEN CAST(c.max_length / 2 AS VARCHAR(10))
                WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length = -1
                    THEN ''MAX''
                ELSE CAST(c.max_length AS VARCHAR(10))
                END + '')''
            ELSE ''''
            END + '' COLLATE Latin1_General_CI_AS '' +
            CASE WHEN c.is_nullable = 1
                THEN '' NULL''
                ELSE '' NOT NULL''
            END
        FROM sys.columns c WITH(NOLOCK)
            JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
            JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id
                    AND c.user_type_id = t.user_type_id
        WHERE t.name IN (''char''
                            , ''varchar''
                            , ''text''
                            , ''nvarchar''
                            , ''ntext''
                            , ''nchar''
                        )
            AND o.[type] = ''U''
        ';

        SET @cmd = REPLACE(REPLACE(@cmd,'?', @banco) , 'XXXcollateXXX', @collate)

        BEGIN TRY
            EXEC sp_executeSQL @cmd -- Executa comando gerado pelo script
        END TRY
        BEGIN CATCH
            INSERT INTO tblCollateScript (rotina, script,Data)
            VALUES ('pr_BuscaCotas', @cmd, GETDATE());
        END CATCH
        DELETE FROM #tmp WHERE name = @banco
    END
    DROP TABLE #tmp
END
GO

This code lists the script to modify all databases (to change table collation, etc.), however I'm facing LOTS of problems, including:

PK is reference of another table.
IX is reference.

So I need to drop EVERYTHING and create again (including functions).

Is there an easy way to do this?

I had to do this some years ago for a SQL Server 2005 DB.  It was awful.  I've not had to do it on 2008, thankfully, but as far as I know it still works the same way.

Unless specified explicitly, the collation is copied from the next higher object when an object is created.  So creating a table will copy the database's collation.  Collations are not inherited dynamically at runtime i.e. changing the DB's collation will not change any table's collation.

So you are on the right track.  You have to change the collation at every level from column up.  Moreover you will almost certainly want to change the collation for the system databases (master, model, msdb and tempdb) otherwise you may get collation conflicts when a query plan uses tempdb and you will have such plans.

Furthermore I remember that the new column collations did not take effect until the data had been written.  I ended up performing

update <table>
set <string_column1> = <string_column1>,
    <string_column2> = <string_column2>;

for every table in the database.

If I ever had to do it again I'd install a new instance from scratch with the proper collation, then migrate the tables, then the data, then the constraints.  Hopefully you have good source control in place.


Calculating Average Excluding Outliers I'm looking for a SQL Server function that will let me take an average, excluding outliers. Basically something where:

1, 2, 1, 2, 7

Would average out to 1.5.

I'm going to implement this in a financial analysis Microsoft SQL script.

My query is way too long and complicated to fit here, and it's not something I want to post publicly. AVG doesn't look like it has an option to exclude outliers per se, though it does include a PARTITION BY.

A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average.
You then take the average of the new range.
This is quite a basic bit of code (don't forget the CAST to a DECIMAL) which you can expand upon to make it more suitable to your needs.
CREATE TABLE #nums (num INT);

INSERT INTO #nums VALUES (1), (2), (1), (2), (7);

WITH AvgStd AS (
  SELECT
    AVG(num) AS avgnum,
    STDEVP(num) AS stdnum
  FROM #nums AS n
)
SELECT
  AVG(CAST(num AS DECIMAL(5,2)))
FROM #nums AS n
CROSS JOIN AvgStd
WHERE num < (avgnum + stdnum)
  AND num > (avgnum - stdnum);

DROP TABLE #nums;


Can I have one table be an "alias", or "symlink", to another? I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables.

After a discussion with a colleague about the potential to save drive space, I'm wondering if mysql or postgresql has the ability to create on table as an "alias", or "symlink", of another.

I want the behaviour to be very similar to that of a soft file symlink; where reading from either the symlink itself or it's target will yield the same output, and writing to either will update the target.

As far as I know, a new Postgresql lets you have INSTEAD OF triggers on views. So having one table, one view as SELECT * FROM table1, and INSTEAD OF trigger for insert, update, delete should work for you. This approach is not gonna work in Mysql though


Remove substring from one column and put it on other column I have a table like this:

CREATE TABLE `mytable` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `column2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And some data:

id | column1                                       | column2
____________________________________________________________
1  | Something                                     |
2  | Something else - Lets add more data           |
3  | Something else - Another example - More stuff |

I would like to update this table, by putting everything after the first - (space + dash + space) into column2 and then removing it from column 1. If there is no -, leave it as it is. Such as this:

id | column1        | column2
__________________________________________________
1  | Something      |
2  | Something else | Lets add more data
3  | Something else | Another example - More stuff

Any suggestions?

UPDATE mytable
SET column2 = SUBSTRING(column1, 3 + LENGTH(SUBSTRING_INDEX(column1, ' - ', 1))),
    column1 = SUBSTRING_INDEX(column1, ' - ', 1)


Why null is getting compared with null in the join condition in SQL Server 2016? It's a simple self join I am trying to do on below data

and the O/P I am getting after running following query (This is not the complete output, just showing the null part of it)

select a.col1, a.col2, a.Length1, b.*
from [dbo].[Combine] a
  left join [dbo].[Combine] b on a.col1 =b.col1

Why Null is getting compared and coming 9 times?

Maybe the column col1 for these rows has a literal string value of ‘NULL’ and not a missing value null indicator? - sqlraptor

You can test by adding the conditions and comparing the output of the queries:

WHERE a.col1 = 'NULL'

WHERE a.col1 IS NULL


Restored DB to Dev and test environments, now user can not login I took a backup of our production database and restored it to both the test and development environments last night, but this morning a single user is not able to login to the test/dev DBs. I can go into SSMS and connect to SQL Server with the user but when I try to connect to the specific databases I am given a 4064 login failed error. The credentials should be correct as I can turn around and login to production and run queries with no issues. I have compared both permissions sets and did not see any difference between prod and dev/test. They both have "connect" permission to the database schema and they are both part of the "db_reader" and "db_executor" groups. What might I be over looking in the test/dev system that is preventing this user from connected to the Database?

Have you tried running EXEC sp_change_users_login @action='Report'; to see if they are orphaned for that database?

If there is a result, you can run with the Auto_Fix option to remap the user.


Query hangs indefinitely - Postgres 9.6 We had an issue in where a query would hang indefinitely with the inclusion of certain IDs in the last WHERE IN statement here:

SELECT 1
FROM items
WHERE id NOT IN (
                 SELECT items_id
                 FROM "items_attribute"
                 WHERE "items_attribute"."attribute_id" IN
                       (88376, 88354, 88380, 74984, 12983, 1205, 10931, 78061)
);

Those IDs that caused the hanging did not seem to present with any data anomaly. Do you see any issues with this query that would cause recursion or any potential issues? We seemed to resolve the issue by making the subselect DISTINCT. The select in question returns 164760 rows. There are no views here, only small indexed tables.

Explain:

Seq Scan on items (cost=2594.08..3816558214.87 rows=164034 width=0)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=2594.08..25511.56 rows=139699 width=4)
           ->  Bitmap Heap Scan on items_attribute  (cost=2594.08..24267.06 rows=139699 width=4)
                 Recheck Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))
                 ->  Bitmap Index Scan on items_attribute_attribute_id_index  (cost=0.00..2559.16 rows=139699 width=0)
                       Index Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))

Thank you so much for taking a look!!

Try dropping and recreating any indexes on items.id or items_attribute.attribute_id.


Slow MySQL query, not sure why I'm working with an intranet application for property tax collection, and I've hit a snag.  The following view runs much slower than I'd expect:

create or replace view propertytaxessub0 as select
    propertysid,
    sum(amount) as totaltax
from taxes group by propertysid;

The "taxes" table is as follows:

create table taxes (
    sid                     int(10) unsigned not null auto_increment primary key,
    propertysid             int(10) unsigned not null,

    authority               char(30) not null default '',
    amount                  decimal(14,2) not null default 0,

    index (propertysid),
    index (authority)
) engine = innodb;

Here's a single-record lookup, and its EXPLAIN output:

mysql> select * from propertytaxessub0 where propertysid = 2;
+-------------+----------+
| propertysid | totaltax |
+-------------+----------+
|           2 |   121.97 |
+-------------+----------+
1 row in set (0.00 sec)

mysql> explain select * from propertytaxessub0 where propertysid = 2;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |  53342 | Using where |
|  2 | DERIVED     | taxes      | index | NULL          | propertysid | 4       | NULL | 467217 |             |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
2 rows in set (2.07 sec)

I have no idea what I've done wrong here.  It's not a very powerful server, comparatively, but if you do this:

mysql> select sum(amount) as totaltax from taxes where propertysid = 2;
+----------+
| totaltax |
+----------+
|   121.97 |
+----------+
1 row in set (0.00 sec)

... it runs just fine.

I'm coming at this from the MS Access side of the house, where I created named queries all the time and used them this way to good effect.  What am I missing here?

From the look of the select query in the view
SUGGESTION #1 : Don't use VIEWS
Views are notorious for acting up with Query Optimization
According to MySQL Documentation

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Percona calls Views a Performance Troublemaker
SUGGESTION #2 : Use another index (Optional)
You definitely need an additional index to assist the query
ALTER TABLE taxes ADD INDEX propertysid_amount_ndx (propertysid,amount);
ALTER TABLE taxes DROP INDEX propertysid;

That way, all the data needed for the view are in the index only. The other two indexes are not enough. Why do I say that? Even though the propertysid index was used, the amount has to retrieved from the table. Essentially, the query passes through both the index and the table.


ASPState database locking and growth problems We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods

The DeleteExpiredSessions proc is run every minute via an agent job. Sometimes this job is taking many minutes to run and delete expired sessions
Requests from the application to the ASPState database are very slow. I believe this is because there are exclusive locks being held on the table by DeleteExpiredSessions procedure

Code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
AS
        SET NOCOUNT ON
        SET DEADLOCK_PRIORITY LOW

        DECLARE @now datetime
        SET @now = GETUTCDATE()

        DECLARE @tblExpiredSessions TABLE
        (
            SessionID nvarchar(88) NOT NULL PRIMARY KEY
        )

        INSERT INTO @tblExpiredSessions (SessionID)
            SELECT SessionID
            FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
            WHERE Expires < @now

        --EXPIRED SESSION LOGGING
        /*
        DECLARE @ExpiredSessionCount Int;
        SELECT @ExpiredSessionCount = COUNT(SessionID)
        FROM @tblExpiredSessions;
        */

        IF @@ROWCOUNT <> 0
        BEGIN
            DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT SessionID FROM @tblExpiredSessions ORDER BY CHECKSUM(NEWID())

            DECLARE @SessionID nvarchar(88)

            OPEN ExpiredSessionCursor

            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                END

            CLOSE ExpiredSessionCursor

            DEALLOCATE ExpiredSessionCursor

        END

        --EXPIRED SESSION LOGGING
        /*
        BEGIN TRY
             INSERT INTO DeleteExpiredSessionLog(RunStart, RunEnd, ExpiredSessionsDeleted)
             VALUES (@now, GETUTCDATE(), @ExpiredSessionCount);
        END TRY
        BEGIN CATCH
             --SWALLOW ANY EXCEPTION
        END CATCH;
        */

    RETURN 0

The log file is filling up and in some cases forcing autogrowth, despite the db being in simple recovery

In addition to these, a trace reveals multiple requests for the same session coming in quick succession. For example exec dbo.TempResetTimeout @id=N'32gghltsuoesnvlzbehchp2m2014c0f1' 14 times in <1 second, so we're wondering about this as a cause or congestion, but not sure of the root of this behavior.

Any suggestions or explanation around this behavior would be appreciated.

I suspect you implemented Greg's replacement procedure as pre-emptive optimization. The one-row-at-a-time approach restricts locking to a single row, sure, but it's going to take a lot longer - especially if you force SQL Server to attack rows in a random order.

My suggestion would be to revert to the original procedure:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME;
  SET @now = GETUTCDATE();

  DELETE ASPState..ASPStateTempSessions
    WHERE Expires < @now;
END
GO

If you find that this becomes a performance issue because of the locks taken, you can split this out and even reduce impact on the logs using something like:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS
    (
      SELECT TOP (1000) SessionId
        FROM dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    )
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END
END
GO

This is going to be much better, IMHO, than using a randomized cursor to delete one row at a time. You can tweak the value in TOP (1000) based on actual observation.

A couple of other ideas that might help:

(a) set the ASPState database's recovery model to simple (it defaults to full).

(b) change ASPState_Job_DeleteExpiredSessions to run every 5 or 10 minutes instead of every minute. If this job is taking > 1 minute to run then it is going to always be running. Hopefully it isn't completely linear, meaning that waiting 5 minutes instead of 1 won't queue up more than 5 minutes of work instead of 1. Implementing my suggestions above should help with this.


What is blocking 'SELECT... WITH(NOLOCK)'? While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run:

SELECT Quantity FROM [Production].[TransactionHistory]  WITH(NOLOCK)

while executing a rebuild from mentioned article and observe that it is being delayed until index rebuild finished

What is blocking SELECT ... WITH(NO LOCK) and how to avoid it?

Update:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:

USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);

In a second window, execute your NOLOCK query (again, make note of the spid):

SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);

In a third window, check:

SELECT session_id, blocking_session_id, last_wait_type
  FROM sys.dm_exec_requests
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...


Get SSRS Datasources from ReportServer Housekeeping on an old SSRS, this particular server is 2008R2.

I have the query below which gets me the name, location, creation and modification overview for all the reports.  I would also like to include the 'Connection String' at least, and some other details if possible from the data sources properties.  But I can't find it in ReportServer.  I have seen people use PowerShell to get it out of SSRS web service, I was hoping to find a T-SQL solution.

How can I display Data Source details with a SQL query?

--List all the reports on SSRS via "ReportServer" database

USE [ReportServer]
GO
SELECT Name
--, [ItemID] --Primary key
, [Path]
, [Description]
--, [CreatedByID] --need link to get anything usable from here
, Created.UserName as CreatedByUser
, [CreationDate]
--, [ModifiedByID] --need link to get anything usable from here
, Modified.UserName as ModifiedByUser
, [ModifiedDate]

FROM [dbo].[Catalog]
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Created
        on Catalog.CreatedByID = Created.UserID
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Modified
        on Catalog.ModifiedByID = Modified.UserID

WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html

ORDER BY [Path], Name

Get SSRS Datasources from ReportServer

  I would also like to include the 'Connection String' at least

  How can I display Data Source details with a SQL query?

TSQL for getting the Connection String of SSRS Data sources

-- Transact-SQL script to get connection string of all SSRS Shared Datasources.

/*
Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
to document the usage or to search for a specific server/database.

Please remark: Querying the ReportServer database directly is not a supported way.
Works with SSRS 2005 and higher version ReportServer databases.
Requieres select rights on the "Catalog" table in ReportServer database.
*/

-- Connection strings of all SSRS Shared Datasources
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

Here's a query to add the data source name as well using your TSQL:

USE [ReportServer]
GO

SELECT CATALOG.NAME
    ,CATALOG.[Path]
    ,DataSource.NAME datasource
    ,CATALOG.[Description]
    ,Created.UserName AS CreatedByUser
    ,CATALOG.[CreationDate]
    ,Modified.UserName AS ModifiedByUser
    ,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2
ORDER BY [Path]
    ,NAME


MongoDB replicate data item across multiple shards Is it possible to replicate a data item across multiple shards?

My end goal is to determine how up-to-date the data in a replica slave is. I'm only concerned with subsets of the data, so I don't think examining oplogs will work. Instead, I plan on having a counter increment in the master, every time a modification to the appropriate data occurs. Then, I can fetch the slave's replicated counter to see how current it is.

The problem occurs when I try to shard the data. I need the counter replicated across every replica, not just the replica set that owns the counter data item. So, I need some way to replicate the counter to every master shard, where it will then be replicated to each slave. Is this possible?

Modifications to the counter would still go through the master shard that "owns" the counter. Data reads would need to aggregate the counter value from each shard/replica, taking the minimum value. I read through the MongoDB documentation, and I really don't think it's possible. But perhaps one of you MongoDB experts/developers can shed some hope.

Someone on SO mentioned replSetGetStatus, which may be an option. However, I would like separate counters for different subsets of the data, where the replica status corresponds to all the data.

Create 'c' -database
Add  c -collection
Shard this collection with _id
Set this collection balacing:false
Pre-split this collection with _id values: 0,1,2,3,.. (or your real shard names). How many shards you have; one per shard
With moveChunk command, move one chunk to every shard, so that right _id is on right shard.

Now if you update this c.c's counter-key with $inc -operator (what is atomic operation) and without _id is set but multi:true, you can always "query" min value of that counter-key and answer tells what shard and what was value.

db.getSiblingDB('c').c.update({},{$inc:{"counter":1}},{multi:true})


How to SELECT from SHOW TABLE STATUS results I'd like to limit the rows and columns that come back from the

SHOW TABLE STATUS

command in MySQL 5.1.  Is there a way to get this same information through a SELECT statement so I can manipulate the results in a normal way?

This has more columns than SHOW TABLE STATUS; but does the trick:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

UPDATE 2011-06-07 19:02

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();

These queries work if you set the current database.

You can also hard code the specific database:

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';


Help with recursing self-referencing table I have two tables:

Datasource ( A self referencing table)
------------------------------
Id, Type, ParentId, Name
1   A     NULL      Foo
2   B     1         Bar
3   C     1         FooBar

Variable
------------------------------------
Id DataSourceId Name
1  3            Foo
2  2            Bar
3  1            Foobar

My desired output is something like:

Output
---------------------------------------------
VariableId  DataSourceId
1           1
2           1
3           1

Such that for each variable, the datasource is the topmost parent in the hierarchy.

I'm struggling with the required recursion of the self referencing table.

Start with the recursive CTE to get the tree. Keep track of the parent too (faking the bottom level), to identify when it's null.

WITH Tree AS (
    SELECT Id AS VariableId, DataSourceId, DataSourceId AS Parent
    FROM Variable
    UNION ALL
    SELECT t.VariableId, t.DataSourceId, d.ParentId
    FROM Tree t
    JOIN DataSource d
    ON d.Id = t.Parent
)
--Now get the top levels
SELECT VariableId, DataSourceId
FROM Tree
WHERE Parent IS NULL;


Build a simple query and relationship table This is my first post here. Thanks for having me!

I have two questions for a table I'm building into my mysql database.

Question 1: I have a need to input an artist name and the result/select be a distinct twitter handle. How might I build this query as efficiently as possible based on an optimal solution for Q2 (Preferably in one line).

Question 2: Based on the requirement above how should I build my table 'twitter' into the database. Below is a start, but happy to discuss best practice.

Simplified Example: Input = 'Beatle'. Desired output '@beatleMe'

table: songs (note artist is not distinct)
ID  ARTIST  SONG
1   Mike J  Beat It
2   Beatle  Yellow Sub
3   Beatle  Lucy Diamond

table: twitter (note artist is distinct)
ID  ARTIST  HANDLE
1   Mike J  @mikeJ
2   Beatle  @beatleMe

I'm from an excel background, so I think in terms of lookups. But I know I can get more out of my database with foreign keys, joins etc (if they are relevant here I'm not 100%). So am keen to learn.

The song table will be maintained most regularly, so it would be good to have the artist columns mirrored/linked is that possible?

A good starting place is to research database normalization. It explains some concepts on how to best structure tables.

More specifically, I'd name the tables "Song" (singular is often preferred) and "Artist" (probably a more central concept than a twitter account). A song would then have an ArtistID which refers to the Artist table. To get the twitter account for an artist you only need to look at the Artist table, you don't need to look at the Song table at all, e.g.:

select TwitterHandle
from Artist
where ArtistName='Beatle';


"Keyword not supported: Server" error when using ADO.NET connection string to connect to Azure DB I have an ASP.Net project that I am running. I have added a database to Azure, and Azure generated a connection string for the database. When running the application I however get an error stating that the server keyword within the string is not supported. Here is my connection string:

/**/
<add name="AnimalEntities" connectionString="Server=tcp:uuu.database.windows.net,1234;Initial Catalog=MyDataBaseName;Persist Security Info=False;User ID=xfakeID;Password=xxxfakePassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.EntityClient" />
/**/

I have analysed the answer on here but haven't been able to figure out what my eventual string should be.

How should I structure my string?

As marc_s stated in the accepted answer (in the link provided in the question):

  For Entity Framework ... you need to use a special type of connection string which is quite different from the straight ADO.NET connection strings ...

Your provider, as shown in the code in this question, is: System.Data.EntityClient. Yet you are using standard ADO.NET connection string keywords.

You can either:

change the connection string to be similar to what is shown in marc_s's accepted answer,

OR
change the provider name as shown in the #2 answer (by WasiF), which is really just changing EntityClient to be SqlClient


Logshipping from SQL Enterprise 2012 SP1 to 2012 SP3? I have a requirement to setup a warm standby from an SQL Enterprise 2012 SP1 server. I can only use log shipping.

I understand that versions have to be the same for the STANDBY option to work.

Question is - can I restore from SQL 2012 SP1 to 2012 SP3 and use standby mode? Or is that counted as a different version?

And if so, can I log ship from enterprise to standard for example?

Question is - can I restore from SQL 2012 SP1 to 2012 SP3 and use standby mode? Or is that counted as a different version?

Yes you can restore the user database backup and this would be possible because version remains same, although this is NOT the recommended approach. Ideally the service pack level should be same. After you have done the restore please plan to upgrade the primary server to SP3 ASAP.

  And if so, can I log ship from enterprise to standard for example?

Yes, subject to condition the backup does not includes enterprise features data or the time you took backup of enterprise DB it should not be using any enterprise features other wise restore on standard will fail. To see whether enterprise features are used query enterprise DB with

select * from sys.dm_db_persisted_sku_features


Database is not accessible after 'taking it offline' process failed I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying

'an exception occured while executing a transact SQL statement or batch

-> ALTER DATABASE failed because a lock could not be placed on database 'myDB'. Try again later.
    ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)'

Now if I try and access the DB it says it is not accessible. What can I do to make my DB accessible again?

My aim was to detach the DB, relocate its secondary database file to a new drive and reattach it (simply because of space issues).

You should never do this using detach. Until we know more about exactly what state your database is in now, for next time, you should do the following:

ALTER DATABASE SET OFFLINE
For each file, ALTER DATABASE MODIFY FILE
copy the files to the new location
ALTER DATABASE SET ONLINE
when everything is working, delete the original files

The reason you don't want to do this with detach, and the reason you want to copy the files instead of moving them, is because when something goes wrong in either of those processes, you have exactly 0 copies of your database. Or the situation you're in now.


Split two rows into two columns I have the following table:

id   | name   | action | count
------------------------------
 1   | Value1 |    0   | 27
 1   | Value1 |    1   | 49
 2   | Value2 |    0   | 7
 2   | Value2 |    1   | 129
 3   | Value3 |    0   | 9
 3   | Value3 |    1   | 7

I need to make the 'action' column appear twice, with the count value of each line in it, something like this:

id   | name   | action1 | action2
---------------------------------
 1   | Value1 |    27   | 49
 2   | Value2 |    7    | 129
 3   | Value3 |    9    | 7

How can I do this? Here's my script:

SELECT m.id,
    t.name,
    m.action,
    count(m.id) AS count
FROM table1 m
LEFT JOIN table2 t ON (m.id = t.id)
WHERE m.status != '2'
GROUP BY m.id,
    t.name,
    m.action
ORDER BY 1, 3

You can use a conditional aggregate:

select m.id,
       t.name,
       count(case when m.action = 0 then m.id end) as action1,
       count(case when m.action = 1 then m.id end) as action2
from table1 m
  left table2 t ON (m.id = t.id)
where m.status <> '2'
group by m.id, t.name;

In the upcoming 9.4 release the count(case(...)) could be written a bit shorter using the new "filtered" aggregate: count(id) filter (when m.action = 0)


What is the modern way to partition PostgreSQL across machines, when the data is "naturally partitionable" After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature.
Today I see data stores with quite different eyes than before. Things like Riak have spoiled me in a way that I can no more tolerate single points of failure, "down for maintenance" etc. Of course, (or I hope), I havent' lost my sanity totally. This is a a personal project that doesn't quite (or yet)  have extremely high requirements.

Most of the sharding solutions don't give me what I want (at least on a glimpse), probably because my problem is quite "easy" to solve. At least on conceptual level (ignoring the restraints that RDBMs themselves bring to the table).

I have a small amount of "shared" data, which can be duplicated freely. It doesn't have requirements of hard consistency. This can be stored in a dynamo-like database and will scale infinitely. But I still would like to go with a single database if possible.
I have lots of "per-user" data. That is - lots of users, with every user having data of absolutely reasonable size, really fit to be stored on a single PostgreSQL node. We are talking about 10s of thousands records at maximum.
I never need to query cross-user and I don't need cross-user atomicity.

This sounds extremely easy to achieve. At least when I'm looking at it with my "NoSQL eyes".

Here are my naive starter ideas:

At the very extreme, I could just serialize the whole user as a single Key/Value in Riak. Of course, constant de/serialization of several megabytes of data will be slow and that's why I'm considering using PostgreSQL. Lots of Riak K/Vs is a no-go, as I need atomicity/transactions within each user's data.
I could use an SQLite database per user, and use something like GlusterFS for the redundancy/availability. This is probably the solution I'm going to choose if I can't find something equally good using PostgreSQL. Pros: Can down/up scale really well; Cons: I'd prefer having PostgreSQL's types and strictness over SQLite

So, what I would ideally request from a PostgreSQL sharding solution:

Automatically keep several copies of every user's data around (on different machines). Be able to dynamically switch the master node per user/shard (if the previous master goes down).
Be able to dynamically up/down scale, by adding/removing server nodes. Mostly like Riak is able to do.
Do not require my application to know which nodes to talk to and when.

Postgres-XL is attempting to solve this as of 2014. They're aiming directly at big data on PostgreSQL, and they have developers from Stado onboard.


How to be sure that a SQL Script is running with ANSI encoding? Maybe this is a stupid question or I'm asking it in the wrong way.

How can I be sure that a script ( with thousands of lines ) is running with ANSI encoding?

Let's say we created a script using Notepad++ ( programmers code for SQL and ORACLE at the same time ), and we save it with Encode in ANSI :

OK. Then, we have inside our script a Â character. If our client just copies this script into some tool that is using a different encoding ( really, I don't know why, but some users do this, and I know for sure it's not to find errors), this Â  will be transformed into a: , but since our script has thousands of rows, nobody will notice this at the client side.

We know users don't see this, they will not read the entire script to be sure everything is ok. Not even DBAs that are paid to do this, will do this ( I'm a DBA and I for sure read all scripts all the time ).

So, how can I be sure, that when the user press F5, all script is encoded with ANSI, the same way we sent it, without these strange characters? Can we only achieve this with the right collate for the database?

I was trying to think something like this on the first row of the script with a case when asci character = the ascii(character) then ok else ERROR ( using ascii to test ):

Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII

Solomon, this is the query:

Select *,
Case
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
END as STATUS_TESTE,
Case
       When Teste_CHAR=Teste_CHAR_ASCII
       Then 'Everything is OK'
       Else 'Script will not run. your encode is different from ours'
END as Mensagem_TESTE
 from (
Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII
) A

So, how can I be sure, that when the user press F5, all script is encoded with ANSI, the same way we sent it, without these strange characters?

You can't be sure. This is just the unfortunately complicated nature of text encoding, especially non-Unicode encodings. Everything is just bytes. What we see on the screen is just an interpretation of those bytes. One encoding may certainly display a different "character" than another encoding for the same byte, or byte-sequence (depending on the encoding), but technically, bytes are bytes and all bytes are valid.

In the case of Â, you can't detect a difference since there is no difference. You only see the xC2 in Notepad++ because that is the byte value of that character, yet that, by itself, is not a valid UTF-8 or UTF-16 / UCS-2 byte sequence, so Notepad++ shows you just the byte by itself.

Now, going between one of the Unicode encodings (UTF-8, UTF-16, or UTF-32) and an 8-bit encoding could be detectable by finding a character that is not in any 8-bit code page and comparing it to ? / CHAR(63), and if they match, then you are no longer using a Unicode encoding.

The deficiency here is that 8-bit encodings have no way to indicate what encoding / code page they are. You just have to know. But, the Unicode encodings have the option of placing a few bytes at the beginning of the file to indicate the type of encoding being used. This sequence of bytes is called the Byte Order Mark (BOM) and if in the correct encoding, will be non-visible.

So, your best-bet is to use one of the Unicode encodings and be sure to save files with the Byte Order Mark (BOM) since you usually have the choice to save in the Unicode encoding with or without the BOM. In Notepad++ (which I use) the two UCS-2 options are both BOM-only, but UTF-8 has the choice. If your scripts are currently using ANSI, then in the Encoding menu in Notepad++, select Convert to UTF-8-BOM and then save the file. Then, when copying and pasting into SSMS, all should be fine. And opening that file in most editors will automatically detect that it is encoded as UTF-8 because the BOM is there.

  Can we only achieve this with the right collate for the database?

This has nothing to do with SQL Server. This has to do with the client tool and what encoding it is using. SSMS is almost certainly using UTF-16 LE (Little Endian) as that is what Windows / SQL Server / .NET uses.

Regarding the query recently added to the end of the question:

The ã has a value of 0xE3 in ANSI encoding, which isn't valid in UTF-8 or UTF-16. In Notepad++, changing the encoding to be UTF-8 (using "Encode in", not "Convert to") results in it showing just the xE3. Copying and pasting that version of the query into SSMS takes that byte plus the next one (the one used for the closing ') and converts it to 㧩 which breaks the query due to no closing quote. You can fix that by adding 2 spaces after the ã so that it looks like:

CHAR(ASCII('ã  '))

That will still work as expected when the encoding has not been changed since the ASCII function only returns the value for the first character and the additional characters (the 2 spaces) are ignored.

If that script is imported or changed to be UTF-8, it will appear as follows in Notepad++:

CHAR(ASCII('xE3  '))

and that xE3 will be a single "character". Copying and pasting that version of the query into SSMS will show up as follows:

CHAR(ASCII('㠠'))

And running that will produced the desired "error" result.

HOWEVER, please be warned that this is not a fool-proof / guaranteed test. It mainly just indicates that the script was incorrectly opened as being UTF-8, UTF-16, or an 8-bit code page that does not contain the ã character.

This approach does not indicate an error if the script is opened as an 8-bit encoding that is not ANSI but still contains the ã character, and could be misinterpreting (i.e. changing) other characters.

The only way to guarantee ANSI encoding is to find a character that is a) not available in any other 8-bit code page, and b) not the same in UTF-8 or UTF-16. I am not aware of any such character, though I also have not checked them all against all available code pages.

BUT, if you are only dealing with people opening the files as UTF-8, then making the adjustment shown above should work for that situation.


Foreign key for array column I have a table:

CREATE TABLE methods
(
    method_id serial PRIMARY KEY,
    method_name varchar(100)
);

I now want to create a table with the following columns:

CREATE TABLE experiments
(
    method integer[] REFERENCES methods(method_id),
    trials integer
);

I get an error:

  Key columns "method" and "method_id" are of incompatible types: integer[] and integer.

I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already:

PostgreSQL 9.3 development: Array ELEMENT Foreign Keys

My interest is that 'method' should be an array of integers referencing 'method_id' from table 'methods' but I can't figure it out. I thought that the link above might be a solution, but seems that was not implemented (?)

Some posts propose using junction/join tables:

Foreign key constraint on array member?

I am an absolute beginner and I could not figure it out yet. For me, it is a matter of understanding first how to tackle this issue of a foreign key on an array. Multiple methods can be considered as forming a class of methods. Each class can have a certain number of trials.

As you simply cannot create a foreign key against an array column, you should store the experiments in the proper relational way.  This is what you mention as a junction table.

It is a table like the following:

CREATE TABLE experiments (
    method_id integer REFERENCES methods (method_id),
    method_class_id integer REFERENCES method_class (method_class_id)
);

Here you will have one row per method-class pair (which, in turn, may mean several trials, based on your comments above), instead of just one per trial as in your current design.  This means a slight storage overhead, but it gives you the flexibility - queries will use proper joins which can be supported by indexes, for example.  Also, removing methods from a class will be much easier.

Note: I suppose above that you have a method_class table, too.


get count of subjects with at least one record in posts table I have two tables: posts and subjects.

posts may have subjects, but not all subjects may have been assigned to some posts. Now I want to count only those subjects with at least one post related to them.

subject columns are: id, name
posts columns are: id, title, subject_id

Try:

select count(*) from subjects where exists (select 1 from posts where subjects.id = posts.subject_id);


Store vectors in SQLite I am starting to work with SQLite in Qt Creator. I never worked with SQLite before. How can I create a table to store the following data?

int id;
QString fullName;
int age;
QVector<QString> friendsFirstName;
QVector<QString> friendsSurname;

In fact, my doubt is how to store vectors that must be linked to a single record? SQLite provides some vector type?

Thanks in advance

Only some basic types.
You can however use the BLOB type to save whatever you want.
Are you going to do the vector processing outside to SQLite?

https://www.sqlite.org/datatype3.html

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE
floating point number.
TEXT. The value is a text string, stored using the database encoding
(UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.


MySQL high CPU usage (MyISAM table indexes) I have a problem with an inherited MySQL database.
From time to time mysqld uses up to 2300% CPU..
The only solution is to service mysql stop and run an myisamchk -r on a table.
After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?

Edit (from the comments):

Using 5.5.29-0ubuntu0.12.04.2-log

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 500
#table_cache = 512
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 16M

SELECT SUM(index_length) ndxsize
FROM information_schema.tables
WHERE engine='MyISAM'

returns

+----------+
| ndxsize  |
+----------+
| 59862016 |
+----------+

SELECT SUM(data_length+index_length)/power(1024,2) datndxsize
FROM information_schema.tables
WHERE engine='MyISAM'

returns:

+--------------------+
| datndxsize         |
+--------------------+
| 488.69915199279785 |
+--------------------+

The server has 16GB of RAM, but it is not a DB server...It is running nginx + php-fpm

SUGGESTION

Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.

You could just bump up key_buffer_size to 64 MB.

You must add this to /etc/my.cnf

[mysqld]
key_buffer_size=64M

You don't even need to restart mysql. Just run this:

SET @MB = 1024 * 1024;
SET GLOBAL key_buffer_size = @MB * 64;

You can also convert the row format of all your MyISAM tables. This will just about double the disk space used by will increase read performance 20-25%. I wrote about this before as well:

Which is faster, InnoDB or MyISAM?
Best of MyISAM and InnoDB

Here is a script to convert all your MyISAM tables to Fixed Row Format

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -A --skip-column-names -e"${SQLSTMT}" > /root/MakeFixedRows.sql
mysql -uroot -p < /root/MakeFixedRows.sql

ALTERNATE SUGGESTION

Given you have a total of 488 MB of of MyISAM data and indexes, I would like to suggest the following: Switch Everything to InnoDB. I have two reasons:

REASON # 1

MyISAM only caches indexes. InnoDB caches data and indexes. See my post : What are the main differences between InnoDB and MyISAM?

This will reduce disk I/O for reading data and indexes. In your particular case, you can easily handle an InnoDB Buffer Pool of 512M and basically fit everything into RAM.

REASON # 2

The MyISAM storage engine cannot access multiple CPUs. InnoDB can. In fact, I have many posts on tweeking InnoDB for this:

Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

EPILOGUE

I would like you really consider the conversion from MyISAM to InnoDB

When to switch from MyISAM to InnoDB?
Any gotchas at all with converting from MyISAM to InnoDB?
InnoDB vs MyISAM with many indexes


SQL Managed Instance - Cross Subscription Restores/Migration Short Version:

I am trying to restore a backup from one SQL Managed Instance to another SQL Managed Instance.  These are in different subscriptions.  Using Example 3 from here I can at least get it to the point where I see the restore starting in sys.dm_operation_status, but the error message is "Subscription [target] does not contain [source SQL MI]".

Questions:
How can I migrate the database from one SQL Managed Instance to another (in a different subscription)?  My current workaround is to do copy_only to a blob storage and restore that way.

Once we enable TDE (in-progress) we won't be able to do copy_only backups to blob.  What then?

Long Version:

Fairly recently our company made the jump to running our production workload in Azure on SQL Managed Instances (and other PaaS/IaaS) and this went fairly well.  We had a large enough window identified that we were able to do the initial load with a full backup, although I did familiarize myself with the Database Migration Services (DMS).

But now we are expanding out our dev/test/qa environments and of course we now want to restore from production to these environments.  For isolation purposes, each is getting it's own subscription.

These commands work, in the sense that I can at least see the restore operation start in sys.dm_operation_status but it fails with an error.  The error is "Subscription [target] does not contain [source SQL MI]" and leads me to believe that cross subscription restores won't work for us (although cross region would).

DMS does not appear to support the scenario of using Database Migration Services as a source.

Select-AzSubscription -Subscription $SourceSubscriptionID

$GeoBackup = Get-AzSqlInstanceDatabaseGeoBackup -ResourceGroupName $SourceResourceGroupName `
     -InstanceName $SourceSQLMI `
     -Name $SourceDatabaseName

Select-AzSubscription -Subscription $TargetSubscriptionID

Restore-AzSqlInstanceDatabase -FromGeoBackup `
    -GeoBackupObject $GeoBackup `
    -TargetInstanceDatabaseName $TargetDatabaseName `
    -TargetInstanceName $TargetSQLMI `
    -TargetResourceGroupName $TargetResourceGroupName

Any advice, ideas?  I'm willing to use preview versions of the various Azure PowerShell if necessary although I've not found anything to indicate that it's different there.

POSSIBLE WORKAROUND

I did get a link from one of our Microsoft contacts that seemed to indicate that you can stand up a managed instance in the same subscription, restore to it and then move the managed instance to another subscription.  I have NOT attempted this so I don't know.  The link they provided was here although I have my doubts about it's effectiveness.

copy only backups are currently the only solution. Cross subscription pitr and geo restore don't work because currently we cannot verify that initiator has all required permissions both on source and target subscriptions.


One SQL Server 2014 AG node down for 4 days - will it restart into AG? We are planning one of our server rooms to be shutdown for 4 days. We have one node of each of our AGs in this server room.

It only just occurred to me that perhaps there may be an Availability Group tombstone setting (similar to replication), whereby an AG node once off line for a while may have a "use by" date and may not restart into the AG. I have looked a lot in MS doco and so far can't find anything that indicates an AG node might be timed out of the AG.

We run SQL Server 2014 SP1 on Windows 2012.

There is no tombstone in Availability Groups. However, one thing you need to consider is the mode your AG is in for the replica that is being taken down for 4 days, and how much log is going to need to be kept for that period.

An AG syncs up other replicas by sending the data written to the log of the database, if you are going to stop a replica for 4 days that could be ALOT of log space needed.

You are basically suspending data movement to that replica by taking it offline (or shutting it down). That is acceptable for a few hours on moderate loads, but when you get into days you need to remove that replica from the AG altogether. This is going to prevent the need to keep all the log data in the database log files for any database in that AG.


What is Transaction Path Analysis? I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my understanding is that it is about tracking what columns are used by queries to access data, in preparation for deciding indexes. This seems like common sense, but I'm surprised I can't find more specifics on it.

Is there a concise definition for transaction path analysis, a process for completing one, or any alternative more commonly used phrases that mean the same thing?

This term relates to the process of identifying the significant events in the transaction life-cycle, including error-processing, auditing, save-points, nested transactions, and distribution paths for distributed transactions.

I think you may find more results with the synonymous term Transaction Life-cycle Analysis.


MySQL row length is almost the triple of what I expect I shamelessly took title and content from this question and adapted it to my similar yet different problem:

This is the table I have:

CREATE TABLE `test` (
    `field1` MEDIUMINT(8) UNSIGNED NOT NULL,
    `field2` INT(10) UNSIGNED NOT NULL,
    `field3` SMALLINT(5) UNSIGNED NOT NULL,
    `field4` SMALLINT(5) UNSIGNED NOT NULL,
    `field5` FLOAT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`field1`, `field2`),
    INDEX `field2` (`field2`, `field1`)
)
COLLATE='latin1_swedish_ci'
ENGINE=ndbcluster
ROW_FORMAT=DEFAULT

I expect the row length to be 15 bytes:

3 bytes for field1 mediumint(8)
4 bytes for field2 int(10)
2 bytes for field3 smallint(5)
2 bytes for field4 smallint(5)
4 bytes for field5 float

But show table status; returns

Name: test
Engine: ndbcluster
Row_format: Fixed
Rows: 143120123
Avg_row_length: 44 <--------------  (I assume this is in bytes?)
Data_length: 7659913216

As you can see, I have quite a few rows, so row length really does matter.

Why does it take so much space?

And why is Data_length so much bigger than expected?

Data_length - (Rows * Avg_row_length) = 7659913216 - 6297285412 = 1362627804 = 1.26 GB

And what are those digits in brackets after the type names for? (e.g. mediumint(8))
I guess it's the char length of the field's string representations and I have the strange feeling that MySQL's ndbcluster engine stores them exactly like that!
Which seems amazingly stupid to me...

I don't think I've ever come across any description of how ndbcluster affects row layout. (I haven't looked hard, though. Also haven't read the source code.)  But, for example, innodb tables have a well-documented row layout. (Innodb row structure.)  You can find quite a few things that affect the overall length of a row.


Levenshtein vs MATCH vs Others for best MySQL string match I've a database with around 1.9 Million rows. My DB details

Server: Localhost via UNIX socket  Server type: Percona Server Server
version: 5.5.42-37.1 - Percona Server (GPL), Release 37.1, Revision
727  Protocol version: 10 User: ****@localhost  Server charset: UTF-8
Unicode (utf8)

Currenty Using:
I'm using the following algorithm on my FULLINDEX column First, I match my required string as per the below query
SELECT title FROM my_db WHERE MATCH (`Title`) AGAINST ('my string' IN BOOLEAN MODE)

And then I use a levenstein() distance function on the results row server side through PHP to get the closest match to the string.
My Questions:

Would it be faster to implement a levenshtein entirely instead of a MATCH, AGAINST on a fulltext for such a huge database?
Is there any implementable algorithm which is better than levenshtein or for that matter the best such algorithm existing right now?
Is there any other work around instead of MATCH or levenshtein() ?
How will each the below search modifiers

IN NATURAL LANGUAGE MODE,
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION,
IN BOOLEAN MODE,
WITH QUERY EXPANSION,

enhance or optimize my searching and results? Hence, which one would be the best?
Thanks!

A FULLTEXT index is very efficient for small resultsets in a huge table.  Using levenshtein involves checking each row.  So, I agree with your approach to do FT as a first step.

I would suggest

Remove short words, etc, from the string.  (not necessary for MyISAM since it ignores them; necessary for InnoDB, else it returns nothing)
IN BOOLEAN MODE but without any "+" on the words;
ORDER BY MATCH... DESC and add LIMIT.  This keeps edge cases from coming up with thousands of rows for the next step.
Check levenshtein distance.

Keep in mind that the end result will be less than perfect, but at least it should be "fast enough".


SQL Server backup fails on disk space error SQL Server 2008 R2 RTM running on Windows 2008 64bit.

Having problems backing up a single database with the following command

backup database [somedatabase]
to disk = '\\somehost\d$\BACKUP\somedirectory\some_database.bak'
with compression, init, stats=1

The database backs up just fine for a while

93 percent processed.
94 percent processed.

Then I get an error about diskspace

  Msg 3202, Level 16, State 1, Line 1
  Write on "\somehost\d$\BACKUP\somedirectory\some_database.bak" failed: 1130 (Not enough server storage is available to process this command.)
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

This doesn't make any sense because there is over 800GB of available disk space and the backup is using 121GB.

In SQL Server's logs I get this message

Date        9/25/2013 1:04:56 PM
Log     SQL Server (Current - 9/25/2013 12:15:00 PM)

Source      Backup

Message
BackupIoRequest::ReportIoError: write failure on backup device
'\\somehost\d$\BACKUP\somedirectory\some_database.bak'. Operating
system error 1130(Not enough server storage is available to process
this command.).

Some information:

Backups are being executed from the server locally and the backup files are being saved to \\somehost which is another server close by.
Both the local and remote hosts have sufficient disk space.
Compression is on

Any assistance on getting to real cause of these backup failures would be appreciated.

Thank you

Update 16:48

As of R2, Compression is a backup option on standard. Thank you RLF. Still having the same problem with or without. The only difference is that the backup fails at 18% without compression. Decided to monitor disk space to make sure nothing else was eating up GB during the backup.

As expected, available disk space went down to 600GB while using compression and 400GB without. Disk space never dipped below 400 though.

The error not enough server storage available is misleading.  This is not disk space, but Windows SMB management memory.  Try restarting the target machine (where the backup is being saved), or at least restart the Server service.

See the following Microsoft support link for instructions on potential registry changes that may be necessary at the machine where the backup is being stored.

http://support.microsoft.com/kb/304101

Also, you might try a search for "Windows Server error 1130" for more info.


Product quantity bound to colors and sizes table structures I've been thinking about how I'll be able to create products with sizes and colors which will match the max quantity of a product (or their total will be the max.quantity ).

Let's say I got a product which has a total 12 quantity.

In those 12 there are colors such as RED,BLUE,GREEN. AND there will be 3 sizes such as SMALL, MEDIUM, LARGE

Like:

3x RED SMALL
1x RED LARGE

2x BLUE SMALL
2x BLUE MEDIUM
1x BLUE LARGE

1x GREEN SMALL
1x GREEN MEDIUM
1x GREEN LARGE
Total:12X

Now, sometimes some products wont have any colors, only sizes. Sometimes only colors, no sizes. Sometimes no color neither sizes.

What is the best way of structuring tables to acheive this?

I think like:

Products:
PID,name,(maxqty?)

Sizes:
SID, name

Colors:
CID,name

Used_sizes_colors:
PID,SID,CID,qty

How would you do this?

The model you described looks good to me.

You can add a trigger that will work on UPDATE and INSERT and will check if SUM(qty) from Used_sizes_colors table for updated or inserted PID is greater than Products.maxqty, and if it is: rollback transaction and display error.


;WITH First_table
AS (SELECT '2015-03-08' AS [Date],'Diag1' AS Diagnostics, 2932 AS ID
    UNION ALL SELECT '2015-03-09','Diag2', 2932
    )
,Second_table
AS (SELECT '2015-03-09' AS [Date], 'Eval1' AS Evaluation ,2932 AS ID
    UNION ALL SELECT '2015-03-09','Eval2',2932
    UNION ALL SELECT '2015-03-10' ,'Eval3' , 2932)

SELECT
    KT.[Date] AS [Date]
    ,FT.Diagnostics AS [Diagnostics]
    ,STUFF(ST.Evaluation,1,1,'') AS [Evaluation]
FROM
    (
        SELECT [Date] ,[ID]
        FROM First_table
        --GROUP BY [Date] ,[ID]

        UNION

        SELECT [Date] ,[ID]
        FROM Second_table
        --GROUP BY [Date] ,[ID]
    ) AS KT  --keyTAble
    LEFT JOIN First_table AS FT
        ON kt.[Date] = FT.[Date]
        AND kt.[ID]= FT.[ID]
    OUTER APPLY
        (
            SELECT
                ',' + Evaluation
            FROM Second_table AS ST
            WHERE
                KT.[DATE] =ST.[Date]
                AND kT.[ID]= ST.[ID]
            FOR XML PATH('')
        )ST(Evaluation)