Large time difference between two almost identical simple queries....why? I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:

SELECT m.Metadata, COUNT(*) As Count
FROM TWG10MinData twg, Metadata m
WHERE twg.metadata = m.Metadata and (m.Metadata % 1000) = 100
GROUP BY m.Metadata
ORDER BY m.Metadata

Some statistics for this query are: 67000 logical reads, 3000 ms CPU time, 800 ms time elapsed.

SELECT Metadata, COUNT(*) As Count
FROM TWG10MinData twg
WHERE Metadata IN (1100,2100,3100,4100)
GROUP BY Metadata
ORDER BY Metadata

Some statistics for this query are: 20 logical reads, 0 ms CPU time, 0 ms time elapsed.

Table "twg" has a nonclustered index on "Metadata" field (its primary key fields don't appear on the query). Table "Metadata", has its field "Metadata" as primary key and therefore it has a clustered index on it.
As you can see, the only difference is specifying the concrete values that, at the moment, result from this computation "(Metadata % 1000) = 100". Shouldn't SQL engine compute first the valid values for Metadata and then apply the filter? Is this so much time consuming to cause such a big difference in their performance numbers?

Thanks in advance!!

I am willing to bet the issue is that the first does a scan of the table, while the second can look things up from an index.  This happens all the time.  The reason, as Phil has said, is that there are an infinite number of possibilities.

If you want to make the first approach work better with more flexibility you need to use a recursive query structure.  Something like:

WITH RECURSIVE sparse_scan AS (
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
     WHERE twg.metadata = m.Metadata and m.Metadata = 1100
  GROUP BY m.Metadata
 UNION ALL
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
      JOIN sparse_scan s ON (m.Metadata = s.Metadata + 1000)
     WHERE twg.metadata = m.Metadata
  GROUP BY m.Metadata
)
SELECT * FROM sparse_scan order by m.Metadata;

This works differently, running the query for 1000, 2100, 3100, 4100, etc, until a row is not returned.  You can be a little fancier to skip cases where a row does not exist but the next one does, but the basic approach  is something I use frequently, albeit on PostgreSQL.


Unable to view SQL Server services Whenever I enter SQL Server Configuration Manager and choose SQL Server Services I would always get the following error message: "The remote Procedure call failed. [0x800706be]"
everything else works fine but not able to configure SQL Services anymore. has anyone came across this and managed to fix the problem?

If you are still running RTM (or SP1 or SP2 I guess), SP3 was released recently - give that a try and see if it helps fix the Configuration Manager issue.

(Applying a service pack has helped others in a similar scenario.)


Does UDP Port 1434 need to be open for named instances with static TCP? I have built a named instance which is running on a static port, 50101.

The network guys have opened the port on the firewall to allow incoming connections. I have asked them open UDP port 1434 too, as I believe the SQL Browser Service runs on this port and needs to pass the correct port for the named instance back to the requesting connection. Even if the named instance is running on a static TCP port.

Is that correct?

SQL Browser / UDP 1434 is not just for dynamic ports, right?

It needs to be open if the port is not explicit in the connection string?

Really need to end this argument!

MP

UDP 1434 is used for Named SQL Server instances and SQL Browser service listens on this port for any incoming requests to a named sql server instance. The browser service will respond to the client with TCP port no. for the requested named instance.

From BOL :

  The SQL Server Browser service lets users connect to instances of the Database Engine that are not listening on port 1433, without knowing the port number.

  To use SQL Server Browser, you must open UDP port 1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the port number.

-

  It needs to be open if the port is not explicit in the connection string?

Yes. If browser service is not running, you HAVE to specify servername,portNo e.g. YourSecureSQLServerName,50101


Change logging level programmatically for multiple jobs I have multiple Sql Agent jobs, with multiple steps each.  For a large subset of these jobs, I want to set the logging levels to PERFORMANCE instead of BASIC -
 the logs are taking a lot of log space, and, more importantly, are causing a slowdown for other processes.

I went into SysJobSteps, but there is no column that I can update.  I am considering changing hundreds jobs so doing this manually isn't an option for me.

How do I do this using TSQL?

Here is an image of the 4 options available:

The logging level is held in the command column of msdb.dbo.sysjobsteps. By editing the command you can set the logging level you require.  There is a system stored procedure - sp_update_jobstep - to achieve this.

From a quick look on my local system, SSIS job steps' commands seem to be a reasonably straight-forward concatenation of labeled values. A bit of regex and some substitutions should be sufficient.


Generate English sentence and omitting zeros I try to generate an English sentence from two columns. My aim is to convert the table
| a | b |
|---|---|
| 0 | 1 |
| 2 | 3 |
| 4 | 0 |

into the following list
1 B
2 A and 3 B
4 A

I would like to know if there is a less verbose method than the following:
with tmp(a, b) as (
    values
        (0, 1),
        (2, 3),
        (4, 0)
)
select
    case
        when tmp.a > 0 and tmp.b > 0 then
            tmp.a || ' A and ' || tmp.b || ' B'
        else
            case
                when tmp.a > 0 then
                    tmp.a || ' A'
                else
                    tmp.b || ' B'
            end
    end
from tmp

One way you could achieve the goal is by presenting each row as a column (or unpivoting each row) and applying string_agg to it with and as a delimiter. More specifically, it could be implemented like this:
WITH
  tmp(a, b) AS
  (
    VALUES
      (0, 1),
      (2, 3),
      (4, 0)
  )
SELECT
  a,
  b,
  (
    SELECT
      string_agg(word, ' and ')
    FROM
      (
        VALUES
          (nullif(a, 0) || ' A'),
          (nullif(b, 0) || ' B')
      ) AS v (word)
  ) AS sentence
FROM
  tmp
;

As you may have noticed, there is slightly more going on here than just unpivoting and aggregation. In particular, the query is also applying nullif to each value in order to replace a 0 with a null. That way, when a null is concatenated with a string, the result will be a null as well. And when string_agg comes across a null, it simply omits it.
So, for example, unpivoting the first row – (0, 1) – results in a column that looks like this:
null
1 B

Aggregating this column with string_agg yields 1 B, because the null is omitted and there is only one value left, so the delimiter is not used.
In contrast, when unpivoting the second row, we get:
2 A
3 B

We have more than one value here, and when string_agg is applied, it uses the specified delimiter  and  to delimit the values, yielding the string 2 A and 3 B.
And in the last row, we again have only one non-null value, so we get another string without an and.
This solution may not look much more elegant than the CASE solution in your question when applied to only two columns. However, it scales better in case you have more columns to concatenate, as you would only need to add more rows to the correlated query's VALUES constructor:
VALUES
  (nullif(a, 0) || ' A'),
  (nullif(b, 0) || ' B'),
  (nullif(c, 0) || ' C'),
  (nullif(d, 0) || ' D'),
  ...

You can check this query out here at db<>fiddle.uk.


Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:

Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in its table.
Process this set and return other set of rowids
As a result, I want to access the tuples pointed by the returned rowids

For instance, I have a table with the rowids => {1, 2, ..., 10000}. Then, I have processed my algorithm, and as result I have the following rowids => {3, 100, 8394, 12}

However, I have some doubts:

What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?
How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

These are two different questions and you shouldn't ask compound questions on StackExchange.

What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)
How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

An extension doesn't magically make everything faster.


Providing that you'll always close a job before opening another under the same ID (although personally you should never have two seperate jobs running under the same ID without another unique identifier to distinguish (You've opened 4909 and 4910 twice))

if you join the table on itself distinguishing them between Open and Closed you can get it all in one short statement

select opened.jobOrderId as [Job Order ID],opened.dateadded as [opened date], closeed.dateadded as [closed date]
FROM JobHistory as opened
LEFT JOIN Jobhistory closeed on opened.jobOrderID = closeed.jobOrderID and closeed.status='Close' and closeed.dateadded > opened.dateAdded
WHERE opened.status='OPEN'

If you've got an actual unique identifier for the job I'd recommend joining on that rather than the JobOrderID column and you won't then need do the statement:

and closeed.dateadded > opened.dateAdded

as that is simply there to ensure that you don't close something that was opened in the future

Ste


Are there reasons to not use SCHEMABINDING on database objects? Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime.

The benefit is that schema bound objects protects the database from accidental or deliberate changes that could break dependencies later.

The cost of the protection is that maintenance is more complicated because schema bound objects sometimes require altering multiple objects in a particular order to achieve what would otherwise be a simple localized change. This cost is relative since without the schema binding a change could silently break something that requires more maintenance later.

I think the benefit outweighs the cost, especially for established databases that aren't changing much (less changes so less maintenance) or where you don't know the dependencies (less familiar so more risk).

Are there situations where schema binding shouldn't be used?

I don't see any reasons of NOT using SCHEMABINDING on database objects. Its like safe guarding your objects.

Obviously there are pros and cons associated to it.

If you are not going to create Index Views, then you dont need SCHEMABINDING for views.

Basically, SCHEMABINDING

prevents the views from becoming invalid due to the change in table structure e.g. dropping columns from table.
makes UDF's deterministic - as described at Improving query plans with the SCHEMABINDING option on T-SQL UDFs

The only downside that I see is, it will make changing the table schema a bit difficult, even if it is just a small change e.g changing a NOT NULL column to allow nulls.


SSMS 17.1 stopping autocomplete suggestion of variable types I'm trying to figure out how to stop autocomplete suggestion of variable types while keeping the autocomplete suggestion for table and column names (so disabling autocomplete function is note the solution).

For example, if I type NVARCHAR, the autocomplete suggests nvarchar and once I continue typing, it automatically correct the word.

It is a convention of my company to write variable types uppercase and the autocomplete function transform them to lower case each time. I know i can hit "Esc" to avoid the correction, but do this each single time is something really annoying.

UPDATE:
Actually the same problems happens when tiping for example IS:

CASE WHEN colName IS NULL

becomes

CASE WHEN colName IS_CALLERSIGNED NULL

if i don't press esc

