Is DBCA in Oracle for a single database or the entire system? I ask this because I was going to create a simple phone list database for learning.  But when I used dbca, I was asked did I want to install the sample databases, HR, etc.  It also had the default locations for all archive_log files, passwords for sys, sysman...I thought dbca was just to create a single database.

What is dbca for?

DBCA will be reside on Oracle home. You can create as many number of databases in system using dbca. It will create default locations based on your unique SID (and dbunique name).


Creating index on computed field: string or binary data would be truncated I have a table Foo with the following fields:

ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint)

Now I want to create an index on LongValue, so that I can easily look up on serialized values that represent numbers.

create nonclustered index IX_Foo on Foo(LongValue);

Which spits out the following error at me:

  String or binary data would be truncated.

Yes, there is existing data in SerializedValue.  But what, pray, can be truncated by creating an index on a computed field?

The error isn't caused by creating the index. The error is caused by TRY_CAST when the computed column values are evaluated at index creation.

If I run this:

SELECT TRY_CAST(REPLICATE(CONVERT(nvarchar(MAX), N'a'), 4001) AS bigint)

I get the same error.

The documentation says (emphasis mine):

  If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

Now, it's not exactly clear under which cases it will fail with an error (seems kind of asinine given the whole point of the function, but anyway...), so we can fix up the code by transforming the input values (use something reasonable for the data in your table), as there's no need to process a huge string when it won't fit in a bigint anyway:

SELECT TRY_CAST(LEFT(REPLICATE(CONVERT(nvarchar(MAX), N'1'), 4001), 100) AS bigint)

This returns NULL as the value isn't valid, but it doesn't bomb with an error.

If you have a string with a value that is too long, then the index create will fail.  I tried a little test code using SQL Server 2012.

CREATE TABLE dbo.foo
(ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint));

INSERT INTO dbo.foo (serializedvalue) VALUES(REPLICATE(' ', 4000)+'1');

CREATE INDEX GotToTry ON foo(LongValue);

DROP TABLE dbo.foo;
GO

My quick experiment showed that the code works as long as the nvarchar(max) value is 4000 characters or less.  (Of course, all blanks with nothing on the end collapses to no characters and thus works just fine.) The 4001st character triggers the String or binary data would be truncated message.  So you might examine your data for a SerializedValue that is longer than 4000 characters.

EDIT:  Yes, the conversion is to a BIGINT.  The problem is not the BIGINT, but is the NVARCHAR(MAX).  For example:

If a row contains '1111111111111111111' it will both CREATE INDEX and convert the value to BIGINT.
If a row is 0 to 4000 '1's, it can CREATE INDEX, but the value may be NULL since it overflows BIGINT.
If a row is longer than 4000 characters, then the CREATE INDEX fails.

So, it seems that the actual contents of the NVARCHAR(MAX) is what matters to the CREATE INDEX.

EDIT: Jon Seigel identified that the TRY_CAST triggers the failure on create index when the string is longer than nvarchar(4000).


How to drop SQL Server database currently in use and in Single user mode I have a Database on SQL Server 2008, which I want to drop.
Currently it is in single user mode and it is currently in use.

select * from sys.sysprocesses

returns

Msg 924, Level 14, State 1, Line 1
Database 'G_MAIN_DE' is already open and can only have one user at a time.

and I do not know how to identify the session I have to kill.

An attempt to set it offline

ALTER DATABASE G_MAIN_DE SET OFFLINE WITH ROLLBACK IMMEDIATE

yields

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'G_MAIN_DE' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

To identify the session currently using the database you can try

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('G_MAIN_DE')

if above process is not working,then go to services and kill the sqlservices in taskmanger. Then will be able to set to multiuser.


How to get one row out of many rows with same column id I have a use case in which I have three tables.

Courses Table

Course_ Id   Name
 1   MathMatics
 2   Physics

Teacher Table

 Teacher_ID  Teacher_Name
    1    AAA
    2    BBB

TeacherVsCourses Table

 id  Course_id  Teacher_id
 1   1   1
 2   2   1
 3   1   2

My application is programmed using the MVC design pattern of ASP.NET. I am saving individual records into the database table as shown below:

What I want in my view is instead of two rows for Teacher-id=1, I need 1 row with comma separated courses.

How can I achieve this?

You can return a comma separated list of values by using the string_agg function.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15


Same record shows up multiple times in ordered paginated query Some records show up on multiple result "pages" with the following query:

SELECT
    `description`,
    `wallpaper`
FROM
    `bbr_bar`
WHERE
    `country_id` = '2510769'
ORDER BY
    `online` DESC

For instance, record with id 99 shows up on the first "page" (LIMIT 10 OFFSET 0) but also on the last one LIMIT 10 OFFSET 50. Obviously this should not happen.

The problem disappears under the the following circumstances:

if I don't filter the query, or don't order it (both country_id and online are int(11), not foreign keys)
if I remove one of the two fields from the SELECT clause (both varchar(255), not foreign keys)
if I delete some tables or fields in the schema. However, afters hours of effort, I am still unable to say for sure which tables/fields have an impact and which ones don't. Looks like random (even if I know it is not). Hence I don't paste here any CREATE TABLE or INSERT statement as I don't think they are relevant.

Only thing I can say for sure is that if I mysqldump all tables and restore them in a new schema, the problem is still there.

(There are other fields in the table but adding/removing them has no effect on the result).

Questions:

Am I right if I conclude that something is corrupted in my schema?
How could I fix this?

(MySQL version: 5.6.24 Win32 x86)

I have some bad news for you: According to MySQL, IT'S NOT A BUG !!!

There are two bug reports on this behavior and is considered non-critical

