MySql - is possible that performance issues are caused by dynamic SQL? We have this MySQL SP, which uses dynamic SQL.
It seems it does NOT perform well under load.

It is possible that this SP becomes slow under load because it uses dynamic SQL?
Can dynamic SQL cause performance issues in MySql (eg because it is not cached by the engine)?

Please note that this SP is called from several other SPs. It passes results to parent SPs using a temp table.

UPDATE - problem solved (see my answer below)

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    end if;

END

Good news - it seems dynamic SQL is NOT a bottleneck.

The solution was to change temp tables to MEMORY engine.
It did the trick and made AMAZING difference. Solution was suggested in

Can MySql nested SP be a bottleneck?

create temporary table areas (
    id int not null,
    code varchar(30),
    name varchar(100),
    shortName varchar(100),
    levelid int not null,
    sortOrder int not null,
    key (id)
) ENGINE=MEMORY;


Unique combination of values I want to create a database for my program. Most likely I will use SQLite, although others are an option.

I will have columns a, b, c, d. a is unique and will be my primary key. b, c, d are allowed to contain duplicate value.

However, I don't want combinations of b, c, d to repeat. For instance, sample valid db:

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  t   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Sample invalid db (rows 2 and 7):

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  y   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Is there standard database rule or concept that applies to this?

I can of course simply do validation in my code, by manually querying the db. I believe I could also construct a fifth bcd column, which concatenates the other three, and is required to be unique. But both of these seem hacky. Is it possible to somehow "declare the columns unique in combination only" in a more direct and obvious way?

For instance, I could declare (b, c, d) as my composite primary key (not sure if SQLite supports this but I think SQL does). That should accomplish my goal. However, I don't want these columns to actually be my PK.

CREATE UNIQUE INDEX your_unique_index ON your_table(b, c, d);

Create an index instead. It has the same as the unique constraint.


Configure mongo for one shot full db read I have a kubernetes cluster on bare metal with 3 machines having 8Gb of ram each. All my apps and a mongodb 4.0.9 replicaset runs on it.
There is an import program that:

Download a 8Gb mongo dump from external source and restore it in fresly created database A. There is one collection whitout indexes.
Fully browse the restored collection with one find({}) and 1M next().
Emit amqp messages for each doc, then messages are stored in mongo database B, C, D.
Drop the database A.

Part 2 use a lot of ram.

How should I configure mongo database A (and let database B normal) to reduce at max the footprint of the import operation? (Because more important tasks are running in the cluster)

For example, can I configure mongo to not create cache for the temporary collection?

Starting in MongoDB 3.4, the default WiredTiger internal cache size is the larger of either:

50% of (RAM - 1 GB),
or 256 MB.

For example, In your case from the 8GB of RAM the WiredTiger cache will use 3.5GB of RAM (0.5 * (8 GB - 1 GB) = 3.5 GB).

This can be reduced to for example to 1GB by using the following admin command only when the mongorestore operation happens.

db.adminCommand( { "setParameter": 1, "wiredTigerEngineRuntimeConfig": "cache_size=1G"})

and revert this back to 3.5 GB once the restoration is completed.

Note:

By reducing the wired tiger cache size will increase the time to restore the data.


Items from last month AND if not possible, items from this month So the title is not really clear, but I could not think how to put it into shorter words better.

My problem is:

I have a database, which I use for web-scraping, with different articles and their prices. The prices may change each month. To be able to have a history, I created two tables:

items which has all articles that are currently in this month's crawling. The table has all the infos on every article
log which has only an article-id, price and date

So basically in items every article is unique, while log can have multiple occurrences of an article.

What I want to do now: I have created a view which yields every article from items and among other things calculates the price difference to last month (as the price and date in items is updated each month).

Here's an excerpt from my view:

CREATE VIEW XLSX AS
    SELECT DISTINCT ON(items.article)
           items.article,
           items.price as "price_new",
           ROUND(CAST(items.price - log.price as numeric), 2) as "price_difference",
           log.price as "price_old"
    FROM   items
    INNER JOIN log
    ON     items.article = log.article
    WHERE  (items.date - log.date) < 25
    ORDER BY items.article, log.date

The problem I now have is that new articles are currently not being included in the view. Say for example that at the start of July I have 20 new articles, which are inserted into items and log with date = '2017-07-01'. Obviously they are not included in the view, due to the constraint to only include articles from log that are older than 25 days (since the crawler may run periodically multiple times a month, updating the prices more than once).

Could I use CASE here to get articles that are older than 25 days or if there are none, get those that have the same date?

Use LEFT JOIN LATERAL ... ON TRUE to preserve all rows of the right table:

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN LATERAL (
   SELECT price, date
   FROM   log
   WHERE  article = i.article
   AND    date < (i.date - 25)  -- make expression sargable, plus fix off-by-1 error
   ORDER  BY date DESC          -- to make sure ...
   LIMIT  1                     -- ... we pick at most 1 row
   ) l ON TRUE
ORDER  BY i.article, l.date;

Returns all rows in items, and you get NULL for price_difference where no previous older than 25 days is found in log.

What is the difference between LATERAL and a subquery in PostgreSQL?

I added ORDER BY ... LIMIT 1 to make sure we only pick the one latest entry. If data in table log can be trusted to never return more than one row, you can skip the added lines.

Or, if the query does not actually depend on items.date, a single subquery depending on the current date using DISTINCT ON can be cheaper (with the right indexes):

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN (
   SELECT DISTINCT ON (article)
          article, date, price
   FROM   log
   WHERE  date < (CURRENT_DATE - 25)
   ORDER  BY article, date DESC
   ) l USING (article)
ORDER  BY i.article, l.date;

About DISTINCT ON:

Select first row in each GROUP BY group?


Best Approach to Migrating Oracle to MySQL - Run on EC2 in AWS I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database.  I want to then make an EC2 app on AWS to kick off whatever I've written for this.  Are there any approaches that one might say is better than another (for instance, use Ruby/Rails instead of Perl)?  Is there a tool that I might be able to wrap into an app and place it on my EC2 instance?

Full disclosure, I'm pretty much a complete newbie when it comes to AWS, so if there is something really basic that I'm missing (you could use some type of Linux-based MySQL Workbench-like tool) I would definitely appreciate that.

I realize this is a bit specific, thank you in advance!

There are two tasks here. The migration to MySQL and setting up AWS. Setting up AWS is off topic here - try Superuser or Stackoverflow.

You are in for a tough job. MySQL is a much simpler database and does not have as many features as Oracle. Also many things in Oracle are non-standard making it difficult to migrate to / from.

The process is basically as follows:

Dump Oracle database schema / DDL. You will end up with a file full of CREATE statements
Update the DDL to allow for types that Oracle does not support. Read the MySQL manual. Use InnoDB as the backend
Dump Oracle functions
Rewrite the Oracle functions. This is probably going to be the most time consuming step. You will have to look at the Oracle and MySQL documentation
Run your converted DDL and functions against your new MySQL database
Dump the data from Oracle
Restore the data into MySQL. You may have to temporarily disable some contraints


Pivot query issue while converting row to column I have these two tables:
Table 1: tbl_bulkuploadfields

fid |  categoryid | fieldname
--------------------------------------
1   |      1      | educationlevel
2   |      1      | institution_name
3   |      1      | course
4   |      1      | year_of_passing
5   |      1      | percentage

Table 2: tbl_bulkuploads

category | fieldname | fieldvalue
-----------------------------------
1        |     1     | SSC
1        |     2     | H.B.Kapadiya
1        |     3     | Primary school
1        |     4     | Mar-2000
1        |     5     | 89%
1        |     1     | HSC
1        |     2     | Vishwaniketan
1        |     3     | Higher Secondary
1        |     4     | Mar-2002
1        |     5     | 70%

Below is the structure which I want as a result of a pivot query:

category | educationlevel | institution_name | course | year_of_passing | percentage
------------------------------------------------------
   1     | SSC            | H.B.Kapadiya     | Primary School | Mar-2000 | 89%
   1     | HSC            | Vishwaniketan    | Higher Secondary|Mar-2002 | 70%

I have written the below query but it is returning only a single row instead of multiple rows:
select distinct B.Category,
MAX(IF(tbl_bulkuploadfields.fieldname = 'educationlevel', B.fieldvalue, NULL)) AS educationlevel,
MAX(IF(tbl_bulkuploadfields.fieldname = 'institution_name', B.fieldvalue, NULL)) AS institution_name,
MAX(IF(tbl_bulkuploadfields.fieldname = 'course', B.fieldvalue, NULL)) AS course,
MAX(IF(tbl_bulkuploadfields.fieldname = 'year_of_passing', B.fieldvalue, NULL)) AS year_of_passing,
MAX(IF(tbl_bulkuploadfields.fieldname = 'percentage', B.fieldvalue, NULL)) AS percentage  from tbl_bulkuploads B
inner join tbl_bulkuploadfields on tbl_bulkuploadfields.fid = B.fieldname
where B.Category = 1 GROUP BY B.Category

It is obvious, by how you have arranged the rows in your example, that there are two entities in the second table, each with its own set of attribute values.

However, in SQL it is a convention that rows in a table have no inherent order. Therefore, if you want the server to distinguish between the two sets, you need either

a column that would specify the row order (so that by using that order you could somehow determine where a new set of values starts):

category | fieldname | fieldvalue       | roworder
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 2
1        |     3     | Primary school   | 3
1        |     4     | Mar-2000         | 4
1        |     5     | 89%              | 5
1        |     1     | HSC              | 6
1        |     2     | Vishwaniketan    | 7
1        |     3     | Higher Secondary | 8
1        |     4     | Mar-2002         | 9
1        |     5     | 70%              | 10