For example, if i type NVARCHAR, the autocomplete suggests nvarchar
  and once i continue typing, it automatically correct the word.

This autocompletion is not just a "convention", SQL Server types that you can see querying sys.types are all lowercase, and this is a good reason to use them ALWAYS lowercase:

if one day your code migrates to a server with case sensitive collation, no one of your UPPERCASE types will be recognized as system type.

So yours is not a good convention and I think you cannot change this at least in SSMS because they(MS) are aware of this possible issue.

You can look for some customizable code formatter in internet


What allows SQL Server to trade an object name for a string passed to a system procedure What causes it to be legal to pass an object name to the system stored procedure sp_helptext?

What mechanism converts the object name to a string?

e.g.

-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same as a string
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 1 incorrect syntax near '.'
-- an additional case that does not work.
sp_helptext [dbo].[myproc] -- Msg 102, Level 15, State 1, Line 1 incorrect syntax

It seems odd that I'm not required to single quote valid proc names, unless it has a . separating schema name and procedure name. I'm looking for an explanation of how it gets auto converted from a quoted name to a string literal to be passed as the value of the parameter.

I don't have a specific issue to resolve; I am simply inquisitive about things that aren't documented.

The first argument to the system stored procedure sp_helptext is:

  [@objname= ] 'name'
  Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. The object must be in the current database. name is nvarchar(776), with no default.

In addition, the documentation for Delimited Identifiers (Database Engine) states:

  Using Identifiers As Parameters in SQL Server
  Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.

  Single-part Parameter Names
  If the parameter is a single-part identifier, the name can be specified in the following ways:

  Without quotation marks or delimiters
  Enclosed in single quotation marks
  Enclosed in double quotation marks
  Enclosed in brackets

  Multipart Parameter Names
  Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.

The first argument to sp_helptext accepts both single-part (nonqualified) and multipart (qualified) object names.

If the T-SQL parser interprets the item after sp_helptext as a single-part name (in accordance with the four bullet points above), the resulting name is passed as the (string type) argument value expected by the procedure.

When the parser sees it as a multipart name, the text is required to be surrounded with single quotation marks as stated.

The key feature of a multipart name is a . separator (outside any delimiters).

These examples from the question are successfully interpreted as single-part names:

myproc       - single-part (without quotation marks or delimiters - bullet #1)
[myproc]     - single-part (in brackets - bullet #4)
'myproc'     - single-part (in single quotation marks - bullet #2)
'dbo.myproc' - multipart with the required single quotation marks
[dbo.myproc] - single-part (in brackets - bullet #4)

The last two examples from the question are both parsed as multipart parameter names (due to the exposed . separator). They produce an error because they lack the required enclosing single quotation marks:

dbo.myproc     - multipart without the required single quotation marks
[dbo].[myproc] - multipart without the required single quotation marks

This extra example using double quotation marks is successful:

"dbo.myproc" - single-part (in double quotation marks - bullet point #3)

Note that it is successfully interpreted (for the procedure parameter value) as being a valid single-part name, but the procedure code is able to interpret the (multipart) string it receives flexibly (using PARSENAME and OBJECTID).

As a final point of interest, note that using double quotation marks here does not depend on the setting of QUOTED_IDENTIFIER.


GHOST_CLEANUP Lots of IO and CPU Usage I have been trying to troubleshoot an issue I have detected on a UAT SQL Server, there is some resource usage even if the server is not being used...

On Activity monitor I have an average of 5% CPU usage (Real SQL Server Usage double checked with DMVs) and also a 20mb/s disk IO, this has been the same for 2 days now...

I have found that the only process that is doing IO Operation is the ghost cleanup...

After reviewing what data file is doing the IO Operation I found that I have 1500Reads/sec (NO WRITE OPERATIONS) on one Database where some users were doing load tests days ago...

What can I do with this, will this process finish? Is there anything I can do?

EDIT

After taking some time on troubleshooting this I finally found that after disconnecting the Database from the Availability Group GHOST_CLEANUP started to really free up space on my Database, took 4 hours for him to free 80GB of Storage. After connecting the Database to the Availability Group GHOST_CLEANUP starts to not free space again... Weird.

What can I do with this, will this process finish? Is there anything I can do?

This is how Ghost Cleanup works it kicks up after every 10 seconds and does the cleanup. The ghost cleanup does not filters out specific pages to cleanup, when ghost cleanup operation starts it takes up 10 pages which are marked as ghosted and cleans them up. This process happens every 10 sec unless scan which run and finds out ghost records come and says that there is no ghost records.

Yep ghost cleanup is single threaded, I/O intensive and also fills up transaction log and you cannot avoid this unless you disable it. To disable this process you can enabled trace flag TF 661 documented in This support article. As a good advise it is  NOT recommended but since you have testing system you can of course try it.

So I believe the testers are doing some big delete operation may be periodically forcing lot of ghost records and this forcing ghost cleanup.

I would suggest you wait till the process completes. Like I said it will pick not more than 10 pages and so depending on amount of pages deleted it may take some time.

Some very good readings on Ghost Cleanup process.

Inside Storage Engine Ghost Cleanup in Depth
Turning Off Ghost Cleanup Tasks for Performance Gains

EDIT:

  My main concern however is that if I disable the Ghost Cleanup task, how then I will clean the rows marked to be deleted?

I would like to quote from the post Turning Off Ghost Cleanup

  If you disable the ghost cleanup task, the space taken up by deleted records will NOT be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.

One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.


Modify Table-Valued Parameters I have created a Table-Valued Parameters on my SQL Server 2008 R2 install.  I created a field as a varchar(25) and now I need to modify it to a DateTime

What would be the process for doing such?

One option is to drop the type and then re-create it. You will have to modify all the modules that use it first, so a better and less disruptive option might be to create a new table type with the right data type, modify each of the modules to point at the new type, then drop the old one.

The syntax is simply:

DROP TYPE dbo.TableValue_Test;

However this will fail while modules still reference the type. If the TVP is only referenced in client code, you might have less to worry about, but you should still update the app code to use the right data type from end to end.


Insert if not exists, concurrently I am having concurrency issues with my inserts in a stored procedure.  The relevant part of the procedure is this:

select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
    insert into table1 (othervalue) values (@_othervalue)
    select @_id = Id from table1 where othervalue = @_othervalue
END

When we run 3 or 4 of these stored proc's concurrently we get multiple inserts on occasion.

I am planning on fixing this like so:

insert into table1 (othervalue)
    select TOP(1) @_othervalue as othervalue from table1 WITH(UPDLOCK)
    where NOT EXISTS ( select * from table1 where othervalue = @_othervalue )

select @_id = Id from table1 where othervalue = @_othervalue

The question is, is that how to concurrently insert without duplicates in sql server? The fact that i have to use TOP to only insert once disturbs me.

You could use a merge statement with serializable hint.

merge table1 with (serializable) as T
using (select @_othervalue as othervalue) as S
on T.othervalue = S.othervalue
when not matched then
  insert (othervalue) values (othervalue);


How to make cdc skip the point that it is stuck at? I have an issue where the transnational log has grown to its max size.
Running fn_dblog I noticed that its complaining that a drop was done. It looks like cdc is stuck there. The description column in fn_dblog displays DROPOBJ;0X01.......

Also, the cdc capture job gives the following error message "...Msg 8152, Level 16, State 2, String or binary data would be truncated. For more information, query the sys.dm_cdc_errors dynamic management view."

So this is what happened that could have caused this...

I created a script that (1) disabled triggers (2) created table (3) created cdc table. I ran this and it was successful.
I made a mistake with the table name, so I dropped the table. I did this without disabling the cdc for this table.
I had then edited the script (mentioned in point 1) by correcting the table name and ran it again, yes it complained about the cdc already exiting.

I did not do anything else at this point.

Can someone please advise on a solution to maybe allow cdc to bypass where it is stuck at.

I'm new to databases please bare with me.

You can try running the cdc cleanup job.
It should have a name similar to this.

[cdc.Replication_cleanup]


In MySQL 5.5: can I capture the output of SHOW ERRORS? Is it possible to capture the messages that can be listed with SHOW ERRORS and store them in a log table? Like, is there a table somewhere, that I can select from?

Use

SELECT *
FROM sys.statements_with_errors_or_warnings
WHERE last_seen > NOW() - INTERVAL ??? MINUTE;

Set a reasonable time limit.

Or query to performance_schema, tables events_statements_current, events_statements_history and events_statements_history_long.


MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin I am a developer so was hoping someone with DBA experience could help me out here.

I am using a MySQL database (MYSQL 5.6).

I came across a situation where a particular column stores Ids generated by any external system (to be provided by user input) and hence requires case sensitivity (abcd is a different id from ABCD). It seemed pretty natural to me to change the collation for that column to latin1_bin from latin1_swedish_ci where my database and tables all use latin1_swedish_ci collation.

I use the following statement to change the collation:

ALTER TABLE T MODIFY C VARCHAR(200) BINARY NOT NULL;

Are there any performance implications of doing this change? My application does not use COLLATE specifically in any query.

Also, column C is part of an index and a unique constraint with 2 other columns say A, B in table T (A,B,C). A and B are also foreign keys in table T.

Do I need to drop the index and unique constraints and re-create them?

Also, how do I rollback this change? Will this statement do the job:

ALTER TABLE T MODIFY C VARCHAR(200) NOT NULL;

The table T looks like :

|id|A|B|C|created_at|

id is auto increment.

Please help.

Not significant performance difference.

It will require rebuilding any indexes involving that column.  That will take time, but it is a onetime issue.

Any *_ci collation is slightly more complex than a *_bin collation, at least when doing comparisons or sorting.

If you choose to undo the change, be explicit about the collation.  Your proposed ALTER hopes that the 'default' will be used.

An ALTER .. COLUMN will rebuild the necessary indexes.


Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both? I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and separate user-facing OrderNumber or I can only have an OrderNumber as my primary key as the OrderNumber is going to be unique?
Also, I was considering OrderId to be a UUID type. So can I do the reverse of having only OrderId and considering it as User-Facing Id as well?

You can do either or both, it doesn't really matter much, other than for some minor performance tuning things as Akina points out.
But yes you're allowed to have two fields that are unique, but only one primary key can exist, so you'd have to choose if that will be your OrderId or your OrderNumber, or a composite key that includes both fields.
Typically if the OrderNumber is a numeric data type field and unique then most people would just use this as the primary key, there would be no need for an OrderId (as this would be a column with a redundant purpose). Otherwise if the OrderNumber was something more complex in format, e.g. "124-72-2101-yum" then having a separate field for OrderId as your primary key would probably be a better choice.
The only other thing to consider, is I see you mentioned using UUID for OrderId. If you were to make a UUID field as your primary key (and keep the clustered index on it) there would be performance degradation when trying to JOIN your other tables to it (depending on how big your tables were). You theoretically could make it the primary key without making it the clustered index, and again if your OrderNumber field is of a numeric type then you can make that the clustered index for performance reasons, but at that point you might as well just use OrderNumber as your primary key too and get rid of the OrderId field.
An INT or BIGINT column provide just as much benefits as UUID in regards to being a primary key, but with much better performance. The only benefit UUID carries over them is somewhat global uniqueness which can be useful in certain contexts (like a mobile app that needs to function offline and be consolidated with the main database later on). Though there are ways to achieve this only using INT and BIGINT data types as your primary keys too, it just requires a little more implementation work. But even then my personal preference would be to use INT or BIGINT and put in the little bit of extra work upfront, to not worry about continually trying to optimize performance of my queries later on. And even UUID has a small chance for collisions / producing the same ID twice too (across systems), so it's not perfect.


Order of output row in random order When I do select query on single table then I get output rows ordered by primary key which is perfect, following query:

select * from recondata where importid=131;

but when I'm joining this table to another table output of row is random, ReconStatus contains only statusId (Primary key) and Status and in ReconData, dataID is primary key and there are no other key. I'm using following query:

select dataid,coalesce(clientPortfolioname,portfolioName) as portfolioName,
                            Status, Message, PositionId, PositionIdType,    PositionName, ClientMarketValue,
                            ClientCurrency, ClientMarketValueBase, MarketValueBase, MarketValueLocal,
                            MVDiff, SourceInstrumentType, Weight, WeightByClient, ImportType,AssetType,
                            AssetName,ExtraInfo
                            from
                            ReconData rd inner join ReconStatus rs on rd.StatusId = rs.statusid
                            where
                            ImportId=131;

Output is also not based on primary key of second table.
Can someone explain on what basis MS SQL is ordering output rows ?

You cannot predict the order of a query. Even though when the query is only reading one table. This has to dowith several factors like vacuuming or used indexes. Always think in sets with databases.


Slow SQL query with LEFT JOIN What my query is doing:

I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current member-sessions/logins and with my statistics table I also want to see member-logins of the past. Thus I want to store these data into my bot_sessions_statistics table as well to make sure they are not being updated anymore:

The original query:

INSERT INTO bot_sessions_statistics (member_id, session_token, username, ip_address)
SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN
(
    SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
    FROM bot_sessions_statistics as stats
    WHERE date_active >= date(NOW())
) AS stats
ON sessions.member_id = stats.member_id
WHERE latest_renewal >= date(NOW())
AND stats.member_id IS NULL

The performance of the original query:

Query_time: 86.364613  Lock_time: 0.000085 Rows_sent: 0  Rows_examined: 1088312551

The table structures:

Table bot_sessions:

Table bot_sessions_statistics:

SHOW CREATE of both tables: http://pastebin.com/UUzEPX5v

The performance of single queries:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE latest_renewal >= date(NOW())

Returns 44.2k rows (Duration: 0.078s / Fetch: 1.607s)

SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
FROM bot_sessions_statistics as stats
WHERE date_active >= date(NOW())

Returns 23.3k rows (Duration: 0.047s / Fetch 0.920s)

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not.
At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows.
I guess your query will at least require an index on latest_renewal (but, again, check with EXPLAIN).

This is my understanding of your query, on which I based my simplification:

SELECT sessions.member_id
    ,sessions.session_token
    ,sessions.username
    ,sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN ( /* */
    SELECT stats.member_id
        ,stats.session_token
        ,stats.username
        ,stats.ip_address
    FROM bot_sessions_statistics AS stats
    WHERE date_active >= DATE (NOW()) /* a. I think this gives all of 'today' statistics */
    ) AS stats ON sessions.member_id = stats.member_id /* b. you're joining on 'member_id' which is NOT NULL in sessions, so any row from this subquery will have a value for (stats.)member_id */
WHERE latest_renewal >= DATE (NOW())
    AND stats.member_id IS NULL /* c. this will filter out all the rows from the JOIN, since no stats.member_id can be NULL */

Next, at first sight (if I'm not mistaken) I think your SELECT query can be rewritten without the LEFT JOIN, like this:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE
    NOT EXISTS (
        SELECT 1 FROM bot_sessions_statistics
        WHERE date_active >= date(NOW())
    )
AND latest_renewal >= date(NOW())

If the above is really correct, compare the results of EXPLAIN on both.


mysql need to show all months data for the selected year I got stuck with my query. I need to show monthl (current + previous) records.

Let's take below example for year 2014:-

I have 7 records in the month 03(March)

I have 2 records in the month 04(April)

So total records in year 2014 is 9.

Now we come to year 2015:-

I need to show all records in Jan(No new records added in database), This should be 9.

My Query shows 0.

I need to show all records in Feb(5 new records added in database), This should be 14.

My query shows 5.

Query works fine when I am checking for month 03 and year 2014, showing all 7 records. but not for all conditions.

  I am calling same query for all months(01-12).

SELECT count(pkID) as TRecords
FROM   students
WHERE  (fkCategoryID ='56'
OR     fkSecondaryCategoryID ='56'
OR     fkOptionalCategoryID ='56')
AND    MONTH(`DateAdded`) <='1'
AND    YEAR(`DateAdded`) <='2015';

Maybe:

SELECT
   MONTH(`DateAdded`),
   YEAR(`DateAdded`),
   count(pkID) as TRecords
FROM students
WHERE
    fkCategoryID ='56' OR
    fkSecondaryCategoryID ='56' OR
    fkOptionalCategoryID ='56'
group by
    MONTH(`DateAdded`), YEAR(`DateAdded`)

Or maybe some more filters. Your question is not all clear.

Or maybe:

if object_id ('tempdb..#students') is not null drop table #students

create table #students
    (
    pkID int,
    DateAdded date
    )

 insert #students values
      (1, '20100101'),
      (1, '20100201'),
      (1, '20100301'),
      (1, '20100401'),
      (1, '20100501'),
      (1, '20100601'),
      (2, '20100101'),
      (2, '20100201'),
      (2, '20100301')

SELECT
   MONTH(DateAdded),
   YEAR(DateAdded),
   count(pkID) as TRecords,
   sum(count(pkID)) over(partition by YEAR(DateAdded) order by MONTH(DateAdded))
FROM #students
group by
    MONTH(DateAdded), YEAR(DateAdded)
 order by 2,1

Or maybe:

SELECT
   MONTH(s.DateAdded),
   YEAR(s.DateAdded),
   count(s.pkID) as TRecords,
   (select count(pkID)
    from #students
        where
            YEAR(DateAdded) <= YEAR(s.DateAdded)
            and MONTH(DateAdded) <= MONTH(s.DateAdded)
   )
FROM #students s
group by
   MONTH(s.DateAdded), YEAR(s.DateAdded)


Delete millions of rows based on ids in another table I have two tables on MySQL:

HISTORY_TABLE (4 million rows) (column1 indexed)
EXTRACT_TABLE (600K rows) (column1 indexed)

For every EXTRACT_TABLE.column1 there are multiple HISTORY_TABLE.column1 rows.

Objective: I want to delete all records that MATCH criteria:

HISTORY_TABLE.column1 = EXTRACT_TABLE.column1

I tried two different possibilities:

Simple query that matches criteria (10 hours)
Copying the NOT EXISTS rows into a new table (2days+)

I found via searching that deletion can be done in chunks with a stored procedure, but I don't know how.

Is there an example on how to do a cursor based on EXTRACT_TABLE to read in chunks and delete the other table? Or is there other way to accomplish my objective?

Since you are deleting a large percentage of the table, it would be better to build a new table with the rows to keep:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        LEFT JOIN extract  ON ...
        WHERE ... IS NULL;
RENAME TABLE real TO old,
             new TO real;    -- swap tables (sort of)
DROP TABLE old;              -- clean up

Even that may be too invasive.  See the following for techniques for deleting "in chunks".  They won't finish "fast", but they won't have nearly as much impact on other activity.  http://mysql.rjweb.org/doc.php/deletebig

(FOREIGN KEYs and TRIGGERs are likely to cause trouble.)


Baffled with MySQL LEFT JOIN query that I have to speed it up I am kind of a puzzled with this query trying to optimize it but no way I am able to (due to my limited knowledge as a DBA). So, I asked the experts.

In my setup I have three (primary) tables. contacts, dnc_contacts (dnc = do not call) and callrequests. I have to select the contacts from contacts table, and copy the contact into callrequests table where contact is not equal to contacts from dnc_contacts. It all looks good so far, but since the data is growing, things are getting slower and slower. The original query I am currently using is:

INSERT IGNORE callrequests (contact_id, contact, call_status, campaign_id, phonebook_id, dnc_group_id, created_date, active_call, processed)
SELECT c.id,
       c.contact,
       1,
       '134',
       '70',
       '21',
       '2014-03-14 14:50:58',
       0,
       0
FROM contacts c
LEFT JOIN dnc_contacts d ON c.contact <> d.contact
WHERE phonebook_id = '70' AND d.group_id = '21'
ORDER BY c.id ON DUPLICATE KEY
UPDATE phonebook_id =
VALUES(phonebook_id), dnc_group_id =
VALUES(dnc_group_id), call_status = if(call_status = '2', VALUES(call_status), call_status)

This query is taking around 8 minutes and plus to complete depending on the data in contacts and dnc_contacts table. Schema for all these 3 tables is at http://sqlfiddle.com/#!2/1ec3a

Setup

The setup is a dedicated Xeon machine with 12 cores having 64 GB RAM and 38 GB InnoDB buffer pool size. The DB size is around 6 GB.

Callrequests table has around 5427519 records

Contacts table has around 20498053 records

Dnc_Contacts table has around 4042246 records

Here is the explain output from the select query above,

+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys                    | key  | key_len | ref   | rows    | Extra                    |
+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | d     | ref  | fk_dnc_contact_dnc_group1_idx,v1 | v1   | 4       | const | 2014474 | Using index              |
|  1 | SIMPLE      | c     | ref  | fk_contact_phonebook1_idx,v1     | v1   | 4       | const | 2218560 | Using where; Using index |
+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+

Is there a way the query can be speed up or maybe if we can change the logic either?

Your query does not do what you think it does. It results in much more rows than you want to because of the cross join. The only reason you haven't noticed it, is that the excessive number of rows produced are not really inserted in the 3rd table due to duplicate keys which trigger either the INSERT IGNORE or the ON DUPLICATE KEY UPDATE

(And you should really keep one of these two, there is absolutely no reason to have both IGNORE and ON DUPLICATE KEY UPDATE. MySQL will either ignore the duplicate keys or try to update the existing row. Does it make sense to ignore someone and talk to him at the same time?)

You probably need:

FROM contacts c
  LEFT JOIN dnc_contacts d
    ON  d.contact = c.contact
    AND d.group_id = 21
WHERE c.phonebook_id = 70
  AND d.contact IS NULL

Try the above (just SELECT first, without inserting), to see if it gives you the wanted results.

An index on dnc_contacts (group_id, contact) would help with efficiency.


SQL Find Minimum Value Greater than Previous Row How do I find the minimum value greater than the previous row?
This code needs to be optimized to run over million rows and business keys, we may have 3-10 timevalue columns at most. I am thinking its some type of recursive CTE.

background: conducting slowly changing dimensions for many tables which are being denormalized. And trying to find proper begin dates.

Data Sample:

create table dbo.timetest
(
TimeTestIdentityId int primary key identity(1,1),
businesskey int,
timevalue1 int,
timevalue2 int,
timevalue3 int
)

insert into timetest
values
(5,131,134,137),
(5,131,138,135),
(5,131,140,135),
(5,143,141,145),
(5,149,141,148),
(5,150,141,148),

(6,134,137,140),
(6,134,141,138),
(6,134,143,138),
(6,146,144,148),
(6,152,144,151),
(6,153,144,151)

Final Values:

,etc

Row are ordered by TimeValue1.

Select MIN value from multiple columns

My solution using a cursor and a temporary table, I can imagine another solution, but it involves an expensive recursive query.

CREATE TABLE #tv
(
    businesskey int NOT NULL,
    id int NOT NULL,
    timevalue int,
);
GO

DECLARE @bk int, @tv1 int, @tv2 int, @tv3 int;
DECLARE @last_min int = 0;
DECLARE @last_bk int = 0;
DECLARE @id int;

DECLARE curtv CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT   businesskey, timevalue1, timevalue2, timevalue3
    FROM     timetest
    ORDER BY businesskey, TimeTestIdentityId;

OPEN curtv;
FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;

WHILE @@fetch_status = 0
BEGIN

    -- First row of a businesskey, basically set counters to 0
    IF @last_bk  @bk
    BEGIN
        SET @last_bk = @bk;
        SET @last_min = 0;
        SET @id = 0;
    end

    SET @id = @id + 1;

    -- select minimum positive value
    -- it could be replaced by the below inline UDF but I think it doesn't worth.
    SELECT @last_min = min(v) + @last_min
    FROM   (VALUES (@tv1-@last_min),(@tv2-@last_min),(@tv3-@last_min)) as t(v)
    WHERE  v > 0;

    INSERT INTO #tv VALUES (@bk, @id, @last_min);

    FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;
END

CLOSE curtv;
DEALLOCATE curtv;
GO

-- one temp table has been generated you could add an index.
ALTER TABLE #tv ADD CONSTRAINT [PK_tv] PRIMARY KEY (businesskey, id);
GO

SELECT * FROM #tv ORDER BY businesskey, id;
GO

IF OBJECT_ID('tempdb..#tv') IS NOT NULL DROP TABLE #tv;
GO

businesskey | timevalue
----------: | --------:
          5 |       131
          5 |       135
          5 |       140
          5 |       141
          5 |       148
          5 |       150
          6 |       134
          6 |       138
          6 |       143
          6 |       144
          6 |       151
          6 |       153

db<>fiddle here

Using a recursive CTE:

IF OBJECT_ID('min_positive') IS NOT NULL
    DROP FUNCTION min_positive;
GO

-- inline UDF you should add as many parameters as you need.
CREATE FUNCTION min_positive(@v1 int, @v2 int, @v3 int)
RETURNS TABLE AS
RETURN
    SELECT MIN(v) AS minpos
    FROM (VALUES (@v1),(@v2),(@v3)) AS t(v)
    WHERE v > 0;
GO

;WITH ct1 AS
(
    -- set a rank for each businesskey
    -- I need it for the anchor in the recursive part
    SELECT businesskey, timevalue1, timevalue2, timevalue3,
           RANK() OVER (PARTITION BY businesskey ORDER BY TimeTestIdentityId) rk
    FROM   timetest
)
, ct2 AS
(
    -- first row of each businesskey
    SELECT businesskey, timevalue1, timevalue2, timevalue3, rk,
           (SELECT minpos
            FROM   min_positive(timevalue1, timevalue2, timevalue3)) AS lm
    FROM ct1
    WHERE rk = 1

    UNION ALL

    -- next row of the businesskey untill the end of the rank
    SELECT ct1.businesskey, ct1.timevalue1, ct1.timevalue2, ct1.timevalue3, ct1.rk,
           (SELECT minpos + ct2.lm
            FROM min_positive(ct1.timevalue1-ct2.lm, ct1.timevalue2-ct2.lm, ct1.timevalue3-ct2.lm)) lm
    FROM ct1
    JOIN ct2
         ON ct2.businesskey = ct1.businesskey
         AND ct1.rk = ct2.rk + 1
)
SELECT   businesskey, lm
FROM     ct2
ORDER BY businesskey, rk;
GO

db<>fiddle here


Insert values with leading zeros How do I insert values with leading zeros in it?

Example.

db2 "insert into db2inst1.asd_currency
(currency_code, currency_name, currency_decimal_count, currency_code_desc)
values ('007', 'afghani', 2, 'afa'), ('009','lek', 2, 'all')"

Its output would give me something like below

db2 "select * from fcm_currency"

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
            4 afghani                                   2 afa
            8 lek                                       2 all
            7 afghani                                   2 afa
            9 lek                                       2 all

  4 record(s) selected.

However, I need to retain the leading zeros because it's a code and I'll be needing that for my application.

EXPECTED OUTPUT:

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
          004 afghani                                   2 afa
          008 lek                                       2 all
          007 afghani                                   2 afa
          009 lek                                       2 all

  4 record(s) selected.

As is suggested in the comments, you should change the data type to, for example, char(3). If that is not possible, you can transform the value of the column like:

SELECT SUBSTR(DIGITS(CURRENCY_CODE),1,3) AS CURRENCY_CODE
FROM ...

or perhaps:

SELECT DIGITS(CAST(CURRENCY_CODE AS DECIMAL(3,0))) AS CURRENCY_CODE
FROM ...

which would be the same as:

SELECT DIGITS(DECIMAL(CURRENCY_CODE, 3,0)) AS CURRENCY_CODE
FROM ...

when you retrieve it


MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE I am working with MySQL 8.0 through MySQL Workbench on a Windows machine.

I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data contains blank spaces or periods . to denote the absence of values for some positions.

I have received the data in the form of an Excel spread-sheet. As I have read that using CSV format may sometimes cause problems, I have saved the spreadsheet as a tab-delimited text file. I have created a table in MySQL Workbench according to the fields of the spreadsheet and everything is ready for the import. My problem is upon typing the following code into the MySQL Workbench:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

I receive errors about incorrect values.

Some of the positions of the data are not of the type that the database expects. For example, there is a column that expects a DATE. The issue is that in the data, some fields corresponding to the column are blank. I think what is happening is that when MySQL tries to import that blank value, the error is caused.

Other columns have either empty spaces (no values) or indeed a single period . to denote that there is no value for that position. Depending on the column, the database may expect type Date or Int. I have searched extensively for advice on the subject. The first answer on this page had looked hopeful, but after implementing a copy of the solution I received an error of the type:

  Error Code: 1366. Incorrect integer value: ''for column 'abs_2012' at row 1.

abs_2012 is of type Int and row one's value is an empty space. What is particularly strange is that abs_2012 is the last column of about 30 I haven't received any errors about the previous columns.

Does anyone have any idea at all?

Many thanks in advance

Use SET form of LOAD DATA INFILE (load the whole line into a user-defined variable and then parse it into separate fields).

Example:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
IGNORE 1 ROWS
( @temp )
SET @count = LENGTH(@temp) - LENGTH(REPLACE(@temp, '\t', '')),
    id = CASE WHEN @count > 0
              THEN SUBSTRING_INDEX(@temp, '\t', 1)
              ELSE NULL END,
    field1 = CASE WHEN @count > 1
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 2), '\t', -1)
                  ELSE DEFAULT() END,
    field2 = CASE WHEN @count > 2
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 3), '\t', -1)
                  ELSE CURRENT_TIMESTAMP END,
    /* and similar expressions for all another fields */

The problem is that a record is inserted always - ever if a line is empty. So you need to remove those "empty" records after importing, and set some correct literal values to NOT-NULL fields which do not have default value.

  Depending on the column, it may be of type Date or Int.

You can easily create unique parsing statement for each separate field dependent by its data type. Like

fieldX = CASE WHEN @count > X
              THEN some_parsing_function_or_expression(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))
              ELSE {default value proper for field's data type} END,

or the same using intermediate variable

@tmpX = CASE WHEN @count > X
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))
             ELSE 'default value proper for field data type' END,
