Inaccurate 'Actual' row counts in parallel plan This is a purely academic question, in so much that it isn't causing a problem and I'm just interested to hear any explanations for the behaviour.

Take a standard issue Itzik Ben-Gan cross-join CTE tally table:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TallyTable]
(
    @N INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
    WITH
    E1(N) AS
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    )                                       -- 1*10^1 or 10 rows
    , E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
    , E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
    , E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows

    SELECT TOP (@N) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8
)
GO

Issue a query that will create a 1 million row number table:

SELECT
    COUNT(N)
FROM
    dbo.TallyTable(1000000) tt

Take a look at the parallel execution plan for this query:

Note the 'actual' row count prior to the gather streams operator is 1,004,588. After the gather streams operator the row count is the expected 1,000,000. Stranger still, the value is not consistent and will vary from run to run. The result of the COUNT is always correct.

Issue the query again, forcing non-parallel plan:

SELECT
    COUNT(N)
FROM
    dbo.TallyTable(1000000) tt
OPTION (MAXDOP 1)

This time all operators show the correct 'actual' row counts.

I've tried this on 2005SP3 and 2008R2 so far, same results on both. Any thoughts as to what might cause this?

Rows are passed across exchanges internally from producer to consumer thread in packets (hence CXPACKET - class exchange packet), rather than a row-at-a-time.  There is a certain amount of buffering inside the exchange.  Also, the call to shut down the pipeline from the consumer side of the Gather Streams has to be passed in a control packet back to the producer threads.  Scheduling and other internal considerations mean that parallel plans always have a certain 'stopping distance'.

As a consequence, you will often see this sort of row count difference where less than the whole potential rowset of a sub-tree is actually required.  In this case, the TOP brings execution to an 'early end'.

More information:

Parallel Execution Plans Suck
Understanding and Using Parallelism in SQL Server.


Amazon's Aurora PostgreSQL I've created few different instances, one instance is of Amazon Aurora PostgreSQL and other instances are of PostgreSQL, MySQL etc. How do I find our the Postgres instance is running within an AWS Aurora cluster or not, from CLI? When I say from CLI this means terminal not AWS CLI.

Thanks.

I'd recommend checking for the existence of the rdsadmin database (to be precise, this database exists for any RDS instance, not just an Aurora-based one).   One way to check would be to use the psql command-line tool to output a list of databases.  Just replace [AuroraClusterEndpoint] and [UserName] in the following

psql -h [AuroraClusterEndpoint] -U [UserName] --password -l

Of course, this assumes you haven't created a database named rdsadmin on your non-RDS Postgres cluster.

You could also use the SHOW ALL command within psql to check for the existence of an RDS-based run-time parameter:

With both solutions, just use whatever scripting tool you're comfortable with (Powershell, Bash, etc...) if you want to programmatically parse out the results


Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes? Why is it, when analyzing a query through Database Engine Tuning Advisor, statistic recommendations disappear after some or all of the suggested indexes are implemented?

For example, Query X suggests 5 indexes, and 5 sets of stats.  After I implement the indexes I rerun the analysis (just curiously seeing what the efficiency improvement comes from, indexes or stats) and notice that not only are the indexes gone from the recommendations, but the statistics are also.

Obviously, these aren't the stats that are inherently a part of the index that was built.

After I implement the indexes

I highly recommend you to read Aaron's excellent blog post:

Don't just blindly create those "missing" indexes!

There are stats automatically created with indexes, so there's no statistic to suggest when an index exists (because by definition that statistic is already there). DTA is suggesting both an index and a stat - an index is usually best, but most costly in terms of maintenance; a stat is second-best, and can help with cardinality estimates, but not with things like seeking or sorting.  Not sure why it's obvious these aren't the same stats - but it could also be that those queries have benefitted enough from the indexes you created that there is no further benefit to derive from adding stats.

This answer consists of comments converted into a community wiki answer


What all are the recoverable points of failure in an Oracle database? I am testing a product against an Oracle database for failure scenarios. The product talks to Oracle through a session and maintains a few tables. I want to make sure that whenever Oracle recovers from whatever errors it can recover, the product can recover too.

For this, I will be manually simulating failures and checking what happens. What are all the failure scenarios I should try?

Not really sure what you are asking for here.. If your database is backed up correctly, then there is no unrecoverable points of failure. However most "failures" will result in your session being terminated or at the least being thrown an error. In most cases you wont want to deal with specific errors, but rather simply rollback the transaction ( if possible ) and log the issue for inspection.

If you are testing a 3rd party application, and assuming your are on a standalone database, I'd just test it for session termination to see if it automatically reconnects itself, correctly cleans up the work it was doing, etc. You could also lockup its session I guess and see how it handles timeouts.

Sorry for the vague response, but the question is quite vague.


SQL Server Management Studio cannot Connect to Always On Availability Listener I am trying to connect to the AG Listener in SSMS.
I choose [Connect]
Then [Database Engine]
I type in the name of the listener I created.
SQLListener
[Windows Authentication]
Click [Connect]

After about 1-minute, I receive the following ERROR message

  TITLE: Connect to Server

  Cannot connect to SQLListener.

  ADDITIONAL INFORMATION:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)

  (Microsoft SQL Server, Error: 2)

  The system cannot find the file specified

I watched a video on YT where this guy runs through the creation of the AG.
He names his GROUP and his LISTENER
Identical names, and can connect to the Listener, through SSMS.
However, I cannot.
My Group is: SQLGroup
My listener is: SQLListener
(Are they supposed to be identical names?)

I wanted to make sure my Listener port was available and was able to be reached
so I ran the following on the primary SQL Core Server.
The listener is registered in DNS and in AD - Computers as SQLListener.

cmd = netstat -ano|fine "2433"
Returned
TCP    192.168.2.77:2433   0.0.0.0:0   Listening   252
I did an NSLookup on the SQLListener.

  CMD nslookup SQLListener
  Returned:
  Server: MasterDC.Domain.local
  Address: 192.168.2.88
  Name: SQLListener.domain.local
  Address: 192.168.2.77
  Everything is accurate.

Do I need to open the port 2433 on my work station to add the listener within SSMS?
What is it that I am missing here?

Wayne

When you configure your listener to use a non-default port (any port other than 1433), then you need to specify the port number in the server name when connecting in order to connect successfully.

This is detailed in the documentation here.


SPN for Availability group(AG) listener for SQL Server 2016 Requesting some inputs..
We have a 2 node SQL 2016 Availability group configured and apps currently use primary replica for connection. We are now planning to use listener instead. Questions is, how to make sure we are using kerberos authentication for application connectiions?. Is it mandatory to manually register SPN for the listener? or is it automatically created?. is this a one time activity?. How to make sure SPN is registered automatically for the listener when the AG restarts or failover to second node?. Appreciate all the responses.

If you configure the listerner with a domain admin account it should be automatic but usually it is not enough.
It's better to have full control of what you are doing.
First thing to remember is that an spn is a registration done on the domanin compunter account or domain user account that is running sql server service. While it is the same for both nodes, you have to do only one registration.
Check existing spn for your system:
setspn -L domain\sqlserverservicelogin

or
setspn -L domain\clusterservercumputeraccount$

Then you can register a listener in this way:
setspn -S MSSQLSvc/listener_name:port domain\sqlserverservicelogin
setspn -S MSSQLSvc/listener_name.domain.local:port domain\sqlserverservicelogin

It's better to register both listener name and listener FQDN to be sure to match less precise application configurations.
The same commands with computer accounts:
setspn -S MSSQLSvc/listener_name:port domain\clusterservercumputeraccount$
setspn -S MSSQLSvc/listener_name.domain.local:port domain\clusterservercumputeraccount$

To register spn you have to be domain admin.
A Microsoft tool that can help you if Microsoft Kerberos Configuration Manager for SQL Server:
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Once everything is on place you don't have to do anyting else unless you change something like serfice users, listener names and/or ports


How do I back up On Premise Microsoft SQL Server 2008 to Azure cloud I have a 50 GB hosted SQL Server database at SoftLayer.

I want to do backups to cloud storage, as the SoftLayer managed SQL Server backups are ridiculously priced. Eventually we will totally move away from their hosting model to pure cloud, but that is a topic for another question.

For the time being, I want to automate backup of "on premise" SQL Server databases to a cloud storage provider. Ideally, I would stay with Microsoft Azure, but I am open to S3 or other options if they are available.

A SSIS package to move the data would be the route I'd go. This SO thread has many suggestions of solutions users tried apart from SSIS, incl 3rd party tools.


How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-? I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables which contained CLOB data could not be migrated using a single table to table insert query otherwise we would end up with dirty data. I am able to do the inserts by dynamically generating a single insert statement per row and running thousands of lines of insert statements. The problem is I've now come across a table which contains over 300k records with CLOB data in them. The one record at a time insert is taking an extremely long time and may at this rate run over 24hrs which is unacceptable.