or
a column that would serve as an entity identifier, so that it would be clear which set of values belongs to which entity (and, thus, on which row in the output it should end up):

category | fieldname | fieldvalue       | entityid
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 1
1        |     3     | Primary school   | 1
1        |     4     | Mar-2000         | 1
1        |     5     | 89%              | 1
1        |     1     | HSC              | 2
1        |     2     | Vishwaniketan    | 2
1        |     3     | Higher Secondary | 2
1        |     4     | Mar-2002         | 2
1        |     5     | 70%              | 2

The first option is certainly much inferior, because in order to get the desired result you would need to obtain some kind of entity identifier one way or another, and with the first option you would have to derive it somehow based on the order of rows. Note that you would probably be restricted to always storing the values in a specific order, and in particular it would be mandatory that values belonging to the same entity be stored in consecutive rows only.

With the second option you could store the values arbitrarily: the dedicated entity ID column would unambiguously determine which set of values the row should belong. Your query would then be very similar to what you already have, you would only need to add entityid to the GROUP BY:

SELECT
  b.Category,
  MAX(IF(f.fieldname = 'educationlevel',   b.fieldvalue, NULL)) AS educationlevel,
  MAX(IF(f.fieldname = 'institution_name', b.fieldvalue, NULL)) AS institution_name,
  MAX(IF(f.fieldname = 'course',           b.fieldvalue, NULL)) AS course,
  MAX(IF(f.fieldname = 'year_of_passing',  b.fieldvalue, NULL)) AS year_of_passing,
  MAX(IF(f.fieldname = 'percentage',       b.fieldvalue, NULL)) AS percentage
FROM
  tbl_bulkuploads AS b
  INNER JOIN tbl_bulkuploadfields AS f on f.fid = b.fieldname
WHERE
  b.Category = 1
GROUP BY
  b.Category,
  b.entityid
;


Why does selecting an attribute of an Oracle object require an alias? My GIS software has an Oracle 18c object type called ST_GEOMETRY.
The object type has attributes:

The attributes of the spatial type represent the following
information:

Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or
polygon), the value of which is a bit mask derived from the
st_geom_util stored procedure.
Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point
for each separator.
Minx, miny, maxx, maxy: The spatial envelope of the geometry
Area: The area of the geometry
Len: The perimeter length of the geometry
SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the
ST_Spatial_References table
Points: Contains the byte stream of the point coordinates that define the geometry

I can select an object's attribute using one of two methods:

Use Oracle's TREAT() function.

select
    treat(shape as sde.st_geometry).numpts as num_points
from
    a_test_table

NUM_POINTS
----------
        37
       161

Or I can simply use an alias:

select
    a.shape.numpts as num_points
from
    a_test_table a

NUM_POINTS
----------
        37
       161

Question:
Why is it necessary to use an alias to select an attribute from an Oracle object?
If I were to remove the alias...
select
    shape.numpts as num_points
from
    a_test_table

Then I would get an error:
ORA-00904: "SHAPE"."NUMPTS": invalid identifier

Edit:
This appears to apply to the native Oracle SDO_GEOEMTRY methods too.
select
    a.shape.get_wkt()
from
    table a

Because Oracle made it so.
t_alias

t_alias
Specify a correlation name, which is an alias for the table, view,
materialized view, or subquery for evaluating the query. This alias is
required if the select list references any object type attributes or
object type methods. Correlation names are most often used in a
correlated query. Other references to the table, view, or materialized
view throughout the query must refer to this alias.


Long request freeze mysql server i'm working on a 5.7 Mysql server with MyISAM tables.

All my tables ares used by internal software for my customers. (and the tables, defined indexes, and storage type are imposed by the software)

I have create somes of request for generate reports. My reports work with multiples joined tables and lot of conditions. A request can make 5 minutes for display a result.

My problem is not the time ( because it's really due to the complexity of request). But during the request, all connexions are freezed and the users can't works.

  Have you some idea for change this situation ( i have already
  optimised my requests, and y can't work more on this side) ?

i search a solution about concurrent access, or other solutions.

note : my request are "read only".

thanks for help, and sorry for my lack of english.

Option 1: Change engines
First thing to do, if at all possible, is probably change MyISAM by InnoDB, which handles concurrency much better. See "MyISAM vs InnoDB", specially the point about "Table-locking vs Row-locking". I quote them:

When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.
When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

[emphasis mine]
Option 2: Master/Slave replication
Another possible (although probably not cheap) solution is to have your database being replicated using an asynchronous Master/Slave setup. [I guess that if you cannot change from MyISAM to InnoDB, it won't be easy to setup a replicated DB... but I don't know either your possibilities nor your limitations.]
Your users (i.e.: your application) should always be working with your Master node, and all INSERTs, UPDATEs and DELETEs should always and only be executed there. The SELECTs can be executed in either node, taking into account that the Slave might be slightly not up-to-date. The SELECTs that take a long time should be executed in the Slave, so all the users in the Master node are not blocked at all.
The Slave node can not be completely up-to-date (as the replication is asynchronous, it can lag behing the master by anything from a few miliseconds to a long time, if the Slave could not process the updates from the master and they "accumulate").
This kind of scenario is not uncommon in applications where you have the Master node working with a setup optimized for OLTP (OnLine Transaction Processing); and the Slave node with a setup optimized for OLAP (OnLine Analytical Processing). OLTP means (mostly) entering and manipulating data, and making relatively simple queries, with small amounts of data every time, whereas OLAP means querying data, normally in large amounts and with aggregations and computations (i.e.: analysis and reporting).


MongoDb linearizable read concern restrictions Could someone explain me some part of Mongodb linearizable read concern documentation:

  Linearizable read concern guarantees only apply if read operations specify a query filter that uniquely identifies a single document.

Is it mean that i have to have unique index on fields that presented in query filter?

For example let's answer on 4 questions:

I have collection test without unique index on A field. db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000)

Is it linearizable and i can't get stale read? If answer yes, is it mean that there no reason to use linearizable read concern in reads by fileds which not presented in unique index?
I have collection test with unique index on A field.
db.test.ensureIndex({A:1}, {unique:true});
db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000);

Is it linearizable and i can't get stale read?
I have collection test with unique index on A field.
db.test.ensureIndex({A:1}, {unique:true});
db.test.find({A:"someValue", B:"someValue2"})
       .readConcern("linearizable").maxTimeMS(10000);

Is it linearizable and i can't get stale read?
I have collection test without unique index on A field. But find method return only one document in result.
db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000); //returned {_id:"someId", A:"someValue"}

Is it linearizable and i can't get stale read?

Dmitry,

As of MongoDB v3.6, MongoDB only supports the linearizable read concern on single documents, hence the wording of the documentation. However, it doesn't mean you have to run the query using a unique index, but that you have to be sure your query only returns one document. Of course, it can only help to have a query filter that uses a unique index (such as the default index on the _id field), since this will ensure that your query returns a single document.

In your specific cases, queries #2 and #3 can enforce linearizable reads and it will not return stale reads.

For query #4, you will also get a linearizable read, assuming the query only returns one document (which is your assumption).

For query #1, you're not guaranteed to get linearizable reads IF your query returns more than one document so you can indeed get stale reads in that situation (and your cluster happens to be partitioned at the time the query hits the old primary).

Note that your query syntax for the find() method means that you're requesting in documents that include an A property with a value equal to 1 (as a double) [and a B property of value 1 in query #3), while your ensureIndex({A:1}) command specifies that the index should be built on the A attribute. I hope you are aware of that because your queries are otherwise a bit confusing.


Table view created with triggers, someone please explain why? I'm part of a project that inherited a piece of software that has a unique MS SQL database design that I'm trying to understand why they designed it the way they did.

Basically for every table, for example table tab_user, there is a duplicate table that is called tab_user_view.  The tab_user_view is populated with triggers placed on the tab_user table to populate the tab_user_view. All of our pages that list data retrieve data from the _view tables. I kind of understand this is to avoid locking and increase performance, but why wouldn't they just use traditional views?  The original software was written in 2003 so is it possible some tech wasn't available that we now have? Thanks

UPDATE

-Yes, there is some data manipulation for the _view tables, but the same thing can be achieved with views, so why?

The reason is because the skills for evaluating competence in any discipline are the same skills for competence itself. They thought this design was a good idea because they weren't very skilled in database design, and thus were not skilled enough to recognize that it was a terrible idea.


Why do sys.columns and INFORMATION_SCHEMA.COLUMNS display different number of columns I am trying two methods to display the columns with a particular name:

INFORMATION_SCHEMA.COLUMNS

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='SUPPLIER_NAME';

SYS.COLUMNS

SELECT *
FROM SYS.COLUMNS
WHERE NAME='SUPPLIER_NAME'

Why do the queries display different output?

The difference between INFORMATION_SCHEMA.COLUMNS and sys.columns is the object types they cover. INFORMATION_SCHEMA.COLUMNS is restricted to tables and views.  You can take a look at the code behind it by doing this:

EXEC sp_helptext [INFORMATION_SCHEMA.COLUMNS]

If you look at the bottom in the where clause you will see

o.type IN ('U', 'V')

This is where it is restricted to the tables and views only.

If you then go look at the definition of sys.columns you will see that it includes columns for a number of other objects:

Table-valued assembly functions (FT)
Inline table-valued SQL functions (IF)
Internal tables (IT)
System tables (S)
Table-valued SQL functions (TF)

Run this and you will be able to see what the extra object is and what type it is.

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id),
    OBJECTPROPERTYEX(object_id, 'BASETYPE'), *
FROM sys.columns


Why does SQL Server table partitioning stop working? For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is partitioned by asset ID in order to store the real-time data. Data packets arrive approximately every 2 seconds for each asset. Each data packet can have from 300 to 12000 data points.