fieldX = CASE WHEN @tmpX = 'default value proper for field data type'
              THEN @tmpX
              ELSE some_parsing_function_or_expression(@tmpX) END,


Trigger that updates another table when inserting and deleting from one table I need help with modifiying this trigger. I'm new to triggers. When it's executed it is supposed either to delete 1 from nrOfCopies or add 1 to nrOfCopies on a based on the specific isbn(a char(20)), but this does it on all the rows. Becuase its a trigger i cannot use a parameter.

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN
       PRINT 'DELETE'
       UPDATE book
       SET nrOfCopies=(SELECT nrOfCopies FROM bookcopy WHERE isbn )
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE book
       SET nrOfCopies=nrOfCopies+1
   END
END

The column I need to specify is called isbn. How can I specify it to only make the changes on one specific isbn column?

When this trigger is being fired i get 'Primary key violation' error message. I dont know how to fix this!

Something along the lines of the below... just join to the inserted/deleted tables.  Please do test this though with real life scenarios:

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN
       PRINT 'DELETE'
       UPDATE b
       SET nrOfCopies= nrOfCopies-1
       from book b
       inner join DELETED d
         on d.isbn = b.isbn
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE b
       SET nrOfCopies=nrOfCopies+1
       from book b
       inner join inserted i
         on i.isbn = b.isbn
   END