What is my best option for migrating such a large amount of CLOB data from Oracle? Should I use the bulk tools like BCP, BULK INSERT or OPENROWSET?

Edit/Update: I have since learned that my troubles are most likely due to the UTF-8 encoding at the source Oracle system. Both methods I've used for migrating both CLOB and BLOB data have resulted in mismatched rows or missing rows.

UTF-8 has some known issues with SQL Server. The ones in particular I'm dealing with, Linked Server OPENQUERY and SSIS Project deployments, are both fixed in SQL Server 2019.

You could check out - https://www.attunity.com/products/replicate/attunity-replicate-for-microsoft-migration/

"Attunity Replicate for Microsoft Migrations is a special, no cost offering for Microsoft customers to help migrate data from popular databases to the Microsoft Data Platform."


Mysql datetime field partial match Currently I am designing a mysql table structure that will hold a couple of million rows.
I want to search in it on datetime field by date year and so on but the date will be entered by user so it may not be full date all the time. The field is indexed.
Will it be fast enough to use wildcard on the datetime field. Like so

SELECT `id`, `moment`, `object_uid`, `content` FROM `events` WHERE moment like '2015%';

Will it use the index?

There is an option to determine whether or not the user have entered year or month and year and so on but I prefer to let MySQL do the job.

SQLfiddle

It's hard to say if your query will use the index or not because that will be based on what MySQL's optimizer decides is best based on your data distribution and statistics.

In any case, a wildcard at the end is Sargable, so at least the wildcard isn't what's preventing your index usage.
See for example this post by Brent Ozar or this by Rob Farley

On the other hand, if you search for year only my guess would be the query won't be selective enough to use an index, year and month might but that's hard to tell.

See my reply to this question for an explanation: Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?
It's a SQL Server question but the same principle applies.

  Since SQL server reads entire pages, and not records, your query (as
  indicated by statistics) needs to be selective enough for the index to
  be  used, if SQL server estimates that it's going to have to read
  every page  anyway it will go for a table scan.


What reasons are there to stop the SQL Server? All I've read about is how potentially damaging it is to stop SQL Server because it creates a cold cache and sucks up memory. So why would someone want to stop the SQL Server? If you can provide any links to articles so that I can read more into this I would really appreciate it!

This question was posed by my teacher. Unless it's some trick question, it has me absolutely stumped. His exact question was:

  Conduct research using the Internet and learn why someone would want to stop the SQL Server. Explain your answer.

This was in the context of us exploring how to use SQL Server 2008 R2. I'm not sure if he is asking for the obvious answer, or if there is something I'm missing.

Brent listed some invalid reasons for stopping the service, but there are valid reasons too:

Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
Windows patching


Concatenating Strings & Using FORMAT Together - NOT WORKING I'm literally pulling my hair out trying to use the FORMAT function to format a date into a simple string.  Please someone pull the pin and tell me what stupid thing I'm missing!

Works:

SELECT TOP 10 FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Works:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Does NOT Work:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10) FROM REV ORDER BY REV_ID DESC

I can't figure out how to simply concat a string onto the end of the output of FORMAT.

It doesn't work because you are attempting to concatenate the last two characters after specifying the column alias. Move the AS VARCHAR to after the + CHAR (10).

You currently have:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10)

when instead it should be:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') + CHAR(13) + CHAR(10) AS [VARCHAR]

P.S. Please note that I added square brackets around the column name alias. It is a good habit to get into doing that always. It clearly isn't always necessary, but is required when using spaces, most punctuation, etc. And it also avoids potential issues when using reserved keywords (such as VARCHAR, though that isn't causing any problems at the moment).


Postgres Join multiple table to replace id of parent table with value I have following tables:

dependency
===========
id  service  dependent_service  dependency_type
--  -------  -----------------  ---------------
1   9        2                  16
5   2        7                  15

dependency_type
===========
id  name
--  --------
15  internal
16  external

service
==========
s_id  s_name  description  s_type
----  ------  -----------  ------
2     abc     ABC          1
9     cde     CDE          2
7     xyz     XYZ          3

service_type
==============
id  name
--  ---
1   api
2   db

I am basically trying to replace the ids in parent table (dependency) with the name and value from the main table. Ex. replace dependent_service (2) with value (abc). Similarly for oter fields.
So my result table looks like this:

Result table
===============
r_id  r_service  r_dependent_service  r_dependency_type  r_service_description  r_dependent_service_description
----  ---------  -------------------  -----------------  ---------------------  -------------------------------
1     cde        abc                  external           CDE                    ABC
5     abc        xyz                  internal           ABC                    XYZ

I tried to JOIN all tables using WHERE clause, but its resulting duplicate rows.

Select * from dependency as d INNER JOIN dependencytype as dt ON d.dependency_type = dt.id INNER JOIN
(service as s INNER JOIN servicetype as
st ON s.service_type = st.id) ON 1=1

Can anyone please help me here. Let me know if you need more information.

This is what you want:

select *
from dependency as d
join service as s on d.service = s.s_id
join service_type as st on s.s_type = st.id
join dependency_type as dt on d.dependency_type = dt.id;

dbfiddle here.


Insert query optimisation - Oracle One of my developers is trying to perform insert into a table by selecting from a table.

Below is the query that was run. The query takes extremely long time for execution (4hrs odd).

Number of records on the table where it is getting imported is 1.2 million.

Database - Oracle 12c on Linux with archivelog mode on.

insert  /*+ parallel(24) */  into data1.frido_dev select  /*+ parallel(24) */  *
from DATA_MAIN;

How can the insert of such bulk volumes be optimized? Can we use append? Will that be helpful?

I'm not good at tuning queries, would be great if someone helps me out here.

Unless you have a special case, an INSERT with 1.2 million rows should be completed in seconds or minutes at most.

Yes, direct-path insert may significantly reduce execution time.

The above hint in INSERT has no effect without the necessary prerequisites.

Enable Parallel DML Mode

  A DML statement can be parallelized only if you have explicitly
  enabled parallel DML in the session or in the SQL statement. To enable
  this mode in a session, run the following SQL statement:

  ALTER SESSION ENABLE PARALLEL DML;

  To enable parallel DML mode in a
  specific SQL statement, include the ENABLE_PARALLEL_DML SQL hint. For
  example:

  INSERT /*+ ENABLE_PARALLEL_DML */ …

  This mode is required because parallel DML and serial DML have different locking, transaction, and
  disk space requirements and parallel DML is disabled for a session by
  default.

  When parallel DML is disabled, no DML is executed in parallel even if
  the PARALLEL hint is used.


MySQL/MariaDB Limitations we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated.

We're nearly like an hoster (excepted that we do not provide any file hosting). When you subscribe an hosting plan you often have 1 to X databases and that's what we want to do.

In our company we have 2 DBA: 1 says that we can't have more than 500 DB by instance (due to memory limits) and the 2nd one says that's there's no limitation (excepted FS limitations).

Could you help me to understand or give me some tips (because I'm a project manager and dev. solution need to be chosen and I not a DB expert).

Thanks

Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas as fast or as slow as if they were in the same schema). I've managed instances with hundreds of thousands of schemas, and the multiple directories were not the performance bottleneck. It is a pure identifier thing. While you can run into some issues, those are indirect to having more or less schemas: for example, in 5.6, a slave can apply changes in parallel only for rows from different schemas.

Why do I say that both are right, in a way? Because having too many objects in the database is a very common source of issues. When you have lots of databases, you usually have 10x or more the number of tables, and that may mean thousands of file descriptors open (that requires memory), thousands of data dictionary cache entries, millions of open table cache entries, etc., which can lead truly to a CPU and above all, memory, bottleneck.


Sync'ing MySQL stored procedures to Git Is there a decent way to sync MySQL database source code (ie: functions and stored procedures) to git?

I'd like to be able to develop stored procedures within my IDE and version control them in git so I can easily move them between servers, and identify changes to the code.  It would be great if I could use git-hooks  to auto update my stored procedures on push.  It seems like a reasonable thing to want to do, but I don't see anyone doing things this way.

I'd rather not attempt to store a long SP in a migration, or something like Liquibase.  Ideally, I'd like to store the pure SQL in my git repo.

Disclaimer: I'm a new DBA, so I may be missing something obvious.

I do this by putting each stored procedure/function into its own SQL file and in a folder and then executing them with a a recursive PHP script.

Example file procedure_name.sql would look something like this.

DROP PROCEDURE IF EXISTS procedure_name;
DELIMITER //
/**
Procedure Documantation
*/
CREATE PROCEDURE procedure_name()
BEGIN
   Code goes here
END//
DELIMITER ;

This also makes it easy to do a find and replace of column names etc.

The PHP code looks like so

foreach ($paths as $p) {
     if (strpos($p, '.sql') !== false) {
            echo $p . "\n";
            exec("mysql -u " . $user . "   -p" . $password . " < $p");
      }
 }