MySQL 5.6 (Bug #69732 : LIMIT clause results in duplicate data across pages)
MySQL 5.5 (Bug #65307 : Problem with order by and limit offset)

Here is the rationale expressed in Bug #69732

  Without a distinct ORDER BY the result order is undefined. Period.

  Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is nothing that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...

  So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.

In your particular case, the online field is probably not distinct enough. Even if you index bbr_bar by (country_id,online), this may still be not distinct enough. You may have to involve another column to drive the sort order (perhaps an auto increment field or a timestamp field).

Give it a try!!!!

Although is not a real solution, it was working for me:
After experiencing the same problem as you i tried to apply indexes on the questionable fields, that fixed the problem for me.


Why Does the Transaction Log Keep Growing or Run Out of Space? This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:

In SQL Server -

What are some reasons the transaction log grows so large?
Why is my log file so big?
What are some ways to prevent this problem from occurring?
What do I do when I get myself on track with the underlying cause and want to put
my transaction log file to a healthy size?

A Shorter Answer:

You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

If it is a recovery model issue, the simple answer could be Switch to Simple recovery mode if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.

There could be other reasons but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.

A Longer Answer:
What Scenarios can cause the log to keep Growing? There are many reasons, but usually these reasons are of the following two patterns: There is a misunderstanding about recovery models or there are long running transactions. Read on for details.

Top reason 1/2: Not Understanding Recovery Models

(Being in Full Recovery Mode and Not Taking Log Backups - This is the most common reason - the vast majority of those experiencing this issue are.)

While this answer is not a deep dive in SQL Server recovery models, the topic of recovery models is critical to this problem.

In SQL Server, there are three recovery models:

Full,
Bulk-Logged and
Simple.

We'll ignore Bulk-Logged for now we'll sort of say it is a hybrid model and most people who are in this model are there for a reason and understand recovery models.

The two we care about and their confusion are the cause of the majority of the cases of people having this issue are Simple and Full.

Intermission: Recovery in General

Before we talk about Recovery Models: let's talk about recovery in general. If you want to go even deeper with this topic, just read Paul Randal's blog and as many posts on it as you want. For this question, though:

Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation It is the log's job to say "Hey.. this never really finished, let's roll it back" during recovery. It is also the log's job to see that you did finish something and that your client application was told it was finished (even if it hadn't yet hardened to your data file) and say "Hey.. this really happened, let's roll it forward, let's make it like the applications think it was" after a restart. Now there is more but that is the main purpose.
Point in Time Recovery
The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an "oops" in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn't always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.

Recovery Models

Onto the recovery models:

Simple Recovery Model
So with the above introduction, it is easiest to talk about Simple Recovery model first. In this model, you are telling SQL Server: "I am fine with you using your transaction log file for crash and restart recovery..." (You really have no choice there. Look up ACID properties and that should make sense quickly.) "...but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file."

SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
Full Recovery Model
With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.

Switching from Simple to Full has a Gotcha.

There are rules and exceptions here. We'll talk about long running transactions in depth below.

But one caveat to keep in mind for Full Recovery Mode is this: If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in Simpleuntil you switch to Full Recovery Model AND take your first Full Backup.

Full Recovery Model without log backups is bad.

So, what is the most common reason for uncontrolled log growth?
Answer: Being in Full Recovery mode without having any log backups.

This happens all the time to people.

Why is this such a common mistake?

Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.

Model's initial recovery model setting is always Full Recovery Model - until and unless someone changes that. So you could say the "default Recovery Model" is Full. Many people are not aware of this and have their databases running in Full Recovery Model with no log backups, and therefore a transaction log file much larger than necessary. This is why it is important to change defaults when they don't work for your organization and its needs)

Full Recovery Model with too few log backups is bad.

You can also get yourself in trouble here by not taking log backups frequently enough.
Taking a log backup a day may sound fine, it makes a restore require less restore commands, but keeping in mind the discussion above, that log file will continue to grow and grow until you take log backups.

How do I find out what log backup frequency I need?

You need to consider your log backup frequency with two things in mind:

Recovery Needs - This should hopefully be first. In the event that the drive housing your transaction log goes bad or you get serious corruption that affects your log backup, how much data can be lost? If that number is no more than 10-15 minutes, then you need to be taking the log backup every 10-15 minute, end of discussion.
Log Growth - If your organization is fine to lose more data because of the ability to easily recreate that day you may be fine to have a log backup much less frequently than 15 minutes. Maybe your organization is fine with every 4 hours. But you have to look at how many transactions you generate in 4 hours. Will allowing the log to keep growing in those four hours make too large of a log file? Will that mean your log backups take too long?

Top reason 2/2: Long Running Transactions

("My recovery model is fine! The log is still growing!)

This can also be a cause of uncontrolled and unrestrained log growth. No matter the recovery model, but it often comes up as "But I'm in Simple Recovery Model - why is my log still growing?!"

The reason here is simple: if SQL is using this transaction log for recovery purposes as I described above, then it has to see back to the start of a transaction.

If you have a transaction that takes a long time or does a lot of changes, the log cannot truncate on checkpoint for any of the changes that are still in open transactions or that have started since that transaction started.

This means that a big delete, deleting millions of rows in one delete statement is one transaction and the log cannot do any truncating until that whole delete is done. In Full Recovery Model, this delete is logged and that could be a lot of log records. Same thing with Index optimization work during maintenance windows. It also means that poor transaction management and not watching for and closing open transactions can really hurt you and your log file.

What can I do about these long running transactions?

You can save yourself here by:

Properly sizing your log file to account for the worst case scenario - like your maintenance or known large operations. And when you grow your log file you should look to this guidance (and the two links she sends you to) by Kimberly Tripp. Right sizing is super critical here.
Watching your usage of transactions. Don't start a transaction in your application server and start having long conversations with SQL Server and risk leaving one open too long.
Watching the implied transactions in your DML statements. For example: UPDATE TableName Set Col1 = 'New Value' is a transaction. I didn't put a BEGIN TRAN there and I don't have to, it is still one transaction that just automatically commits when done. So if doing operations on large numbers of rows, consider batching those operations up into more manageable chunks and giving the log time to recover. Or consider the right size to deal with that. Or perhaps look into changing recovery models during a bulk load window.

Do these two reasons also apply to Log Shipping?

Short answer: yes. Longer answer below.

Question: "I'm using log shipping, so my log backups are automated... Why am I still seeing transaction log growth?"

Answer: read on.

What is Log Shipping?

Log shipping is just what it sounds like - you are shipping your transaction log backups to another server for DR purposes. There is some initialization but after that the process is fairly simple:

A job to backup the log on one server,
a job to copy that log backup and
a job to restore it without recovery (either NORECOVERY or STANDBY) on the destination server.

There are also some jobs to monitor and alert if things don't go as you have them planned.

In some cases, you may only want to do the log shipping restore once a day or every third day or once a week. That is fine. But if you make this change on all of the jobs (including the log backup and copy jobs) that means you are waiting all that time to take a log backup. That means you will have a lot of log growth -- because you are in full recovery mode without log backups -- and it probably also means a large log file to copy across. You should only modify the restore job's schedule and let the log backups and copies happen on a more frequent basis, otherwise you will suffer from the first issue described in this answer.

General troubleshooting via status codes

There are reasons other than these two, but these are the most common. Regardless of the cause: there is a way you can analyze your reason for this unexplained log growth/lack of truncation and see what they are.

By querying the sys.databases catalog view you can see information describing the reason your log file may be waiting on truncate/reuse.

There is a column called log_reuse_wait with a lookup ID of the reason code and a log_reuse_wait_desc column with a description of the wait reason. From the referenced books online article are the majority of the reasons (the ones you are likely to see and the ones we can explain reasons for. The missing ones are either out of use or for internal use) with a few notes about the wait in italics:

0 = Nothing
What it sounds like.. Shouldn't be waiting
1 = Checkpoint
Waiting for a checkpoint to occur. This should happen and you should be fine - but there are some cases to look for here for later answers or edits.
2 = Log backup
You are waiting for a log backup to occur. Either you have them scheduled and it will happen soon, or you have the first problem described here and you now know how to fix it
3 = Active backup or restore
A backup or restore operation is running on the database
4 = Active transaction
There is an active transaction that needs to complete (either way - ROLLBACK or COMMIT) before the log can be backed up. This is the second reason described in this answer.
5 = Database mirroring
Either a mirror is getting behind or under some latency in a high performance mirroring situation or mirroring is paused for some reason
6 = Replication
There can be issues with replication that would cause this - like a log reader agent not running, a database thinking it is marked for replication that no longer is and various other reasons. You can also see this reason and it is perfectly normal because you are looking at just the right time, just as transactions are being consumed by the log reader
7 = Database snapshot creation
You are creating a database snapshot, you'll see this if you look at just the right moment as a snapshot is being created
8 = Log Scan
I have yet to encounter an issue with this running along forever. If you look long enough and frequently enough you can see this happen, but it shouldn't be a cause of excessive transaction log growth, that I've seen.
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
About the clearest description yet..

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk.

•What are some reasons the transaction log grows so large?

Long Active Transaction
High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc.
Any HA like Replication, Mirroring configured which holds the log and does not allow it release the log space

•Why is my log file so big?

Check for the log_reuse_wait_desc column in sys.databases table to know what is holding the logs from truncating:

select name, log_reuse_wait_desc
from sys.databases

•What are some ways to prevent this problem from occurring?

Log backups will help you control the log growth unless there is something that is holding up the logs from being reused.

•What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?

If you have identified what actually is causing it then try to fix it accordingly as explained in below page.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Having proper log backups scheduled is the best way of dealing with log growth unless for an unusual situation.


How can i return the date of max id? I want to select the max id_product and date that corresponds to this max id.
my problem is that it returns the max id_ product with a different date.

I can't use the max(date) because it will return always the max what i need is the date of this max id.

This my query :

 SELECT
   MAX(id_product),
   `date`
 FROM `products`
 WHERE `fk_stat` = '1' and `id_order` = '77185' ;

The result:

But the value that's must return:

09/07/2015 for id = 152752

Thanks for your help.

Do it with a subquery,assuming unique values on id_product

SELECT id_product,date  FROM products
WHERE id_product IN
(SELECT MAX(id_product) as maxid FROM products WHERE
fk_stat = '1' and id_order = '77185')


How to get line that causes error when loading from csv to MySQL I'm trying to load data from CSV file, but occurs error

Error Code: 1366. Incorrect integer value: 'abcdxxx' for column 'col' at row 123545.

I Have file with multiple breaklines CRLF's in content, the line separator is LF, text tools cannot find returned string, so I suppose there is problem with charset. Do exists posibility to have returned values(line) from file that caused error server returned? I cannot find values that caused error.

Errors occured because of unescaped backshlashes. Values preceeded with \ were inrepereted as ascii signs, but they weren't. To avoid this error backslash - \ should be escaped by additional backslash - \ so occurences of backslashes should be changed to double backslashes - \\ .


SQL Server R2 Standard Edition MAXDOP setting I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the
following config:

Version      : SQL Server 2008 R2 Standard Edition
Processor    : 2 x AMD Opteron(TM) Processor 6234 = 24 cores
Hyperthreading enabled

For this processor each individual socket has two NUMA nodes, each of which has 6 cores in it. Microsoft’s KB article, Recommendations and guidelines for the “max degree of parallelism” configuration option (http://support.microsoft.com/kb/2806535) recommends that
“For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.” so I would want to set MAXDOP to 6 (the number of cores in one NUMA node.)

However, Microsoft’s documentation on the max degree of parallelism Option http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx tells me that the maximum value that can be used for MAXDOP in SQL Server 2008 R2 Standard Edition is 4.

So, i’m guessing that the version  recommendation of MAXDOP 4 overrides the processor/NUMA recommendation of MAXDOP=6? Anyone else have this configuration and know what happens if I try and set MAXDOP to 6 regardless?

Standard edition will not generate a parallel plan that uses more than 4 processors i.e. MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.

This is limited to processors (socket) not cores. So in your case with 2xAMD processors it will generate parallel plan.


Recommended mirroring alternative? SETUP

2 Amazon EC2 SQL 2014 servers in different availability regions.
Synchronous mirroring
S3 for archiving full backups & logs at night
Right now for simplicity, nightly full compressed backups
Tran Logs every 15 mins (would do more if can reduce the runtime)
Ola Hallengren backups running on both principal and mirror servers

SLA

My SLA terms to meet don't have heavy restrictions at this point so (odd I know!):

"Opps Deleted Something" restores would be a nice to have, but I don't a hard delivery requirement on this right now. I do log backups every 15 mins right now.
High-Availability: not fully required, downtime of an hour would be acceptable if needed
Point in time recovery: I'm able to provide due to the transaction log backups every 15 mins, however 2 hours would be acceptable for now.

Network Bottleneck

It looks like one of the biggest bottlenecks is the approx 300mb network pipe that seems to get overloaded by the need to synchronously mirror to another EC2 instance. I could maintain a lot more databases on the same instance, reducing costs likely if I removed mirroring.

Mirroring or Alternative Approach For performance with reasonable availability

I'm looking for the best value while still delivering a reasonable uptime. Since I'm on SQL standard, this means that asynchronous mirroring is not an option (we are running sql 2014).

As I've evaluated options I'd appreciate another perspective on basic disaster recovery. The overhead of mirroring seems to be a big bottleneck, but if I remove it then I'm concerned about how to provide better availability. Ideally, I'd run mirroring asynchronously, but we'd need to move backwards in sql server editions, and not sure if that would be the best approach

always on high availability groups

Not proceeding with this due to the increased complexity and licensing cost at this time. Open to exploring in future, but at this time looking to avoid Enterprise licensing cost.

It sounds like you may benefit from a Failover Cluster if all you're looking for is availability. You can run them on standard edition and if you take an N+1 route, you can have two nodes for HA and have one in a DR site ready to take over. You'll need to work on your storage availability for the DR side, but that's a whole other can of worms.

Another option may be Log Shipping since you're already taking log backups every minute. You can ship the logs off to your HA/DR server.

You can get more information on how to do it and the implicatioons for AWS here:

Implementing Microsoft Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups in the AWS Cloud

So achieving the best solution with the least amount of money happens to be my speciality.
I would recommend using Redgate software's SQL Compare tool and run database syncs (Typically completes in less time than tlog shipping.
I do these in 3 minute intervals, I have my DB read server separated from my DB write server.
Focus reporting and DB retrieval from the DB read servers and focus only writes to the DB write server. (Practice is more difficult than theory but your customers will thank you).
Use standard SQL licenses on your server but remember the most important part of setting up your network ALWAYS CREATE A DEDICATED NIC FOR BACKUPS and DB Syncs and tell that non-prod data to step aside. (Just remember that you might need to add a route rule for the backup NIC)
Always validate that traffic is flowing as you intend. Now depending on the size of your DB I could make some recommendations on some settings that would speed up all operations back from seconds to MS but I'll wait for someone to ask that question


inserting data from one table to another after casting the values I have a #temp table containing all the data that needs to be inserted into a table named Customer.
The column names of the two tables are exactly the same, but the data types are different. So I need to cast from one type to another before inserting the data into the Customer table.

The solution has to be implemented for a dynamic condition. i.e, I don't know the column name or data type.

I would be writing some code to do this, I'm not sure whether it would be achievable using purely SQL. You'd need to dynamically create a (or some) SQL statements, which may be a question for the chaps over in the programming section, however:

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename' AND COLUMN_NAME = 'columnname'

Would give you the datatype; you could then select the data from your other table and cast it to the datatype, you can also pull column_name from information_schema but without a table name.. That's another question!

DECLARE @SQL varchar(8000)

SET @SQL = ''

SELECT @SQL = @SQL +  ' CAST(' + COLUMN_NAME + ' as ' + ISNULL(DATA_TYPE + '(' + CAST(CHARACTER_OCTET_LENGTH as varchar(20)) + ') )','') + ' as ' + COLUMN_NAME + ','  FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourPermTable'

SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-1)

SET @SQL = 'SELECT ' + @SQL + ' FROM YourTempTable'

PRINT @SQL

Add the insert part and it should do the trick!


Linked Server to Oracle seems to be slow we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider.

The enabled options for provider are:

Dynamic parameter
Nested queries
Allow inprocess
Supports "Like" Operator

The options of the linked server are:

@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'

The processes using this linked server just take orders from Oracle, do what it has to do and then sends back to Oracle informations about what have been done.

According to the end users, it seems to be slow.

How can I ...

Assess that the configuration is done appropriately?
How can we make sure that performance is good ? Monitoring queries that goes through it, etc.

You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True


CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard? Just came across a weird issue, whereby depending on how I create an index, an index name is required.

http://dev.mysql.com/doc/refman/5.5/en/create-index.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

CREATE INDEX `random_name` ON `my_table` (`my_column`); # Requires an index name

ALTER TABLE `my_table` ADD INDEX (`my_column`); # Does not require an index name

It seems to me that the CREATE INDEX call, should not make index name required. I'm wondering if this is a MySQLism, or a SQL standard?

I don't think that the SQL standard defines how to create indexes at all.

A quote from this Wikipedia page:

  Standardization

  There is no standard about creating indexes because the ISO SQL
  Standard does not cover physical aspects. Indexes are one of the
  physical parts of database conception among others like storage
  (tablespace or filegroups). RDBMS vendors all give a CREATE INDEX
  syntax with some specific options which depends on functionalities
  they provide to customers.

The Postgres manual seems to support this here:

  There are no provisions for indexes in the SQL standard.

More evidence under this related question on SO.

If you don't provide a name for the index, MySQL will automatically name the index for you.

ALTER TABLE my_table ADD INDEX (col1);
ALTER TABLE my_table ADD INDEX (col1, col2);
ALTER TABLE my_table ADD INDEX (col2);

SHOW INDEX FROM my_table;

The ALTER TABLE ADD INDEX statements above will produce the following indexes:

col1
col1_2
col2

If you create an index with only one column, it might not be necessary to provide a name for the index (since the name of the index will be the same as the column name). But if you create an index with multiple columns, it might be better to provide a name for the index so it will be easier to identify the index.

It is good practice to provide a name for the index (and prefix it with idx or something to identify it as an index):

ALTER TABLE my_table ADD INDEX idx_my_column (my_column);

-- or
CREATE INDEX idx_my_column ON my_table (my_column);


how to find min max of sum? hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me !

    SELECT TOP 1    dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM           dim_product dp
    INNER JOIN  fact_sales fs
        ON     dp.product_code = fs.product_code
    GROUP BY
            dp.product_name
    ORDER BY
        SUM(fe.units_sold) DESC
UNION ALL
     SELECT TOP 1     dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
      FROM   dim_product dp
      INNER JOIN  fact_sales fs
      ON     dp.product_code = fs.product_code
      GROUP BY
             dp.product_name
      ORDER BY
                SUM(fe.units_sold) ACS ;

You could try this

WITH cte AS (
    SELECT dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM   dim_product dp
    INNER JOIN  fact_sales fs ON dp.product_code = fs.product_code
    GROUP BY dp.product_name
)
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold DESC
) a
UNION
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold
) b