The table partitioning and sharded servers work very well. But every once in a while it seems that one of the servers seems to stop using the partitioning which results in deadlocks and timeouts as new data arrives when deleting old data and bulk loading the new data.

My question is why is this occurring, and is there a way to check to see when or why this is happening. I know that this is the issue since I can move the data to a staging table, drop the original table, recreate the original table with the same partition function and scheme and finally copy data back from the staging table to the new "original" table and the issue is resolved.

As requested the partition scheme.  The asset ID is what the function uses as stated above

CREATE PARTITION SCHEME [RMAPartitionScheme] AS PARTITION [RMAPartitionFunction]
TO ([HULL269_484_LymanMartin], [HULL275_541_ClarenceTriche],
    [HULL277_546_RussellAdams], [HULL278_550_CharlieComeaux],
    [HULL281_561_CInstaller], [HULL284_587_GrandIsle],
    [HULL290_621_ShipIsland], [HULL291_638_HornIsland],
    [HULL296_653_StimStarIV], [HULL293_654_CatIsland],
    [HULL292_655_SanibelIsland], [HULL297_678_DauphinIsland],
    [HULL137_679_StimStarBrasil], [HULL809_680_Robin],
    [HULL1304_690_FastTrack], [HULL174_691_CRuler],
    [HULL1328_692_FastTiger], [HULL240_695_CFighter],
    [HULL778_730_Kudu], [HULL064_738_FastCheetah],
    [HULL063_739_FastServer], [HULLT100_746_ITS100BSM],
    [HULLBT10_749_ITSBuckeye], [HULL315_779_Elrington],
    [HULL302_780_MarshIsland], [HULL321_781_Courageous],
    [HULL316_782_LaTouche], [HULL317_784_BainBridge],
    [HULL324_787_Contender], [HULL325_788_Champion],
    [HULL318_790_Ingot], [HULL326_792_Challenger],
    [HULLN01_797_SeawayMoxie], [HULLAS1_799_IslandCommander],
    [HULL301_800_DeerIsland], [HULL810_804_Lucy],
    [DEFAULT_FILE_GROUP])

And the function

CREATE PARTITION FUNCTION [RMAPartitionFunction](int)
AS RANGE LEFT
FOR VALUES
     (484, 541, 546, 550, 561, 587, 621, 638, 653, 654, 655, 678,
      679, 680, 690, 691, 692, 695, 730, 738, 739, 746, 749, 779,
      780, 781, 782, 784, 787, 788, 790, 792, 797, 799, 800, 804)

I imagine the slowness (and thus deadlocks, etc), and the perception that partitioning "stop working," are because your queries suddenly stop getting partition elimination.

There are lots of reasons that you can miss out on partition elimination:

data type mismatches and auto-parameterization (see Why Doesn’t Partition Elimination Work?)
not being specific enough with predicates when joining to other tables (see Implied Predicate and Partition Elimination)
limitations related to join types (see why no partition elimination)

  My question is why is this occurring, and is there a way to check to see when or why this is happening.

You should get actual execution plans for the specific slow queries, or maybe just one of them.  Get one when it's working great, and one when the problem is happening.  You should be able to see the difference (likely a difference in the number of partitions being read).  Feel free to post a question about it on the site.

Maybe stats get out of date and you get a hash join with the partitioned table on the probe side.  Or you suddenly get a lower cost for the queries and they're being trivial-plan'd / auto-param'd.

The whole workflow of "dropping and recreating everything makes it work" makes me very suspicious that statistics are involved in the root cause of the issue.

The specific answer to your "why" question will likely be in the execution plans.  Until then, these are just educated guesses, but hopefully they point you in the right direction =)


In a MySQL cursor, will it be possible to skip an iteration or row of the cursor? Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!.

The problem is that I have a cursor that is crossed, but believe declare a variable that while I run the courses I perform a validation, I would like that when the validation is TRUE, I can jump to the next "row" of the cursor, skipping this "record" so that the following cursor stages do not run.

I know that in the CURSOR I could generate a "sub-query" doing the validation directly, but I would like to be able to learn if it is possible to perform this "jump" of the cursor.

Here is a bit of my code.

DECLARE queryCriterionHasArticle CURSOR FOR
SELECT
  CCLA.CLEG_ID,
  CCLA.ALEG_ID,
  CCLA.CCL_ID,
  CCLA.CCLA_ID,
  CCLAC.CRCA_ID
FROM
  SER_LEGAL.CONFIGURACION_has_CUERPO_LEGAL_has_ARTICULO_CUMPLIMIENTO AS CCLAC
  JOIN SER_LEGAL.CONFIGURACION_has_CUERPO_LEGAL_has_ARTICULO AS CCLA ON CCLA.CCLA_ID = CCLAC.CCLA_ID
  AND CCLA.ENT_ID = companyId
  AND CCLA.CCLA_ESTADO = 1
  AND CCLA.ESA_ID = 1
  AND CCLA.CCLA_TIPO_CLASIFICACION = 1
WHERE
  CCLAC.CRC_ID = criterionId;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
  noMoreRowsOne = TRUE;
-- ####################################################################################
-- Se comienza a recorrer un cursor que tiene los articulos asociados al criterio
-- ####################################################################################
OPEN queryCriterionHasArticle;
queryCriterionHasArticle_LOOP : LOOP FETCH queryCriterionHasArticle INTO legalBodyId,
legalArticleId,
legalBodyConfigId,
legalArticleConfigId,
criterionConfigId;
IF noMoreRowsOne THEN CLOSE queryCriterionHasArticle;
LEAVE queryCriterionHasArticle_LOOP;
END IF;
-- ####################################################################################
-- 11/06/2019
-- Se procede a agregar una validacion donde no se conside aquellos articulos que tengan
-- una evaluacion en SER_LEGAL.EVALUACION_has_ARTICULO WHERE EHA_TIPO = 1 AND EHA_ORIGEN = 2
-- debido a que estos articulos han sido evaludos con la integracion SISQUIM/ALMACENAMIENTO
-- estos articulos no se pueden modificar desde pre-evaluacion
-- ####################################################################################
SET
  validationIntegrationSisquim = (
    SELECT
      IF(
        COUNT(0) = 1,
        TRUE,
        FALSE
      )
    FROM
      SER_LEGAL.EVALUACION_has_ARTICULO
    WHERE
      CCL_ID = legalBodyConfigId
      AND CCLA_ID = legalArticleConfigId
      AND EHA_TIPO = 1
      AND EHA_ORIGEN = 2
  );
IF (
  validationIntegrationSisquim IS TRUE
) THEN -- ¿¿¿¿¿LEAVE queryCriterionHasArticle_LOOP;?????
END IF;
END LOOP;

I appreciate your help and guides.

Cursors can usually be turned into queries.  Programming SQL with cursors in avoiding the power of SQL.

But first, a lesson on ordering.  A "table" is unordered.  Doing a SELECT gives you no guarantee of what the rows will be delivered in -- unless you have an ORDER BY clause.

Is this what you are trying to do?  "Fetch some rows from a combination of tables, but stop when some condition is met."?

If, instead, you need to "Fetch all the rows from a combination of tables, except the rows that meet some condition", then a single query like this will suffice:

SELECT ...
    FROM a JOIN b ...
    WHERE NOT EXISTS ( SELECT 1 FROM SER_LEGAL.EVALUACION_has_ARTICULO WHERE ... )

No cursor needed.

If you really need to stop at the "first" occurrence of such, then provide the ORDER BY so we can discuss further.


SQL Server Edition for MS 70-463 I just started the MS 70-463 Implementation of warehouse, on the video course the tutor is directing towards SQL Server 2012 Evaluation Edition, but I have already have a SQL Server 2012 Express.

Can someone tell me if that is enough for the course I want to follow please? Is there functionality that I won't be able to perform for the 70-463 course?

No, you will need an Enterprise-equivalent (Evaluation/Developer) Edition to take the 70-463 course. It requires a broad range of Enterprise-only features, including column store indexes and partitioning, among many others.


Can't Select a View on Informix I'm using Informix IDS 11.50 Innovator-C edition running on Slackware Linux 12.0. I've been running a very small Database on it(less than 10000 records on the biggest table).

I'm trying to select a view from a web service, and every time there's a high load user load queries are being dropped with messages like:

  ERROR [HY000] [Informix .NET provider][Informix]Could not write to a temporary file

Or

  ERROR [HY000] [Informix .NET provider][Informix]Could not open or create a temporary file

The Web Service is using the .NET Informix Driver which relies on the Informix ODBC API.

Searching for those errors on the web or in the online documentation I found that it's something related to DBTEMP environment variable and the DBSPACETEMP configuration parameter.

Both are set. DBTEMP pointing to a directory with all permissions(rw), three dbspaces are listed on DBSPACETEMP. One regular dbspace and two flagged as temporary dbspace.

Storage is not a problem, the directory and the dbspaces have a lot of free space left.
The tables that the view Targets use R-tree indexes, and those are stored on a dedicated dbspace. One more thing I'm using a Geodetic Datablade on the database.

