Construct an SQL query to find the names of each department with more than 5 professors

Let's do this a little more visually:
SELECT dept.name as department_name, COUNT(prof.id) as professor_count
  FROM Department dept INNER JOIN Professor prof ON prof.deptID = dept.deptID
 GROUP BY dept.name
 ORDER BY professor_count DESC;

This will give you all departments plus the count of professors sorted by (arguably) the most expensive departments. Seeing the data in front of you will allow you to verify its correctness before continuing.
Now, if you want to show just the departments with more than five professors, you can add HAVING professor_count > 5 between GROUP BY and ORDER BY.


As a starting point to troubleshoot this, you may check what's read from recovery.conf with log_min_messages set to debug2 in postgresql.conf on the slave.

On server start, the trigger file should be shown in the log within a set of entries like this:

 DEBUG:  standby_mode = 'on'
 DEBUG:  primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
 DEBUG:  trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'
 LOG:  entering standby mode

If the trigger_file entry doesn't show up, the most plausible explanation would be that you're editing a recovery.conf at a wrong location.

If on the other hand it's found at startup, when later creating the trigger file to fail over, this entry should appear:

 LOG:  trigger file found: /var/lib/postgresql/9.3/main/failover_trigger


Is there a way to identify the object behind the database synonym?

This seems to work for me:

SELECT
    [Schema] = Sch.name
  , Object   = Syn.name
  , Syn.base_object_name
FROM sys.synonyms          AS Syn
    INNER JOIN sys.schemas AS Sch
        ON Sch.schema_id = Syn.schema_id;


Design a db table where rows may have NULL in multiple columns

You normally would have your data normalized. This means you will have one table for the tour packages, and another one for each days itinerary:

CREATE TABLE tour_packages
(
    tour_package_id INTEGER PRIMARY KEY,
    tour_package_name varchar(150),
    other_data varchar(1000)
) ;

CREATE TABLE package_schedules
(
    tour_package_id INTEGER NOT NULL REFERENCES tour_packages(tour_package_id) /* ON DELETE CASCADE */,
    day INTEGER NOT NULL,  -- Day 1 of itinerary, day 2, ...
    day_description varchar(1000) NOT NULL,

    -- The rows in this table are identified by a combination of tour_package_id and day
    PRIMARY KEY (tour_package_id, day),
    -- We can have the database check that we don't have day 0, or day negative
    CHECK (day >= 1),
    -- If you want to limit the number of days:
    CHECK (day <= 30)
) ;

With the proper CONSTRAINTS defined for your tables (which include in this case the PRIMARY KEYS, the REFERENCES and CHECKS) you can be quite sure that your data follows the rules.

The only thing that an SQL database (as of now) cannot do for you is to make sure that you don't have "missing days" just by declaring constraints.

That is, the situtation produced by the following INSERTs cannot be avoided, and should be taken care of at the application level:

We have one tour package:

INSERT INTO tour_packages
    (tour_package_id, tour_package_name)
VALUES
    (1, 'My first tour') ;

We add now the schedules for days 1 and 3, but we miss day 2:

INSERT INTO package_schedules
    (tour_package_id, day, day_description)
VALUES
    (1, 1, 'First day of first tour: Welcome') ;

-- We are missing day 2

INSERT INTO package_schedules
    (tour_package_id, day, day_description)
VALUES
    (1, 3, 'Third day of first tour: Going to visit Honolulu') ;

This could actually be accomplished on the unnormalized scenario that you provided, with a large collection of CHECK constraints; where each day_n can only be null if day_n+1 is also null. That is, all the NULL values must be on the right side and be consecutive.

CHECK(day_1  IS NOT NULL)
CHECK(day_2  IS NOT NULL OR (day_2 IS NULL AND day_3 IS NULL))
...
CHECK(day_29 IS NOT NULL OR (day_29 IS NULL AND day_30 IS NULL))

I'd say that the advantage that the unnormalized design has because of this is far outweighted by all the inconvenients you may have. The day that you will move from 30 day-max packages to 90 day-max will become absolutely obvious. The day that you want to query: Is there any package where any scheduled day moves through Honolulu? you will find out as well.

The "missing day" is usually dealt with at the application layer, not the database. The database could in theory also handle this (probably) by using triggers, or with a database that allows the definition of functions that can perform complicated checks, and put them into CHECK( no_empty_days(tour_package_id)). In practice, it's something I've seldomly found.

The previous definitions are standard SQL and run on all versions available in DBFiddle.uk (as of today). Take into account, however, that MariaDB (or MySQL) allow the declaration of CHECK constraints, but then do not enforce them.

You can check everything at dbfiddle here


Bad practice to return more columns than required

Some argue that all queries should only return the exact data they need as this would reduce I/O etc...

And they are correct!

...others argue that it is OK to have a query that returns a few extra columns such as int or nvarchar(50) as this reuses execution plans and makes development faster

These folks are also correct!
There are always tradeoffs with these things.
Imagine a web page where users can edit their profile, populated by "query 1" here:
SELECT Id, FullName, UserName, Phone
FROM dbo.Users
WHERE Id = @Id;

And then there's an admin page that uses "query 2" here:
SELECT Id, FullName, UserName, Phone, IsActive, AdminComments
FROM dbo.Users
WHERE Id = @Id;

Performance
For the situation where we're using both queries, we have (non-exhaustive list of pros and cons):

Con: two different plans taking up space in the plan cache
Con: developers had to write two different queries / stored procedures
Pro: the first query will only be doing the work it needs to (no extra I/O)
Pro: the two queries can be optimized differently
Pro: the two queries can be indexed for separately

If we just have query 2, the situation is basically inverted (only one plan in the cache, only one query / proc had to be written, but the query uses more I/O, etc).
Security
There's also an aspect of security: you might not want those "admin only" columns getting queried for non-admin pages, even if they are never presented to the user.
Maintainability
Sharing the same query for multiple different pages that don't need all that data can also lead to maintainability problems down the road.  What if query 2 suddenly needs to join to other tables, in order to pull in additional information about user activity that's not available to regular users?
Now the normal user page is being impacted by the admin query.  And developers at that time might not realize they need to go back and check on the regular user page.
What if some enterprising developer removes columns from query 2, because it's decided admins don't care about them?  Now the regular user page is actually broken.
Takeaway
Re-using queries can be a valid time-saving strategy, but it requires good change management and testing later to make sure that you don't break anything when these re-used queries are modified.  It's also possible various factors will cause the queries to be split up later (performance, feature changes).  You'll have to keep that in mind to determine if the up front time saving is worth the cost later.
Generally speaking, I think it's better to have queries that are specific to each use-case.  You'll notice that this results in lots of stored procedures / queries, which has it's own maintenance overhead.  There are some things you can do to help with that though (keep the procedures small where possible, include a comment in the procedure that indicates what app function / page uses that query, name the procedures very clearly).


MSSQL - Should I change min server memory to 0

all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

Barring few cases keeping min and max server memory same can be counter productive. In such configuration when low memory flag is signaled SQL Server will not trim down its memory consumption below min server memory value. If memory pressure is severe SQL Server process may be paged to disk causing immense SQL Server slowness. To make this configuration worse if you have min and max server memory same and you on top of that provide LPIM to SQL Server service account this would be tantamount to SQL Server saying "do what you can I am not going to release memory". Such configuration can cause paging of OS process which requested memory or caused severe memory pressure, and even may lead to unexpected shutdown of OS.

Should I let SQL server to acquire and release by itself (Min Server Memory = 0)?

Please leave it to default value. On a system having multiple SQL Server instances and if you are biased to one of the system and want that its memory consumption, once reached, should not go below certain value then you can set min server memory to this threshold. On all other cases let min server memory be default.

Is it possible to get CPU overload by MSSQL Dynamic Memory Manager?

No that I know, never heard or came across such scenario. The memory code works to balance memory across SQL Server process and this not overload CPU. Even when SQL Server is growing its memory consumption you would hardly notice CPU rising because of growth, it may be due to other processes running.


What do you call numerical coding systems that are left-aligned?

That sort of coding format is simply a representation of a tree or forest - each code represents the node's location in a hierarchy where each node (except the root node(s)) has exactly one parent.

That hierarchy could be directly modelling the physical world (as Dewey Decimal Classification does for books in a library) or it could be something more "virtual".

I don't think this sort of coding has a generally recognised name, so if you need a concise phrase for use in your documentation just pick something short that results in a relatively unambiguous acronym, be consistent with its use, and make sure it is included in your terms of reference. Perhaps "Hierarchical Encoded Identifiers", "Hierarchical Classification", "Business Location Codes", or similar.

Don't be hung up on it being numerical: this is not significant as there are no mathematical operations that are meaningful when applied to these codes. The codes could be using any set of characters, even multiple characters per level in the hierarchy, and for this system someone has chosen to limit the alphabet of the encoding to the characters 1, 2, 3, ... It would be just as meaningful for the encoding to use other characters or to have separators between each position, so your last two examples could be MQE/EOG/CM/CM/BaL & MQE/EOG/CM/CM/BUM or MXCCL & MXCCU in similar schemes with different alphabets - all operations would be equally meaningful (searching, lexical sorting, extracting the meaning back from the code, and so forth) or not (mathematical operations) in these encodings.


SQL Server Express greatly reduces the resources it uses if it is idle for a period of time.  This may cause connections to time-out if the machine is busy after SQL Server idles.
http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx contains some details about this behavior, including:

When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory. This frees up memory for other applications to use.
This behavior makes sense for a single user database engine - it supports higher performance when the application using the data is running, but once that application is shut down, memory is released to allow other applications to use it. I like to think of this as "being a good citizen" in the application community. There is always a catch when making this kind of trade-off though, and SQL Express is no different in this aspect. When SQL Express transitions from idle to active some of the memory that was released needs to be reclaimed; this results is a slight lag during "startup time" when you're first connecting to SQL Express. You can actually see this happening in the Widows Application Log, when you first activate SQL Express after some idle time, an entry is written to the log that reads like this:
Server resumed execution after being idle 16056 seconds: user activity awakened the server. This is an informational message only. No user action is required.

Also, SQL Server Express will "auto-close" any database that is no longer being used 300 milliseconds after activity ceases.


Incremental statistics: find out which partitions have been resampled

My first question would be to ask why you're actually using incremental in the first place. Here's an answer that I posted regarding incremental statistics, a blog post by Erin Stellato that illuminates one of the primary complaints and pitfalls with incremental statistics (they're not used at the partition level by the optimizer), and two blog posts by me that work through evaluating any potential use case for incremental statistics.

Having said that, to know when a partition's stats have been sampled you can use an undocumented DMF (sys.dm_db_stats_properties_internal()) to get partition-level information. I have a comment on this blog post that describes how to understand the hierarchy at a fairly high level.

select
    sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    parition_number = isnull(sp.partition_number,1),
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter = coalesce(sp.modification_counter, n1.modification_counter)
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = '<<TABLENAME>>'
    and s.name like '<<STATNAME>>%'
order by s.stats_id,isnull(sp.partition_number,1);


Using EXECUTE IMMEDIATE inside PL\SQL Block

In Oracle when you create a table or execute an alter statement there is an implied commit. You may want to create the table in a separate function returning a boolean value with "PRAGMA AUTONOMOUS_TRANSACTION"  then the calling procedure would get a true/false response as to whether or not the table was created. you will then be able to commit or rollback your insert.


Oracle XE 12c won't configure - getting the "port 5,500 in use" error message

I found the solution with the assistance of our DBA team.

The problem was I using a fully qualified hostname in the /etc/hosts file. I had interpreted the solutions I found that cited "hostnames must match" to include the domain name as well.

If your hostname is xxxx.yyyy.zzzz, then you add just "xxxx" to /etc/hosts, per the instructions provided:

127.0.0.1    xxxx.yyyy.zzzz    anything-else   xxxx

In my case, I added just "iv4x" to the loopback line.


TRANSACTION_*, MSQL_TRANSACTION_MANAGER, NESTING_TRANSACTION_FULL, NESTING_TRANSACTION_READONLY

Within this group of latch classes that are used during various transaction-related operations, the TRANSACTION_DISTRIBUTED_MARK latch is unique. It is used when placing markers in the transaction logs to allow for recovery to a named point. There is only one transaction mark latch in any instance of SQL Server 2005. This latch rarely, if ever, encounters contention, and thus there is no need for an extensive description. The source of any contention is also clear, because this latch is used by only a single operation. The other latch classes in this group are used in various transaction contents.

this post is original from:

http://www.informit.com/articles/article.aspx?p=686168&seqNum=5


Greyed out menu options, broken activity monitor

The behavior you are seeing happens if you aren't recognized as having permissions to manage services on the remote server.  This MSDN article describes some possible solutions on how to grant users rights to manage services.

Does the service account for the agent have local administrator permissions on the server?  Have you stopped and started database services and the SQL Server agent service on the server since encountering this problem?


How can I make only *one* user controlled database

You can only really hope to log this and raise alerts.  Users who can administer the hardware can always stop the service and bring a database up in single user mode etc.  They can give themselves elevated permissions etc.

It's a common requirement to segregate administrative duties so that server admins cannot see confidential files or confidential data.

However, because every administrative role can typically elevate their rights or create other users, requiring users to use least privilege accounts for their daily work and auditing what is done with elevated accounts is really the only way to deal with it.


Following information was found on sybasewiki Hope this helps !

How to start the sybase server?

After log in your Linux/Unix machine.

Step 1:  Find out current location or present working directory.
Step 2:  Locate environmental file SYBASE.sh or SYBASE.csh profile which is ,by default, located in $SYBASE.
Step 3:  Run environmental file.
Step 4:  Verify SYBASE.sh or SYBASE.csh run properly.
Step 5:  Find out RUN_Serverfile
Step 6:  Start server with startserver.
Step 7:  Start backup server with startserver.

Step 1: pwd   -- know your present working directory.
Step 2: cd  <directory_name_where_SYBASE.sh_present> --find out SYBASE.sh profile which can you find SYBASE ASE directory.
Step 3: . SYBASE.sh   -- run it and include it.
Step 4: echo $SYBABE  -- shows path where $SYBASE created which verify that SYBASE.sh run prperly.
Step 5: cd $SYBASE/SYBASE_ASE/install
        ls -ltr  -- find out RUN_server_file name.
Step 6: startserver -f RUN_servername

Starting Back Up Server :
 Step 7:startserver -f RUN_SYB_BACKUP

Note: depends upon installation where SYBASE ASE Server installed, so relative path of Run_serverfile can be differ.


Inserting all csv files into a table in MySQL

See these previous answers:

https://stackoverflow.com/questions/8538995/how-to-import-multiple-csv-files-into-a-mysql-database

https://stackoverflow.com/questions/6552042/mysql-loading-multiple-files-into-a-table

Essentially, no.  But it's easy to script this to get a similar result (e.g. one command to import multiple files using a loop).


PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

pg_start_backup will perform a checkpoint, as dezso notes. This does have an impact, but your database performs checkpoints quite regularly anyway, and must do so to function, so they're clearly not a problem for you. An early checkpoint means that less data has been accumulated, meaning that if anything a checkpoint from pg_start_backup will be lower-impact than normal.

Where you need to worry is the rsync or equivalent pg_basebackup step. The read I/O from this won't be too bad since it's sequential, but it'll still probably significantly hurt your database's I/O performance, and it'll also tend to push hot data out of RAM cache in favour of less-used data, causing cache thrashing as the more-needed data is then read back in.

You can use nice and ionice to help limit the I/O impact (but not the cache impact); however, there's a cost to that. The backup will take longer, and until you complete the backup and run pg_stop_backup your system is - as I understand it - accumulating WAL it cannot delete, accumulating checkpoint debt for a BIG checkpoint at the end of the backup run, and is accumulating table and index bloat because it can't clean up dead rows. So you really can't afford to have the backup take forever, especially if you have very high churn tables.

In the end, it's hard to say whether you can safely use pg_start_backup and pg_stop_backup for hot backups in your environment. Most people can, but if you're close to the edge of what your hardware can do, have tight timing requirements, cannot afford the risk of a stall, and have very high churn tables as well as very big tables, it might be troublesome.

Unfortunately, you pretty much need to test it and see.

If you can, it might be worth issuing a CHECKPOINT then taking an atomic snapshot of the volume your database is on instead using LVM, your SAN's tools, EBS, or whatever you're on. If you can do this, you can then copy the snapshot at your leisure. This approach isn't suitable for taking a base backup for PITR/warm standby/hot standby, but it's perfectly good for a static backup copy, and is much lower impact on the system. You can only do this if your snapshots are atomic and your entire database including WAL is on a single volume, though.

One possibility I haven't yet investigated is combining the two approaches. It occurs to me that one could possibly (untested and possibly wrong and unsafe, I don't know yet):

pg_start_backup
Trigger snapshots of all tablespaces, the main datadir, and the xlog volume
pg_stop_backup
Copy WAL up to the final archive from pg_stop_backup
Copy the data from the snapshotted volumes

Essentially, the idea is to reduce how long the DB has to be delaying its checkpoints by taking a point-in-time of each volume that you can copy at your leisure.


The old csscan used to do this, but have you actually run DMA?  DMA does a scan of your whole database and then lists all the tables that will suffer a lossy conversion.  This is a preview before it actually goes ahead and does the conversion.  You will have a higher chance of lossy data if you go from a multi-byte character set like AL32UTF8 to a single-byte character set like WE8MSWIN1252.  There are many reasons why you would have lossy data, and part of these are due to databases being accessed by multiple applications and tools on multiple operating systems, with locale/NLS_LANG set correctly or not.  If NLS_LANG is set correctly then oracle does single-pass conversions between source and target database.  This depends on the application though.

Oracle does state that there is a performance overhead (maybe 5%) on using a multi-byte character sets, but specifies AL32UTF8 as the default as that covers all character sets world-wide.  I don't know of any view that calculates conversion times; that's part of the RDBMS.

We personally have chosen WE8MSWIN1252 on our databases for two reasons:

  It was going to take too long to convert from US7ASCII to AL32UTF8
  It covers most European character sets and we have separate AL32UTF8 databases in our eastern countries

Hope that helps!


SQL Server 2005/2008 UTF-8 Collation/Charset

No, there isn't. SQL Server doesn't support UTF-8.
You need to define your columns as nvarchar/nchar if you want unicode data. Note, internally SQL Server stores this as UCS-2.
Note that this has ben requested from MS on Connect and there is an older KB article. And some info on this blog too


Sequential scan with large sub-query filter "never" ends

Your queries are slow because they in fact do a lot. In your

explain analyze
    select * from bubu where id not in
    (select bubu_id from kuku limit 33554431);