CROSS APPLY produces outer join In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A
            ) AS ca;

The query uses CROSS APPLY (not OUTER APPLY) so why is there an outer join in the execution plan instead of an inner join?

Also why does uncommenting the group by clause result in an inner join?

I don't think the data is important but copying from that given by kevinwhat on the other question:

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)

Summary

SQL Server uses the correct join (inner or outer) and adds projections where necessary to honour all the semantics of the original query when performing internal translations between apply and join.

The differences in the plans can all be explained by the different semantics of aggregates with and without a group by clause in SQL Server.

Details

Join vs Apply

We will need to be able to distinguish between an apply and a join:

Apply

The inner (lower) input of the apply is run for each row of the outer (upper) input, with one or more inner side parameter values provided by the current outer row. The overall result of the apply is the combination (union all) of all the rows produced by the parameterized inner side executions. The presence of parameters means apply is sometimes referred to as a correlated join.

An apply is always implemented in execution plans by the Nested Loops operator. The operator will have an Outer References property rather than join predicates. The outer references are the parameters passed from the outer side to the inner side on each iteration of the loop.
Join

A join evaluates its join predicate at the join operator. The join may generally be implemented by Hash Match, Merge, or Nested Loops operators in SQL Server.

When Nested Loops is chosen, it can be distinguished from an apply by the lack of Outer References (and usually the presence of a join predicate). The inner input of a join never references values from the outer input - the inner side is still executed once for each outer row, but inner side executions do not depend on any values from the current outer row.