You say the directory has rw permission; does it also have x permission.  You need x permission to access the files in a directory (so you might set the permissions on the directory to ... gritted teeth ... 777, but you shouldn't.  Is the directory owned by user informix and does it belong to group informix?  Ideally, it should be owned by informix:informix, and the permissions should be 770 (or drwxrwx---).


Storing sports teams with multiple alternative names I am designing a MySQL database to store some football match results and statistics in.
The data for populating the DB will be scraped from multiple sources and the names of the teams will be slightly different on each website. (Some have old names used before the team was rebranded and changed its name completely; some will have abbreviations such as United -> Utd; some will have FC in the name, some won't; other sources will have the names in a language different than English.)
I'm wondering what would be the best way to store that data.
My thinking is to use the TeamID in the main table and have an extra table as a dictionary for translating multiple variants of the team name into the ID. Of course it would be quite a hassle to populate such a "dictionary".
Example
TeamID | TeamName
-----------------------------
1      | Manchester United
1      | Man Utd
1      | MUFC
2      | PSG
2      | Paris Saint-Germain

This is actually a big problem with South American clubs, where one club can have five completely different names in various sources and it's not as easy as shortening United to Utd or abbreviating the name.
So I'll have a script scrape the name of a team, find it in the "dictionary" table, then write all the statistics and info into the main table using just the TeamID. That should also save some memory.
Please let me know if that's a good solution and if you have some different approaches for me.

Wiki answer from comments on the question:
Yes, this is what I do at the day job with textbook names where different schools will call the same textbook something completely different. Hopefully you won't run into an issue where different team names can overlap, as that makes this sort of problem 10x harder. - matigo
You may want to add a source column and an active to help disambiguate abbreviations since, presumably, a single site won't use the same name for two different teams at the same time even if two different sites use the same name for different teams.

Speaking about storing (subj) I'd add a column for additional attributes. Like "primary", "official full", "official short" and so on.. Speaking about scraping script I'd recommend to create normalizing vocabulary, which sets the relation between "any form" of a token and its canonical name (Utd -> United). This may simplify in "some name" converting to canonical name (or in searching for the most close canonical names list). - Akina


How to pull high-level server information from Mysql MySQL workbench shows the following under server stats:

I know it can grab the Connection Name and the Port from the data that I'm using to connect, but where does it grab the Host, socket, Version, Compiled For, and Running Since from? Is there a query that can grab all that information?

This info comes from server system variables. Look in the Administration secion -> Status and System Variables:

Here I filtered for all variables that contain version. In plain SQL you would use this query: select @@version, select @@innodb_version etc.
Of course, some of the info is just what you specified when you defined the connection (like socket, ip, port, config file etc.).


Effectively modelling extended classes I have 3 different types of content for a website, 'Article', 'Video', 'Review'.  Each of those three types of content contain common fields and type specific fields.  For this, I have created the following 5 tables:

content - being the primary table containing the common fields across all content types
content_type - name, description, and type specific table name of available content types
article - the table that contains all the type specific fields for content of type 'Article'
video - the table that contains all the type specific fields for content of type 'Video'
review - the table that contains all the type specific fields for content of type 'Review'

I am a bit confused as to properly create the relationships between the tables. Realistically one 'content' row relates to one 'article' (type specific table) row.  To me this sounds like a one to one - non-identifying relationship.  Where I am think I am getting most confused, is the type can be one of many. So should they be One to Many or Many to Many, identifying / non-identifying?

I need to be able to search the content fields and then retrieve the corresponding type specific row based on the search.  I also need to be able to search the type specific fields, and retrieve the corresponding content row for search matches.

Also to note, I am using MySQL with Doctrine/ORM, however I am not looking for the SQL statements here I am just looking to better understand what the relationships should actually look like.

You have supertype (content) which may be one of different suptypes (article, video,review). One approach to model such relationship in database is :

CONTENT(content_id, content_type_id, PK(content_id), UNIQUE(content_type_id,content_id));

 ARTICLE(content_id, content_type_id,
  other attributes,
  PK(content_type_id,content_id),
  FK(content_type_id,content_id), CHECK (content_type_id = 'article_type_id'));

VIDEO(content_id, content_type_id,
  other attributes,
  PK(content_type_id,content_id),
  FK(content_type_id,content_id), CHECK (content_type_id = 'video_type_id'));

etc.

UNIQUE(content_type_id,content_id) seems redundant, but it's used to enforce storing of the sub-types in proper table, and only once. CHECK constraint restricts proper content type in each of detail tables. As far as I remember, Mysql still doesn't have unique CHECK constraints, but you can use enum to emulate them.


Adding coordinates to table (forcing 1:1 association without unique identifiers on either side) I have a table (around 60-80 million rows) in a postgresql database, that identifies census tracts that the observation belongs to (identifies an area of the US).  I want to place a random point within that polygon, and associate each observation (row) with a point.

I have written code that will create the random points, using arcGIS. (I wrote a script that will generate ~ 1kk points in <1 min)  What I cannot figure out is how to tie the coordinates back to the original data.

If I pipe the coordinates back into the sql database, then i can create a 3 column table of tract ID's, X and Y coordinates.  but if i join this table to the original data, then it'd essentially create a many to many join, which is not what i want.  I have 1:1 data, but I don't have unique identifiers on either side.

How would i force a 1:1 association?

PostgreSQL uses PostGIS for spatial stuff. You're in good hands, out of all the RDBMs GIS extensions PostGIS is the most powerful.

Use the Loader_Generate_Script. It'll return a table with a geometry column.

Then you just have to run

ALTER TABLE tiger.census_table ADD random_geom geometry(4326, POINT);
UPDATE tiger.census_table
  SET random_geom = ST_GeneratePoints(census_table.geom,1);

That's the easy way. It's a 5 min job.

You usually don't want a random point though. You usually want the centroid to be useful.


Query condtion for values not being the default value? Can I reference a columns default value directly in a query?

Something like this: SELECT column FROM table WHERE column <> column.DEFAULT_VALUE

Background: I'm writing a application test, and I want to easily find out which columns have non-default values. I could parse out the default value from the schema definition which is stored on disc, or figure out what the default is in various more or less convoluted ways, but I'm hoping there's a syntax for asking the server directly.

Yes, there is, in MySQL. You can use the DEFAULT() function:
SELECT column
FROM table
WHERE NOT (column <=> DEFAULT(column)) ;

Instead of <>, the NOT (.. <=> ..) construct that uses the null-safe equal operator <=> is needed for nullable columns, to have values returned when the default is NULL and null values returned when the default is not null.
Tested in SQLFiddle


what is more important on a query execution plan, cardinality or selectivity? The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. However, other posts that explain the difference between cardinality and selectivity point that selectivity is more important.

Example:

  select max(price) from tickets where country = ‘CANADA’;

  Let us say we have a table: tickets with 180 rows. Based on the above
  example, there are 10 rows in the table for country = ‘CANADA’. The
  query returns only one row, the max(price). selectivity = ?
  cardinality = ?

  selectivity = number of rows accessed/total number of rows = 10/180 =
  0.05 (5% of the rows were accessed) cardinality = number of rows accessed = 10

My opinion is that selectivity is only found once we have the cardinality, therefore cardinality remains the more important thing to find the best query execution plan. What do you think? Could you please answer with a detail explanation about your understanding? Thanks

Since one is the function of the other, they are equally important.

Implementation details differ between various DBMSes, but in general the process that collects table and index statistics calculates cardinality for key columns and stores these values wherever it keeps statistic information, usually some catalog tables.

For example, in addition to the full table cardinality (180 in your example for table tickets) it may gather and store cardinalities (i.e. the number of distinct values) for some columns. This allows the optimizer to estimate selectivity for those columns, dividing the column cardinality by the table cardinality.

Many DBMSes also maintain histograms for key columns, that is, cardinalities of specific values in these columns. In your example the engine might store among other statistics the fact that there are 10 rows with 'Canada' in the country column, 18 rows with 'USA', 8 rows with 'Brazil' etc.

Similarly, statistics for an index would contain not only the total number of (potentially) non-unique key values, but also the number of distinct key values for the entire key and also often for each "sub-key"*, allowing the optimizer to compute selectivity when needed.

* As an example, for a non-unique index containing columns A, B, and C the engine would store the full index cardinality (which is equal to the table cardinality), and the distinct counts of combinations (A, B, C), (A, B), and (A).


How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8 I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump.

Later, I realized that I needed to downgrade back to MySQL 5.7 (after not even having realized that I'd upgraded).

I've uninstalled 8 and installed 5.7, but I've been having trouble importing my data from the .sql file from the dump.

First, I got the error ERROR 1273 (HY000) at line 1753: Unknown collation: 'utf8mb4_0900_ai_ci', which I solved by using Notepad++ to replace:

utf8mb4_0900_ai_ci with utf8_unicode_ci
utf8mb4 with utf8
utf8_unicode_520_ci with utf8_unicode_ci

Then I also deleted the section of the .sql file related to the 'mysql' database.

Now the command mysql < alldb.sql runs without error, but when I go look at my data, a lot of it is missing.

P.S. I feel optimistic that someone can help me, but it's a bit worrying that MySQL 8 docs say:

  Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.

You are suppose to remove utf8mb4 character set & collation, and add relevant character set in your backup file & then try to restore.

Like :

:%s/SET character_set_client  = utf8mb4/SET character_set_client  = latin1/
:%s/SET character_set_results = utf8mb4/SET character_set_results = latin1/
:%s/SET collation_connection  = utf8mb4_general_ci/SET collation_connection  = latin1_swedish_ci/

:wq

I think still you will face issue for mysql schema restoration as its architecture is changed in MySQL 8.0 than MySQL 5.7


mysql auto_increment I deleted 11 rows and I want the auto increment id values to reset to 6,7,8,9 … How can I achieve that without using the update statement each row one at a time. I want to find out if there is a way to avoid this sort of issue in the beginning when creating the database.

You cannot prevent this issue of reordering once you begin inserting data.

Notwithstanding, if you want to reorder the IDs sequentially, you have no choice but to rebuild the table.

Here is the SQL to do that

ALTER TABLE students RENAME students_outoforder;
CREATE TABLE students LIKE students_outoforder;
TRUNCATE TABLE students;
INSERT INTO students (stud_name,class,subject)
SELECT stud_name,class,subject FROM students_outoforder ORDER BY id;

After running this, run this

SELECT * FROM students;
SHOW CREATE TABLE students\G

You will see the IDs reordered 1..8 and the AUTO_INCREMENT of the table at 9

Then, you can get rid of the old table

DROP TABLE students_outoforder;

Give it a Try !!!

CAVEAT: Do not try this with the students table has foreign key constraints in it.


How can I change the sequence_name parameter after renaming a sequence (PostgreSQL) I have renamed a sequence with the following command:

ALTER SEQUENCE TableIdSeq RENAME TO NewTableIdSeq;

however, when I issue the \d NewTableIdSeq command, I get the following output:

       Sequence "dev.newtableidseq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | tableidseq            <--------------- HASN'T CHANGED!!
 last_value    | bigint  | 3
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 30
 is_cycled     | boolean | f
 is_called     | boolean | t

As you can see, the sequence_name property of the sequence is still set to the old name. How can I change the name here as well?

Don’t worry about it. The sequence name stored inside the sequence is ignored. This field is gone in PostgreSQL 10.


How to check if Oracle DB process is running in Unix and where Oracle DB is installed I am new to Unix and trying to find out a command that checks if Oracle DB is installed in my Unix box. I came across commands ps -ef|grep smon and ps -ef|grep pmon in net. But which is the correct command that I can use.

Also how to find out the installation directory where the Oracle DB is installed if ORACLE_HOME variable is not set?

I am using bash shell.

This depends on the flavor of *nix where you run on and whether the installation was customized or not. If something of Oracle was installed on your system, the directory /etc/oracle or /var/opt/oracle normally would have to exist. Normally they contain a file /etc/oraInst.loc or /var/opt/oracle/oraInst.log that points to the inventory that contains the central registry of the installations done on the host, if it has been done in a standard way.

If you found the oraInstloc you know at least that some installation has been taken place. The inventory contains the details about what was installed and where. Normally this directory is protected.

If the /etc/oratab or /var/opt/oracle/oratab file has been maintained, it contains a list of all instances running on your system, including the software locations. This is the file that is used by the oraenv utility that sets the minimal environment variables you need to be able to use that software for the specified ORACLE_SID.

The oratab, if maintained shows all defined ORACLE_SID's, also when they are not running.

But again, asking your dba might save a lot of time.


Are nonclustered indexes used when joining tables? I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them.

But I've read recommendations to noncluster index all FKs, I believe that's for helping joining.

Is there then some type of join that uses nonclustered indexes?

Have a look at the query execution plan, that should tell you if it is using an index.

I don't know why a sort join couldn't use an index, keys can be read from the index and then sorted if required, which saves scanning the table.


DB Design - Assigning users to multiple sites with different roles I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules:

A user can manage multiple sites
A user can only have one role per site
A site can have multiple users

I already have two designs that I came up with, I just need validation which one would work better against my business rules.

First is using a ternary table site_user_role that consists of a relationship between site, user and role table. But this already breaks the rule of a user having only one role per site because those 3 primary keys would be unique and I can create a combination of two roles for a user within 1 site. Any complications with this design?

The second idea is having two binary tables. The site_user and site_user_role table. This arrangement I am quite sure I will not duplicate a role for a user on a site. But Is it weird to have a primary key aside from the Foreign keys on the site_user binary table? Any complications with this design?

For the simplicity of maintaining it in the future (especially if it's not you) I would go with your first design. It contains a table with information on the user (login, name,etc), a table for the site (location, category) and then a table linking a person to a site (Steve is the account admin at Site 3) The second design would make things more complicated than they really needed to be


Login Stuck in Database? I'm working on a project to add some permission-based roles to our database servers to restrict users from modifying data if they don't need to be doing so. I've got four groups for each client; Users, PreProd Users, Data Admins, and PreProd Data Admins. I've got things set up fine for the users, but I'm running into some trouble with the Admin side.

I'm very new to SQL, so a lot of my testing is trial and error. I think I may have botched something quite badly though. Two of my Logins, demo_Data_PP_Admin, and demo_Data_Admin, seem to be stuck. When I paste my script in to CREATE those logins, I am told that the logins already exist. Visually, I can confirm they do not exist. Attempts to DROP LOGIN [demo_Data_PP_Admin] or DROP LOGIN [demo_Data_Admin] are met with

Msg 15151, Level 16, State 1, Line 1
Cannot drop the login 'dh\demo_Data_Admin', because it does not exist or
you do not have permission.

I know this is a super confusing issue, and I'm trying to sort it all out myself with my relatively beginner knowledge here.

TL;DR, the command CREATE LOGIN [dh\demo_Data_Admin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO has a red underline beneath [dh\demo_Data_Admin], claiming the login already exists, but I can visually confirm that it does not.

I can try my best to answer any questions that people may have. Again, I apologize for the confusing nature and potentially confusing explanation of events. An answer to this question will likely lead to another question as well.

IntelliSense gets confused occasionally.

Try refreshing the IntelliSense cache by pressing CTRL-SHIFT-R or hit "Edit -> IntelliSense -> Refresh Local Cache" from the menu-bar to see if the underline goes away.

Alternately, try running CREATE LOGIN [dh\demo_Data_Admin] FROM WINDOWS WITH DEFAULT_DATABASE=[master] - if the login already exists, this will return an error.  If no error is thrown, the login didn't already exist, and has now been created.

You can check if the login exists with this query:

SELECT *
FROM sys.server_principals sp
WHERE sp.name = 'dh\demo_Data_Admin';

If the login exists, a row will be returned with details about the login.


What are the bad design points in the following (simple) Entity-Relationship Diagram? The objective is to design an Entity-Relationship Diagram for a company that has addresses, telephones, emails and representants. Here's the list of the constraints:

a company has one or more telephones
a company has one or more e-mails
a company has one or more address
an address has one or more representants
a representant has one or more telephones
a representant has one or more e-mails
if a representant is removed, then it must be saved in a list "last
representants".

Other constraints are (hopefully) represented in the diagram, using the following notation:

circle: attribute
double-lined rectangle: weak entities
double-line: total participation
line ending with an arrow: cardinality 1:N (arrow points to the "1")

Entity Relationship Diagram

I designed that diagram. I'm sure there are some bad design points, because I'm a beginner, and I would like to hear your opinions, suggestions and criticisms.
More information
(based on the answer by miracle173)
English is not my native language, I should've told that in my question, so please excuse my poor word picking. By representant I mean a person that will speak on behalf of the company. With telephones I mean one or more phone numbers. With emails I mean one or more email addresses.
Here is why I chose telephone and email as being weak entities: when a company is removed, I want the list of telephones numbers and the list of emails addresses associated with that company to be also removed. Did I achieve that goal with the use of weak entity?
An address of a company can have more than one representant, but the representant can only work for one company at a time (hence the restriction in my diagram). I agree, some singular and some plural is bad practice. I will fix that.
Changing the relation to more precise verb, like "represented by" (instead of "an address has representants"): from top to bottom, one can read "An address is represented by representants"; from bottom to top, it gives incorrect reading ("A representant is represented by a company"), when it should be "A representant represents a company". So I think it makes difference the reading direction.
What is wanted with "it must be saved" is: to have a short list, like a small history, of the last representants that worked for the company. Example: If John - which is a representant of Miracle's Corporation - suddenly is fired from the company, he should be removed from the Representants list (along with his phone numbers and email addresses), but it is desired to keep him in that short history list after his deletion from Representants.

I am not sure if this is the right place to pose such questions. But here some remarks and questions anyway. They are too long for a comment.

I don't understand the word representant. I did not  find it at http://dict.leo.org/. Dou you mean representative?
With telephones you mean phone numbers and with emails you mean email adresses?
Why is telephone a weak entity? What is its identifying entity? For me it is not a weak entity. A phone  extension may be a weak entity. The same holds for email and address.
can a representant have more then one address? This is not excluded by the text but it is excluded by the ER diagtram.
Why do you name some entities as singular and some as plural? I think a consisten notation will be more clear.
Why do one need a reading direction? What is the difference if one reads the diagram from bottom to top? I see no difference.
"if a representant is removed it must be saved in a list" That does not describe any relationships between a removed representant and the remaining entities. "it must be saved" is a term of computer programming and not of the business domain. It is not clear to me what you want but using an attribute to a relation may be not the appropriate solution. From your drawing I conclude a telephone is assigned to a representant. If the representant is removed the removed representant has a telephone too? If yes then this telephone cannot be used by then new representant for this address because a telephone has only one representant assigned.


configure: error: SQLite3 library not found I'm getting the below configure error while doing the install the Zabbix reporting tool on Linux.
i have tried both Versions(zabbix-2.0.9,zabbix-1.8.17), but getting same issue.

Log:

checking for macro __VA_ARGS__... yes
checking return type of signal handlers... void
checking for getloadavg... yes
checking for hstrerror... yes
checking for getenv... yes
checking for putenv... yes
checking for sigqueue... yes
checking for /proc filesystem... yes
checking for file /proc/stat... yes
checking for file /proc/cpuinfo... yes
checking for file /proc/0/psinfo... no
checking for file /proc/loadavg... yes
checking for file /proc/net/dev... yes
checking for long long format... no
checking for -rdynamic linking option... yes
checking for libperfstat 5.2.0.40 fileset... no
checking for libperfstat 5.3.0.60 fileset... no
checking for architecture... linux (linux-gnu)
checking for the linux kernel version... unknown family (3.0.76-0.11-default)
checking if Oracle support is enabled... no
checking for SQLite3 library >= 3.0.0... no
configure: error: SQLite3 library not found

Please help me how to fix this issue?

Thanks,

I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried the following command and it worked. May be it'll help someone else too.
sudo apt-get install libsqlite3-dev


How to manage many-to-many relationships when there is also a hierarchy among the many? I have these entities – employee, task, phase, project – and these relationships:

project 1:m phase
phase 1:m task
employee m:m task, phase, project

The first two relationships form a simple part-of hierarchy.

The semantics of the third relationship are slightly complex.
An employee, in any given week, can be assigned to any or all of the following:

zero or more tasks
zero or more phases
zero or more projects

(Being assigned to nothing at all, though, is not recorded!)
In other words, the level of detail of an employee assignment can vary.
Semantically, at least, it is impossible to be assigned to a "part" entity without also being assigned to the greater entity. However, being assigned to a "whole" does not imply assignment to any of the "parts".

My current table design is to have a table for each entity and a junction table for the m:m relationship:

employee   [emp_id,   etc]
task       [task_id,  phase_id, etc]
phase      [phase_id, proj_id,  etc]
project    [proj_id,  etc]
assignment [emp_id,   task_id,  phase_id, proj_id, week]

However, given that a task always belongs to a phase and a phase always belongs to a project, how should the foreign keys in assignment be valued? Do I choose one of these?

Only the id corresponding to the lowest known entity in the project-phase-task hierarchy gets a value, the higher entity ids get nulls.
Problem: Those nulls don’t seem right.
All the ids the corresponding to the lowest known entity and up get values.
Problem: The non-null ids seem redundant (non-normalized).

Or should assignment be like this

[emp_id, assign_id, level, week]

where level indicates to which table (task/phase/project) the generic assign_id refers?
Problem: Theoretically, assign_id might need to be of three different types.
(Though, actually, they're all long integers, in my situation.)

Is this a “design pattern”? That is, is it an example of semantics that the relational DB model doesn’t readily handle?
Does it have a name?
What is the best practice for handling the situation?

I'm using MS Access but I'm interested to know how solutions might vary across different relational DBMSs.

Martin,

If I am understanding correctly, the employee can be associated with one or more tasks, phases, and projects.  This implies a many to many relationship between an employee and a task, phase, or project.  If this is the case, throw out the idea of the assignment table completely.  Instead, create three different junction tables that can be used to establish the many to many relationships:  EmployeeTask, EmployeePhase, and EmployeeProject.

The Task, Phase, and Project tables will allow for relationships to be established as you have defined in your post.  The three junction tables will enable an employee to be included in the hierarchy at any of the three levels.

Your SELECT statements will require LEFT OUTER JOINS with the junction tables to pull back the appropriate employee information, which is fine.  Adding the junction tables will provide a much more normalized database design.


Can a poor database layout cause performance issues for simple queries? I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too?

For example, an insert into table that has about 100 000 rows takes 9 seconds to execute. Could it be caused by a bad database layout, or should I search for performance bottlenecks elsewhere?

I have 3 tables:

aiki_users
ocal_files (connected with both id and username not foreign keys/refeerences) I have tags in field (sparated by coma instead of separateded table)
ocal_favs with favorites (suppose to connected with ocal_files and aiki_users) but no real connection (foreign keys)

There are slow queries that use LIKE in this way: upload_tags not like '%pd_issue%'

or

select *
from ocal_files
where upload_tags LIKE '%summer2010%'

These take 5-7 seconds to run (on heavy load). On heavy load (right now) we have 200 active users (shown by Google analytics).

Some of the most frequent queries that use a lot of LIKE and RLIKE searches were replaced by Sphinx; those pages run fast.

Yes, slow queries will cause other queries to be slow.

Server has only finite amount of IO it can do, if it busy doing heavy LIKE operation, like in your case, it can NOT use index it will make other queries slow.


Firebird, select gives token unknown I have a Firebird database with a table foo that have a field called WHEN. Is there a way to perform a query using the field name in SELECT?

This works:

SELECT * FROM foo;

This not:

SELECT WHEN FROM foo;
SQL error code = -104
Token unknown - line 1, column 18
WHEN

Any hints?

The token WHEN is a reserved word, and can't be used as a column name without explicitly quoting it. Either rename the column, or quote it:

select "WHEN" from foo

Be aware that quoting names makes them case sensitive. Unquoted object names in Firebird are case insensitive, but stored in uppercase.


MySQL INNODB SELECT query hanging on copying to tmp table I have a query that is hanging (seemingly) randomly on a specific query.  Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website and when I check the processlist it gets stuck on copying to tmp table.

I believe the hard drive is just not keeping up, which is causing the hang on the copying to tmp table.

I don't believe I can optimize the query any more efficiently.  I was wondering if I could set up my server's second hard drive as the temp table storage so that way I'm spreading out some of the load being put on the server.

The query:
SELECT DISTINCT
    activity_seismo_info.ID,
    activity_seismo_info.CREATED_AT,
    activity_seismo_info.UPDATED_AT,
    ....
    activity_seismo_info.ASI_EXTRA_5,
    seismo.ID,
    ....
    seismo.SEISMO_NOTES

FROM `activity_seismo_info`
    JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID)
    JOIN location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
    LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID)

WHERE location.ID='193'

ORDER BY activity_info.ACTIVITY_EVENT_TIME DESC LIMIT 30

I am on a P4 with 2GB RAM and two hard drives (the second hard drive is not used)

PHP5.2

MySQL 5.0.51a-3ubuntu5.4

symfony 1.2 application with Propel

UPDATE

thanks to @DTest's suggestion, I found my query was returning all the rows in the table before running a limit on the result.  But for future reference, is there a way to setup MySQL to use a second hard drive as the 'scratch' drive for temp tables?

In my.cnf use this option

tmpdir=/tmp (this is the default anyway, just choose another folder)

Restart of mysql required

BTW since the location.ID is '193' why even involve the location table ???

Try the following :

SELECT DISTINCT
    activity_seismo_info.ID,
    activity_seismo_info.CREATED_AT,
    activity_seismo_info.UPDATED_AT,
    #....
    activity_seismo_info.ASI_EXTRA_5,
    seismo.ID,
    #....
    seismo.SEISMO_NOTES

FROM `activity_seismo_info`
    JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID)
    JOIN (SELECT ID from location WHERE ID='193') location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
    LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID)
;


Social (Classmate like) database schema appreciation This is a database schema I propose for a networking website like the Classmates.com web site.

Do you think the database has all the tables needed for such a project? What would you rather do?

The database has 3 parts:

the payment part is surrounded by the black color box
the social part by the blue color box
the classmates part by the red color box

Any advice or critics is welcome. thanks.

I have been inspired by database models presented on the databaseanswers.org web site.

1.   payment (black box)

1.1)  No currency information?
In the Payments table, although you store an amount and date, you store no currency.  Perhaps you plan on only charging in one currency, but I feel that method will only last so long if this is intended to scale.

1.2)  What is this second payments table?
What is the second payments table for?  The one below and slightly left of the actual payments table?
It seems to have only an id column, which is the only column in this diagram to specify a data type!?  I'm guessing this is some diagram artefact.