END


PRODUCT_ID as primary key rather then the UPC(UNIVERSAL PRODUCT CODE) I have a very strange kind of question in my mind, I'm using the EAV model in my E-COMMERCE DB. I have a product table with primary key p_id which is auto_increment, and to save its attributes I have a different table product_attribute, that also stores SKU of every product and have p_id as FOREIGN KEY, which there I am using attribute-value pair. So, my question is, if I missed storing a product of size 7 of any product of a company A in product_attribute table and stored all the other sizes. How, will I know that what was my p_id for the product I missed at a later time when I want to store it? SHOULD I use UPC instead of p_id?

Auto-increment IDs are what's called surrogate keys. In most cases, data has some sort of a natural key as well. In your case, it sounds like the natural key is the UPC.

So, if there's a natural key, why create a surrogate key? There are several reasons. Two of the most important to me are size and changeability.

When a natural key is a varchar or nvarchar value, that key is frequently much larger than 4 bytes (the size of an int). For frequently used keys, the space difference involved can be noticeable.

Also, natural keys are most often values that have a meaning outside of the database. It may happen rarely, but occasionally something like a UPC code may change. If it's used as the key to your product table, updating all references to it can take a long time, and require a large number of locks.

In your example, I assume that the product of size 7 that didn't get entered would still have its UPC available. If so, you'd look up the UPC code's product_id, and use that value in your EAV table.

If the UPC is not stored in the database, some sort of identifying information still must be. Use the SKU of one of the related products (for example), or (better yet) add an appropriate overall description to the product record so you can locate it. If there's nothing related in the database, then logically the product ID could be anything, and wouldn't have to be connected to any existing data.


Convert datetime stored as char the YYYMMDDHHmmSS format to datetime I'm working with a table where the date and time of an event is stored as a char in the format YYYMMDDHHmmSS or '20120606122012'. I need to compare this to the current timestamp to see if it's been more than 20 minutes since the event happened, but that seems to be more difficult than I anticipated.

Is it possible to do this, or if not: is there a different approach that works?

Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.

Something like this:

declare @S varchar(50)
set @S = '20120606122012'

select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)


MySQL replication fails due to duplicate keys after master is restarted I'm new to MySQL load balancing, I want to try the master-slave concept.

Whenever I setup master-slave for the first time, and both have up-to-date fresh data, that works perfectly fine. However,  whenever the master goes down, or I stop and then restart it, the slave starts generating errors because of duplicate keys.

Why would this be happening and how can I prevent this?

My application has a lot of update queries something like 5 SELECTs then 3 UPDATEs or INSERTs

My data will grow with time and I'm expecting quite a high load in the future.

Don't stop a master while any slaves are connected to it. If you need to stop it, then stop the slaves first and the master at the end. This way, you won't have any errors like the one you described.


How to design tables such that a person can work for many companies? Database design such that a person can work for many companies?

You'd need a many-to-many table that maps people to companies.  Something like (I'm guessing at your table definitions)

CREATE TABLE employment (
  person_id INTEGER REFERENCES person ( person_id ),
  company_id INTEGER REFERENCES company ( company_id ),
  CONSTRAINT pk_employment PRIMARY KEY ( person_id, company_id )
);


"Your password has expired" after restart mysql when updated mysql 5.7.8-rc(debian) echo time login in mysql shell it shows:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

and error log shows:
Your password has expired. To log in you must change it using a client that supports expired passwords.

MySQL password expired

Typically, in a script shell, you get the message: "Your password has expired. To log in you must change it using a client that supports expired passwords."

From MySQL 5.7.4 to 5.7.10, the default value for the default_password_lifetime variable is 360 (a year). For those versions, if you make no changes to this variable or individual user accounts, all user passwords will expire after 360 days.

To prevent automatic password expiry log in as root:

mysql -u root -p

then, for clients that automatically connect to the server (e.g. from scripts.) change the password expiration settings for those clients:

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

or just disable automatic password expiration for all users:

SET GLOBAL default_password_lifetime = 0;

Links and resources:

MySQL: Password Expiration and Sandbox Mode
MySQL: Password Expiration Policy
Password expiration policy in MySQL Server 5.7


SP how can I sum the amount of all leftchild in parentid I have this data and table structure,I am creating MySQL stored procedure to sum all the amount in the leftchild of the parentid, I don't have enough knowledge in stored procedure.and I need some help please because I'm lost.