For more details see my post Apply versus Nested Loops Join.

  ...why is there an outer join in the execution plan instead of an inner join?

The outer join arises when the optimizer transforms an apply to a join (using a rule called ApplyHandler) to see if it can find a cheaper join-based plan. The join is required to be an outer join for correctness when the apply contains a scalar aggregate. An inner join would not be guaranteed to produce the same results as the original apply as we will see.

Scalar and Vector Aggregates

An aggregate without a corresponding GROUP BY clause is a scalar aggregate.
An aggregate with a corresponding GROUP BY clause is a vector aggregate.

In SQL Server, a scalar aggregate will always produce a row, even if it is given no rows to aggregate. For example, the scalar COUNT aggregate of no rows is zero. A vector COUNT aggregate of no rows is the empty set (no rows at all).

The following toy queries illustrate the difference. You can also read more about scalar and vector aggregates in my article Fun with Scalar and Vector Aggregates.

-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

db<>fiddle demo

Transforming apply to join

I mentioned before that the join is required to be an outer join for correctness when the original apply contains a scalar aggregate. To show why this is the case in detail, I will use a simplified example of the question query:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

The correct result for column c is zero, because the COUNT_BIG is a scalar aggregate. When translating this apply query to join form, SQL Server generates an internal alternative that would look similar to the following if it were expressed in T-SQL:

SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

To rewrite the apply as an uncorrelated join, we have to introduce a GROUP BY in the derived table (otherwise there could be no A column to join on). The join has to be an outer join so each row from table @A continues to produce a row in the output. The left join will produce a NULL for column c when the join predicate does not evaluate to true. That NULL needs to be translated to zero by COALESCE to complete a correct transformation from apply.

The demo below shows how both outer join and COALESCE are required to produce the same results using join as the original apply query:

db<>fiddle demo

With the GROUP BY

  ...why does uncommenting the group by clause result in an inner join?

Continuing the simplified example, but adding a GROUP BY:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

The COUNT_BIG is now a vector aggregate, so the correct result for an empty input set is no longer zero, it is no row at all. In other words, running the statements above produces no output.

These semantics are much easier to honour when translating from apply to join,  since CROSS APPLY naturally rejects any outer row that generates no inner side rows. We can therefore safely use an inner join now, with no extra expression projection:

-- Rewrite
SELECT A.*, J1.c
FROM @A AS A
JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

The demo below shows that the inner join rewrite produces the same results as the original apply with vector aggregate:

db<>fiddle demo

The optimizer happens to choose a merge inner join with the small table because it finds a cheap join plan quickly (good enough plan found). The cost based optimizer may go on to rewrite the join back to an apply - perhaps finding a cheaper apply plan, as it will here if a loop join or forceseek hint is used - but it is not worth the effort in this case.

Notes

The simplified examples use different tables with different contents to show the semantic differences more clearly.

One could argue that the optimizer ought to be able to reason about a self-join not being capable generating any mismatched (non-joining) rows, but it does not contain that logic today. Accessing the same table multiple times in a query is not guaranteed to produce the same results in general anyway, depending on isolation level and concurrent activity.

The optimizer worries about these semantics and edge cases so you don't have to.

Bonus: Inner Apply Plan

SQL Server can produce an inner apply plan (not an inner join plan!) for the example query, it just chooses not to for cost reasons. The cost of the outer join plan shown in the question is 0.02898 units on my laptop's SQL Server 2017 instance.

You can force an apply (correlated join) plan using undocumented and unsupported trace flag 9114 (which disables ApplyHandler etc.) just for illustration:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY
(
    SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
    FROM   #MyTable AS mt2
    WHERE  mt2.Col_A = mt.Col_A
    --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

This produces an apply nested loops plan with a lazy index spool. The total estimated cost is 0.0463983 (higher than the selected plan):

Note that the execution plan using apply nested loops produces correct results using "inner join" semantics regardless of the presence of the GROUP BY clause.

In the real world, we would typically have an index to support a seek on the inner side of the apply to encourage SQL Server to choose this option naturally, for example:

CREATE INDEX i ON #MyTable (Col_A, Col_B);

db<>fiddle demo

Cross Apply is a logical operation on the data. When deciding how to get that data SQL Server chooses the appropriate physical operator to get the data you want.

There is no physical apply operator and SQL Server translates it into the appropriate and hopefully efficient join operator.

You can find a list of the physical operators in the link below.

https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017

  The query optimizer creates a query plan as a tree consisting of logical operators. After the query optimizer creates the plan, the query optimizer chooses the most efficient physical operator for each logical operator. The query optimizer uses a cost-based approach to determine which physical operator will implement a logical operator.

  Usually, a logical operation can be implemented by multiple physical
  operators. However, in rare cases, a physical operator can implement
  multiple logical operations as well.

edit/ It seems I understood your question wrong. SQL server will normally choose the most appropriate operator. Your query doesn't need to return values for all combinations of both tables which is when a cross join would be used. Just calculating the value you want for each row suffices which is what is done here.


Does SQL Server execute queries in parallel? Does SQL Server execute queries in parallel?
In other words, if I run a heavy query that takes 10 seconds to execute, and at the same time start another heavy query that takes 10 seconds, will the second query actually start after 10 seconds, or will they start both at the same time?

You mean "concurrently". The answer is yes, with caveats that are too broad to discuss here.
In fact, the whole point of RDBMS is concurrency.

"Parallel" has a precise meaning in SQL Server: "a single query is distributed over more than one processor core".

If you are writing the queries like below...it will run parallelly

SET XACT_ABORT ON
Begin Try
     Begin Tran
        --Your Sql Statement
     Commit Tran
End Try
Begin Catch
    Rollback Tran
End Catch

Note - Your parallel query will come in waiting state in case any bulk insert in a table


Does unique email in user table violate normal forms? USER(userID, email, name, address, ...)
userID

primary key
incrementing bigint

email

unique identifier
can be modified by user

To me this seemed like a good idea, but then I started to wonder: does the uniqueness of 'email' violate the third normal form (3NF)?

To extend the hole idea why index doesn't matter
A indexed filed onlys describes that every row is unique, but this doewsn'z means that it is dependend from other columns than the promary key

A relation is in third normal form if there is no transitive dependency for non-prime attributes

following this definition, it still stands an indexed email field is  dependent on the primary key and does not depend on other columns it is non transitively dependent
Which means that it correspnds teh 3NF


SQL Server - Login failed. The login is from an untrusted domain and cannot be used with Windows authentication I am getting below error while trying to connect SQL from remote server. Local login is working fine. Can anyone help on this. Thanks in advance.
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.186.12.10]"
My environment :
Client - already test AD/DNS domain joined
Remote SQL Server - already prod AD/DNS domain joined
I have running inside test AD/DNS server isolated environment. No relationship between test AD/DNS server and prod AD/DNS server. Test domain name : contoso.com Otherwise , PROD domain name : contoso.com
Test AD/DNS Server : 10.190.10.1
Prod AD/DNS Server : 10.150.10.1
Do I have to join prod AD/DNS client machine as well ? I mean , is there any workaround solution ?
Thanks in advance,
Last Update:
Solved my issue.