example, the database is fetching up to 33,554,431 rows in undefined order from kuku, doing another scan on bubu to select rows that are not among the fetched 33m+ rows, and then finally fetching and returning rows that fit such a condition.

If you wanna do

delete from bubu where id not in
(select bubu_id from kuku)

then your options are:

Just run it and wait.
Do it in chunks:

Fetch all the IDs that you are going to compare against into a separate table:

CREATE TABLE temp_bubu_ids AS SELECT bubu_id FROM kuku;

Fetch 1000 or so IDs from the IDs table, delete them and then delete rows from bubu that match your condition:

WITH deleted_ids AS (
    DELETE FROM temp_bubu_ids

    -- DELETE doesn't support LIMIT
    WHERE ctid IN (
        SELECT ctid
        FROM temp_bubu_ids
        LIMIT 1000
    )
    RETURNING id
)
DELETE FROM bubu
WHERE id NOT IN (
    SELECT id
    FROM deleted_ids
)

Rinse and repeat until all the appropriate rows get deleted.


InnoDB foreign key and index duplication?

ix_supplier_id and ix_manufacturer_id are not required; as long as you have the fields intended for foreign keys indexed by themselves (as in: (part.supplier_id)) , the names of the keys are arbitrary. You can also have them in composite indexes, but there must be at least one index of the whole field and nothing but the field. So help me DDL =).


As far as I can tell, your query is using an index, the one that backs your primary key.

Look at the EXPLAIN Output Format documentation for the "join type" column. The second bullet point for the index join type is what your select is using:

  A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.


Is there a particular name for the type of structure of this database?

I don't know if there is a name for the structure that you are displaying in Excel, but the reporting layer rarely maps to the best way to store the actual data in a relational manner.

This link discusses database normalization https://en.wikipedia.org/wiki/Database_normalization

Given your requirements above, I would use three tables, one for people, one for skills and another to map them together.  The decision about where to store a skill level (for example, Spanish 4) is up to you.  You could either make a seperate skill called "Spanish 4" or store the level information on the PersonSkill table in the example below.

Your reporting layer can handle displaying the data, even pivoting it based on skill name, or do whatever else you need to do with it.  Example below is for MSSQL specifically but other RDBMS would be similar.