1.3)  You'll need more than just the name to take payments!
The payment_methods table currently doesn't store the type (i.e. credit card, paypal, BitCoin, whatever) of the method.  There also seems to be no linked table of payment_types for this either.

You will need to store stuff like credit card numbers, etc somewhere  (hopefully not in this database!).  You will need some sort of linking information for those stored payment methods to be kept in the payment_methods table.

2.   social (blue box)

2.1)  Who exactly is me?
I'm assuming the Me table is some sort of façade or profile type of table.  Perhaps allowing a given User to shield themselves from the embarrassment of letting their collage/university friends see the Music they were into in high school? :P

I do not believe it makes sense to double store the first, middle and last names of the user.  It would be much better to go with a solution like that which @beeks suggested in the comments above.

2.2)  My_Profile_Details... field?
I cannot figure out why this is a single field.  I assume it's some sort of memo type, for storing a lot of text (i.e. a CV of sorts).  I think this needs MUCH more decomposition.  At a minimum, into Sections of some sort.

There also seems to be nothing in a Proflie that allows a user to select what Schools/Collages/Universities/Enterprises they studied/worked at, etc?

3.   classmates (red box)

3.1)  Internationalisation?
This part of your design is missing information regarding country. Even if you're only looking for schools/colleges in a particular county, employers (which is what I assume Enterprise is for) may be in any country.