I believe the issue is the client machine's domain isn't trusted by the remote server when logging in via Windows Authentication in SSMS. You should still be able to login with your AD credentials from your local SSMS client by switching the Authentication mode from Windows Authentication to Azure Active Directory - Password.
If you run into the subsequent error "The certificate chain was issued by an authority that is not trusted" then you can tell SSMS to trust it manually by clicking the "Options" button on the Login window. Then under the "Connection Properties" tab, check off the "Trust server certificate" checkbox. Then click the "Connect" button.
Screenshot Example

When the client tries to connect, it will pass its existing Kerberos ticket, the one created from it's Domain Controller on Test AD when the user logged in.
SQL Server will pass the ticket to it's own DC, which will fail it. Since the domain of Production has no trust relationship with Test domain, there is no way for the DC to trust what this ticket represents.
To get this to work without moving machines between domains, you need to setup a trust relationship between the two AD domains. Then, the Production DC can verify the ticket with the other DC.
There are many ways to create trusts. Importantly, they can be one-way, so you don't have to have both domains trusting each other. You can also setup a Forest Trust, which can be easier to manage.
The two DCs need to be able to talk to each other, so you may need to open ports and setup DNS records.
I suggest you speak to your Sysadmin or whoever runs your AD domain, if any.


what is running VSS on my server Client's SQL Server farm DBs are backed up by third-party tool that does not use VSS. About half of the instances show backups being made by some tool that is using VSS. I can disable SQL Server VSS Writer to stop these errant backups, but how do I track down what is running the VSS backups?

keywords: Volume Shadow Copy Service, volume-shadow-service [I don't have rep to create tags]

I believe that if you run native SQL Server backups (using T-SQL BACKUP) instead of DPM protection agent-based SQL backups, the SQL Server VSS Writer service must not be used on your SQL Server database folders.  (The same problem is true for other third-party backup tools.)

This DPM/VSS service is not used by native SQL backups. If you prevent the DPM protection agent backups from triggering SQL Server VSS snapshots, this will protect your chain of log backups.

If DPM/VSS does continue to backup the SQL Server files, then the log chain is broken, because there are two tools (T-SQL BACKUP and DPM/VSS) backing up the log files.


Oracle DB refresh on 11g using 10g Cold backup I am a sql dba, I am having only a little knowledge on Oracle. looking for help regarding oracle db restore.
I am having an Oracle 10g cold backup, i need to restore this on Oracle 11g setup, can you please help me understanding the process how to perform.

Datapump command like impdp can be used for importing the 10g DB contents into the 11g DB.

Update:

The above datapump command is an example of logical backup which can be done when a database is in running mode.
Below are the common steps followed during a logical backup and restore:

Export(Backup) of full DB is taken in old server:

expdp system/password@db10g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log
Dumpfile moved to new server
Import(Restore) dumpfile in the new server:

impdp system/password@db11g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log

  More details on this can be found in -->
  https://oracle-base.com/articles/10g/oracle-data-pump-10g (Better than
  Oracle docs)

Now coming back to the actual question, what you are asking is a restore of the cold backup in 11g DB server.

The restore of the cold backup can be done by using RMAN command(s) as below:

RMAN> restore database from TAG='COLD_BACKUP_FILE_NAME'

I assume that you have taken backup of all the necessary files during the cold backup process.

Hope this helps you !!
Please share your progress with regard to this :)


How to manage log file size in large, heavy-use database We are a team of mathematicians (i.e., no DBA experience).

We have a large database in SQL Server 2012.  It has more than 2 TB of data (hundreds of tables, each with millions of rows and hundreds of columns wide).  Each month, we receive a bundle of additions and revisions to the data, requiring us to perform extensive updates to our database by deleting, replacing, or updating most or all of the tables.

Our work is mostly focused on crafting the SQL logic to calculate the results that we need.  We're not running a real-time call center.  We apply a few indexes as needed, and we're pretty happy with the performance.

The problem is the log file.  Naturally, the log file grows and grows with so much data manipulation.  Our log file currently stands at about 1 TB.  We have a good amount of disk space, but it is not infinite.

From what we read on the Internet, we understand that the log file is necessary for transaction integrity, rollbacks, and recovery.  But for our specific purposes, we don't care about any of that.  We will probably never perform a rollback, nor would we ever attempt a recovery.  Worse comes to worse, we would simply download the data files again and create a new database from scratch.

We really just want the log file to go away and never come back.

We set the database recovery mode to Simple, naively thinking that this meant "no recovery mode", but we were quickly cured of those illusions.

We also understand that there are many wrong things not to do (detaching, shrinking, etc.).  We just don't know the right thing to do.

Maybe someone is going to suggest that we set a limit to log file growth.  However, that leaves two issues:
(1)  How do we get rid of the 1 TB which is already there?
(2)  We previously tried that, and as we approached the specified limit we started getting Error 9002 (log file full) here, there, and everywhere.  So now we are afraid of applying a size limit.

How do we tell the database "No log file, please" without any hurt feelings?

If you never anticipate doing a point-in-time restore, then you just need to do 2 things:

Change the database to SIMPLE recovery mode
Do large changes in small batches.

In SIMPLE recovery mode, the log file is used only for transactions in process. When the transaction completes, the space in the tran log will be marked for re-use, and the next transaction will just re-use the space*.