/** CREATE TABLES */
CREATE TABLE dbo.Person
    (
    PersonID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Skill
    (
    SkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , SkillName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PersonSkill
    (
    PersonSkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person (PersonID)
    , SkillID INT NOT NULL REFERENCES dbo.Skill (SkillID)
    )

/** GET SOME DATA **/
INSERT INTO dbo.Person
(PersonName)
VALUES ('John Smith') --1
    , ('Jane Doe') --2

INSERT INTO dbo.Skill
(SkillName)
VALUES ('Coding') --1
    , ('English') --2
    , ('Spanish') --3

INSERT INTO dbo.PersonSkill
(PersonID, SkillID)
VALUES (1,1)
    , (2,1)
    , (2,3)

/** QUERIES */
--What Skills does John have?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE P.PersonName = 'John Smith'

--Who can speak Spanish?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE S.SkillName = 'Spanish'


Is there a backup operator role in Microsoft SQL Server 2008?

It is not a system level role, it is assigned in each database.  Assign the login "public" and "db_backupoperator" roles in each database it needs to backup.

http://www.sqlbackuprestore.com/backupandrestorerights.htm


If the "cube" needs to process all rows, indexes won't speed up the process as the querying engine would just do a table scan.

I think the best advice I could give is to familiarize yourself with query plan output so that you can see how the database is satisfying the query requests. This will allow you to see what access methods are used to retrieve data. It would also allow you to answer you're own question: if you create the proposed index, does sql server use it to fulfill your query?

I think the answer to your question is going to be "No". If your "cube" is being materialized by executing "SELECT DISTINCT columnB FROM table", and you currently have an indexes on "columnA" and "columnB". "Consolidating" those separate indexes into a single index on "(columnA, columnB)" is unlikely to help your "cube" queries on "columnB". Your mileage may vary, not having access to the schema nor query source makes these kinds of questions difficult to answer definitively.

Having said that, I think the correct response is to point you towards the SQL Server data warehousing functionality instead of rolling your own solution.


In a SSDT project how to avoid redeploying the assembly?

I've got the same problem when using the Schema Compare tool in Visual Studio 2013. From what I can tell, the reason it wants to keep deploying the SQLCLR assemblies is the Module Version ID (MVID) in the DLL. The MVID is a guid that is automatically generated by the C# compiler during compilation so each rebuild get the DLL a new MVID.

One option is to try and avoid a rebuild of the DLLs. If you're building locally with Visual Studio, you'll have to avoid the "clean" and "rebuild" options. If you're using a build server such as TFS you'll need to configure the build to not clean the workspace.

The other option that I'm going to try is to have the C# code in a separate project, and "import" the DLLs into the main database project as and when the C# code changes. Pretty much as I used to do when using the older style "DataDude" projects. Using the latest version of SSDT for VS2013, I've added the SQLCLR assembly DLL as a reference  in the main SQL Server project, and set the "Model Aware" property to "True". This properties seems to be required to allow me to reference the assembly in the store procedure / function DDL statements.


Sql Server 2008 Database server Minimum Requirements

Here are the min specs from Microsoft if that's what you're look for. It's not going to give you user counts and such: http://msdn.microsoft.com/en-us/library/ms143506.aspx


This is Weblogic. The FORMS services are actually answered by a Weblogic Java Server. It is named WLS_FORMS and you can start it via the web logic management console (available by default as http://localhost:7001/console).

Click on the servers link on the left, then on the main pane, select "Control" tab. Select the checkbox next to WLS_FORMS and select "Start listening".


Increasing the size of the other database files by 1GB resolved the problem.


Best way to use insert statement on Oracle

insert first
when CPF is null
then into COMPANIES_ADDRESS (ZIPCODE, STREET, "NUMBER")
else into CUSTOMERS_ADDRESS (ZIPCODE, STREET, "NUMBER")
select  V_ZIPCOD, V_STREET, V_NUMBER
from    dual

P.s.
Using reserved word as identifiers (in this case NUMBER as a column name) is a really bad practice.


MariaDB Multi-source Replication SQL_SLAVE_SKIP_COUNTER

Change the default_master_connection variable for the session to make it work for a specific multi-source connection:

STOP SLAVE 'src1';
SET @@default_master_connection = 'src1';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE 'src1';
SET @@default_master_connection = '';


Can I use RMAN to backup and restore data in a certain point in time?

There is a feature specifically for that:

 CREATE RESTORE POINT before_test1 GUARANTEE FLASHBACK DATABASE;
 select NAME,SCN,TIME from v$restore_point;
 exec perform_my_test1;
 SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;
 FLASHBACK DATABASE TO RESTORE POINT before_test1;
 DROP RESTORE POINT before_test1 ;

This is a database's feature, not RMAN's (commands are for sqlplus), see doc for the full setup and disclaimers.

To more precisely answer your question, RMAN can also be used for the task. The general script is:

run {
  set until scn 123123123 ;
  restore database /* from tag ... */;
  recover database;
}

Don't use bare RESTORE DATABASE FROM TAG because without SET UNTIL it doesn't work as you'd expect in some scenarios.

I wouldn't say any of these two solutions are inherently safe, both have as many pitfalls as any other Oracle's advanced features, so you need to do some research upfront.


How do I transfer logins from 2008R2 to 2014 with passwords?

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure.  This will allow you to transfer logins and retain the password from on instance to another.


Even though binlog_format is a global variable, it doesn't exactly set the "format" of the binary log itself -- it sets the format in which DML events are logged for execution by the slave.

In STATEMENT mode, the actual DML queries that change data on the master are written to the log as they were received by the master, and subsequently executed on the slave.

In ROW mode, DML queries are not logged as SQL; instead, "images" of the actual changed rows are written to the log, which the slave applies directly to its data set.  When rows are deleted, the data contained in the deleted row is written to the log; when rows are inserted, the inserted rows are logged, and when rows are updated, the data from the old and new version of the row is logged.

Row-based logging has a number of advantages over statement-based logging, perhaps the most significant being the fact that it's absolutely deterministic, since the actual data from the actual rows is sent from machine to machine.  There are interactions with no user-defined variables, no non-deterministic functions (e.g. UUID()), no issues with rows being accessed in any particular order (e.g. DELETE ... LIMIT) ... but since row-based logging only deals with the data in the individual rows, it's only applicable to DML.

DDL is still logged an SQL query -- a statement-based log event -- because that's how DDL is done -- you alter a table with ALTER TABLE... the row based log events are "another way" of changing row data, but there would be no reason for MySQL to implement "another way" of handling ALTER TABLE (or similar events).  The row-based logging format is only interested in row images, not table objects.

DDL has none of the same problems with determinism that DML has, which is the primary problem that row-based replication solves -- there's more than one way to DELETE FROM t1 LIMIT 1 (which '1' row would get deleted is generally predictable but technically undefined) but there's already exactly and only one way to ALTER TABLE t1 DROP COLUMN c1.

How does this affect replication?  It doesn't have any negative impact on replication, or any particular impact at all, since that's how row-based replication has always worked since it was introduced in MySQL.

It's something to keep in mind if you are filtering replication because of the potential impact on how filtering rules are interpreted... but if you are thinking about filtering replication (not replicating all of your tables), my advice is that it's usually best to just take those thoughts... and let them go.  It's hard to think of any really good cases where filtering replication is worth the trouble when the default "replicate everything" is so much more straightforward and less subject to nuance, and if you have data that for some reason shouldn't be on the slave, there's always the BLACKHOLE storage engine.


Where info is personalised it should be normalised into linked tables that only join to rows where that optional column has been added.  Also if you might be adding many columns or long data it is common to have a core user table with extended info tables mapped on a one to one basis.  These methods allow for smaller indexes more specialised to each query scenario.

Generally, good normalisation and matching tables well to real world entities rather than theoretical ones gives the most adaptable and future proof design.


What does it means when you have "sql_exit_invoked" in System Health?

Extended Events are pretty poorly documented.

Debug channel extended events are even more poorly documented.

Based on my testing, you'll need to use the "shutdown_option" value from those event entries as a clue for where to look next.  Here's what they mean in my limited testing:

ORDERLY_SHUTDOWN

restart or stop the service cleanly (using Config Manager, Services snap in, etc)
restart Windows cleanly (start menu -> shutdown / restart)

NICE_SHUTDOWN - stop the service using the SHUTDOWN; T-SQL command
FAST_SHUTDOWN - stop the service using the SHUTDOWN WITH NOWAIT; T-SQL command

Note that I also tried the following, and they did not register anything in the Extended Events session:

shutdown Windows by holding down the power button
kill the sqlservr.exe process from the Task Manager

I was unable to get the SHUTDOWN_NOT_SET version of the event to fire, so that one remains a mystery.

In your screenshot, I can see that ORDERLY_SHUTDOWN is the option in your scenario. In that case, since this seems to imply a clean shutdown of the service or Windows PC, I would look into the Windows Event Viewer "System" log and see why the machine is being restarted, or what service / component is issuing a SHUTDOWN command to the SQL Server serevice.

Good luck!


Different default innodb_buffer_pool_size on Windows with MySQL 8.0.18

8M is a very old default for MySQL.  Yes, it is laughable.  It is much too small for any instance today.

Either that my.ini was left over from some previous installation, or it is worth a bug report.

If you have at least 4GB of RAM, set innodb_buffer_pool_size to about 70% of RAM.


How to check in a trigger if the entire row remains the same after an update?

You can simply compare the old and the new records using is not distinct from which handles NULL values correctly (if all columns are defined as NOT NULL you can simply use = or <>)

if old is not distinct from new then
   .... do something
end if;

The same can be done to check if at least one column changed:

if old is distinct from new then
   .... do something
end if;


Add New Columns that Repeat Min and Max Dates

You can use a SQL Server windowing function like this:

--demo setup
Declare @t table ([month] date, linenumber int)
insert into @t([month],linenumber) values
('12/30/2018',123), ('1/29/2019',123),
('7/30/2018',789), ('8/29/2019',789)

SELECT [month]
    ,linenumber
    ,min([month]) OVER (PARTITION BY linenumber) AS mininvoicedate
    ,max([month]) OVER (PARTITION BY linenumber) AS maxinvoicedate
FROM @t

| month      | linenumber | mininvoicedate | maxinvoicedate |
|------------|------------|----------------|----------------|
| 2018-12-30 | 123        | 2018-12-30     | 2019-01-29     |
| 2019-01-29 | 123        | 2018-12-30     | 2019-01-29     |
| 2018-07-30 | 789        | 2018-07-30     | 2019-08-29     |
| 2019-08-29 | 789        | 2018-07-30     | 2019-08-29     |

For more information about SQL Server windowing functions, check out the documentation in this post SELECT - OVER Clause (Transact-SQL)


Debug why MySQL/mariadb table is crashing

Turns out I ran into this 5-yr-old bug: http://bugs.mysql.com/bug.php?id=54828 in combination with a leftover (no idea from where) zero-byte file.

How I debugged it: I set up the query log:

set global general_log 'mysql.log';
set global general_log = 'ON';

which turned out to write to a file far below /tmp/systemd-private... because I'm running systemd. tail -f that file showed the crash happened when the application I'm running executed "OPTIMIZE TABLE".

Invoking that manually showed error message:

Can't create new tempfile: '/var/lib/mysql/zxnifltlapzlcumb/items.TMM'
Operation failed

and that happened because a month-old zero-byte file already was in that place. I deleted it, and the problem went away.


Restore previous version of database in sql server

There is a chance if the answer to all the below questions is NO

Was the database name exactly identical in Prod and Dev?

When restoring Database A on the Dev server, did you explicitly choose to move the data and the log files to the same path (and overwrite them) where the original dev Database A resided?

Did you drop database A on the Dev box before restoring from the prod backup?

If the answer to all the above questions is No, then there is a chance that your restore did not overwrite the original files of database A on the dev server.
When restoring a database from a backup that was taken on a different server/DB, SQL Server creates data and log files on the same path as that of the original database (in your case prod copy of database A) that the backup belongs to (unless explicitly mentioned in the MOVE clause of the RESTORE command). So if the path of the data and log files on Prod is different from the Dev box, then the restore operation will not overwrite the files unless you explicitly specify WITH MOVE 'filename' TO 'path' in the restore command along with the option REPLACE.
In this case, check the local drives on the dev server and see if the files of the original Database A are still there (assuming the path of the files on database A is different on the dev box than on the prod). If you succeeded in finding the orphan files, and you can attach them as a different database (eg: A_dev).


Insert New Foreign Key Row for new Strings

Assuming someval is actually an auto-generated column like a serial.

In Postgres this data-modifying CTE (writeable CTE) would do the job:

WITH sel AS (
   SELECT r.someval, i.someval_str, i.machine
   FROM  (SELECT ?, ?) AS i(someval_str, machine)
   LEFT   JOIN new_data_someval_ref r USING (someval_str)
   )
, ins AS (
   INSERT INTO new_data_someval_ref (someval_str)
   SELECT DISTINCT someval_str FROM sel WHERE someval IS NULL
   RETURNING someval, someval_str
   )
INSERT INTO new_data (someval, machine)
SELECT COALESCE(sel.someval, ins.someval), sel.machine
FROM   sel
LEFT   JOIN ins USING (someval_str);

Everything standard SQL, not sure whether it's all implemented in SQL Server identically.

For a query with multiple input rows and ample explanation:

How do I insert a row which contains a foreign key?

There is a tiny chance for a race condition under heavy concurrent load. (Doing everything in a single statement minimizes the time window.) If that's the case:

Is SELECT or INSERT in a function prone to race conditions?


Move an Oracle database by moving the raw data files

This is largely dependent on the differences is Oracle version, and OS version. IF both are at the same version, and Oracle is patched in the same manor then the answer becomes a factor of:

IF all of the database related files will reside in the same place THEN

...do a cold clean shutdown of the database

...copy the datafiles, temp, undo, redo logs, control files AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup normally

IF the file structure will change, but the instance name will not THEN

...do a cold clean shutdown of the database

...copy the datafiles, undo, redo logs AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...do not copy controlfiles

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup nomount

...recreate the controlfiles using the "create controlfile reuse ~ noresetlogs" command

...add back in TEMP tablespace

IF the file structure will change AND the instance name will as well THEN

...do a cold clean shutdown of the database

...copy the datafiles, undo, redo logs AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...do not copy controlfiles, temp or redo logs

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup nomount

...recreate the controlfiles using the "create controlfile set ~ resetlogs" command

...add back in TEMP tablespace

This is the typical, old-style clone technique. Normally, I am a fan of the RMAN DUPLICATE, but if this is a one time thing, I probably wouldn't worry about it. If this isn't 11g, you would be taking a separate backup, transporting that for use by rman. If it is 11g, duplicate can use the "from active database" clause, so long as the instances can reach each other on the network.


How to select info about indexes with specified columns from all databases in the instance using TSQL?

You can do dynamic SQL in a cursor to do it on every database on the server that way :

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(1000)
DECLARE database_cursor CURSOR FOR
SELECT name FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @Command = 'USE [' +  @DB_Name + '];  select
   idx.[name] as [index_name],
   sc.[name] as [schema_name],
   obj.[name] as [table_name],
   ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,''LIMITED'') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;'
 EXEC sp_executesql @Command

 FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor


Purge mysql and it's configuration completely from MAC.

You do not need to purge anything, you just need to follow these instructions to reset the administrator password. If you happened to delete the admin user, just create a new one on the init file:

create user root@localhost;
grant all privileges on *.* to root@localhost;


ORA-01033: ORACLE initialization or shutdown in progress using Oracle Express

I believe restarting the applicable windows service will bounce the database.

net stop oracleservicexe
net start oracleservicexe

Alternatively you can connect as sys and provide the password instead of using internal authentication, then bounce the database from there.

>sqlplus /nolog
SQL>connect sys as sysdba
Password:

You may also want to check the alert log for what, if anything, is keeping the database from completing startup or shutdown.


Problem installing Oracle 19c to symlinked directory from RPM

you cant do that unless oracle user has a permission on the original directory here is an example explain to you what is exactly happening (in the example I am going to create normal text file)
[aabuhasna@aabuhasna ~]$ sudo mkdir /test
[aabuhasna@aabuhasna ~]$ sudo ln -s /test/ ./test
[aabuhasna@aabuhasna ~]$ cd ./test/
[aabuhasna@aabuhasna test]$ touch 1
touch: cannot touch '1': Permission denied
[aabuhasna@aabuhasna test]$ ls -alrt
total 8
dr-xr-xr-x. 19 root root 4096 Oct 21 18:15 ..
drwxr-xr-x.  2 root root 4096 Oct 21 18:15 .
[aabuhasna@aabuhasna test]$
[aabuhasna@aabuhasna test]$ sudo chmod 0777 /test
[aabuhasna@aabuhasna test]$ touch 1.txt
[aabuhasna@aabuhasna test]$ ls -altr
total 8
dr-xr-xr-x. 19 root      root      4096 Oct 21 18:15 ..u
-rw-rw-r--.  1 aabuhasna aabuhasna    0 Oct 21 18:18 1.txt
drwxrwxrwx.  2 root      root      4096 Oct 21 18:18 .
[aabuhasna@aabuhasna test]$

but in your case you need to use sudo chown -R oracle:oinstall /u01/oracle this is a prerequisite from Oracle, there is no workaround for that
Note: softlink will not provide full access and\or ownership on the original directory


When does MySQL begin to start sending data to a client?

The buffer pool is not used to materialize query results. It stores table and index pages to be shared between all connected sessions.

Each session allocates its own result buffer, where the selected data is copied from the buffer pool. This applies whether a join is involved or not.

If no additional operation is required, partial results will be sent to the client as soon as the result buffer is full. If the result set must be sorted, that will be done in the sort buffer (and temporary tables if necessary), then sorted data will be sent to the client, via the result buffer, as before.

You can read more about how MySQL uses memory in the documentation.


How do I execute a PL/SQL package from my database SQL> prompt?

You cannot execute a PL/SQL package, as it is simply a container for one or more routines (stored procedures and functions). Typically you use packages to organize various related routines.

You execute (call) individual routines in a package by referencing them by their names, e.g.

exec PKG_NAME.SPROC_NAME

or in your case

exec C_FIX_STUCK_COS.FIX_STUCK_COS

as both package and SP have very similar names (which is not necessarily a good idea in itself).


Running Total of a count per day in SQLite

For anyone trying something similar : this is what worked in the end :

select
a.ORDER_DATE,
sum(b.ID_COUNT) RunningTotal
from
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) a,
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) b
where a.ORDER_DATE >= b.ORDER_DATE
group by a.ORDER_DATE
order by a.ORDER_DATE;