Also, If a classmate starts their own enterprise, do they have the same relationship to that enterprise as someone who works for it?

3.2)  Institution Names
You have assumed Universities (and I suppose high schools, primary schools and collages too) have only one name, and that name is in English (or whatever language you're intending to store).
It is quite common for universities to have different names in different languages.  Institution names can also change over time (renamed, etc).

3.3) Institution Decomposition
Not all Universities divide themselves into Faculties, some are simply a collection of Schools (think Oxford, etc).  These can also change name over time.  Also, both faculties (schools), universities (and probably collages, primary and secondary schools too) can merge (i.e. get taken over/bought out) over time.

3.4) Institution Location Multiplicity
Your design seems to assume a University can only be in one town.  Many Universities have different campuses in different towns.  Some even have campuses across international borders.


Copy-only as the routine backup We are in planning to upgrade from SQL Server 2008 R2 to SQL Server 2014, with the intention of using Availability Groups.

One of the secondaries will be used to do backups.

I have tested and found that I can do point in time restores as long as I have the copy-only full, and the chain of log backups that span the copy-only up to the time we want to restore to.

We do NOT use differentials, and have no plans to.

I cannot see any downside to this approach other than perhaps not being able to shrink the data file without a "normal" backup. We do not normally shrink our databases unless we do something to blow them up, and by then we are in intervention mode anyway.

So my question is, will I regret going this route?