(* This isn't strictly true, there are "checkpoints" that run occasionally that actually clear the space for re-use, but for the most part it works without intervention; only finicky DBAs will likely care about the inner details here.)

So keep your transactions bite-sized:

If you need to delete all rows of a table, don't do a DELETE of 100m rows (that will blow up the log), do a TRUNCATE instead, or just DROP the table and recreate it.
If you can't do that (you need to delete just some rows), do it in smaller batches
When doing large data imports, learn about bulk imports or an SSIS Fast Loads that load data quickly and commits the data every so often
Same thing for updates, don't update every row of a massive table, use more selective WHERE clauses to limit your updates to ~100k rows at a time. Learn how to use cursors in SQL

The exact details are going to depend on your actual database and actual activity, of course, but a database with 1TB of data shouldn't have a tran log file any larger than, say, 100Gb, depending on how big your batches are.

With regard to shrinking the existing file, you can use the following script to see the free/used space in your data and log files:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

If the used space in the log is high, then you likely still have open transactions. Look for the 3rd-party tool sp_WhoIsActive for digging into your current activity/open trans.

When you are ready to shrink, pick a reasonable target size, and execute a SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mylogfile', SIZE = 100000)

(That size is in MB).

If your log file still grows, go back to sp_WhoIsActive to see what transactions are running so long, and figure out how to do them in batches.

Here is to get rid of 1TB log for Non-DBA or New-DBA. Assuming you have SSMS installed.

Right click the database has huge log file.

Please choose Files -> File type: Log, you will see 'Available free space:'. Under Shrink action, Reorganize pages before releasing unused space, Shrink file to ? MB(Minmum is 0 MB). After you specify the value, then OK.


How to hint many-to-many join in SQL Server? I have 3 "large" tables that join on a pair of columns (both ints).

Table1 has ~200 million rows
Table2 has ~1.5 million rows
Table3 has ~6 million rows

Each table has a clustered index on Key1, Key2, and then one more column. Key1 has low cardinality and is very skewed. It is always referenced in the WHERE clause. Key2 is never mentioned in the WHERE clause. Each join is many-to-many.

The problem is with cardinality estimation. The output estimation of each join gets smaller instead of larger. This results in final estimates of low hundreds when the actual result is well into the millions.

Is there any way for me to clue the CE into making better estimates?

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Solutions I have tried:

Creating multi-column stats on Key1, Key2
Creating tons of filtered stats on Key1 (This helps quite a bit, but I end up with thousands of user-created stats in the database.)

Masked execution plan (sorry for the bad masking)

In the case I'm looking at, the result has 9 million rows. The new CE estimates 180 rows; the legacy CE estimates 6100 rows.

Here's a reproducible example:

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2),
     DataSize (Key1, NumberOfRows)
     AS (SELECT 1, 2000 UNION
         SELECT 2, 10000 UNION
         SELECT 3, 25000 UNION
         SELECT 4, 50000 UNION
         SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
     , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
     , T1Key3
FROM DataSize
     CROSS APPLY (SELECT TOP(NumberOfRows)
                         Number
                       , T1Key3 = Number%(Key1*Key1) + 1
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT
       Key1
     , Key2
     , T2Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1*10)
                         T2Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT
       Key1
     , Key2
     , T3Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1)
                         T3Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

DROP TABLE IF EXISTS #a;
SELECT col = 1
INTO #a
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1
INTO #b
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN #Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Just to be clear, the optimizer already knows that it's a many-to-many join. If you force merge joins and look at an estimated plan you can see a property for the join operator which tells you if the join could be many-to-many. The problem that you need to solve here is bumping up the cardinality estimates, presumably so you get a more efficient query plan for the part of the query that you left out.

The first thing that I would try is putting the results of the join from Object3 and Object5 into a temp table. For the plan that you posted it's just a single column on 51393 rows, so it should hardly take up any space in tempdb. You can gather full stats on the temp table and that alone might be enough to get a sufficient accurate final cardinality estimate. Gathering full stats on Object1 may help as well. Cardinality estimates often get worse as you traverse from a plan from right to left.

If that doesn't work you can try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint if you don't already have it enabled at the database or server level. Microsoft locks plan-affecting performance fixes for SQL Server 2016 behind that setting. Some of them relate to cardinality estimates, so perhaps you'll get lucky and one of the fixes will help with your query. You can also try using the legacy cardinality estimator with a FORCE_LEGACY_CARDINALITY_ESTIMATION query hint. Certain data sets may get better estimates with the legacy CE.

As a last resort you can manually increase the cardinality estimate by whatever factor you like using Adam Machanic's MANY() function. I talk about it in another answer but it looks like the link is dead. If you're interested I can try to dig something up.

A reach.   No real basis other than try.

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key2 = t2.Key2
      AND t1.Key1 = 1
      AND t2.Key1 = 1
     JOIN Table3 t3
       ON t2.Key2 = t3.Key2
      AND t3.Key1 = 1;


How to get first and last value by same item in sequence, with possibility of the same item occurring again Similar to this question but with the possibility that the same name can appear again in the following rows.

I have this table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1
9  Daniel   6
10 Daniel   5
11 Daniel   5

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9
Ray     5         5
Charles 1         1
Daniel  6         5

WITH
cte1 AS ( SELECT *,
                 CASE WHEN name = LAG(name) OVER (ORDER BY id)
                      THEN 0
                      ELSE 1
                      END the_same
          FROM test ),
cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num
          FROM cte1 )
SELECT DISTINCT
       name,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id) value_1st,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id DESC) value_last
FROM cte2;

fiddle

SELECT s.name,(CASE WHEN s.name =t.name THEN MAX(s.id) END) AS 'value_1st',(CASE WHEN s.name=t.name AND  s.id =MAX(s.id) THEN s.value END) AS 'value_last'
FROM tableA s,(SELECT distinct name FROM tableA)t
WHERE s.name = t.name


Struggling with MySQL character set, collation I am facing problems with inserting Spanish characters from my application.

When I am inserting on the DB the characters are inserting properly, but when I am trying passing the same data in from the application, the data stored in the table isn't correct.

For example, when I insert directly on DB server:

CREATE TABLE  prod_inns(`prod_name` varchar(100) COLLATE latin1_bin DEFAULT NULL)

insert into prod_inns values(Otoño);

I see this:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño       |
+-------------+

delete  FROM  prod_inns ;

However, when I insert the same value from the application form, I see this instead:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| OtoÃ±o      |
+-------------+

Here are my MySQL settings:

MySQL>  show global variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL>  show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

What's wrong with my settings? What should I do to store the data and read the data properly?

Note: I am using mysql-5.7.19

Don't use latin1, use utf8.  You have "Mojibake" because the bytes in the client are utf8-encoded, yet you said they were latin1.

More on Mojibake and what to do about it:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

You need to alter your database character set and collation as per the instructions here:

i.e.

ALTER  DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name

in your case, it should be

ALTER DATABASE my_database
CHARACTER SET utf8
COLLATE spanish_unicode_ci

(or spanish2 if you want Traditional - as per here)


How to store schema-less data efficiently in a relational database? I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.

Option 1:
Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.

   id  |  name   | meta
1       Bob       {"city":"GoTown","birthdate":"1980\/8\/14","cat":"Felix"}
2       Alice     {"city":"Streamville","birthdate":"1986\/6\/6","dog":"Rex"}

Option 2:
Store keys and values together in a stack.

user_id  |    key   |   value
1         name       Bob
1         city       GoTown
1         birthdate  1980/8/14
1         cat        Felix
2         name       Alice
2         city       Streamville
2         birthdate  1986/6/6
2         dog        Rex

Option 3:
3 tables: entites, keys, values. Store each key name only once.

user_id  |   name
1         Bob
2         Alice

key_id   |   keyname
1         city
2         birthdate
3         cat
4         dog

user_id   |   key_id   |   value
1          1            GoTown
1          2            1980/8/14
1          3            Felix
2          1            Streamville
2          2            1986/6/6
2          4            Rex

Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.

EDIT: Added some data to represent schema-less data

Some really nasty problems would happen with these tables (idea)

Data Redundancy (duplicated data) what you need to keep in synchronisation (and you do you check this because MySQL doesn't have an nice JSON to records function?)
You can't force correct values on the database (say goodbye to data integrity and hello to garbage-in garbage-out data) example key birthdate could have value "hello"

And you need some kind of pivot query to fetch the key/values

If you really need dynamic key values (EAV) storage there are some more options:

Maybe MySQL 5.6 NoSQL using the InnoDB memcached Plugin
XML -> query with xpath
RDF
NoSQL like MongoDB
MariaDB dynamic columns and MariaDB also supports json.

Have you consider NoSQL?

Because I work for a NoSQL vendor, I am a bit partial but I was trying to take your problem definition and view it from a lens of what technology is best.  This description is ideal for NoSQL given it supports unstructured data and all the options you shared are doable in NoSQL.  In my opinion, option 3 is pretty attractive because in a minimal amount of time you can create those tables and be done with it.  And if you need to add additional columns, its trivial and you can keep versions of your data.  And most of the NoSQL DBs support querying using many different ways and most of these fall within your model.

Most of the NoSQL DBs like FatDB, and RavenDB come to mind and would work well for this for the Windows space.  MongoDB, etc, for others.


Splitting a result row into multiple rows I have the following query:

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS money) AS ActualAmount,
       MIN (a.TotalCases) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS money) AS BudgetAmount,
      MIN (ve.BudgetCases) AS BudgetCases
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;