After making the changes one can either execute the sql file in MySQLWorkbench of run the PHP script. To sync them to another server you push them up with git pull them down and then execute the PHP script.

Do note that if you have functions they need to be created first.


How can I configure SQL Server 2012 to use multiple NICs? How can I configure SQL Server 2012 to use multiple NICs?

I've a server with 4 NICs. I want to use 2 NICs to query my databases, and 2 NICs to retrieve results from them.

I want them to work in load balancing mode and still continue to work if one nick is down.

It is possible?

As long as the OS is configured for NIC teaming SQL server will use the team.  SQL Server doesn't talk directly to the network cards, all the communications goes through the TCP/IP stack which is a part of the OS.  However the OS is configured to use the NICs will be how SQL Server uses the NICs.

To do what you describe will require doing NIC bonding (aka. NIC teaming).


how to convert my PostgreSQL database table into CSV? i need to convert my PostgreSQL table into csv, how to do this?
i have visited many sites they suggest the PostgreSQL command to convert the table into CSV but i always get the error message.

the query i tried was :

COPY persons TO 'C:\tmp\persons_db.csv' DELIMITER ',' CSV HEADER;

but PostgreSQL generates the error message as:

ERROR:  could not open file "/tmp/banke.csv" for writing: No such file or directory
********** Error **********

ERROR: could not open file "/tmp/banke.csv" for writing: No such file or directory
SQL state: 58P01

how to do this?
is there any other software or application that can convert the table into csv ?
if so then please suggest.
thank you all..

On Windows, PostgreSQL runs as "Network Service". "Network Service" will need write permissions to c:\tmp if you want it to write there.

Generally I recommend using psql's \copy command instead of copy, as it doesn't need superuser permission in the db, and can write wherever your shell user can write.

Open up cmd:

> cd c:\tmp
> set PGDATABASE=$nameOfYourDatabase
> set PGUSER=$pgUsername
> set PGPASSWORD=$thePassword
> psql

> \copy persons to 'persons_db.csv' with (format csv, header true);
> \q


Best index for jsonb in Postgres We have a table containing around 500k rows. The database table is supposed to grow to million of records.

This is how the table looks like:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And these are some of the queries we need to perform efficiently:

SELECT  "public"."influencers".*
FROM "public"."influencers"
WHERE (ig->'follower_count' IS NOT NULL)
ORDER BY (ig->'follower_count') DESC
LIMIT 9
OFFSET 0

SELECT *
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
LIMIT 9

SELECT SUM(CAST(ig ->> 'follower_count' AS integer))
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
  AND (ig->'follower_count' <= '10000')
  AND (ig->'follower_count' IS NOT NULL)

ig -> follower_count are numeric values.

I read that GIN indexes are mainly intended for searching through composite items (text) so I'm guessing the best index to use would be a BTREE. Would that be correct?