It sounds like you've done your homework to a large degree.  Clearly you're aware of the log chain implications for COPY_ONLY backups.  My advice to you would be to ensure you have a clearly though-out and tested restore plan.

Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan.

If you can do standard, i.e. not COPY_ONLY backups, then do them.  I might consider doing standard FULL backups on the primary node every so often just because you can never have too many backups.


ER Diagram translation to tables I am trying to translate the ISA relationship in this ER diagram to database tables, and I am not sure how to enforce the total and disjoint participation constraint of Laptop and Desktop in Computer. (There are other attributes, but they are not shown here for simplicity.) Here are some options I've explored:

a) Have just one table for Computer, with the attributes of the subtypes in the parent entity. This is not an option, since some functional dependencies in the next part of the question forbid this. There isn't a "type" flag attribute in the diagram either.

b) Two tables, one each for Laptop and Desktop, and pull the attributes from Computer into the separate tables. I can't do this, since it would destroy the relationships that Computer has with other entities.

c) One table each for Computer, Desktop, and Laptop, with foreign key constraints on cid. I don't like this option, since it does not enforce the total and disjoint participation constraint.

This is a practice exercise, but there are no solutions. I would greatly appreciate any help!

                           /  \
        ------------     /      \    ---------
 (cid)--| Computer |====/Supplies\---| Store |
        -----||-----     \     /     ---------
             ||   \        \ /
             ||    \
             ||     \     / \        ---------------
            /d \     \--/Fixes\------| TechSupport |
(battery)   -----        \   /       ---------------
  \        /    \         \ /
--------- /      \ ----------
| Laptop |        | Desktop |--(monitor)
----------        -----------

Enforcing option (c), using a type attribute:

CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;

CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

and using deferred foreign key constraints on nullable columns:

CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL                                    -- these 2 columns
, desktopID INT NULL                                   -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL   -- but only one of them
       OR desktopID IS NOT NULL AND laptopID IS NULL   -- is NULL
        )
, CHECK (laptopID = computerID)                        -- and the non-NULL one is
, CHECK (desktopID = computerID)                       -- equal to computerID
) ;

CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
    REFERENCES Computer (computerID)
) ;

CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
    REFERENCES Computer (computerID)
) ;

ALTER TABLE Computer
  ADD CONSTRAINT Laptop_Computer_FK
  FOREIGN KEY (laptopID)
    REFERENCES Laptop (laptopID)
    DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK
  FOREIGN KEY (desktopID)
    REFERENCES Desktop (desktopID)
    DEFERRABLE INITIALLY DEFERRED ;

Tested in Postgres, at SQL-Fiddle


Trying to create a Data Model It may sound stupid but it's my first experience ever with databases.

It's a simple database for a car accessories store. I was hoping when making a sale on a certain item it effects the quantity of this item, not sure if this is possible with this design. Also as shown in the image an item could be for one car or more so I thought it should be many to many.

You need to read a business data modeling book such as those by Silverston, Hay, or Fowler
You are confusing a Product, which is the specification of an Asset (such as a vehicle). A product does not have a quantity. For cars, the Product has a model number (Mazda 3) and the Asset has the serial number (VIN xxx).
You should also read up on Table Inheritance (Fowler).


MySQL Workbench ERROR 1064 I have a problem with my database.  I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error.  I searched for the cause of the error for several days.  Since I cannot find the problem,  I've decided to share it with you.

Message log:

Executing SQL script in server

ERROR: Error 1064: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ') NULL DEFAULT NULL , n_category VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY K' at line 3