which gives me the following output.

+---------+-------------+----------+--------------+-------------+-------------+--------------+
|  Month  |   Region    | Category | ActualAmount | ActualCases | BudgetCases | BudgetAmount |
+---------+-------------+----------+--------------+-------------+-------------+--------------+
| January | New England | Cola     | 439365.6000  | 31200       | 0           | 0.0000       |
| January | New England | Juice    | 110051.7600  | 8424        | 0           | 0.0000       |
| January | New England | Water    | 800.0000     | 40          | 0           | 0.0000       |
| January | NY / NJ     | Cola     | 552583.0800  | 40692       | 0           | 0.0000       |
| January | NY / NJ     | Juice    | 139824.3600  | 10764       | 0           | 0.0000       |
| January | NY / NJ     | Water    | NULL         | NULL        | NULL        | NULL         |
+---------+-------------+----------+--------------+-------------+-------------+--------------+

But for some code I am working on, I need each row to be split into four rows and add a string identifier to indicate the type of row, like this:

+---------+-------------+----------+--------------+-------------+
|  Month  |   Region    | Category |     Type     |    Value    |
+---------+-------------+----------+--------------+-------------+
| January | New England | Cola     | ActualAmount | 439365.6000 |
| January | New England | Cola     | ActualCases  | 31200       |
| January | New England | Cola     | BudgetAmount | 0.0000      |
| January | New England | Cola     | BudgetCases  | 0           |
| January | New England | Juice    | ActualAmount | 110051.7600 |
| January | New England | Juice    | ActualCases  | 8424        |
| January | New England | Juice    | BudgetAmount | 0.0000      |
| January | New England | Juice    | BudgetCases  | 0           |
+---------+-------------+----------+--------------+-------------+

How can I accomplish that by tweaking my query -- OR by creating a query based on the View of this query?

As Mark mentioned, UNPIVOT would be a good choice here.  I often find it's easier to throw the original query into a temp table then unpivot that.  You could use that approach, CTEs or views depending on performance.  All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS MONEY) AS ActualAmount,
       CAST (MIN (a.TotalCases)  AS MONEY) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS MONEY) AS BudgetAmount,
       CAST (MIN (ve.BudgetCases)  AS MONEY) AS BudgetCases
INTO #tmp
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;
GO

SELECT [Month], Region, Category, ActualAmount, ActualCases, BudgetAmount, BudgetCases
FROM #tmp
UNPIVOT ( [Type] For [Value] In ( [ActualAmount], [ActualCases], [BudgetCases], [BudgetAmount] ) ) upvt

If I were doing this I would code it in Python (or another suitable language) and for each row I read in from the original file write four to the new file. In SQL it would be a case of combining four SELECT statements and then UNIONing them together.


How to estimate/predict data size and index size of a table in MySQL I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer

For an example we have a table City with InnoDB engine,lets say in future (in next 1 year) it will have 1 million of records so what will be the estimated data size and index size of that table in this period.

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

UPDATE

What will be the estimated upper bound (Max size of table) with 1 million records and how can we estimate it.

Given the table description, I see

66 bytes per row of data
4 bytes per row for the primary key
7 bytes per row for country code index

3 bytes for the country
4 bytes for Clustered Key attached to the country code

Total of 77 bytes of data and keys
This does not factoring housekeeping for BTREEs or Tablespace Fragmentation

For a million rows, that would 77,000,000 bytes (73.43 MB)

As for measuring the table, for a given table mydb.mytable, you can run this query

SELECT
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

To measure all tables grouped by Database and Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

Run these queries and you can track changes in database/engine disk usage.

Give it a Try !!!

It's tedious. But the details are in the docs.

To be as accurate as possible, which is rarely necessary, you'll need to read about the table structure and index structure, too.

If I were in your shoes, I'd build the table, populate it with a million rows of test data, and measure the change in size. Depending on your application, you might need to take the size of transaction log files into account, too.


TRUNCATE TABLE statement sometimes hangs Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb  stalls. There are other bug numbers opened for prior versions as well.

You can use:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

It gets tricky for tables with AUTO_INCREMENT values: new table is created with an AUTO_INCREMENT value which is immediately taken in the working table. If you do no want to  use same values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;

I faced same issue, and its resolved by restarting mysql server.

systemctl restart mysqld

I know this is not an expected solution, but if you are stuck then you can restart mysql server.


How to improve performance of frequently updated table in mysql? my table structure::