CREATE TABLE IF NOT EXISTS `mytree` (
  `parentid` int(11) NOT NULL,
  `memberid` int(11) NOT NULL,
  `position` char(1) NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mytree` (`parentid`, `memberid`, `position`, `amount`) VALUES
    (8, 27, 'R', 0.00),
    (8, 28, 'L', 0.00),
    (24, 26, 'R', 0.00),
    (0, 1, '', 5500.00),
    (24, 25, 'L', 0.00),
    (21, 24, 'L', 500.00),
    (21, 23, 'R', 0.00),
    (18, 20, 'R', 1500.00),
    (18, 19, 'L', 0.00),
    (15, 18, 'R', 2000.00),
    (15, 17, 'L', 0.00),
    (13, 16, 'L', 0.00),
    (13, 15, 'R', 2500.00),
    (12, 14, 'R', 0.00),
    (12, 13, 'L', 3000.00),
    (10, 12, 'R', 3500.00),
    (10, 11, 'L', 0.00),
    (7, 10, 'R', 4000.00),
    (7, 9, 'L', 0.00),
    (5, 8, 'R', 500.00),
    (5, 7, 'L', 4500.00),
    (1, 6, 'R', 0.00),
    (1, 5, 'L', 5500.00),
    (20, 22, 'R', 0.00),
    (20, 21, 'L', 1000.00);

here is my stored proc

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

DECLARE row_total decimal(10,2);
DECLARE total decimal(10,2);
DECLARE m_left int;
DECLARE run_left int;
DECLARE p_id int;

set total =  0;

select memberid into p_id from mytree where position = 'L' AND parentid = p_memid;

WHILE  p_id != 0 DO

 select sum(amount) into row_total from mytree where parentid = p_id
  GROUP BY parentid;

 select memberid into m_left from mytree where parentid = p_id;
 set total = total + row_total;
 set tot_left_amount = total;
 set p_id = m_left;

END WHILE;

END

Example if the parentid is 5 ,so the leftchild is 7 then go down to his subtree sum it all,so the total amount in the left child is '22500'? please correct me if I'm wrong.

please help me.

Thank you in advance.

Assuming the following:

alter table mytree add constraint ck1tree unique (memberid);
alter table mytree add constraint ck2tree unique (parentid,position);

One suggestion is to start with a procedure that calculates the sum of the subtree (regardless of position):

There is probably more elegant solutions to this, but I'm in a bit of a hurry and I think it will get you started so I'll post anyhow. Note that you will probably have to adjust the variable @@max_sp_recursion_depth;

set @@max_sp_recursion_depth = 10;
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE sum_subtree
    (IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE tmp decimal(10,2) default 0;
    DECLARE tmp2 decimal(10,2) default 0;
    DECLARE a decimal(10,2);
    DECLARE mid int;
    DECLARE finished int default 0;

    DECLARE c1 CURSOR FOR
        select memberid, amount
        from mytree where parentid = p_memid;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

    OPEN c1;
    get_amount: LOOP
        FETCH c1 into mid, a;
        IF finished = 1 THEN
            LEAVE get_amount;
        END IF;

        -- recursive call to procedure
        CALL sum_subtree(mid, tmp);
        SET tmp2 = tmp2 + tmp + a;
    END LOOP;
    SET tot_left_amount = tmp2;
END $$
delimiter ;

call sum_subtree(7,@x);
select @x;
+----------+
| @x       |
+----------+
| 18000.00 |
+----------+

Now you can create a procedure that select the value from the 'L' branch, call this procedure and return the sum of those two values. Hope it helps

Edit: example of calling procedure:

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(
    IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    DECLARE a decimal(10,2);
    DECLARE m int;
    DECLARE s decimal(10,2);

    select memberid, amount into m, a from mytree
    where parentid = p_memid and position = 'L';

    call sum_subtree(m,s);
    set tot_left_amount = a + s;
END $$
delimiter ;

call count_left_right_amount(5,@y);

MariaDB [test]> select @y;
+----------+
| @y       |
+----------+
| 22500.00 |
+----------+
1 row in set (0.00 sec)


I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number. So i need query to select anything that has an end date that is six months or older and write the SQL. Does this make sense?
Where End_Date <= dateadd(month, -6, getdate())
Since we want to go back 6 months we add -6 to today's date, which is the last parameter in the formula (getdate() get's today's date from the server).  This will yield an answer of 10/8/2020  So any end date on the table that is less than or equal to 10/8/2020 will be selected to the results .
If this doesn't make sense or you need more info let me know.

If you have the ability to change the data model and the structure of this table, partitioning by date is the best way to handle maintenance like this. A partitioned table is a just a special type of table that is divided into segments, called partitions, that make it easier to manage individual segments. If you map each partition to a date, then dropping or moving data for a specific data is as easy as a truncate or a partition switch.
There is some documentation here, and some examples that could be helpful.
If you're stuck with this table structure and still need to do this operation, this can become difficult if the number of rows being deleted is substantial, so a few comments:

A DELETE is a fully logged operation so if that's touching many rows, you could end up filling up SQL's transaction log. In those cases, batching the deletes to handle the top X rows at a time can help with committing smaller transactions.
If your database is in the SIMPLE recovery model, it's sometimes more performant to just insert the rows you want to keep into a new table and use that as  means to delete rows. That insert can be minimally logged using the WITH (TABLOCK) hint and can be more performant than a fully logged DELETE depending on the volume of data.
It's worth mentioning you won't encounter these issues if your table is partitioned and can truncate individual partitions.


Error during CSSCAN of Oracle11 database I have an issue when trying to change the character set on an Oracle 11.2 database.

Here are the particulars:

Oracle 11.2 64 bit, running under Oracle Linux 7
This is a new installation (i.e. the only users are those that were created during the Oracle install), using the defaults given during the Oracle installation
The current NLS_CHARACTERSET is AL32UTF8, and I need to
convert it to either WE8MSWIN1252 or WE8DEC

Most of my experience with Oracle has been with Windows, and I've never had to change the character sets before.

Now I can't just change the character set from AL32UTF8 to WE8DEC or WE8MSWIN1252 because those aren't supersets. So, I ran CSINST.SQL, and ran the CSSCAN command. Most of the scan was successful, but I ended up receiving the following errors:

  . processs 1 scanning XDB.PRIN_PT[AAAN9DAACAAAEGoAAA]
  ORA-30967: operation directly on the Path Table is disallowed
  CSS-00144: failed to scan table XDB.PRIN_PT

Does anyone have any idea what the problem can be and/or how to fix it?

I've done some searching.I did find someone who has mentioned deleting and recreating XML indexes, but I've never done that.

This is known issue for Oracle Database version 11.2 or higher where XDB is installed. My Oracle Support says this can be ignored and has been fixed in 11.2.0.2

Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) (Doc ID 745809.1)


Performing "dummy" queries in SQL I'm working through the book SQL Cookbook and there's an example where the author uses a query which is purely cosmetic (it's after the first union all statement):

select
    ename as ename_and_dname,
    deptno
from emp
where deptno = 10
union all
select
    '----------',
    null
from t1
union all
select
    dname,
    deptno
from dept;

(The table t1 doesn't seem to have been defined yet in the text, but let's just assume the table exists and the query produces a single row.)  It seems like it could be useful in SQL to have a kind of "empty table" that's a member of every database for performing such queries.  In any case, what's the best way of doing this kind of thing?

You can use the DUAL table.

  DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.
  Quoting from MySQL doc-ref


Error Code: 1248 Alias needed in MySQL I have recently started learning MySQL and am trying to join data from two tables into a third table built.

The two tables are:

'Company Financials'   -   Column of 'Revenue'

'Fortune500'   -    Columns of 'Year' and 'Company'

My query code is:

INSERT INTO Company(ID, Year, Company, Revenue)
    (select Year, Company, Revenue
    from (select Revenue from Company_Financials
    union select Year, Company from Fortune500));

Fortune 500 Table:

Company Financials Table:

I've had multiple errors related to this code, but have been able to figure out why they were wrong.  This one is stumping me :(

Any help is much appreciated!

You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query.
Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue)
SELECT FR.Year, FR.Company, CF.Revenue
FROM Fortune500 FR
JOIN Comp_Financials CF ON CF.ID = FR.ID;

Added More explanation in this post, why you need to avoid UNION, and use JOIN for your requirement.


PostgreSQL update column in one table with value from another, based on key from first table Sorry about the title, I wasn't sure how best to word this. My situation is this:
I have two tables:
paper_author [paper_id, author_id, author_name]
authors [author_id, author_name]

Right now, the author_name column in paper_author is NULL. I would like to select the author name from the authors table and insert it into the author_name column in the paper_author table. I've written the following query:
UPDATE paper_author pa SET author_name = (SELECT author_name FROM authors a WHERE a.author_id = pa.author_id);

Which I think will do what I want, but this is taking a very long time to run (days). For reference, the table paper_author has ~900M rows and the table authors has ~200M rows. The author_id is the primary key in the authors table. author_id has an index in the table paper_author. The rows in paper_author are not unique on author_id (i.e. each author_id may appear multiple times associated with different papers).
Is there a more efficient way to write this? Have I missed something?

An update of that magnitude is inevitably going to take a substantial amount of time, no matter how good your configuration is or how powerful the hardware. It's possible there might be room for improvement, but there are not enough details to go on here. For a start, it would be interesting to know the output of:
EXPLAIN (VERBOSE, SETTINGS)
   UPDATE paper_author pa SET author_name =
   (SELECT author_name FROM authors a WHERE a.author_id = pa.author_id)

You might want to consider an alternative strategy along the lines of breaking up the update into smaller batches. That will give you a better idea of how long the process might last, and provide better control over your system resources.


Does this query make sense? I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2.
One category of queries that was broken in transit is:

SELECT person_id
  FROM leaveperiods
 WHERE group_id=146
 GROUP BY person_id
HAVING start_date = max(start_date)

According to Sybase, this is a valid query. But Microsoft SQL is stricter, so I get this error:

Column 'leaveperiods.start_date' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the GROUP BY clause.

While looking at this query to try and correct it, I realized that it does not make sense to me. Isn't this exactly the same as the below?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146

Or maybe I should write like this?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146
   AND start_date IS NOT NULL

There are several queries like this in one application and I need to fix them without breaking anything else. Please help.

As far as I understand the semantics of the Sybase non standard GROUP BY a purely mechanical rewrite would be.

WITH T
     AS (SELECT person_id,
                start_date,
                MAX(start_date) OVER (PARTITION BY person_id) AS max_start_date
         FROM   leaveperiods
         WHERE  group_id = 146)
SELECT person_id
FROM   T
WHERE  start_date = max_start_date

But the query does seem odd.

Documentation Extract

  For example, many versions of SQL do not allow the inclusion of the
  extended title_id column in the select list, but it is legal in
  Transact-SQL:

SELECT type,
       title_id,
       avg(price),
       avg(advance)
FROM   titlesgroup
GROUP  BY type

  The above example still aggregates the price and advance columns based
  on the type column, but its results also display the title_id for the
  books included in each group.

+--------------+----------+------------+--------------+
|     type     | title_id | avg(price) | avg(advance) |
+--------------+----------+------------+--------------+
| mod_cook     | MC3021   | 11.49      | 7,500.00     |
| UNDECIDED    | MC3026   | NULL       | NULL         |
| popular_comp | PC1035   | 21.48      | 7,500.00     |
| popular_comp | PC8888   | 21.48      | 7,500.00     |
| popular_comp | PC9999   | 21.48      | 7,500.00     |
| psychology   | PS1372   | 13.50      | 4,255.00     |
| psychology   | PS2091   | 13.50      | 4,255.00     |
| psychology   | PS2106   | 13.50      | 4,255.00     |
| psychology   | PS3333   | 13.50      | 4,255.00     |
| psychology   | PS7777   | 13.50      | 4,255.00     |
| trad_cook    | TC3218   | 15.96      | 6,333.33     |
| trad_cook    | TC4203   | 15.96      | 6,333.33     |
| trad_cook    | TC7777   | 15.96      | 6,333.33     |
+--------------+----------+------------+--------------+


Update table with sum from other table for the same id Given two tables:
Table src
user_id | counter | text
------------------------
5       | 1       | aa
7       | 7       | bb

Table dst
user_id | counter | text
------------------|-----
3       | 5       | ee
7       | 6       | ff

I need to do the following:
If row with user_id exists in src table but not exists in dst table - copy it from src to dst
If row with same user_id exists in both tables, then update the counter in dst with sum of counter from src and counter from dst, and overwrite the text
Final dst should be like that
Table dst
user_id | counter | text
------------------------
3       | 5       | ee
5       | 1       | aa
7       | 13      | bb

I started to insert the rows with missing user_id:
insert into dst
select *
from src as s
where s.user_id not in (select d.user_id from dst as d);

How do I do the update for the exist user_id values?
UPDATE:
What will be the answer in case I have key of two columns user_id and group_id?

this should do what you want
update dst
   set counter = dst.counter + src.counter
from src
where src.user_id = dst._user_id;

If dst.user_id is the primary key (or defined as unique), you can combine both into one statement:
insert into dst (user_id, counter, text)
select user_id, counter, text
from src
on conflict (user_id)
do update
  set counter = dst.counter + excluded.counter;


Oracle Monitoring RMAN: report need backup Hello Oracle Database Admins!

In the last few months I came across that it would be a good idea to monitor rman backups and check if any file needs backup.

RMAN> report need backup;
--> Report ERROR if any records show up.

Aditionally I would like to monitor the following command:

RMAN> report unrecoverable;
--> Report ERROR if any records show up.

I do not want to parse the output of RMAN. So here is my question: Does anyone know a method to monitor these two commands using SQL and/or PL/SQL?

I know that RMAN just uses SQL and PL/SQL to get the result but an sqltrace just lead me into non-documented pl/sql funtions with encrypted code.

A) Oracle replied to my SR that it is not supported run the SQLs manually. This is not entirely correct but I expected something like that.

B) The second command report unrecoverable can be queried with SQL. The result is even more accurate than from RMAN.

select file#, unrecoverable_change#, unrecoverable_time from v$datafile;

RMAN's report unrecoveryble will only report files which contain content which are unrecoveryble after the latest backup. This SQL reports unrecoverable things in general. Normally we do not try to make the database only recoverable after the latest backup.

C) For both commands I wrote a "screen reader". Here are some code snippets:

## Execute RMAN command report need backup
RMANOUT=$(rman target / << EOF 2>&1
report need backup;
exit
EOF
)

## Execute RMAN command report unrecoverable
RMANOUT=$(rman target / << EOF 2>&1
report unrecoverable;
exit
EOF
)

# Filter file list
DATAFILES=$(echo -e "${RMANOUT}" | sed '1,/---/d' | sed '/Recovery Manager complete./d' | sed 's/RMAN>//g' | sed '/^[[:space:]]/d')

## Parse all files
while IFS=$'\n' read -a ROW
do
  # Convert lines into array (separator " ")
  IFS=' ' read -a VAL <<< ${ROW[0]}

  # No files reported
  if [ "${VAL}" == "" ]; then break; fi

  # Do whatever you like! In my case generate XMLdoc
  XMLDOC="${XMLDOC}<ROW><DBID>${DBID}</DBID><INST_ID>${INST_ID}</INST_ID><FILE_ID>${VAL[0]}</FILE_ID><BACKUP_CNT>${VAL[1]}</BACKUP_CNT><FILE_NAME>${VAL[2]}</FILE_NAME></ROW>"
done < <(echo -e "${DATAFILES}")


Why is a connection via an SSH tunnel presenting as 127.0.0.1 and not localhost? I have defined a MariaDB user test@localhost and I have skip-name-resolve ON as recommended for performance reasons.

I'm able to connect with -hlocalhost -P3306 and so is my web application using the hostname "localhost".

If I attempt to connect via an SSH tunnel to "localhost" I receive the error that 'test'@'127.0.0.1' can't connect (as that user and host isn't defined).

In all three situations I've requested to connect to "localhost" from the same machine and for connections established from the server directly this appears to work. So why am I presenting to MariaDB as 127.0.0.1 via the SSH tunnel?

Supplementary question: Is there actually any point in enabling skip-name-resolve for this setup as from what I can see my web app is presenting itself as "localhost" and so there is no resolving going on and the only thing it's doing is preventing 127.0.0.1 being resolved to localhost when connecting via an SSH tunnel?

CentOS 7.6, MariaDB 5.5.64

All connections that were being made on the server to "localhost" were indeed using the Unix Domain Socket and authenticating with the test@localhost user.

I confirmed this with some tests:

mysql -utest -hlocalhost --protocol=SOCKET -P3306 -p  # worked (-P also redundant)
mysql -utest -hlocalhost --protocol=TCP -P3306 -p     # failed
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

The connection via the SSH tunnel was using TCP and as skip-name-resolve was ON was trying to login as test@127.0.0.1 rather than test@localhost.

Since my applications are running on the same server as the database and configured to connect to localhost/UDS, I see no benefit of turning skip-name-resolve ON as this just causes an additional problem when attempting to connect via the SSH tunnel to "localhost".


In Postgres, How can I merge two tables? I have a set of web services that all need updating with a new set of static data.

I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I detect something went wrong.

I aim to ask each service to backup the data I care about, and apply the update... then later ask the service to restore the backup if I need to recover.

To complicate things I may only be backing up some of the data in the table (let's say.. where the ID starts with "My."  so "My.123" will be backedup and restored but "YOURS.345" won't be changed by this process at all.

I'd like to make backup and restore functions that take a table name.

I was thinking of backing up tables with a generic version of the following code:

--=== BACKUP ===--
create table utilities.labpreferences_snapshot ( LIKE utilities.labpreferences EXCLUDING DEFAULTS ) WITHOUT OIDS;

-- copy data
insert into utilities.labpreferences_snapshot
select * from utilities.labpreferences where s.id like 'My.%';

When restoring, I am expecting that some rows may have been modified or deleted or new ones created. I want to make sure I don't break andy foreign keys referencing data in this table, but I don't expect any new foreign keys since the backup state was captured.

I could restore the data in the same way I copy it... but INSERT only works for data that was deleted... so.. I could delete all the data I want to replace..

--option1
--=== RESTORE ===--
-- possibly stop foreign key constraint checking?

-- remove master data
DELETE FROM utilities.labpreferences s where s.id like 'My.%';

-- replace snapshot data
insert into utilities.labpreferences
select * from utilities.labpreferences_snapshot;

-- restart foreign key constraint checking somehow?

but I worry about the foreign keys.

I could delete the added ones, add the deleted ones, and update the modified ones.

--option2
--=== RESTORE ===--

DELETE FROM utilities.labpreferences s where s.id like 'My.%' and s.id not in (select id from utilities.labpreferences_snapshot);

insert into utilities.labpreferences
select * from utilities.labpreferences_snapshot where s.id not in (select id from utilities.labpreferences);

Update ???  -- maybe something like http://stackoverflow.com/questions/3891758/how-to-update-one-table-from-another-one-without-specifying-column-names

The update looks hard.

I'm feeling a little out of my depth. Can anyone suggest a solution?

One approach could be to use a writable view to transform results in-query, eg

ALTER TABLE utilities.labpreferences
    RENAME TO utilities.old_labpreferences

CREATE TABLE utilities.labpreferences
( utilities.old_labpreferences EXCLUDING DEFAULTS )
WITHOUT OIDS;

CREATE OR REPLACE RULE "labpreferences_select" AS
  ON SELECT TO utilities.labpreferences
  DO INSTEAD
  SELECT -- fields with modifications --
    FROM utilities.old_labpreferences
    WHERE s.id like 'My.%'
  UNION
  SELECT -- fields without modification --
    FROM utilities.old_labpreferences
    WHERE s.id not like 'My.%'
    ;

Backing the changes out is as simple as removing the rule and new (empty) table, and renaming the old table back.

You will also need rules for update/insert/delete to this table - the postgres documentation for DO INSTEAD is pretty good on this front - see http://www.postgresql.org/docs/devel/static/rules-views.html


How many disk seeks does it take to write one row on SQLite? As far as I understand, there can be one disk-seek per rotation of a harddisk, therefore I am interested to know how many disk seeks are needed for writing a row to an SQLite table, including locking etc. Assume that there are no indexes on the table (not even a primary key). I assume that there is one disk seek to write a line to a file? As you understand, I want to compare performance with writing the same data to a flat file.

This question was answered on Stackoverflow.

  By default its one transaction per
  disk rotation (roughly),

  http://www.sqlite.org/faq.html#q19

  The way to speedier writes is to wrap
  multiple ones inside a transaction.


Not able to regulate/auto grow tempdb size We have a SQL Server 2017 (RTM-CU15) AG group (3 servers, 2 HA synchronous) that has had performance issues for the past two months. During the resolution of an issue yesterday we found that the instance was struggling with the tempdb, as per the logs:

  A time-out occurred while waiting for buffer latch type 2, bp 000001C9A41D4A40, page 4:40616, stat 0x810b, database id: 2, allocation unit Id... Not continuing  to wait

There were no mention of storage issues in Event Viewer system logs.

Upon inspection, we found that tempdb had 8 data files each of 250MB (2GB in total), even though they were set to autogrow unlimited. This is small in my opinion.

Based on the size of the other DBs on the instance (about 1.5TB) and the time it's taken for some of my own BI queries (30 minutes) I would have expected the tempdb to be way over 2GB.

No one was monitoring the page life expectancy, but I suspect the instance was not able to manage/auto grow its tempdb files. Once we increased the tempdb size, we no longer had performance issues.

I was surprised to find both HA nodes had the same tempdb sizes of 2GB (both have run the production services for some time).

I haven't found this issue online. Is this an issue with this CU or with 2017 in general? What could have prevented the tempdb from auto growing? Should we have looked elsewhere for the performance issue?

Instant file initialization is not enabled:

The AG logs don't have anything useful. The AG did break with the tempdb error that occurred though.

The tempdb database had two months to grow, but it didn't. It stayed at the initial size of 2GB. It should have grown by itself based on the autogrow settings on each file, which were 100MB, unlimited?

As Tibor mentioned in the comments, add your SQL Server Service account to the Perform volume maintenance tasks local security policy so it can perform Instant File Initialization.  I personally don't feel that alone will solve your problem as I suspect you may be running into the byproduct of thin provisioned disk.  A nice definition being as follows:

  A thin-provisioned virtual disk consumes only the space that it needs initially, and grows with time according to demand.

First, are these servers VMs?  If so, is your infrastructure team presenting VM disk that has been thin provisioned?

Alternatively is the SAN presenting thin provisioned disk to the VM or physical hosts (in the event they are not VMs)?

What can happen with thin provisioned disk is that when additional space is requested to be allocated, and the underlying disk pool/group is over-allocated, you'll run into errors similar to what you're seeing as there's no more actual storage available.  From the OS perspective, the disk has plenty of free space to expand into, but you get these random errors that make no sense.  Databases tend to suffer most from thin-provisioning as they often request resources in large quantities and need said resources quickly, and thin-provisioning mechanisms are built for slow and steady rather than varied and rapid.

It is pretty easy to identify if this is your issue or not as you only need to reach out to both your VM admins and your storage admins.  Ask if any of the underlying disk the TempDB (or any other database) files are on are thin provisioned, and if so how much actual space is available to use.  I've run into scenarios where the OS showed I had a 1 TB drive, but in actuality, I was limited to significantly less space as the underlying disk pool didn't have enough actual storage to honor the extra space the OS was told it has.

I hate when resources are thin provisioned (especially when this happens with memory) because you're not working with all the resources you've been promised.  It's basically like telling your kids they can spend $100 but you only have $80 available and you just hope they are fiscally responsible.  I've yet to work in an environment where thin provisioning doesn't eventually cause issues.  Database Servers should be built for usage spikes rather than averages, and infrastructure admins hate seeing idle resources so many times they offer thin-provisioning as a compromised approach.


Does the number of columns been selected affect query speed? I know some sql but don't quite understand the things behind the scene. And this question came to me the other day while I am taking shower...
If we compare these two queries below:
a.    select * from table_abc;
b.    select id, name, timestamp from table_abc;
which query is (theoretically) faster? My guess is a because in query b it takes time to pick out the 3 columns I want. But that's counterintuitive at the same time because that means getting less information takes longer. All comments/answers are highly appreciated!

With most modern relational database management systems, there are three important things to consider relating to your question:

The amount of data being returned to the client. As you've already started to hint at, more data being returned to the client consumer generally means the longer it'll take for the query to complete. (This is generally globally true regardless of where the data is coming from, relational database, non-relational database, etc.)

The amount of data needed to be loaded from the server. This is arguably a subset of #1, but besides longer times to return the data to the client, there's also an increased time in locating and serving the data. Most relational database management systems store data rows  in an object called pages on the disk. In Microsoft SQL Server, for example, the default size of a data page is 8 KB. If the amount of data in a single row exceeds 8 KB, then that row will be stored across multiple pages. When executing a query with SELECT *, that will instruct the server to locate all the pages for the rows being returned which can be multiple pages per row and thus extra work besides additional data being loaded from the server which isn't necessary if you're only using a subset of columns from the dataset being queried. The disk is typically the slowest hardware component to a server, so minimizing the amount of data the server needs to locate and load off disk is substantially beneficial.

Indexes usually won't be able to be used as efficiently when executing a SELECT * query because it's not usual to have a covering index on all the fields of a table. That means instead of being able for the server to efficiently seek out the data on the most performant index for your data needs, it'll likely instead need to scan the entire clustered index which is generally a slower operation.

Long story short, it's bad practice and an anti-pattern to use SELECT * which will 99% of the time be slower than specifying only the columns you need in the SELECT list.


SQLite CSV import won't create a table automatically According to https://www.sqlite.org/cli.html#csv,

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

  There are two cases to consider: (1) Table "tab1" does not previously
  exist and (2) table "tab1" does already exist.

  In the first case, when the table does not previously exist, the table
  is automatically created and the content of the first row of the input
  CSV file is used to determine the name of all the columns in the
  table. In other words, if the table does not previously exist, the
  first row of the CSV file is interpreted to be column names and the
  actual data starts on the second row of the CSV file.

  For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

I tried this and got an error:

$ cd /tmp
$ rm -f database.sqlite3
$ ( echo 'a,b,c' ; echo '1,2,3' ) > somedata.csv
$ sqlite3 database.sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .version
SQLite 3.7.13 2012-07-17 17:46:21 65035912264e3acbced5a3e16793327f0a2f17bb
sqlite> .mode csv
sqlite> .import somedata.csv sometable
Error: no such table: sometable

However, if I create the table first, then it "works", but I end up with an unwanted header row since it is case (2):

sqlite> CREATE TABLE sometable (a, b, c);
sqlite> .import somedata.csv sometable
sqlite> SELECT * FROM sometable;
a,b,c
1,2,3

I've reproduced this problem using /usr/bin/sqlite3 3.7.13 in OS X 10.9.5 (as shown above) and version 3.7.3 in Debian 6.

Am I looking at the wrong documentation for my version of SQLite?  Is there some mode I've neglected to set?  Or is the SQLite documentation totally inaccurate?

The documentation page you linked to is for the current version (3.8.9) of SQLite.

You could dig up the old version of cli.html for 3.7.13, but at that time, .import was pretty much undocumented.


Postgresql: Two concurrent COPY FROM I'm importing several gigantic csv files into a table - so I'm wondering what would happen if I ran COPY FROM jobs concurrently?

It just works, like it should. It doesn't lock the table, so start testing.


Query Store Settings I'm interested in the overall CPU utilization for a database in a time span.  I see how to get that, but I'm concerned about the query store retention settings.

I don't need detailed info about each query execution, but because of the FK from sys.query_store_runtime_stats to sys.query_store_plan, it appears that the overall resource consumption isn't aggregated in total, but only per-plan.  My concern is that if plans are dropped because of a Query Store Capture Mode of Auto and a Size-Based Cleanup Mode of Auto, then I think that means that the execution times for dropped queries won't be included in the totals for an interval.

The docs say "Infrequent queries and queries with insignificant compile and execution duration are ignored", and what worries me there is the word "and".  Will it drop a 1-time query that uses tons of CPU?  Since "Thresholds for execution count, compile and runtime duration are internally determined.", I don't know.

In my experience, no it will not drop that 1 time cpu heavy query

It may have an insignificant compile time, but the execution is not insignificant

But if you are interested in a very accurate measurement of total cpu utilization that includes every request, you should not use a Query Store Capture Mode of 'Auto', then it should be 'All'


Set FORCESEEK for only one of hinted indexes Is there a way to use a TABLE HINT with two indexes on the same table and set the FORCESEEK for only one of them?

The following statement causes an error because the first index is columnstore:

OPTION (TABLE HINT(ta, NOLOCK, INDEX (csi_index, PK_index), FORCESEEK))

I think the answer to your question is no, at least I haven't found a way to do it.

It would be interesting to know what you hope to accomplish with this set of hints, specifically as a query hint (rather than normal inline table hints).

Here's a repro of your scenario:

DROP TABLE IF EXISTS dbo.Hintable;
CREATE TABLE dbo.Hintable
(
    Id int NOT NULL,
    Rando int NOT NULL,
    INDEX CSI_Index CLUSTERED COLUMNSTORE,
    CONSTRAINT PK_Index PRIMARY KEY NONCLUSTERED (Id)
);

INSERT INTO dbo.Hintable
    (Id, Rando)
SELECT
    v1.number,
    MAX(v1.[high])
FROM master.dbo.spt_values v1
WHERE v1.[high] IS NOT NULL
GROUP BY [number];

SELECT *
FROM dbo.Hintable ta
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));

db<>fiddle link

That results in this error, which is different from the one you reported:

  Msg 8722 Level 16 State 1 Line 20
  Cannot execute query. Semantic affecting hint 'nolock' appears in the 'TABLE HINT' clause of object 'ta' but not in the corresponding 'WITH' clause.  Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints match the WITH clause.

That's a documented limitation, and is easy to resolve by removing NOLOCK or adding it to an actual table hint:

SELECT *
FROM dbo.Hintable ta WITH (NOLOCK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));

Now we get the error you implied (but didn't include specifically in your question):

  Msg 366 Level 16 State 1 Line 19
  The query processor could not produce a query plan because the FORCESEEK hint on table or view 'Hintable' cannot be used with the column store index 'CSI_Index'.

There is a way to write the query that compiles with a combination of query and table hints:

SELECT *
FROM dbo.Hintable ta WITH (NOLOCK, INDEX (PK_Index), FORCESEEK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index)));

db<>fiddle link

The execution plan is kind of odd looking, in that it includes a seemingly-pointless index intersection:

Both the scan and the seek filter on Id = 500
the scan outputs Id, Rando, and the columnstore row locator
the seek just outputs the row locator
and then they join on the row locator

The scan can satisfy the query without needing the seek or join. The join doesn't filter out any rows (since it is on the row locator of the base table). The optimizer is honoring the requirement to use both indexes in the only way it can.


how the mysql server settings replicatd in innodb cluster I just started learning about cluster so I don't have much knowledge if question is not appropriate please forgive me.

I have a 3 node InnoDB cluster running .suppose if I want to tune my server by changing the value of the innodb_buffer_pool_size variable or let say I want to add a new variable in configuration file do I need to change the my.cnf file of every server or is there any simple way I can achieve this.

and how do I change the server system variable so that the setting will be replicated to other servers too?

Yes, you have to change the variable on every node, and if you want the change to be permanent, then you also have to change the relevant options file (e.g. my.cnf).

innodb_buffer_pool_size is dynamic (since MySQL 5.7), so at least you don't have to restart the nodes for the change to take effect:

SET GLOBAL innodb_buffer_pool_size=402653184;

(Don't forget to also set the value in the options file so it becomes permanent, i.e. so the change survives restarts.)

EDIT

If you're using MySQL 8.0+ then there's a handy feature that allows you to set a global variable and persist it to a special options file:

SET PERSIST max_connections = 1000;

See the SET syntax documentation for more details.


Why isn't this PosgreSQL LIKE query working? PostgreSQL 9.2.

[local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%';
ERROR:  42883: operator does not exist: inet ~~ unknown
 LINE 1: select * from units where ip_address LIKE '192.168.43.%';
                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:722
Time: 0.309 ms

What am I doing wrong?

The error message clearly describes the problem: "operator does not exist: inet ~~ unknown"

So you should use the native operators for inet type.

In your case it should be

... where ip_address << '192.168.43/24'::inet;

where the value after the slash specifies the number of significant bits in the value (24 bits = 3 bytes = x.y.z.any). For example 192.168.128/17 means any address from 192.168.128.0 to 192.168.255.255.

You can convert an inet value into varchar and deal with it like with any other varchar but it will break any benefits from the original type and disallow indexes if any. However, here is example:

... where ip_address::varchar like '192.168.43.%/32';

Additionally it may provide the wrong result if ip_address value is partially qualified in the way mentioned above (for example if ip_address = '192.168/16' it is not like '192.168.1.%' which is generally wrong because '192.168/16' value holds anything from '192.168.0.0' to '192.168.255.255')