GIN indexes do not work with any jsonb operators EXCEPT ? ?& ?| @>. Clearly, your use of comparison operators >= and <= are not on that list. And all of those operators that it could help are also not on the list (meaning the index won't do anything) afaik.

That means needing indexed comparison operators, you'll need a btree.

CREATE INDEX ON public.influencers ((ig->'follower_count'));

All of that said, I think this is silly. Just put follower_count as a proper column on the actual table and enjoy the performance benefits.


The opposite of ordering by a column in SQL If you have a table and you'd like to sort by a column you can use order by foo to order the results by foo.

Is it possible to do the opposite, such that rows which have the same value for foo would be as far away from one another as possible?

I'm using Postgres 9.5 and would appreciate an answer that would be compatible with Postgres.

Assuming that we have an equal amount of each value that you want to sort (in "maximum distance" style), you could do the following:
[1] Create a "mapping table", that holds all distinct values that need sorting.
[2] Select the values from the mapping table in the order (ascending/descending) as often needed for representing the stored values.

Proof of concept code:

create table t_ (
  id_ serial ,
  num_ integer
);

-- Stick some grouped values into the table.
do $$
begin
  for outer_ in 128 .. 256 loop
    for inner_ in 1 .. 10 loop
      insert into t_ (num_) values (outer_);
    end loop;
  end loop;
end$$;  -- 1290 rows

When you run a select,

select * from t_ order by id_;

you will see that the values in num_ are grouped together.
Then, create a "mapping" table, populate it with DISTINCT values - in (ascending) order - from the original table.

create table map_ (
  id_ serial ,
  num_ integer
);

insert into map_ (num_)
select distinct num_ from t_ order by num_;
-- 129 rows

select * from map_;

Then, JOIN them, and use MOD() for getting the correct values in the output.
The last select has a column for values in the original order, and another column for the new ("maximum distance") order.

select t_.id_ t_id_, map_.num_ "new ordering", t_.num_ "old ordering"
from map_ join t_
  on ( map_.id_ = mod(t_.id_ , ( select count(*) from map_ )))
order by t_.id_;

I know that this does not solve the problem if there are unequal amounts of "original" values.  However, I think it is maybe a step in the right direction.


Include UDFs in replication publication on SQL Server 2012 When trying to replicate UDFs via snapshot replication on a working database I got an error. So I created a simpler database with only a single UDF to test that I could replicate UDFs, because I've been reading that there might be bugs that depend on how you write the SQL I created a simpler test.

I got the same error with this function, so should I assume that I can't replicate UDFs? I mean, I'm given an option to do exactly this. But even the simplest example fails. Am I doing anything wrong? The error is from the snapshot agent on the publisher's side.

This is the function:

CREATE FUNCTION test1
(
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT 1
    )
END
GO

The stacktrace is:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for UserDefinedFunction 'dbo.test1'.
Stack:    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.CreateMirrorCopy(String mirrorCopyName)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopyTransaction(SqlConnection connection)
   at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopy()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.FunctionDependencyNode.get_BaseDependencies()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.GetEnumerator()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.PerformArticleDependenciesAnalysis()
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoReplicationDependenciesAnalysis(Scripter scripter, SqlSmoObject[] smoObjectArray, IDictionary urnKeyArticleLookupTable)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

I'm trying to publish on SQL Server 2012 to a subscriber that is SQL Server 2016. The Distributor is also SQL Server 2016.

I was able to successfully run a snapshot of this UDF on a publication hosted on SQL Server 2016. So I think that it's actually SQL Server 2012 that is not allowing this, assuming that the snapshot agent runs on the publisher.

I've tried with and without the AS keyword. The value of @type in sp_addarticle is 128.

This issue was posted on Connect:

  We can't snapshot any User Defined functions to SQL 2016 version from
  existing SQL 2014 servers. They work fine within 2016 versions and
  from SQL 2016 to SQL 2014\2012.

MS's answer is:

  This issue is fixed in the following cumulative update for SQL Server:

  Cumulative Update 4 for SQL Server 2016.

  It contains one fix: FIX: Snapshot Agent fails when you publish UDF’s
  to SQL Server 2016 Distributor in Transactional Replication
  https://support.microsoft.com/en-us/help/3197883/fix-snapshot-agent-fails-when-you-publish-udf-s-to-sql-server-2016-distributor-in-transactional-replication


Should this be an identifying relationship or not? Well I've attached the image to explain what I need to know:

I thought it was supposed to be an identifying relationship, but now I kinda thought maybe it wasn't? The thing is the user won't necessarily be both: the patient and the professional? So how do I recognize which one he is or whether he could be both(if that's possible)? Should Patient and SpiProfessional have additional primary keys of their own? I've kind of got confused on this one. I was also thinking of maybe adding one additional field to recognize whether the user will be a patient or an spiProfessional, but then again you might want to add other types later and then the user would only be limited for one type...

Patient and SpiProfessional are uniquely identified only by their relationship to User, which means they are in an identifying relationship. It can also be helpful to think of them as "depending" on User.

That was the original question. Then there were two design questions:

  So how do I recognize which one he is or whether he could be both(if that's possible)?

Without knowing more about the application's requirements or application logic, it seems that information can be determined by simple reports.

For example, to find Patients who are not also pros:

SELECT u.* , p.*
FROM User u
INNER JOIN Patient p
ON u.idUser = p.idUser
LEFT OUTER JOIN SpiProfessional sp
ON  u.idUser = sp.idUser
WHERE sp.idUser IS NULL

If you do not care about the User only being one type, then cut off the last three lines. In any case, the best way to do this depends on your application requirements.

  Should Patient and SpiProfessional have additional primary keys of their own?

It's becoming a popular practice to give every table a single column primary key. I like doing this, but it is a preference.

See also this post on StackOverflow.


MySQL - Upgrading from 5.5 to 5.6 I have MySQL community version 5.5.50 running in linux having 4-5 databases on it.

Requirement is to upgrade to Percona Server 5.6.31-77.0.

What are the pre-requisites that I need to check before upgrading? Are there any common errors that i'm likely to face?

I'm simply planning to backup the databases. Remove the MySQL binaries. Install the new Percona Server and restore the databases.

Backing up the database is a best practice right before upgrading to any version. You can use mysqldump, Xtrabackup, mydumper .

Replacing MySQL community packages with Percona Server packages should do the job just make sure to execute mysql_upgrade after starting up PS 5.6 the first time.

  Percona Server is a free, fully compatible, enhanced, open source drop-in replacement for MySQL®

Always check the error log after the upgrade.


How to identify the top disk space consuming tables by file? Is there a query I can run to see the top disk space consuming tables for a given file in a file group?
Need to free up some disk space for a given SAN drive and need to identify which tables are using the most space for a couple of data files on that drive.

You may go to the report --> standard report and select Disk Usage by Top Table. This will be give you the table name in descending order which is used from maximum on disk to minimum on disk.
Alternatively, you may use the SQL below to get details:
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

Or this query:
SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                    ) AS data,
                SUM (ps.used_page_count) AS used
                FROM sys.dm_db_partition_stats ps
                GROUP BY ps.object_id
            ) AS a1

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'
    order by a1.data desc

Note: I have not tested these queries myself, confirming back whether it works or not would be appreciated.
This has been asked on multiple forums and I have used answers from here.
As far as files are concerned, you may need to list filegroups, if none are defined, all the allocations will take place in primary filegroup.
Below query could help you finding physical location:
SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Hope this helps.


Optimising latitude and longitude selection query I have table as follows the schema below.

CREATE TABLE `gdata` (
`alarmTypeID` tinyint(4) NOT NULL DEFAULT '0',
`fleetID` smallint(11) NOT NULL,
`fleetGroupID` smallint(11) DEFAULT NULL,
`fleetSubGroupID` smallint(11) DEFAULT NULL,
`deviceID` mediumint(11) NOT NULL,
`vehicleID` mediumint(11) NOT NULL,
`gDateTime` datetime NOT NULL,
`insertDateTime` datetime NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`speed` smallint(11) NOT NULL,
(see full text)
ALTER TABLE `gdata`
ADD PRIMARY KEY (`vehicleID`,`gDateTime`,`alarmTypeID`),
ADD KEY `gDateTime` (`gDateTime`),
ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`);
COMMIT;

My issue now is that I can run a query as below EXPLAIN select vehicleID,latitude,longitude from gdata where  gDateTime between '2017-07-03 00:00:00' and '2017-07-04 00:00:00'
and the results of the explains is

1   SIMPLE  gdata       range   gDateTime   gDateTime   5       1251    100.00  Using index condition

I have the following function which works to test if the a latitude and longitude is withing a rectangle.

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost`
    FUNCTION `geoProcessor`(pt POINT, mp MULTIPOLYGON)
        RETURNS int(1)
        DETERMINISTIC
BEGIN

DECLARE str, xy TEXT;
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE p, pb, pe INT DEFAULT 0;

SELECT MBRWithin(pt, mp) INTO p;
IF p != 1 OR ISNULL(p) THEN
RETURN p;
END IF;

SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str;
SET str = REPLACE(str, 'POLYGON((','');
SET str = REPLACE(str, '))', '');
SET str = CONCAT(str, ',');

SET pb = 1;
SET pe = LOCATE(',', str);
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p1x = SUBSTRING(xy, 1, p - 1);
SET p1y = SUBSTRING(xy, p + 1);
SET str = CONCAT(str, xy, ',');

WHILE pe > 0 DO
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p2x = SUBSTRING(xy, 1, p - 1);
SET p2y = SUBSTRING(xy, p + 1);
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y > m THEN
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y <= m THEN
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF;
IF x <= m THEN
IF p1y != p2y THEN
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x;
END IF;
IF p1x = p2x OR x <= xinters THEN
SET counter = counter + 1;
END IF;
END IF;
END IF;
END IF;
SET p1x = p2x;
SET p1y = p2y;
SET pb = pe + 1;
SET pe = LOCATE(',', str, pb);
END WHILE;

RETURN counter % 2;

END$$

DELIMITER ;

My issue now I want to find out which vehicle is within a rectangle within a time range value. I have to run two separate queries to first run the first queries get the lat, long value then test if its within the rectangle using the second query. How to opmtimise this?

To find which vehicle(s) are in a specific 'rectangle' ("bounding box") during a particular day,

SELECT ...
    FROM gdata
    WHERE gDateTime >= '2017-07-03'
      AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
      AND latitude > 34.567
      AND latitude < 35.678
      AND longitude > 123.456
      AND longitude < 124.567;

The optimizer cannot do a perfect job of optimizing the query, but you can give it 3 choices and hope that it picks the best:

INDEX(gDateTime),
INDEX(latitude),
INDEX(longitude)

Since each column is used in a "range", there is no advantage in putting two columns in a single index.  (Only the first column will be used.)

Or, with SPATIAL index...

If it is a polygon, not a simple rectangle, then something like

SELECT ...
    FROM gdata
    WHERE gDateTime >= '2017-07-03'
      AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
      AND MBRWithin(pt, ...);

with

pt being an extra column containing a POINT
SPATIAL(pt)
the '...' being a pre-calculated polygon from somewhere else

Do not call your FUNCTION in the WHERE clause; that will be terribly inefficient.

Since you only want a list of vehicleID, not all the locations, this is probably better:

SELECT DISTINCT vehicleID
    FROM gdata
    WHERE ...

DISTINCT is sort of a degenerate case of GROUP BY.  I prefer to use DISTINCT in this situation since it makes it clearer what is going on.

Partitioning?

PARTITION BY RANGE(TO_DAYS(gDateTime)) with 20-50 partitions may help performance, especially if the table will be billions of rows long.  The partitioning would narrow the focus to one (maybe more) partition, then do the spatial (or lat/lng) lookup within just that partition.

Also, partitioning is an excellent way to purge 'old' data.

Use cases 1 and 2 of my partition blog discuss those two ideas.


Foreign and primary keys question I have have looked for an answer for my questions everywhere but they seem confusing to me.Can a foreign key be a primary key in the same table? Can a foreign key be a primary key in another table?

Can a foreign key take values that repeat themselves?

Let's start with definitions then go into differences.

Primary Key
There can be only one Primary Key per table.  It is one of the candidate keys.  A candidate key is a key comprised of a column or group of columns that uniquely identify the row.  In SQL Server none of the values can be NULL but I can't say for certain that is true in other DBMSs.

Foreign Key
A foreign key is a column or group of columns that relate two tables together.    Typically this is called a parent/child relationship.  So for example a child table will have a Parent_Id column that refers back to the Parent table.  This relationship is one of the cornerstones of the relational part of the RDBMS (Relational DataBase Management System).  There can be multiple foreign keys on a table and in fact a foreign key can even relate a table back to itself.

Comparison
As you can see they really have nothing to do with each other.  The column(s) that make up the Primary Key can be the same column(s) that are used in a Foreign Key or even multiple Foreign Keys but they don't have to be.  The Parent table must related by one of the Candidate Keys but that does not have to be the Primary Key.


mysql: free space after drop table with innodb_file_per_table=False how to reclaim free space after drop table?  the innodb_file_per_table=False;
and i'm using version:
Server version: 5.5.44-MariaDB MariaDB Server

The only way to give the free space back to the OS is to dump all the tables, remove ibdata* and reload.

Meanwhile, there is a lot of free space in ibdata1.  When you do SHOW TABLE STATUS, you will probably see that most tables have the same, large, Data_free value.  This is actually how much is free in ibdata1.  As you add more data (to existing or new tables), Data_free will decrease until it gets close to zero; then the size of the ibdata1 file will increase.

Search around for other discussions of innodb_file_per_table.

As for doing a backup with low disk space, consider sending the data across the network to a disk on another machine.  For example, if the MySQL server is host1, do this on host2:

host2> mysqldump -h host1 ... >/a/local/dumpfile.sql

Before you rebuild, be sure to set innodb_file_per_table=ON since it needs to apply when you CREATE the TABLEs.


Passing Table Values into a TVF I have a TVF that takes a userID parameter.

I have another table that contains a list of these userIDs.

I want to pass all these userID values into the TVF.

I have tried this code...

    FROM [dbo].[UserData](u.UserID) data
    JOIN dbo.Users u ON u.DetailID = data.DetailID

but get the error...

The multi-part identifier "u.UserID" could not be bound.

What am I missing?

The error you're getting is that you are using an alias that is defined after the table you're currently referencing. In this instance, you will need to change the order of your tables as mentioned by Mark in the comment.

You can use a CROSS APPLY which is more similar to an INNER JOIN, or you can use an OUTER APPLY which is more similar to an OUTER JOIN.
Based on the syntax of your query above, my guess is that you are wanting a CROSS APPLY.

To echo Mark,

FROM dbo.Users AS u
CROSS APPLY dbo.UserData(u.UserID) AS data


Select last 2 records each time based on the record previously accessed records from the in Sql I have Message table

DDL

CREATE TABLE [dbo].[Message](
    [ID] [int] NOT NULL,
    [Message] [varchar](50) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

DML

INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (1, N'M1', CAST(0x0000A7F400D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (2, N'M2', CAST(0x0000A7F400E6B680 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (3, N'M3', CAST(0x0000A7F500D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (4, N'M3', CAST(0x0000A7F600D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (5, N'M3', CAST(0x0000A7F700D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (6, N'M3', CAST(0x0000A7F800D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (7, N'M3', CAST(0x0000A7F900D63BC0 AS DateTime))
GO

Table Message:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

I have limited no of rows to be accessed , each time 2 rows.But based on number of row accessed , want to get next 2 rows.

For example:

1) 0 row accessed and get 2 rows:

ID | Message | DateCreated
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

2) 2 rows accessed and now get next 2 rows:

ID | Message | DateCreated
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00

3) 4 rows accessed and now get next 2 rows:

ID | Message | DateCreated
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00

so on.

I may think of left join with itself and then use sub-query. But I m lost somewhere with my idea.

I may in other words as

select top 2 rows
from   message
where  ID not in (select *
                  from   message
                  where  rows already accessed )
order  by ID desc;

Its like pagination where I saw 2 records on each page. Unfortunately I will not provide the page number , instead I may say I have accessed rows with ID 7, 6 . Next two rows will be with ID 5, 4 and so on.

As far as I understood it would work as a pagination, and the params you would control on the app side anyways. I'd would the following:

@SQL2012:

SELECT [ID]
     , [message]
     , DateCreated
FROM [message]
ORDER BY ID DESC
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY


Log size is different between primary and secondary node with log_since_last_log_backup_mb We get log backups from the secondary node. But now I see that log_since_last_log_backup_mb in sys.dm_db_log_stats returns null for databases which are in Availability groups, so Ola Hallengren's log backup procedure with

@LogSizeSinceLastLogBackup = 25,@TimeSinceLastLogBackup = 21000

limitations works on those databases. The backup process is currently working on the secondary node.

But I could not understand why it returns null? Have you encountered such a situation?

The following pictures show result of log size since last log backup on the primary and secondary nodes.

Primary Node

Secondary Node

I'd expect it to be NULL on the primary node if a backup has never happened on this node. You can confirm this by checking when the last log backup happened for those databases on the primary node:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,21) --from your primary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC

And then on the secondary:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,33) --from your secondary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC


aggregate column of nvarchar2 and obtain result in nvarchar2 too I need to create a view with an aggregate column of nvarchar2(4000) in Oracle 11g.

What I have:

Table1 with every column of type nvarchar2(4000):

Col_Name   txt_value
X                   a
X                   b
X                   c
Y                   a
Y                   c

The result should be a view like this with both columns of type nvarchar2(4000):

Col_Name   txt_value
X                   a;b;c
Y                   a;c

I have tried with:

CREATE VIEW dict (col_Name, txt_value) AS
SELECT col_Name, LISTAGG (txt_value,';') WITHIN GROUP (ORDER BY txt_value)
FROM table1
GROUP BY col_Name

The only  problem is that LISTAGG() is converting the result in a column of type varchar2(4000) bit I need nvarchar2.

As the documentation describes:

  The return data type is RAW if the measure column is RAW; otherwise
  the return value is VARCHAR2.

So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion.

LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert everything to RAW, then convert the result back to NVARCHAR2.

Below is an example:

SQL> select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               EE8ISO8859P2
NLS_NCHAR_CHARACTERSET         AL16UTF16

My default characterset does not support the € sign.

SQL> create table t1 (id number, c1 varchar2(20), c2 nvarchar2(20));

Table created.

SQL> insert into t1 values (1, 'A', 'A');

1 row created.

SQL> insert into t1 values (2,  NCHR(8364),  NCHR(8364));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID C1 C2
---------- -- --
         1 A  A
         2 ?  €

As you can see, i get a ? instead of it using VARCHAR2.

If I simply try to use LISTAGG on the NVARCHAR2 column, data will be lost during conversion:

select
  listagg(c2, ',') within group (order by id) as list1
from t1;

LIST1
----------
 A, Ź

If I try to cast the result, still not good enough, as data was lost during the conversion to VARCHAR2:

select
  cast(listagg(c2, ',') within group (order by id) as nvarchar2(20)) as list2
from t1;

LIST2
----------
 A, Ź

The remaining option is, to convert to RAW:

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(',')) within group (order by id)) as list3
from t1;

LIST3
----------
AⰠ

Converting a VARCHAR2 delimiter will mess up the data, I need to specify the delimiter also in NVARCHAR2, so instead of ',', I have to use N',':

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(N',')) within group (order by id)) as list4
from t1;

LIST4
----------
A,€


Query for orders that contain a product without using sub-query Order
-----
OrderId

OrderDetail
-----------
OrderDetailId
OrderId
ProductId

How do I write a query that returns all orders that contain a specific product without using a sub-query?

I don't want to write a sub-query because I want to encapsulate the query in a view and use a WHERE clause on the view to supply the product id instead of having a stored procedure and supplying a parameter.

I figure the only way to do this is to join orders to details and GROUP BY on order but even then I don't know how to have the aggregate column resolve to whether the product is amongst the order details.

I have no idea about efficiency* but a view like this:

CREATE VIEW
    Orders_with_a_product_View AS
SELECT
    p.ProductID, o.*
FROM
    dbo.Product AS p
  JOIN
    dbo.[Order] AS o
      ON EXISTS
         ( SELECT 1
           FROM dbo.OrderDetail AS od
           WHERE od.OrderID = o.OrderID
             AND od.ProductId = p.ProductId
         ) ;

would allow you to use it with:

SELECT *
FROM Orders_with_a_product_View
WHERE ProductID = X ;

(*Regarding efficiency: Hopefully the optimizer will "push-down" the condition and not do a cross join of all products and orders.)

It might be better to follow a simpler road, like this:

CREATE VIEW
    Order_Product_IDs AS
SELECT DISTINCT
    ProductID, OrderID
FROM
    dbo.OrderDetail ;

and the utilize it with:

SELECT o.*
FROM dbo.Order_Product_IDs AS op
  JOIN dbo.[Order] AS o
    ON o.OrderID = op.OrderID
WHERE op.ProductID = X ;


Fragmentation Level for Heaps I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and needs to be looked and taken action upon. I checked FAQ at the site and seems his script doesn't support rebuilding heaps. I used below query to find the fragmentation level:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.name is null
ORDER BY page_count desc, indexstats.avg_fragmentation_in_percent desc

My application is supported by vendor and I have been communicating with them to change these heaps to tables and create clustered index however it hasn't yielded any meaningful result yet since they have defined primary key as unique non-clustered index and it is also part of foreign key, so needs to change at many level before doing any change. First of all, it took many days for me to explain the difference between clustered index and primary key with unique index.

I also went through the tweaks suggested by Mr. Brent Ozar for changing the defaults at script provided by Mr. Ola Hallengren for index optimize in order to make it more efficient however I didn't find any details of heap rebuilding.

As per my understanding heap's fragmentation can be handled in two ways as described here:

To create clustered index on table and drop it - This would clear all the fragmentation and also rebuild all non-clustered index however it would be time and I/O consuming.
Rebuilding the heap - This would also clear the fragmentation and rebuild all non-clustered index part of table rebuild.

I can't go for option 1 because I am not aware of columns where clustered index can be created and also this could take longer than option 2.

I am looking for possibility of implementing option 1 in the scripts by Ola Hallengren or alternative method for handling this. Also to add, I would like to rebuild my heaps only when the size of heap is more than 10,000 pages and fragmentation level is more than 80.

I am using Microsoft SQL Server 2014 SP3 Enterprise Edition.

As a DBA - I don't prefer to have heaps in my database however since it is vendor supported application and since they have already defined primary key as unique index and these keys are foreign keys, its very difficult to change them to clustered due to references as well as likeliness of down time.

EDIT: I went through the link provided by Mr. Erik Darling and I can confirm that I have number of heaps with forwarded records or deletes across the databases. Now, I am back to point from where I had started i.e. with those two options. As I mentioned earlier, creating clustered index is very very difficult in my scenario and will require at least months(being optimistic) with likeliness of downtime considering complex foreign key structure. Need advise on rebuilding the heaps and possible side effect.

Heaps have a few special challenges that you can't experience with clustered indexes:

Forwarded Records
Captive Pages

I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them.

At this time, you can't reorganize a Heap table, and rebuilding a Heap table will also rebuild any nonclustered indexes on it. It may be cheaper to drop them, rebuild the Heap table, and then recreate the nonclustered indexes afterwards.

You can read more about this stuff here:

sp_BlitzIndex Self Loathing Indexes
How To Fix Forwarded Records
Mysterious Forwarded Records
Forwarded Fetches and Bookmark Lookups


Should partitioning increase performance of indexed accesses and updates? I often hear that partitioning huge tables should significantly increase query speed and insert/update speed, because indexes are proportionally smaller.
I'm struggling to understand why that should be the case.
In my understanding operations on an index should be of log(N) complexity, so even if we partition a huge table into 100 smaller items then we just partitioned a single index into 100 smaller ones.
If queries frequently access items from the entire dataset, then these 100 indexes will compete for processor cache anyway, so I don't see why database will be hitting the disk less often.
If we are usually only requesting some portion of the data - for example if we have a table with timestamped data and are more often interested in latest data - then the B-tree node count that we have to traverse will be 1 or 2 less. But in un-partitioned table scenario top-most nodes of B-tree will be cached anyway, so the fact that B-tree is slightly higher shouldn't have significant impact on performance.
Variation on previous scenario: if we are commonly accessing ranges of data that are on the same "axis" as the partition key - again, if we have a table paritioned by timestamp and run reports on date ranges - then partitions will provide natural clustering for the data, thus reducing disk accesses and improving query performance. But I don't see how this will improve insert/update performance? And why should it improve performance on queries that do not filter by partition key?
I realize that partitions are useful in some other cases (dropping old data, lock contention with many users, backups, aforementioned clustering, etc) - but I commonly hear that query or update performance should increase as well, hence this question.
I'm most familiar with PostgreSQL, but I suspect the underlying concepts are similar between many relational databases.

Partitioning (at least from the perspective of SQL Server, but probably applies to most RDBMS) is not meant to be a tool for improving DQL query performance. E.g. your bread and butter SELECT statements won't see any impactful performance gains from partitioning, as you mentioned because of the Big O of B-Tree indexing. Rather it's meant to improve the performance (mostly be reducing contention) of DDL and some DML queries, for better database management.
I highly recommend this Brent Ozar article on partitioning (by Kendra Little) where she talks more theoretical implementation (as opposed to technical and specific SQL Server implementation).
One of the key points it mentions is partition switching which in 5 seconds is a methodology of partitioning on a key where your data is rolling (such as on a date field) and allows you to manage old partitions without causing locks or contention on the entire Table. By breaking up your Table into multiple partitions, you can archive your oldest partition without causing downtime on the rest of the Table that is active.
The same idea applies to other operations such as as index rebuilds / reorgs,  and other index and table maintenance, by allowing you to only affect certain partitions at a time and reduce the overall contention and locking on the Table at any given time. You can more smartly manage your Tables and their indexes accordingly.


Log shipping option not showing I am using SQL Server 2008 R2 Standard Edition. I am trying to implement log shipping, but the log shipping option is not showing in the database properties window. The database recovery option is bulk logged.

Shyamev,

Just to make sure the database selected should be a user and not any system databases.

But in many cases we have seen this even if its user database is selected and the option of log shipping is missing.

Solution: Re-install SSMS with the Management Tools - Complete option checked.

Refer to below link:

https://social.msdn.microsoft.com/forums/sqlserver/en-US/01a0ab24-5555-4b55-b399-a866a5b09d1d/transaction-log-shipping-option-is-missing


Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.

I checked this once and found that after this is run, the next DB hit takes a long time may be because of the relocation of fragments or so, but subsequent hits show kind of performance, i am not sure whether the query caching does this trick with optimization or optimization alone does this trick.

Can any one guide me with some real performance difference values if possible so that i can take up further as working with MySQL is gaining gravity in our project.

Please keep in mind that OPTIMIZE TABLE does not perform defragmentation. Internally, OPTIMIZE TABLE perform several operations (copying data to a temp file, recreate indexes, recompute index statistics). In fact, the example I have can be performed manually as shown.
Example: If you optimize mydb.mytable, you enter this command:
OPTIMIZE TABLE mydb.mytable;

Note that mysql performs something the following under the hood:
CREATE TABLE mydb.mytable2 LIKE mydb.mytable;
ALTER TABLE mydb.mytable2 DISABLE KEYS;
INSERT INTO mydb.mytable2 SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable2 ENABLE KEYS;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytable2 RENAME mydb.mytable;
ANALYZE TABLE mydb.mytable;

This is quite useful for tables that experience a high volume of UPDATEs and DELETEs
Performing this can accomplish two things

Prevent mysql from looking through fragments in a table in an attempt to load data into the right sized fragments. Eliminating these fragments will reduce this operation.

Having the index statistics recomputed helps the MySQL Query Optimizer construct better EXPLAIN plans. Otherwise, queries may deteriorate in execution time because the MySQL Query Optimizer decided to take bad guesses at the EXPLAIN plan. This would be a definite symptom of a table that has had a high volume of UPDATEs and DELETEs.

CAVEAT
With regard to caching, caching takes a dive quickly because of doing a full table scan. For MyISAM index pages flow in and out of the MyISAM Key Cache. For InnoDB, data and index pages flow in and out of the InnoDB Buffer Pool.


Suddenly cannot access SQL Server database My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below:

It cannot be detached and attached. I need to 'Take Offline' and then 'Bring Online' to solve it. This problem happens around once a week.

After checking the error logs, the following are the records:

  Error: 18456 Severity: 14 State: 38.
  FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.
  OS error: 32(The process cannot access the file because it is being used by another process.).
  Error: 17204 Severity: 16 State: 1.
  Starting up database 'PWRCMS'.
  Login failed for user 'sa'.
  Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
  Error: 18456 Severity: 14 State: 38.
  Login failed for user 'sa'.
  Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
  Error: 18456 Severity: 14 State: 38.
  FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWLCMS.mdf for file number 1.
  OS error: 32(The process cannot access the file because it is being used by another process.).
  Error: 17204 Severity: 16 State: 1.
  Starting up database 'PWRCMS'.
  Starting up database 'PWLCMS'.

Is there any solution to prevent this happening?

Unknown,FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.  OS error: 32(The process cannot access the file because it is being used by another process.).

Your database is in use from another process. You need to identify what that process is. Perhaps you attach the DB with AttachDatabase connection string option to a different SQL Server Express instance?


Moving TempDB data and log files I had to move our TempDB files around, since both the data and log files were originally on the C:\ drive.

I moved the data file to our E:\ drive and the log file to our F:\ drive.

This worked and I was able to get SQL Server back online, but I noticed that the F:\ drive, which has the ldf file now, also has another data file.

This made a total of two mdf files, one on the E:\ drive and the other on the F:\ drive.

I intended to have just one mdf file and log file seperated on different drives.

Why/how did SQL make the extra file?

First verify if what you're seeing is the same as what SQL Server can see,  by running this:

USE tempdb;
EXEC sp_helpfile;

If all 3 of your files are here, you somehow created them there by accident.

TempDB is a 'special' system DB that gets re-generated every time you start the SQL Server service.

Setup TempDB the way you want it, restart the server so TempDB recreates itself and run the above query again, to see if the new settings stuck.

If you want to still add a file somewhere manually you can do this:

ALTER DATABASE tempdb ADD FILE (
    NAME = N'LogicalNameHere'
    ,FILENAME = N'D:\Whatever\tempDB2orWhatever.ndf'
    ,SIZE = xMB
    ,FILEGROWTH = xMB
    );

If you want to keep all of your TempDB files but just move them around, get the info from sp_helpdb and modify to include your paths/files/names as found here:

USE [master];
GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'E:\Sqldata\templog.ldf');

Note you should usually have multiple TempDB data files. If you don't, you are risking logical file contention, which is not pretty.

Check out Brent's blog for more info on SGAM and PFS contention.


Oracle RMAN substituting variables I'm playing around with the USING clause in RMAN, but cannot seem to get it to work.  Here is my RMAN command:

rman target username/password @${RMAN_DIR}/backup.rman using "db1"

When I run that (with double quotes, single quotes, no quotes, using a parameter), I get the error below.

I'm on Oracle 11.2.0.4 on Linux.

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
timeout      integer        number of seconds to wait for pipe input
checksyntax  none           check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "db1": expecting one of: "double-quoted-string, identifier, integer, single-quoted-string"
RMAN-01007: at line 2 column 1 file: command line arguments

The Linux shell must be interpreting the double quotes before the command gets to rman. Either escape the double quotes:

rman target username/password @${RMAN_DIR}/backup.rman using \"db1\"

or enclose the command in single quotes to prevent the shell from messing with it:

rman target username/password @${RMAN_DIR}'/backup.rman using "db1"'

In the latter case you will have to leave the environment variable outside the quoted command as you do want the shell to expand it.


Restore a SQL 7 database into SQL 2014 without in-between versions? Our software is over 20 years old, and dates way back to databases such as Sybase and SQL 7. Now let's ignore the Sybase part. We have many old customers using very old versions of our software, varying between SQL 7 (2001-2002) to SQL 2014 (We support today). Technically speaking, our software can still actually run on MSDE 2000. But that's besides the point. We enforce all updated clients to use at least SQL Server 2008 R2, but officially support up to 2014. And yes, we do have many old customers still using ancient versions of our software on Windows '95.

Anyway, down to the question. Currently, whenever we have one of these old customers return and wish to upgrade, we have to go through a big elaborate upgrade process. Now the scripts are the easy part. I am actually just finalizing the latest major upgrade script, which supports upgrading databases from our 2001 (SQL 7) version to our latest version. The very same script accommodates for any in-between version.

However, before I can run these scripts, I first have to take the database through a series of manual and tedious steps. I have it sped up to this process:

Acquire database over internet from customer
Restore SQL 7 database directly into MSDE 2000 (on a dedicated XP machine)
Raise database compatibility to MSDE 2000
Backup database to a new file
Restore MSDE 2000 database directly into SQL Server 2008 R2
Raise database compatibility to SQL Server 2008
Backup database to a new file
Restore SQL 2008 R2 database directly into SQL Server 2014
Raise database compatibility to SQL Server 2014
Run all scripts on database for software changes over time
Backup database to a new file
After installing software for customer, restore database over internet

So as you can imagine, a lot of returning customers would be a full-time job. I suppose I could build some sort of automation on this XP machine, but this is still a manual process I'm looking to eliminate. We don't want to even need to acquire their database, and many of them don't want us to see their data.

Is there a method which I can easily distribute onto our client's machine, as part of the installer, which can automatically perform the upgrade from any given database version from SQL 7 to SQL Server 2014?

EDIT

To be a bit more clear on the installation process, this is an installer we already have capable of restoring databases directly from 2000 to 2008 R2. But we need to expand this installer to accommodate for upgrading databases ranging from MSSQL 7 to 2014. As I now understand, I can jump straight from 7 > 2005 > 2014. But that's still a hop I would like to eliminate, if possible. I would hate for our installer to load both 2005 and 2014 on our clients' machines.

If I was in your place, I would go the below route which is not as tedious as you would think of.

Backup your sql 7.0 database and restore it on sql server 2005.
Backup your sql server 2005 database and restore on sql server 2014.

Its a myth that you cannot upgrade a database to a version more that is more then two versions newer.

I have written an elaborated answer about the pre and post migration steps that will be a handy reference to you.

For automation, you can use dbatools (powershell based) - start-sqlmigration that supports migration from sql server 2000 to 2016 (newest version out).

Note: You have to test out a lot of stuff as this is a major leap for your application !


3NF Vs 2NF, which is more strict? Which of these is true:

If a table is in 2nd normal-form then it must be in 3rd normal-form.
If a table is in 3rd normal-form then it must be in 2nd normal-form.

I know only one of them is correct, if it's the first it makes sense.  If it's the second -doesn't make any sense to me. Maybe an example?

A database in 3NF is in 2NF also.  A simple mnemonic is:

The key
The whole key
and nothing but the key
So help me Codd

2NF states that a table contains no fields that are logically a function of a part of the primary key.  3NF states that a table contains no fields that are logically a function of any field of the table but the 'whole' key.  3NF can be viewed as a specialisation of 2NF.


How do I force one record to have a true value for a boolean column, and all others a false value? I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.

Basically, I want to guarantee that this query will always return exactly one row:

SELECT ID, Zip
FROM PostalCodes
WHERE isDefault=True

How would I do that in SQL?

Note: This answer was given before it was clear the asker wanted something MySQL specific. This answer is biased towards SQL Server.

Apply a CHECK constraint to the table that calls a UDF and makes sure its return value is <= 1.  The UDF can just count the rows in the table WHERE isDefault = TRUE.  This will ensure that there is no more than 1 default row in the table.

You should add a bitmap or filtered index on the isDefault column (depending on your platforom) to make sure this UDF runs very quickly.

Caveats

Check constraints have certain limitations.  Namely, they are invoked for every row that is modified, even if those rows have not been committed yet in a transaction.  So if you start a transaction, set a new row to be the default and then unset the old row, your check constraint will complain. That is because it will execute after you make the new row the default, find there are now two defaults, and fail. The solution then is to make sure you unset the default row first before setting a new default, even if you are doing this work in a transaction.
As gbn pointed out, UDFs may return inconsistent results if you are using snapshot isolation in SQL Server.  However, an inline UDF does not run into this issue, and since a UDF that just does a count is inline-able, this is not an issue here.
The strength of a database engine's processing power is in its ability to work on sets of data at once.  With a UDF-backed check constraint, the engine will be limited to applying this UDF serially to every row that is modified.  In your use case it is unlikely that you will perform mass updates to the PostalCodes table, however this remains a performance concern for those situations where set-based activity is likely.

Given all these caveats, I recommend using gbn's suggestion of a filtered unique index instead of a check constraint.


Database behind a Multilingual User Interface This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another:

Suggestion for database structure for multilanguage (2011 Jun)

What's the best database structure to keep multilingual data? (2010 Feb)

What are best practices for multi-language database design? (2009 May)

Schema for a multilanguage database (2008 Nov)

The most popular database scheme for backing multilingual user interfaces seems to be having all the translated texts of all languages in one table with 3 columns: the text id, the language code, and the text itself. The text id and the language code together make up the primary key.

That's all very fine, but now consider a complication: suppose that the texts need to be searchable. Suppose, for example, that this is a multi-language e-shop. This means that for every product category entered into the database, the shop owner will enter the name of the product category in each and every one of the N supported languages, and then the shopper will be able to search for the product category by name, in their own language.

There is a problem: Collation.

Different languages have different collation sequences, and the collation sequence which works for one language does not work for another. So if all texts of all languages are on a single column, what collation sequence are they going to have? How are we going to query the database to find the text id of a specific text? While in a web product search accuracy and performance might not be awfully important, for the purposes of this discussion let us assume that they really matter.

Most database administrators are familiar with the concept of collation in the sense of "the collation of the database". Luckily, that's just the default collation, which is used if no other collation information is present, but there exist other places too, where collation can be specified:

The SQL CREATE INDEX command supports a collation specification. (Though rumors have it that Microsoft SQL Server does not support it; does anyone know about that?)
The SQL SELECT statement also supports collation, but in this case the collation specification works as a function, causing an index scan instead of an index lookup, something which might be impermissible if we want performance. (Then again, if that's the best we can have, it might be better than nothing.)
I also hear that on Microsoft SQL Server you can have non-persisted, computed columns on which you can specify collation and create a filtered index, though I have never heard of this before, and if it is a Microsoft-SQL-Server-only feature, then I'd rather refrain from using it, no matter how cool and well thought out it is.

So, in light of all that, how do we structure our database, and how do we perform our queries, if the goal is an updatable and searchable multilingual database?

This question was inspired by a discussion that took place here: how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

It is possible to store strings with different collations in the same column using SQL_VARIANT:

CREATE TABLE dbo.Localized
(
    text_id     INTEGER NOT NULL,
    lang_id     INTEGER NOT NULL,
    text_body   SQL_VARIANT NOT NULL,

    CONSTRAINT [PK dbo.Localized text_id, lang_id]
        PRIMARY KEY CLUSTERED (text_id, lang_id),
)
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 2057, N'Database problems' COLLATE Latin1_General_CI_AS);
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 1025, N'قاعدة بيانات المشاكل' COLLATE Arabic_CI_AS)

This design has several drawbacks (including being limited to 8000 bytes), not least in the search area: SQL_VARIANT cannot be full-text indexed, and some string comparison features (e.g. LIKE) cannot be used directly either.  On the other hand, it is possible to create a regular index on SQL_VARIANT and perform the more basic comparisons (e.g. <, =, >) in a collation-aware fashion:

CREATE UNIQUE INDEX uq1 ON dbo.Localized (text_body)
GO
-- One row
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Latin1_General_CI_AS)

-- No rows (and no collation error!)
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Arabic_CI_AS)

-- One row, index seek, manual version of "LIKE 'D%'"
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body >= CONVERT(SQL_VARIANT, N'D' COLLATE Latin1_General_CI_AS)
    AND l.text_body < CONVERT(SQL_VARIANT, N'E' COLLATE Latin1_General_CI_AS)

We can also write the usual sort of procedures:

CREATE PROCEDURE dbo.GetLocalizedString
    @text_id    INTEGER,
    @lang_id    INTEGER,
    @text_body  SQL_VARIANT OUTPUT
AS
BEGIN
    SELECT
        @text_body = l.text_body
    FROM dbo.Localized AS l
    WHERE
        l.text_id = @text_id
        AND l.lang_id = @lang_id
END
GO
DECLARE @text SQL_VARIANT

EXECUTE dbo.GetLocalizedString
    @text_id = 1001,
    @lang_id = 1025,
    @text_body = @text OUTPUT

SELECT @text

Of course, full-text indexing is also problematic in the "single table for all translations" design, since full-text indexing (all but) requires a language id setting per column.  The multiple table design described by Joop Eggen could be full-text indexed (though it would naturally require one index per table).

The other main option is to have one column per locale in the base table:

CREATE TABLE dbo.Example
(
    text_id     INTEGER NOT NULL,
    text_2057   NVARCHAR(MAX) COLLATE Latin1_General_CI_AS NULL,
    text_1025   NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,

    CONSTRAINT [PK dbo.Example text_id]
        PRIMARY KEY CLUSTERED (text_id)
)

This arrangement does have a certain simplicity to it, and works well with full-text indexing, though it does require a new column to be added with each new language, and many developers find this sort of structure inelegant and unsatisfactory to work with.

Each of the alternatives has advantages and disadvantages, and will require indirection at some level or another, so it might depend on where the developers concerned feel happiest locating that indirection.  I imagine most people will prefer the multiple-table design for most purposes.


Piecemeal restore only one filegroup without primary filegroup restore I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups.

This db also have filegroups for each month.
Each filegroup has exactly one NDF file.

Like this:

FileGroup: PRIMARY
File: Primary.mdf

FileGroup: FG201801
File: 201801.ndf

FileGroup: FG201802
File: 201802.ndf

FileGroup: FG201803
File: 201803.ndf

etc

My goal would be twofold:

Be able to take backup per partition level.
As I read its only possible when I mark the filegroup readonly.
So i have separated partial backup BAK files.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
Second goal would be (my question is here), to be able to restore only one filegroup, without restoring the PRIMARY filegroup or touching any other filegroup.

Is it even possible?

As understood, if i want to restore for let's say FG201802 only, leaving PRIMARY and the others untouched, then first i have to restore the FULL backup containing the PRIMARY filegroup, then I can restore my partial backup of FG201802.
How could I do the restore of FG201802 without restoring PRIMARY?

Could anybody point me to an online resource where this is demonstrated?
All article on the net (which i found) always starts to restore the PRIMARY full backup then applying the rest of the partial backups one by one.

I wish only restore a partial backup, how could it be done?

Thank you!

You can't restore a file or filegroup without restoring the PRIMARY filegroup. There are a number of reasons for this given your scenario:

Under SIMPLE mode, a Partial Backup always contains the PRIMARY filegroup and all read/write filegroups. You would need to switch to FULL recovery to utilise file/filegroup only backups that can exclude the PRIMARY.
If no PRIMARY filegroup existed, the system tables and metadata for your database would not exist, so no database users, no schema information, nothing that SQL Server needs to know about your data, files and filegroups would be available.
Under SIMPLE, no log backups are taken, so you cannot replay the transactions against the non-PRIMARY read/write filegroups meaning they must be restored from a backup consistent with PRIMARY.

Even under FULL recovery, the primary filegroup must be restored to allow you to restore individual files/filegroups because all the core metadata about your database is there.

The idea of Partial Backups and Piecemeal restores is to allow you to get back online in a disaster quicker by restoring the essential filegroups needed for the database to be operational, and you can restore additional filegroups later.

I would suggest you look at making some changes to your database:

Move all user objects and data out of the PRIMARY filegroup so that restoring it is less time consuming.
Switch to FULL recovery mode and implement separate filegroup backups and frequent log backups.
Mark any filegroups that should not be updatable as READ ONLY.

These change will allow you to simplify your restore process. Assuming you'd made the above changes and wanted to restore the file you've mentioned, the process would be:

Take a tail-log backup
Restore the primary filegroup from backup
Restore all log files with no recovery (except the last restore of the tail log backup, which is done with recovery) (DB is online at this point)
Run "RECOVER" on all the undamaged filegroups (Data in these FGs is now accessible)
Restore the damaged filegroup from backup (DB is now fully recovered)

This would be quicker than partial backups under SIMPLE recovery because the undamaged filegroups do not have to get restored from backup, instead the data files on disk are simply recovered by SQL Server and transactions replayed from the log onto those files as appropriate. If the PRIMARY filegroup is small, and you're taking frequent log backups, the whole process should be quicker than restoring all the read/write filegroups as with partial backups under SIMPLE.


If foreign keys/cascade deletes are bad, why use a database-server with that feature? I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level!

I have read this and this. Most arguments against foreign keys constraint talks about performance, multithreading, locking, scalability, etc.

Assuming arguments against foreign keys are valid, my question are:

If foreign keys are bad, why WordPress/Rails/etc uses a sql-server which supports foreign keys? Will they benefit going away from MySQL to a NoSQL kind of server?
On other hand, can applications coded in a way to make use of foreign keys feature without running into issues?
Is noSQL/redis better if we are using database only for storage and managing "relations" at application/script layer?

Lets start with your first link. It says clearly:

  Working on large databases with referential integrity just does not perform well.

And that is right. Just you likely have no clue that "large database" is terabyte size with billions of rows in a table. A simple select may cascade into hundreds of millions of related elements to be deleted, and then you have a performance problem.

This is a non-issue for regular small databases such as a wordpress log or most CMS - it turns into a problem if you do something like facebook, or handle financial simulation data. I regularly deal with multi billion row tables and deletes that work in stored procedures outside of transactions in batches of x - because the end delete may easily clean up some hundred million rows.

  Will they benefit going away from MySQL to a NoSQL kind of server?

Hardly. They are useful when a professional uses them as appropriate.

  On other hand, can applications coded in a way to make use of foreign keys feature without
  running into issues?

Yes.

  Is noSQL/redis better if we are using database only for storage and managing "relations"
  at application/script layer?

I once did a application review for a technology upgrade in a bank that uses no referential integrity (to get the performance up). Loading the data into SQL Server (which was supposed to replace their aging Adabas installation) it failed with integrity constraint violations. Happens 40% of the historical records were invalid because some * had deleted lookup table values not in use any more (such as old customer classification codes, which got replaced for all active customers, just not the old ones). No referential integrity warning ever came up. The result was some fired people and a problem stuck in workaround and a partially useless data warehouse build on top.

So much for managing relations at application / script layer. Errors WILL happen. Data is valuable, applications change.

Most people complaining about SQL level features would be more advised to read a book about them, and try to understand them, instead of complaining. Sadly a lot of the advice on the internet is written by people that refuse to read even documentation. Always be careful with that. Most advice to NoSql is based strongly on ignorance.


Auto increment value needs to be changed inside trigger I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),
seqno int(11) auto increment
Joint PK index on the above 2 fields

New structure:

auto_id INT (11) auto increment,
id varchar(15),
seqno int(11)

The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;
//

CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`
 FOR EACH ROW BEGIN

SET @newseqno = 0;

SELECT MAX(seqno) INTO @newseqno
FROM my_table
WHERE id = NEW.id;

SET NEW.seqno = (@newseqno + 1);
/* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */
END;
//

Thanks Peter

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly):

CREATE TRIGGER innodb_seqno_trigger
   BEFORE INSERT ON my_table
   FOR EACH ROW
BEGIN

  SELECT MAX(seqno) INTO @newseqno
  FROM my_table
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1);

END;

Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?


Join query using OR on two different columns Is there a more efficient way (performance wise) to write this?

select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match on match.AwayTeam = uTeam.Id
                             or match.HomeTeam = uTeam.id

Putting certain types of OR logic in a join clause will prohibit SQL Server from implementing the join as a merge join or hash join. The query that you provided appears to be such a query.

CREATE TABLE X_UniqueTeams (ID INT);
CREATE TABLE X_Matches (AWAYTEAM INT, HOMETEAM INT);

select *
from X_UniqueTeams uTeam
Left HASH Join X_Matches match
on match.AwayTeam = uTeam.Id or match.HomeTeam = uTeam.id;

select *
from X_UniqueTeams uTeam
Left MERGE Join X_Matches match
on match.AwayTeam = uTeam.Id or match.HomeTeam = uTeam.id;

Both of those queries error out with the following:

  Msg 8622, Level 16, State 1, Line 37
  Query processor could not produce a query plan because of the hints defined in this query.
  Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Only a nested loop join is available to the query optimizer for your query. For some table structures and data structures a nested loop join can be very inefficient. In general, restricting the physical join operators available to the query optimizer is not a good idea. You want other physical join operators to be available so the query optimizer can find a plan with the lowest estimated cost.

There are two common rewrites that I know of to avoid OR logic in a join clause. The first is that sometimes you can just split the LEFT OUTER JOIN into two joins if the cardinality between the tables is 1:1. That doesn't appear to be the case here so that option isn't available to you.

The second option is to use UNION or UNION ALL. You should prefer UNION ALL whenever possible because it avoids a sorting or hashing step.

select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match
on match.AwayTeam = uTeam.Id
UNION --ALL
select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match
on match.HomeTeam = uTeam.id

For this query in particular, first verify that it does what you need it to. Do you truly need to return all columns with SELECT *? Do you want to return teams that haven't played any games? Once your query syntax is correct consider your response time requirements. How fast does your query need to be? Once you know that try testing out the different rewrites available to you. If none of them are fast enough consider adding indexes on the tables.

If you need further help than this you will need to provide the information that the commentators asked for. Even if this answer is sufficient to answer your question, it's good practice to provide that information upfront in future questions.