CREATE TABLE `gps_device_data`(
  `imei_no` bigint(15) NOT NULL DEFAULT '0',
  `ioelementpriority` int(1) DEFAULT NULL,
  `longitude` int(15) DEFAULT NULL,
  `latitude` int(15) DEFAULT NULL,
  `speed` tinyint(3) unsigned DEFAULT NULL,
  `iovalue` varchar(254) DEFAULT NULL,
  `odom` bigint(20) DEFAULT NULL,
  `alarm_category_realtime_id` varchar(70) DEFAULT NULL,
  `angle` int(4) DEFAULT NULL,
  `altitude` int(4) DEFAULT NULL,
  `satellites` int(4) DEFAULT NULL,
  `data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `continue_data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `movement` int(2) DEFAULT NULL,
  `eventsource` int(4) DEFAULT NULL,
  `data_location` varchar(250) DEFAULT NULL,
  `data_poi` varchar(100) DEFAULT NULL,
  `data_validity` enum('valid','invalid') DEFAULT NULL,
  `createdby` varchar(10) DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `modifiedby` varchar(10) DEFAULT NULL,
  `modifieddate` datetime DEFAULT NULL,
  `deletedby` varchar(10) DEFAULT NULL,
  `deleteddate` datetime DEFAULT NULL,
  PRIMARY KEY (`imei_no`,`data_received_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

query that takes too much time to generate report for vehicle.

select  odom,latitude/10000000 as latitude,longitude/10000000 as longitude,
       imei_no,iovalue,data_received_time,continue_data_received_time,
        data_location
    from  gps_device_data
    where  imei_no IN (7781112889,70433357641,356173061134123,
               865190010626029,354188046466178,356307040285917,863354010100132,
               863354010100107,8511139808,4209501721,9974052815,356307044188414,
               5252563256,7709111078,7043335764,2121212121,7043333691,
               7043333684,7043338378,100345,356307048930860,356307040328584,
               358899052926188,7621013796,7043132917,7043312765,9016102493,
               ...
               7820046194,8585858585,559862234897,123456789001,78000000021,
               2220002220,12345678911,863771023760608,863771023758834,
               9898787854,140507978,140908877,140507779,356173061103755,
               7485961230,454545454,147852390,1203203695,358174067325903
                      )
      and  data_received_time >= 1430463600000
      and  data_received_time <= 1446274800000
    Order by  imei_no,data_received_time;

my explain plan

+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | gps_device_data | range | PRIMARY       | PRIMARY | 16      | NULL | 629357 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+

what my project do is track the vehicle get its information and display that information to specific owner of vehicle.

questions:

Are there any alternative queries  which gives me performance than above specified query?

Is it ok to use a Composite key with IN block?

my select query takes 25.93 seconds to execute.  If my above select query takes 2 or 3 seconds to execute its good form me. How can I reduce the time for the select query?

I have 10 millions rows in the gps_device_data table.

You have two "ranges" filtering the data.  MySQL has no good way to do that.

First, let's check some numbers.

How many rows in the result set?
How many rows without the IN clause?
How many rows without the time range?
Is this IN list and this time range typical?

With those, we may be able to decide to focus on filtering on the time range in preference over the IN range.

Meanwhile, let's get rid of the "midnight" bug... Your time range seems to be from the morning of May 1 through midnight of the morning of Oct 31.  Perhaps you meant to include all of Oct 31, not just one millisecond of that day?  Did you mean to ask for a full 6 months?  Suggest this coding style; it's less error-prone:

AND data_received_time >= 1000 *  UNIX_TIMESTAMP('2015-05-01')
AND data_received_time <  1000 * (UNIX_TIMESTAMP('2015-05-01') + INTERVAL 6 MONTH)

Back to the performance question...

If you are looking at a smallish fraction of the total time, then PARTITIONing may be useful for simulating a 2D index.  (I mention this as Case #2 in http://mysql.rjweb.org/doc.php/partitionmaint )

If you break the table into, say, 20-40 partitions PARTITION BY RANGE(data_received_time), then "partition pruning" will first filter on the time range.  Then your PRIMARY KEY, with imei_no first, filters on the other dimension.

I cannot say how much (if any) this partitioning will speed things up.  I would expect small time ranges to benefit (percentage-wise) the most.  A time range that hits all partitions will actually be a little slower than without partitioning.  (Hence, my "check some numbers" questions.)

Schema notes...  (Smaller is faster)

BIGINT is 8 bytes.  Can odom go beyond 16M miles, the limit for `MEDIUMINT UNSIGNED (3 bytes)?
INT(4) -- the (n) means nothing; suggest TINYINT (1 byte) or SMALLINT (2 bytes).
Some of the VARCHARs could be 'normalized' to say a 2-byte SMALLINT UNSIGNED.


Setting up users and admins with schema privileges I currently have a database that is split into two schemas: data and ref.  I have two database roles named Users and Admins.  Users need to be able to INSERT, SELECT, UPDATE, and DELETE from the data schema, and need to be able to SELECT from the ref schema.  Admins need to be able to INSERT, SELECT, UPDATE, and DELETE from both schemas.

All users and admins interact with the data via stored procedures.  Each table has a generated save, select, and delete stored procedure that the application uses (the stored procedure is in the same schema as the object it's hitting).

I previously had the database roles set up to execute against the schemas, but that's not really what I want, because then a user can insert, update, and delete against the ref schema via a stored procedure.

How can I set up security for the Admins and Users database roles to allow users to INSERT, SELECT, UPDATE, and DELETE against the data schema and to only SELECT against the ref schema while allowing the admins to INSERT, SELECT, UPDATE, and DELETE against both schemas?

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go

create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go

create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go

create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go

use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go

setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.

My advice:

Set up 3 more schemas in somewhat of a n:1 (in fact 2:1) relationship
to the schemas with the tables (i.e.: read_data, read_ref,
change_data, change_ref)
put the procedures in the new schemas (ins, upd, del to change, sel to read)
then a GRANT EXECUTE on the schemas will do it (3 times for users, 4 times for Admins)


SQL query to display names of customers who have purchased all the DVD These are the tables I have created and inserted the values accordingly:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

In order to display names of customers who have purchased all the DVD, this is the query that I have tried out:

SELECT Customer_Name
FROM Customer
WHERE
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

But unfortunately, after executing this query it is displaying all the customer names. But I want to display a specific customer name who have purchased all the DVD.

Can someone provide me the correct query ?

You can try something like:

select c.Customer_Name
from Customer c
join (  select Customer_No
        from DVD_Purchase
        group by Customer_No
        having count(distinct DVD_No) = (select count(*) from DVD)
        ) d on c.Customer_No = d.Customer_No

Explaination: first we need to know the number of DVDs available.

select count(*)
from DVD

Then we need to get the number of distinct DVDs purchased by the customers, and to compare it with the total we got previously.

select Customer_No
from DVD_Purchase
group by Customer_No
having count(distinct DVD_No) = <number of DVDs available>

And finally, we just need to join with customers table to retrieve the names.

;with CTE_test1 (customer_name, customer_no,ppo)
as (
select
customer_name,
p.customer_no
, count(p.dvd_no) [ppo]
from dvd_purchase p
  inner join (select count(*)[test], dvd_no from dvd group by dvd_no) d on d.dvd_no = p.dvd_no
  inner join customer c on c.customer_no = p.customer_no
group by  p.customer_no ,c.customer_name)

select  customer_name from CTE_test1  where ppo = ( select count(*) from dvd)


select
  round(
    (cast(current_timestamp as date) - cast(<other_timestamp> as date))
    * 24 * 60
  ) as diff_minutes
from <some_table>;

This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.

This is from postgresql, but I would hope oracle is as advanced as postgresql ...

Select extract(epoch from stop_time-start_time)) ; #(in seconds)
Select extract(epoch from stop_time-start_time))/60 ; #(in minutes)


Calculate row size and max row size for a table Problem:

Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.

What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci

For example a table test is as following

  create table test
  (
  col1 varchar(25),
  col2 int,
  col3 varchar(3),
  col4 char(15),
  col5 datetime
  );

Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.

Found a some sort of similar solution in MSSQL but need its MySQL version

Script to estimate row sizes for any table

Any help is much appreciated.

After much of thinking and researching found one answer which really helped in achieving what  was required. It is a perl script and reference link is

http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

#!/usr/bin/perl
use strict;
$| = 1;

my %DataType = (
"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,
"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,
"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"BIT"=>'($M+7)>>3',
"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,
"BINARY"=>'$M',"CHAR"=>'$M*$CL',
"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',
"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',
"TINYBLOB"=>9, "TINYTEXT"=>9,
"BLOB"=>10, "TEXT"=>10,
"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,
"LONGBLOB"=>12, "LONGTEXT"=>12
);

my %DataTypeMin = (
"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'
);

my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);
my $fieldCount = 0;
my $byteCount = 0;
my $byteCountMin = 0;
my @fields = ();
my $fieldName;
my $tableName;
my $defaultDbCL = 1;
my $defaultTableCL = 1;
my %charsetMaxLen;
my %collationMaxLen;

open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");
%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <CHARSETS>);
close CHARSETS;

open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");
%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <COLLATIONS>);
close COLLATIONS;

open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");

while (<TABLEINFO>) {
chomp;
if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {
$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;
print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";
next;
}
if ( /^create table\s+`([^`]+)`.*/i ) {
$tableName = $1;
@fields = ();
next;
}
if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {
$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);
print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";
$tableName = "";
$fieldCount = 0;
$byteCount = 0;
$byteCountMin = 0;
while ($_ = shift @fields) {
if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {
$dt = uc $dt;
if (exists $DataType{$dt}) {
if (length $S) {
$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);
$dp = "($M : $S)"
}
$D = 0 if !$D;
$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);
$bc = eval($DataType{$dt});
$byteCount += $bc;
$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;
} else {
$bc = "??";
}
$fieldName.="\t" if length($fieldName) < 8;
print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";
++$fieldCount;
}
}
print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";
next;
}
push @fields, $_;
}
close TABLEINFO;

Thanks all for great help.

There are a few questions of this type already, for instance this one: How to estimate/predict data size and index size of a table in MySQL

One difference between that question and your table is the presence of variable length strings in your's - remember to account for the maximum size that they can be.

Also remember that from version 5 onwards that varchar(25) is up to 25 characters not up to 25 bytes so if you are likely to see non-ASCII characters in your strings the column size could balloon up to a maximum of 100 bytes because some characters take four bytes to represent - for instance "pile of poo emoji" (which, I jest ye not, does exist - if your current browser+font support it it looks like: ) is 0xF0 0x9F 0x92 0xA9. Prior to v5 mySQL counted bytes not characters when specifying string type lengths.

Edit Regarding Automation

In terms of automating the process, you should be able to derive all the information needed from the INFORMATION_SCHEMA tables in a similar manner to the script you have found for MS SQL Server. See https://dev.mysql.com/doc/refman/5.0/en/information-schema.html for some documentation covering that.