Create MySQL CHILD and PARENT records at the same time and set CHILD.PARENT_ID by using MySQL Triggers

I think you have to perform this trigger before insert child row and there is a lot of errors in your code.

You must declare variables in the beginning of the script.
You give values to variables with SET
Only LAST_INSERT_ID() function is necessary and if you perform the trigger before update, you only have to set NEW value, and that's it.

DELIMITER $$
DROP TRIGGER `database_name `.`trigger_name` $$
CREATE
    TRIGGER `database_name `.`trigger_name` BEFORE INSERT
    ON `database_name `.`child_table`
    FOR EACH ROW BEGIN
    DECLARE last_id int;
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (0);

    SET NEW.parent_id = LAST_INSERT_ID();
END $$
DELIMITER;


Finding The Actual Application User ID In The Transaction Logs

I want to be able to find out the application user who made the change, not the database user.

Then you're going to need to fundamentally change the way your application behaves in terms of security and auditing. You'll also need to setup some sort of auditing, either in the application or database.

  Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?

No, the log was not meant to be a developer tool.


NOT IN can have slower performance than a left join for some data sets. You can see an example here that compares different ways of excluding items from your selection. Ideally you could identify the IDs you want to include, and this would definitely gain better performance.

So first you could try one of the two query changes depending on your data:

SELECT *
FROM Table
WHERE ID IN (SELECT IDs FROM UserViewingList WHERE UserID = @UserID)

If you're using an app of some sort, you may be able to update a viewing table while the user chews through the first set of data, and then have that available for a quicker response when they request the next set.

Or if you don't have that option/data try replacing NOT IN:

SELECT BaseTable.*
FROM BaseTable
LEFT JOIN ViewedIDs
     ON BaseTable.ID = ViewedIDs.ID
WHERE ViewedIDs.ID IS NULL

The other question though is can you cache this set in an App or is there another way to update the list? You might be able to add pagination or some other tool in your UI that can handle the selection of IDs in memory on a per user basis.

In addition to the query changes, you can add indexing to your User IDs column, if not already indexed (on your source table - BaseTable in the example above).


Query table A for any rows that are like any search term in table B

...
FROM
  TableA
  JOIN
  TableB ON
          TableA.FirstName LIKE '%' + TableB.SearchTerm + '%' OR
          TableA.LastName LIKE '%' + TableB.SearchTerm + '%'
....

Now, this isn't going to be the best of queries. Consider full text search instead (I'm not too familiar with it)

This OR could be split into a UNION


database management system vs database vs tables vs file

It is probably helpful to start thinking not about the low level details, but the high-level properties/behaviours of different kinds of storage.
For example, one might consider simple files (flat, binary content), indexed files (structured access layered on top of a single simple file), with or without locking in either case supplied by OS level primitives.
In such terms, one might say that different database systems (using the term very loosely) can be characterised and contrasted with files by things like:

A query language that is used to read (and to some extent, write) data according to some complex criteria. Simple files can only be read or written by file offset. Indexed files can have some criteria, but the query language is often very simple.

A database system often has multiple types of object stored in it, with metadata structures that allow those objects to be treated in a uniform manner by the query language. With simple files, and indexed files, you have to build all those aspects yourself.

A database system is often network-accessible, including with locking semantics. Simple files can be exposed by a network filesystem layer, but the locking semantics are often lost or changed when you do so. I'm not aware of any system (other than possibly some obsolete systems from the 1980s) that even attempt this for indexed files.

One might continue such an analysis for many other behaviours and properties. As you do that, the details of your questions and assumptions can be answered with regard to those behaviours and properties.
Of course, it is also true that many database systems are built on top of simple files provided by the operating system. But that is not a given - it is in fact common for high performance database systems to access the disks using some kind of "raw" I/O too. Files just happen to be a convenient abstraction to avoid the database system having to replicate a lot of things that the operating system's file access layers already do.


Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher."

SSIS 2012 components had to be installed on Host 1 to resolve the issue.

*If anyone knows of a way to resolve this without installing SSIS 2012 components where they aren't intended, please post and I'll accept your answer.


Out of range errors while with insert statements

The values you're trying to insert are above the max allowed for a signed INT column in MySQL:

3088727691 > 2147483647

You can either switch it to an unsigned INT to make the max value 4,294,967,295 or a signed BIGINT to give you the room to hit up to 9,223,372,036,854,775,807.


How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

Benefits of partitioning a CCI:

Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL Server partitioning guidance out there doesn't take this into account.
Improved flexibility with maintenance operations in that you can do rebuilds at the partition level or do reorgs at the partition level (after partition switching out). You can also send different partitions to different filegroups, but I need to caution you that doing so will almost never improve performance. Filegroups are a maintenance feature. Increasing the file count can improve performance sometimes. Depending on your storage setup you almost certainly want the data relevant to your queries to be spread over multiple files to improve I/O.
Partition elimination covers more scenarios than rowgroup elimination on the same column. For example, a filter of WHERE ID < 0 OR ID > 10 will not quality for rowgroup elimination but will qualify for partition elimination.
Looping by partition can be helpful when performing maintenance operations that require all rows to be changed. For example, suppose you're adding a new column to a table that can be derived from existing columns in that table. Partitioning allows you to efficiently split that work into batches if desired.

Downsides of partitioning a CCI:

Without maintenance the number of rows in delta rowgroups can dramatically increase. Consider an unpartitioned CCI that is loaded with parallel inserts at MAXDOP 8. At most you'll have 4194304 rows in the delta store. If the table is changed to have 50 partitions it's now possible to have 209715200 rows in the delta store.
Query plans for inserts and deletes into the columnstore may contain a sort operator as a child of the DML operator. If this sort cannot get enough memory you can end up with extreme performance degradations. I recommend only modifying one partition at a time if using parallel insert.
If you choose your partition function unwisely you could end up with partitions that are too small. Many people will point you to the 1048576 row limit for a rowgroup as the ideal size, but personally I consider the benefits of getting there to be overblown. You probably do want to avoid many tiny partitions if you can help it though.
If you have too many partitions in your table or your database then bad things might happen. Unfortunately, this isn't very well defined and it's hard to find a credible source for what "too many partitions" actually means. I've heard of and seen issues with query compilation times. There was a recent answer here about DBCC CHECKTABLE as well.

Applying the above to your scenario: with the row counts that you have you shouldn't run into any of the really bad cases. For query performance, some folks need really fast query execution times and they need to skip as many rowgroups as possible. Others just need a minimum level of rowgroup elimination because most of the work done in the query is outside of the columnstore scans. That makes it difficult for someone on the outside to give you a recommendation for the number of partitions. For the 100 million table, anything from 4-100 could be reasonable.

You could try testing some of your queries with different numbers of rows in the partitions to see how performance changes. That can be simulated by creating copies of the tables or by creating a partition function on one table with deliberate skewness and changing what ID you filter by. If you take what results in good enough query performance and verify that you won't have any issues with loading data then you should be good.

The rowstores aren't relevant to the question, or rather, they're a totally different question. Partitioning is not the right tool to improve performance on rowstore query. I've seen performance gains on systems just by partitioning columnstore tables and leaving the rowstore tables alone.


Vertica documentation about LONG datatypes, in either version 7.1 or 8.0 states:

Notes
For optimal performance of LONG data types, HP Vertica recommends that you:

Use the LONG data types as storage only containers; HP Vertica does not support operations on their content.
Use the VARBINARY and VARCHAR data types, instead of their LONG counterparts, whenever possible. The VARBINARY and VARCHAR data types are more flexible and have a wider range of operations.

VARCHAR datatype supports up to 65K bytes long strings. You could use that instead, if the data are not longer.


How expensive are SELECT FROM LIKE statements from a design perspective?

If you are not able to fine tune the filesystem (for instance, by using a smaller block size), and you really must use a database, I'd suggest the following readings:

http://en.wikipedia.org/wiki/B-tree
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

The first will explain the data structure most commonly used by indexes, the B-Tree. The second explains how MySQL uses the B-Tree. The third will tell you about the command EXPLAIN SELECT ..., which is how MySQL describes the query plan (it will tell you which (if any) index it is using, if it is doing table scans -- which you must avoid at all costs).

To create an optimized index, you should first think about the structure of the query (or queries) you will need. For example, it might be something like: select content from files where firstParameter = XXX and secondParameter like 'xxx%'.

You should analyze the cardinality of each column (ie, how many different values can the column have).

You chose the columns with highest cardinality to be the first in an index, leaving those with lower cardinality to the end. Example: suppose you have 2M rows and firstParameter is a number between 1 and 1M, randomly distributed, and secondParameter is the full name of the owner of the file. In such a situation, you want the index (firstParameter, secondParameter), in this very order, since the clause firstParameter = XXX will leave you, in average, with only 2 rows. The cardinality of secondParameter, on the other hand, is much lower: there are much less then 1M possibilities for people's names. Therefore, if your index is (secondParameter, firstParameter), the query where firstParameter = 1 and secondParameter like 'bruno%' will first look for every row in which secondParameter begins with bruno (which will be likely tens or hundreds of thousands), and only then it will look for the other condition.

Also, note that the index is used from left to right. That is, if you have 3 columns, A, B and C and you index (A, B, C), the index will be mostly useless in a query such as where A = 1 and C = 2. It will likely be used to find rows matching A = 1, but after that every row will be checked for C = 2. If most of your queries are like that one (and some might specify B as well), than your index should be (A, C, B).

Finally, note that like 'xxx%' can use an index, while like '%xxx' (or like '%xxx%') cannot. This is, again, because the index is read from left to right. To match xxx%, it knows where to start looking; to match %xxx it must check every single row.

All that said about indexes, I'd strongly suggest you to rework your criteria so that you have something more structured. As you said, you could try to precompute something.

There are other considerations, such as the size of the content. If you can make it fit in under 8KB (which is like 3000 characters if you use UTF-8), then InnoDB will store the data in the same page as the primary key; otherwise, it will store the data elsewhere. If you query by primary key, in the first case you have a single read operation; if you query by another index, in the second case you have 3 read operations: one to find the primary key of matching row, one to find the row by primary key (to read the address of the data) and one to read the data.

Oh, check the amount of RAM of your server. Ideally your data (or at the very least your indexes) should fit in the RAM.

By considering all these points, you should have no problem at all: I don't know the hardware of your server, or its load (since you said it is shared), but 800k rows is close to nothing if you fine tune your indexes; I'm very far from expert and, by doing all the above things, I work daily with (very optimized) tables with 10M, some 100M rows, and the queries are ultra-fast.

I hope that helps. Once you have your table(s), you could ask another question showing the create table statement and describing a bit about your data (sizes, cardinalities, etc) and the select queries you will use, so someone could help you to create an optimized index.


Log debug messages in a SQL CLR C# Stored Procedure

Why not just execute xp_logevent via SqlContext.Pipe.ExecuteAndSend() in your catch block?


Is there any good tool for making Database Design and Prototypes?

I started using NORMA (link fixed Oct 2011) which is useful to capture the relationships and constraints in plain English. See Object Role Modelling too.

This will generate XSDs and SQL scripts.

I hope never to see an ERD ever again...


Upgrade from SQL Server 2008 R2 Enterprise to SQL Server 2016 Standard Edition?

A quick Internet search for 'sql server restore enterprise to standard' indicates this is 'theoretically' possible if you're not using 'Enterprise' features.  The only way you'll truly know is to actually attempt the restore of a backup from SQL Server 2008R2 Enterprise to SQL Server 2016 Standard.

Downgrade from SQL Server Enterprise Edition to Standard Edition

Migrating a database from Enterprise to Standard Edition


MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

Very few users use VIEWs.

The typical approach is to ALTER the table to make the desired schema changes.

pt-online-schema-change is the tool of choice if ALGORITHM=INPLACE is not available for the desired change.


OPTION (RECOMPILE) is your friend here. SQL Server has no clue what the value for @i will be at execution time, so it compiles a plan that will work for any parameter value.

Forcing recompile will override that behaviour and produce a plan with a constant scan.

Example:

DECLARE @i int = 1;
SELECT *
FROM master.dbo.spt_values
WHERE @i = 0
OPTION (RECOMPILE);

If you capture the actual execution plan, it will contain only a constant scan. Without OPTION (RECOMPILE) it contains the regular plan.


Should I use an index or a hash function on a primary key if I have 100000 rows of data?

With 100k records it shouldn't matter.  Even with 100M records it probably won't matter.  I've had queries on tables with billions of rows using good old traditional indexes perform just fine.

It's all about how much memory you have and how fast the disks are.

Granted you'll probably get some more specific information if you tell us what database you are using and provide more specifics.


Imposssible to expand a database in SQL Server Management Studio

Open a new query window and run SELECT * FROM sys.databases.  See if the database in question is listed in the results.

If it is, see if you can access it by running USE MyDatabase.  If that works, see if you can pull up a list of tables by running SELECT * FROM sys.tables.

You may want to run DBCC CHECKDB MyDatabase and see if any problems are detected.


How do I deal with query getting slower when a select parameter is not specified inline?

If you look at the two execution plans you will see that the estimated cardinality is different in each case.  When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path.  When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very unpopular accountId so produces a 'best guess' plan.  Unfortunately for your situation this is awful.

Stored procedures (SPs) would be good.  Then you can put all the query hints you want in there.  Also use WITH RECOMPILE to get around parameter sniffing issues.  If you have good inexes already, and you don't have access to the source SQL or can't use SPs, Plan Guides may be a good option.  If you end up using them be sure to schedule a review every so often to ensure the chosen plan is still optimal.


How do I test if MySQL "require secure transport" is working?

Just try to connect in a non-secure way and be sure it doesn't work.

If you are not sure wether you are using SSL or not to connect, then yes, the simplest way is using the command line client:

mysql --ssl-mode=DISABLED


Have you considered modeling where contact info exists independent of type. Could a contact be used for defense counsel and latter for vendor?

Assuming you are trying to fix the data and hide it from the program might i suggest the following:

Create a generic contact table and import all contacts here
Create bridge tables like tblDefenseCounselContacts with (oldID,ContactID)
View to replace tblDefenseCounsel and triggers as needed.
repeat for other tables.


Sqlite Affinity for hashvalues with certain size

You can use BLOB(32) as data type.

SQLite does not limit the size of text or blob values, and never allocates more storage than actually needed. The number is pretty much ignored by SQLite, and its primary purpose is to serve as documentation for you.

If you want to enforce the length, add a constraint: CHECK (length(my_column) = 32).


Just in case someone else comes looking for the same type of information. I decided that I'd like to import only a subset of wikipedia pages (the most popular subset) and built a couple of tools that build the list of most popular wikipedia pages (from the monthly logs) and filter the sqldump data files before import.


Differential database backups contains pages that have changed since the last full backup.
From MSDN:

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

You cannot restore a differential backup over anything other than the freshly restored full backup used as a base for the differential.

At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

If you attempt to restore a differential backup, without first restoring the Full backup used as the base for the differential, you'll receive an error message, such as

Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Regarding your question:

What is the difference between restoring a diff and restoring a diff with overwrite?

Presumably you are talking about the WITH REPLACE clause on RESTORE DATABASE? RESTORE DATABASE xxx FROM DISK = 'C:\yyy.bak' WITH REPLACE, RECOVERY; will only touch modified pages when restoring a differential backup.
As a test, I performed the following:
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'DiffTest')
BEGIN
    DROP DATABASE DiffTest;
END
CREATE DATABASE DiffTest;
GO

BACKUP DATABASE DiffTest
TO DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH INIT;
GO

USE DiffTest;
GO
CREATE TABLE dbo.SomeDDL
(
    ID INT NOT NULL
);
INSERT INTO dbo.SomeDDL (ID)
VALUES (1);
GO

BACKUP DATABASE DiffTest
TO DISK = 'D:\SQLServer\Backups\DiffTest_DiffBackup.bak'
WITH INIT
    , DIFFERENTIAL;
GO

USE master;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH REPLACE
    , RECOVERY;
GO
/*
    This select will fail because the dbo.SomeDDL table does not exist.

    Msg 208, Level 16, State 1, Line 5
    Invalid object name 'dbo.SomeDDL'.
*/
USE DiffTest;
SELECT *
FROM dbo.SomeDDL;
GO

USE master;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH REPLACE
    , NORECOVERY;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest_DiffBackup.bak'
WITH REPLACE,
    RECOVERY;
GO

/*
    This select runs because the dbo.SomeDDL table has been restored from the differential backup.
*/
USE DiffTest;
SELECT *
FROM SomeDDL;