CREATE TABLE IF NOT EXISTS `telo2p`.`d_category`
(
  `idd_category` INT(11) NOT NULL AUTO_INCREMENT,
  `f_sells` DOUBLE(11) NULL DEFAULT NULL,
  `n_category` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idd_category`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

SQL script execution finished: statements: 6 succeeded, 1 failed

Any advice about what I am doing wrong here?

Remove the (11) from the DOUBLE(11):

`f_sells` DOUBLE  NULL DEFAULT NULL ,

Approximate numeric dataypes (Float and Double) need no parameter. There is also a non-standard syntax with 2 parameters. From the MySQL docs, Floating-Point Types (Approximate Value):

  MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means that values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.


After adding indexes to an AWS RDS MySQL DB getting huge/crippling spikes in IOPS So after looking at the production database, I realised that several queries were running utilizing no indexes at all, and as such, were row-scanning close to 4 million rows. :/

I added the required indexes to both the production db instance and the staging read-replica instance.

The result of adding the indexes on the staging environment has been huge:

You can clearly see when the indexes were added, leading to a massive reduction in IOPS and as-such keeping the burst balance at it's maximum level.

However, on our production instance, all is not quite so perfect:

Again, in general the amount of IOPS has gone down dramatically. However, we are now seeing huge spikes in both read and write IOPS every day or so (no particular schedule) which then completely exhausts our burst balance and cripples the db instance until whatever it is doing is finished, at which point the burst balance goes back up again.

Does anyone have any idea's as to what might cause this sort of behaviour, especially in regards to adding indexes, as these spikes were not occuring before we added them.

Few things you can look forward to this issue.

IOPS limit is based on your volume size if you are using General Purpose SSD.
And IOPS are your reserved IOPS.

In staging its fine. Check both volumes are in the same size.
Sometimes you are getting a sudden spike, in this case, enable Enhanced monitoring to track every 1sec of MySQL activities.
Enable slow query log and set the long-running query time to 0. So it'll capture all the queries.
Compare the enhanced monitoring IOPS graph with the slow query log table, then you can get an idea for the bad queries.


Job with a cursor is abnormally slow I have a query that takes more than 9 hours to complete.

Looking at the execution plan, I find this high-cost operator (query plan link):

How can I improve this job?

Rather than focus on the scan, try to replace the cursor with a set-based approach.

It's a bit hard to get into the details without your database schema (because I don't get any intellisense, and don't know anything about the constraints, etc), but I will outline the general approach below.  You would want to test this and make sure it's logically equivalent to your current process.

1 - Get your cursor dataset into temp table:

SELECT
     listCt.id_traitement
    ,listCt.[N° Contrat]
    ,listct.[date_debut_contrat] as dt_effet_ct
    ,listAssu.[N° Assuré]
    ,CASE
        WHEN listCt.is_derniere_annee = 1 AND listCt.[date_fin_contrat] <= GETDATE() THEN listCt.[date_fin_contrat]
        WHEN listCt.Annee < YEAR(GETDATE()) THEN CAST('31/12/' + CAST(listCt.Annee AS VARCHAR) AS DATETIME)
        WHEN listCt.Annee = YEAR(GETDATE()) AND listCt.[date_debut_contrat] > GETDATE() THEN listCt.[date_debut_contrat]
        ELSE GETDATE()
    END AS date_lnk
INTO #listAssu
FROM [app_db_commercial].dbo.tmp_listing_assure AS listAssu
    INNER JOIN [app_db_commercial].[dbo].[listing_contrat_valeur] AS listCt
        ON listAssu.[id_traitement] = listCt.[id_traitement];

2 - Get your nb_adulte counts, grouped by the columns you're using later to update (id_traitement and [N° Assuré]) into a different temp table:

SELECT
    COUNT(ac.[N° Assuré]) as nb_adulte,
    la.id_traitement,
    la.[N° Assuré]
INTO #nb_adulte_counts
FROM PRODBDD01.[app_db_mirroir].dbo.[Assurés - Contrats] AS ac
    INNER JOIN PRODBDD01.[app_db_mirroir].dbo.Assurés_Liens AS al
        ON ac.NoAssureParent = al.[N° Assuré] AND ac.[N° Assuré] = al.No_Assuré_Lien
    INNER JOIN #listAssu la
        ON ac.[N° Contrat] = la.[N° Contrat]
        AND ac.NoAssureParent = la.[N° Assuré]
WHERE
    ac.NoAssureParent <> ac.[N° Assuré]
    AND ac.is_assure_principal = 0
    AND al.Type_Lien_Assuré > 0 AND al.Type_Lien_Assuré < 10 AND al.Type_Lien_Assuré <> 2
    AND la.date_lnk BETWEEN ac.[Date d'entrée] AND ISNULL([Dt_Fin], CAST('99991231' AS DATETIME))
GROUP BY
    la.id_traitement,
    la.[N° Assuré];

3 - Do the same thing with your nb_enfant counts:

SELECT
    COUNT(ac.[N° Assuré]) as nb_enfant,
    la.id_traitement,
    la.[N° Assuré]
INTO #nb_enfant_counts
FROM PRODBDD01.[app_db_mirroir].dbo.[Assurés - Contrats] AS ac
    INNER JOIN PRODBDD01.[app_db_mirroir].dbo.Assurés_Liens AS al
        ON ac.NoAssureParent = al.[N° Assuré] AND ac.[N° Assuré] = al.No_Assuré_Lien
    INNER JOIN #listAssu la
        ON ac.[N° Contrat] = la.[N° Contrat]
        AND ac.NoAssureParent = la.[N° Assuré]
WHERE
    ac.NoAssureParent <> ac.[N° Assuré]
    AND ac.is_assure_principal = 0
    AND al.Type_Lien_Assuré = 2
    AND la.date_lnk BETWEEN ac.[Date d'entrée] AND ISNULL([Dt_Fin], CAST('99991231' AS DATETIME))
GROUP BY
    la.id_traitement,
    la.[N° Assuré];

4 - Then you can run two set-based update statements:

UPDATE la
SET
     nb_benef_adulte = ac.nb_adulte
FROM [app_db_commercial].dbo.tmp_listing_assure la
    INNER JOIN #nb_adulte_counts ac
        ON la.[id_traitement] = ac.[id_traitement]
        AND la.[N° Assuré] = ac.[N° Assuré];

UPDATE la
SET
    nb_benef_enfant = ec.nb_enfant
FROM [app_db_commercial].dbo.tmp_listing_assure la
    INNER JOIN #nb_enfant_counts ec
        ON la.[id_traitement] = ec.[id_traitement]
        AND la.[N° Assuré] = ec.[N° Assuré];

I'm sure some of those steps could be combined, but this is just a quick and dirty example of how to get away from the cursor.  Despite the heavy tempdb workload here, I'd expect it to still run faster than loads of individual seeks across databases / linked servers.


Replication to subscriber with different name Is it possible in PostgreSQL to set up the following replication scenarios?

Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name primary_product TO server_B (slave) database name primary_product_analytics
Replication between 2 servers which consist of several databases in Server_A, need to be transferred into single database with multiple schemas (custom schema name) on Server_B.

I have successfully setup replication using WAL but I would like guidance on implementing the above scenarios if possible.

WAL-based replication is physical replication, i.e., it replicates all the changes made on the disk of a database cluster. In other words, all databases and their contents are exactly the same.

If you want replicate with different database names, you can use logical replication, which replicates changes made to specified tables.

However, logical replication cannot change the schema name. If you want to copy changes to a table in a different schema, you could do it manually with triggers and postgres_fdw.


Multi-Site clustering on Windows 2012 for SQL Server 2012 I am hoping someone can point me in the right direction here.  We are trying to do a proof of concept for a 3 node multi-site cluster on Windows Server 2012 R2 for our SQL Server environment.  There will be two different subnets, one in each datacenter.  I have come across many articles telling me how to set up the cluster but what I'm having trouble with is the IP address for the cluster.

Does anyone know how I can add the IP from the second datacenter into the cluster?  I have come across articles that mention I would have to add an OR dependency for the second IP but I am not sure how to make this IP even appear as an option to add.  Am I supposed to have the IP address added to the NIC?

I hope my questions aren't confusing and that I'm conveying them clearly.

The cluster will pickup all the different subnets you have configured on your NICs.

Once a server with NICs that have IP addresses of the 2nd subnet is included in the cluster you will be asked to add an IP for each subnet for each role as you add them to the cluster.


logic/design for storing achievments and progress for users I have a game with a databse of users, I now want to implement unlockable achievments in my game. How would I best go about saving this data for each user? I am still fairly new to using a database.

Relevant info:

Each user in the users-table has an ID
There are 30+ achievments
Many achievments are longer processes, that require the ability to save the progress of that achievment for users, ex: Win 8 rounds in a row.
When the game is started it should load all finished/unfinished achievments as well as progress for each.

I don't even know where to start, any sort of help is appriciated.

I think it would be a good idea to create an 'Achievement' table with required condition to be unlock (ex:Win 8 rounds in a row) and  an association table 'UserAchevement' to indicate users.the same To store every user progress (the number of Wining round for exemple) a 'Progress' table is needed.


How to create a versioning DDL/DML change script for SQL Server? I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the current version of the schema, and I want to create a single script that upgrades the schema from any earlier version to the current version.

Something like this (pseudocode):

if (select v from version) = 1
    create table newtable ...
    update version set v = 2
end
if (select v from version) = 2
    alter table newtable add column newcolumn ...
    update newtable set newcolumn = ...
    update version set v = 3
end
if (select v from version) = 3
    ...
end
...

There are many problems here.

Mixing DDL and DML can lead to problems, like adding a new column and trying to update it in the same batch will cause an error that the column doesn't exist. So I thought that I should always separate them into different steps, and each step should be a different batch. Of course, every change step will end in a DML to change the version number, but that's okay. There should be transactions inside the DML batches, and let's not worry about the DDL batches.

I also want to make sure that during one execution, only one step is executed, because error handling could be a nightmare. Stopping the script after one step is also not trivial, neither in SSMS nor in SQLCMD. In my above sample, if we start at version 1, it will be updated to 2, then 3, then 4, etc. So I thought I would reverse the order of changes. First comes the change from 3 to 4, then from 2 to 3, then from 1 to 2, this way, only one step is executed at a time.

Does this look okay? Are there other things to consider?

This is just my 2 cents worth...

The way the company I work for currently does their changes is by keeping each script separate and having every script re-runnable, therefore if you notice an issue with a piece of code, there will not be a problem altering it and re-running the whole script.

So we wrap every DDL statement inside a check to see if the change has already been made:

IF OBJECT_ID(N'dbo.my_table', 'U') IS NULL
BEGIN
    CREATE TABLE.....
END

Otherwise, with the way you've currently got the setup, if you make a mistake in version 2 and accidentally omit a key DDL statement, you cannot just add it to that version and re-run the entire v2 script. Firstly because the version number would have updated to 3, then, even if you alter the version number back to 2, the following line would throw an error as the column already exists:

alter table newtable add column newcolumn ...


Microsoft SQL Server Error: 18456(Administrator is also not able to access) We do have 2 windows servers(xx.xx.xx.118 and xx.xx.xx.119) and also we do have 2 SQL servers installed on those windows servers(xx01 and xx02).

The customer gave us one separate username(user1) to login to windows and SQL servers but while trying to login to xx01 SQL server from 118 servers we are getting the Error:18456. We able to connect to xxo2 SQL server from 118 servers with the same user.

We are unable to connect to xx01 SQL server from 119 windows server also with the same username(user1). We are able to connect to xx02 SQL server from 119 windows server.

We also got the Administrator account details both windows servers but unfortunately, Admin is also not having access to SQL servers.

I have gone through different URLs and tried all the available options but couldn't get the access.

Tried to run as different user...to change any settings first we don't have any user able to access.

The customer has no idea regarding other users having access to SQL servers and also they are not aware of sa account details.

The windows are virtual machines and we RDP to those servers.

Can someone suggest me how to get access to those SQL servers and resolve the issue?

During the installation there is option to choose what are users to be
  added as instance administrators.

  Your customer should add you or your team Distribution list. You
  probably added the built-in Administrators group and now you're
  running as admin, that's the reason you cannot access the instance.

  If yes, then is simply a mater of running the client process elevated
  ('Run as administrator').

Check it out: Connect to SQL Server When System Administrators Are Locked Out

Otherwise;

try to run SSMS (SQL Server Management Studio) on the server with 'Run As Admin' to check if you added builtin\administrators during install. If you cant find any user with enough rights then you have to add a login to the server and give it sysadmin rights.

Start the server in single user mode by starting cmd.exe on the server with administrative rights and running net stop mssqlserver and then net start mssqlserver /m
then in the same window run SQLCMD.

Use the following:

CREATE LOGIN [DOMAIN\ADMINISTRATOR] FROM WINDOWS;
EXEC SP_ADDSRVROLEMEMBER 'DOMAIN\ADMINISTRATOR', 'SYSADMIN';
GO
EXIT

and then run net stop mssqlserver and net start mssqlserver.

If you are running a named instance the servicename is different -
Check services.msc to see the name and you have to run SQLCMD with -S server\instance -E to connect


Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine) Right now both SSRS, SSAS  and SQL Server Engine are on the same server.  We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the SQL Engine and SSRS,SSAS on SQL Server 2012 or 2014.

What we want is:

  Server 1 for SQL Server Engine

  Server 2 for SSRS and SSAS

My questions are:

How they talk each other(the two Servers)?
Where the cube for SSAS stored?
Where the report builder build report?
Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?

1.How they talk each other(the two Servers)?

From the database engine perspective, both SSRS and SSAS are clients like any other application.  These query data from SQL Server using SQL queries.  Microsoft just chose to include these components with SQL Server licensing and media.

  2.Where the cube for SSAS stored?

On the server with SSAS installed (server 2).

  3.Where the report builder build report?

On the server with SSRS installed (server 2).

  4.Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?

In addition to what I mentioned in #1, client applications can query the SSAS server without involving the database engine at all after cubes are built.  However, SSRS has a dependency on the reporting server databases as well as data sources that may also be on SQL Server.

Below are a couple of architecture diagrams you might find useful:

SSAS:
https://msdn.microsoft.com/en-us/library/bb510502.aspx

SSRS:
https://msdn.microsoft.com/en-us/library/ms157231.aspx


Fail-over or switching the Reporting server database I have two databases in server, dbs-01.One is a transactional database and a reporting server database.Reporting is minimum,that is why we have it together in the same instance.

I have transactional replication set to replicate the data from server dbs-01 to dbs-02.Currently i am replicating only the transactional databse only.

I want to replicate the reporting server database also which i can implement.
But my questions is :
If i want my application to point to dbs-02,i can do that my changing the connection strings in the application.
But what about the reporting server database.Because we have the Server Name in the Reporting Services Configuration Manager as  shown below(as dbs-01).

If my dbs-01 is down and my application is pointing to dbs-02 , how can i make the reporting services to work from the dbs-02.?

I am not an application developer so i am not aware of any settings which will help to make that switch.
Also configuring Reporting services for dbs-02 from the scratch is not a solution i am looking for.

You can use AlwaysOn Availability Groups to replicate the SSRS Databases to other servers, and reference the Availability Group Listener in place of the server names to allow for failover between servers.

Be aware that Availability Groups do not replicate server level objects such as logins or jobs. If you have any subscriptions in SSRS these will be scheduled jobs that will need to replicated outside the availability group, and you will need to put a mechanism in place to stop them firing when their server is not the primary node.

Here are a number of blog posts that go into further detail about setting up SSRS onto an Availability Group:
https://marcosfreccia.com/2018/06/25/reporting-services-databases-availability-groups/
https://jbswiki.com/2017/09/06/reporting-services-using-alwayson-availability-group/
http://techtalklive.org/ttlblog/making-sql-server-reporting-services-highly-available/