The above code does the following:

Create a database
Take a backup of that database
Add a table and a row into that table
Take a differential backup of the database.
Restore the full backup.
See if the dbo.SomeDDL table exists (it doesn't, and an error is shown)
Restore the full backup.
Restore the differential backup.
See if the table and row exist - they do.

This proves restoring a differential backup will always replace the pages in the database with those contained in the backup file.


Recommendations on how to organize Queues in Service Broker

This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:

Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to maintain, but I think you should use some sort of template/code generation when creating SSB activated process, since typically much is cookie-cutter code and little is specific to service business logic.

However I can see several potential problems with a single queue, and they're all the kind of problems that manifest only when things are already going bad (ie. under stress/load):

latency and processing serialization. With only one queue is possible to plateau the max_queue_readers at max and have messages which require little processing sit in the queue waiting their turn to be picked up. Separate queues allow low latency services to drain their queues fast, while high load queues churn at their own (slower) pace.
a spike of messages for any service affects all services. Same as above, but for spikes. A dump of a high number of messages (eg. some manual 'oop we need to reprocess this 1MM items') will cause all the other services on the same queue to wait until the spike is drained before they get access to their messages (is a bit more complex due to conversation group locking, but ultimately processing is FIFO).
avoidable visits into the ugly land of large queues. If big-queue problems occur on your RAM/IO/CPU/activate code at N messages, one-queue-for-all-services will reach N faster/more often than one-queue-per-service.

In general my recommendation is to have one-queue-for-each-service.


Concatenation, with separators, where any combo of input fields might be null

Does this do what you need?

select coalesce(city + ', ' + state,
                city + ', ' + country,
                state + ', ' + country,
                country,
                state,
                city) as location

If you are 100% sure that the following holds, too (if there are constraints that make it certain):

  I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

then the last 2 parameters can be omitted as well:

select coalesce(city + ', ' + state,
                city + ', ' + country,
                state + ', ' + country,
                country)


I thought in MySql up-to 255 chars with a varchar it is the number of characters you store plus 1 for length. So if you  put a value that's 10 characters long, that'll be 11 bytes on disk.

UPDATE
Found a link to the documentation to back that up. First table demonstrates the difference between char and varchar: https://dev.mysql.com/doc/refman/8.0/en/char.html


Postgres Query not using Index but faster with Index

It seems that PostgreSQL is overestimating the cost of an index scan, which leads it to prefer a hash join over a nested loop join.
There are two parameters that tell PostgreSQL about the hardware and influence its estimate of the cost of an index scan:

random_page_cost: the greater that is compared to seq_page_cost, the more expensive PostgreSQL will estimate an index scan's random I/O compared to sequential I/O. So you can lower that parameter to encourage index scans.

effective_cache_size: this tells the optimizer how much memory is available for caching data. If the value is high, it will assume that indexes are cached and price index scans lower.

Perhaps adjusting these parameters will change PostgreSQL's mind here, although the cost estimates are suspiciously far apart.


you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this SQL Fiddle

create table table1
(
register_time date,registered int
);

insert into table1 values('2015/03/31',10);
insert into table1 values('2015/03/31',20);
insert into table1 values('2015/03/21',30);
insert into table1 values('2015/02/01',15);
insert into table1 values('2015/02/01',1);
insert into table1 values('2015/02/02',25);
insert into table1 values('2015/01/31',11);

SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP


Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

This seems to work only through a JDBC connection. Your SELECT works with Oracle, PostgreSQL and DB2 when using a Java/JDBC based query tool.

Using the native tools this statement throws an error:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> SELECT  {fn concat ('foo', 'bar')} from dual;
SELECT  {fn concat ('foo', 'bar')} from dual
        *
ERROR at line 1:
ORA-00911: invalid character

The same is true for PostgreSQL:

psql (9.1.1)
Type "help" for help.

postgres=> SELECT  {fn concat ('foo', 'bar')};
ERROR:  syntax error at or near "{"
LINE 1: SELECT  {fn concat ('foo', 'bar')};

and DB2:

(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0

db2 => SELECT  {fn concat ('foo', 'bar')} from sysibm.sysdummy1;
SQL0104N  An unexpected token "SELECT  {" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".
SQLSTATE=42601


The catalog view sys.all_objects won't show nonclustered indexes.  Here's the query you should be using:

select
    object_name(object_id) as table_name,
    name as index_name
from sys.indexes
where name = 'IX_Children_ParentID'

Or to get all of the indexes for that table:

select
    object_name(object_id) as table_name,
    name as index_name
from sys.indexes
where object_name(object_id) = 'Children'


Where can I find a good tutorial or book for various MySQL performance guidelines?

I read these:

MySQL High Availability
High Performance MySQL

They are good enough.


Temporal tables extension error with array columns

The problem is versioning.c:412:

 errmsg("column \"%s\" of relation \"%s\" is of type %s (%i) but column \"%s\" of history relation \"%s\" is of type %s (%i)",
                NameStr(attr->attname),
                RelationGetRelationName(relation),
                format_type_with_typemod(attr->atttypid,
                                                                 attr->atttypmod),
                attr->attndims,
                NameStr(history_attr->attname),
                RelationGetRelationName(history_relation),
                format_type_with_typemod(history_attr->atttypid,
                                                                 history_attr->atttypmod), history_attr->attndims)));

When I changed to show attndims, result was:

ERROR:  column "directories" of relation "test" is of type text[] (1) but column "directories" of history relation "test_history" is of type text[] (0)

I found the problem is in heap_openrv (PostgreSQL function) does not correctly fill this field for tables created by LIKE. I do not know how to repair it. But I found a solution:

CREATE TABLE test_history (
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

After that:

CREATE EXTENSION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TRIGGER
INSERT 0 1
UPDATE 1
 id | a | directories |                            sys_period
----+---+-------------+-------------------------------------------------------------------
  1 | 1 |             | ["2017-02-07 15:12:16.383449+02","2017-02-07 15:12:16.387419+02")
(1 row)


MySQL column defined with NOT NULL is allowing nulls

What version of mysql is this?

What mode are you running in?

SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;

(This should be run in the context of your application, just in case it is changing it).

MySQL is documented thus: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for
the column, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

My own testing fails to duplicate your issue

mysql> CREATE TABLE `my_table` (
    ->   `entry_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `address` varchar(512) NOT NULL,
    ->   `follow_up_to` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`entry_id`),
    ->   KEY `follow_up_to` (`follow_up_to`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL);
ERROR 1048 (23000): Column 'address' cannot be null
mysql> INSERT INTO my_table (follow_up_to) VALUES (NULL);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1364 | Field 'address' doesn't have a default value |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      537 |         |         NULL |
+----------+---------+--------------+
1 row in set (0.00 sec)

The fact the old data had nulls shouldn't matter. The Alter table should have 'truncated' the nulls into empty strings

mysql> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(512) NOT NULL,
  `follow_up_to` int(11) DEFAULT NULL,
  PRIMARY KEY (`entry_id`),
  KEY `follow_up_to` (`follow_up_to`)
) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=latin1
1 row in set (0.04 sec)

mysql> ALTER TABLE my_table MODIFY address VARCHAR(512) NULL DEFAULT NULL;
Query OK, 1 row affected (0.76 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 | NULL    |         NULL |
|      537 | NULL    |         NULL |
+----------+---------+--------------+
3 rows in set (0.04 sec)

mysql> ALTER TABLE my_table MODIFY address VARCHAR(512) NOT NULL;
Query OK, 3 rows affected, 2 warnings (0.83 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'address' at row 2 |
| Warning | 1265 | Data truncated for column 'address' at row 3 |
+---------+------+----------------------------------------------+
2 rows in set (0.04 sec)

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 |         |         NULL |
|      537 |         |         NULL |
+----------+---------+--------------+
3 rows in set (0.04 sec)

mysql> INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL);
Query OK, 2 rows affected, 2 warnings (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> SELECT * FROM my_table;
+----------+---------+--------------+
| entry_id | address | follow_up_to |
+----------+---------+--------------+
|      535 |         |         NULL |
|      536 |         |         NULL |
|      537 |         |         NULL |
|      538 |         |         NULL |
|      539 |         |         NULL |
+----------+---------+--------------+
5 rows in set (0.05 sec)


Is it better to have multiple tables with the same primary key or one big data table?

As per mustaccio's comment, when you have the same PrimaryKey defined in each of your Tables, that then guarantees a one-to-one relationship is enforced between each Table. In other words, based on the schema of your Tables provided in your question, you won't ever be able to have more than one Animal at one Event, or more than one Restriction for a specific Animal, etc. They all share the same unique Location ID.
If that meets your needs then your design is ok.
But if you want to be able to have multiple Animals at multiple Events with varying Restrictions for different locations etc, then you might be better off with the following design:
Add a fourth Table called Locations with the Location ID PrimaryKey.
Remove the Location ID PrimaryKey (and unique constraint) from all the other tables and instead create a ForeignKey relationship between it and your Locations table on Location ID.
Add a new unique PrimaryKey field to the Events, Animals, Restrictions tables with the appropriate names (e.g. Event ID for the Events table, Animal ID for the Animals table, etc).
And to directly answer your main question, it's better to keep these as separate Tables. That is called database normalization and is important for query efficiency and elimination of unnecessary data redundancy. In general, separation of concerns should be split into separate Tables.


Good idea to have an index on datetime column?

Adding an index is definitely a good idea for this.  However, DATEPART(YEAR, datecolumn) = 2012 isnt' sargable so it will still do a scan of the index.
If you want it to use the index then you will need to do:
WHERE dateColumn >= '1/1/2012' AND dateColumn < '1/1/2013'

Please note the placement of the >= and the < signs to get the correct bounding box.


Why are bulk multi-column key queries so slow in MySQL?

With "row constructors", you might get an optimization:

WHERE (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

But...  In old versions, that would work, but lead to a table scan.  I can't say specifically about the version you are running.

When you have this pair of indexes:

UNIQUE(col1, col2, col3)  -- (or plain INDEX)
INDEX(col1, col2)

there is no need for the latter, since the former can handle any queries that need it.

Perhaps the optimal way to write your query is

WHERE col1 in ('v1', 'v2', ...)
  AND (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

With that, it will use any index starting with col1 as a crude filter, then use the other part for the rest of the filtering.

Re "convert to an in method" --  MySQL started out as a clean and mean database; it did most of what anyone needed and did it reasonably well.  That was 90% of the development.  We are now into the other 90% of the development -- the "long tail".  Quite possibly some list somewhere includes "convert to an in method".  If so, it is being prioritized along with the thousands of other rare and obscure optimizations.  Feel free to file a 'feature request' at bugs.mysql.com; that is the way to add it to the list, or bump it up in priority.


Availability Group configuration & licencing

In order to redirect reporitng app or application with only read only queries you need to specify read only intent in application connection string (web.config or app.config in case client application is .NET)

The below is excerpts from SQL Server BOL

  The application intent connection string property expresses the client
  application’s request to be directed either to a read-write or
  read-only version of an availability group database. To use read-only
  routing, a client must use an application intent of read-only in the
  connection string when connecting to the availability group listener.
  Without the read-only application intent, connections to the
  availability group listener are directed to the database on the
  primary replica."

Regarding your second question, if network throughput between two DC is OK you should be fine.

As long as any secondary replica is only solely used for DR (disaster recovery) and does not perform any work like read only request or backup you do not need to license it.


That's new with 8.0.13:

  A new Performance Schema stage, waiting for handler commit, is available to detect threads going through transaction commit. Thanks to Facebook for the patch. (Bug #27855592, Bug #90417)

https://bugs.mysql.com/bug.php?id=90417

Suggest you file a comment with that bug report; could be that it caused a regression.


Switching a partition on a table with one to many relation

You mentioned you added datetime to the PK, but it was not partitioned. You also need to specify the partition scheme when you recreate the PK index so that it is aligned.

Also, note there are restrictions on tables with foreign key relationships. See Transferring Data Efficiently by Using Partition Switching.

  Unfortunately, SQL Server didn't ask anything about index creation on partition or filegroup. I performed that with visual features.

The GUI is nice for some simple quick tasks but not for partitioning a table with billions of rows. Use T-SQL so that you know exactly what is going on. Large tables are unforgiving.


Your subquery is not correlated to the outer query, so it multiplies the rows by the number of rows it returns. Also, in my opinion, using the same table names multiple times without aliases at multiple query levels is bad practice, as it may cause some unexpected "surprises". Your query should be something like this:

SELECT
  i1.ID_INCIDENCIA AS Incidencia,
  i1.DESCRIPCION,
  s1.FECHA,
  s1.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA i1,
  SITUACION s1,
  (SELECT i2.ID_INCIDENCIA as Inicio_ID_INCIDENCIA, s2.FECHA AS Fecha
    FROM SITUACION s2, INCIDENCIA i2
    WHERE s2.ID_ESTADO = '1' AND i2.ID_INCIDENCIA = s2.ID_INCIDENCIA) Inicio
WHERE
  s1.ID_ESTADO = '3' AND
  i1.ID_INCIDENCIA = s1.ID_INCIDENCIA
  AND Inicio.Inicio_ID_INCIDENCIA = i1.ID_INCIDENCIA
ORDER BY
  i1.ID_INCIDENCIA ASC

Added aliases everywhere + ID_INCIDENCIA inside the subquery, so it can be joined to the outer query based on that column.


Objective, SQL Server 2005 and exporting data using **bcp**

Right.

It would appear that free-form text fields in several rows (and varied columns) in two of the tables had embedded newline/carriage return characters, which then messed up the field-count (obviously).

I now am just exporting 5 (and 3, respectively) columns from those tables, and the problem is gone away.  Feeling kind of silly after this.

Thanks everyone for trying to help.


SELECT
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE,
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

or

SELECT
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME,
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 2,3;


mysql innodb space x did not exist in memory

These messages appear to occur when InnoDB hasn't cached a "tablespace object," for a table, which from what I can tell is an internal structure containing the information about the tablespace (.ibd) file for a table.  Under normal conditions all of these would be cached at server startup (and new ones added when tables are created).

So, this suggests you've got data dictionary issues (corruption in ibdata1?) but the message does not, itself, appear to be a fatal one.

The message comes from dict_load_table() inside innobase/dict/dict0load.c, in response to a FALSE returned from a call to fil_space_for_table_exists_in_mem() in innobase/include/fil0fil.h.

Comments there:

/*Returns TRUE if a matching tablespace exists in the InnoDB tablespace memory
cache. Note that if we have not done a crash recovery at the database startup,
there may be many tablespaces which are not yet in the memory cache.
@return TRUE if a matching tablespace exists in the memory cache */

This suggests that those messages, while not normal under typical operating conditions, are not entirely unexpected if your server is in the middle of InnoDB crash recovery.

The next thing that happens is that InnoDB tries to open the tablespace files, and since there are no messages in your log indicating that this next step isn't succeeding, it seems safe to conclude that the tablespace (ibd) files are at least accessible and the problem will be elsewhere.

All of this suggests that there will have been previous messages and later messages in the log that will give a better indication of the nature of the problem.


A CDBMS is a specialized database for handling queries like "show everything that is related to x". Unless that is what you want (and unless you don't want all the features of a traditional RDBMS like MySQL), you don't want a CDBMS.


The session(s) using the temporary table can be queried as:

select sid from v$lock where type = 'TO'
and id1 = (select object_id from dba_objects where object_name = '&temp_table_name');

Then you can kill these sessions or have them release the locks gracefully.


SELECT ISNULL([State],'Total'), SUM(Total), SUM(GoodEggs), SUM(BadEggs)
FROM Detail
GROUP BY
GROUPING SETS (([State]), ());

Although you should probably just do GROUP BY [State] and provide the total row and right column in your application.


SQL server ignores trailing spaces when performing comparisons.  So ' ' is considered the same as ''.  The former is the latter with trailing spaces.


How to create two same name databases in two different MySQL users?

Users on MySQL are sharing the same context in terms of databases. So if you are creating a database for one user, it will be also visible to user2.

You could solve this issue by prefixing your tables for each user, e.g. abc_item1 and setting correct privileges via grant for it -- so only one user is allowed to use this table.

Another way would be to just create different databases for each user -- which is I think the MySQL way of doing such things.


member access to couchDB browser gui fauxton

In CouchDB 3.0.0, the /_all_dbs endpoint became admin-only. This has the side-effect of Fauxton, the “Browser GUI”, not loading properly for non-admin users, as it starts out by reading /_all_dbs.

The release notes have a section that go into the changes.

This is the code change: https://github.com/apache/couchdb/issues/2576

You can get the previous behaviour by setting the config variable [chttpd] admin_only_all_dbs to false, but that allows even unauthenticated users to get a full list of your databases.


MongoDB: The collections metadata lock is taken

Turn off the balancer first, you are likely seeing it trying to balance the collections while you are attempting to split it.  You can do this with the sh.stopBalancer() command.  If you take a look at this answer I go into the necessary steps for pre-splitting in more detail.


If n.newState=0 is always true then just remove it from your where statement.

or if everything you want to return needs to have that true then just change your first OR to a AND.
Then it will check to make sure that it is true and one other thing in your where statement is also true.

WHERE n.newState=0 AND ([all your other OR conditions])


How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file?

Evidently mysql_config_editor set spawns a shell subprocess, so to keep the environment variable available to it, you have to set it with export.  This works:
$ export MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password:

$ ls ~/.mylogin.cnf
ls: cannot access '/home/username/.mylogin.cnf': No such file or directory

$ ls ~/.config/mylogin.cnf
/home/username/.config/mylogin.cnf


The following would do it. SQL Fiddle

WITH T2
     AS (SELECT pro_id,
                COUNT(pid) AS [noof pids]
         FROM   Table2
         GROUP  BY pro_id),
     T3
     AS (SELECT pro_id,
                COUNT(code) noofcodes
         FROM   Table3
         GROUP  BY pro_id)
SELECT T1.pro_id,
       COALESCE([noof pids], 0) AS [noof pids],
       COALESCE(noofcodes, 0)   AS noofcodes
FROM   Table1 T1
       LEFT JOIN T2
         ON T1.pro_id = T2.pro_id
       LEFT JOIN T3
         ON T1.pro_id = T3.pro_id


The account which runs the SQL Server instance doesn't need admin rights on the computer and neither does the account which will manage the SQL Server instance.  If you plan on creating databases within the instance you'll need to make sure that you have sysadmin rights within the instance but this isn't related to the OS rights at all.  The person doing the install will need to make sure that they give you the rights within the database instance that you need before they leave.

The person doing the install that has admin rights will need to enable TCP/IP on the instance as that does require admin rights to do.