How to write a query which finds all circular references when a table references itself? (II)

I think you're looking for a recursive common table expression.

WITH rcte (Id, ParentId, [Path])
AS ( --- Anchor:
     SELECT Id, ParentId,
            CAST(CAST(Id AS varchar(10))+' > '+
         CAST(ParentID AS varchar(10)) AS varchar(max))
     FROM MyTable

     UNION ALL

     --- Recursion:
     SELECT t.Id, rcte.ParentId,
        CAST(CAST(t.Id AS varchar(10))+' > '+
         rcte.[Path] AS varchar(max))
     FROM rcte
     INNER JOIN MyTable AS t ON rcte.Id=t.ParentId
     WHERE rcte.ParentID!=rcte.Id)

--- Returns rows where parent is child, i.e. self-recursion:
SELECT ISNULL(b.Id, a.Id) AS Id, ISNULL(b.[Path]+SUBSTRING(a.[Path], LEN(CAST(a.Id AS varchar(10)))+1, LEN(a.[Path])), a.[Path])
FROM rcte AS a
LEFT JOIN rcte AS b ON b.ParentId=a.Id AND b.Id NOT IN (SELECT ParentId FROM MyTable)
WHERE a.Id=a.ParentId
ORDER BY a.Id
OPTION (MAXRECURSION 0);

First off, every relation is a potential anchor in the tree. Then union those anchors to their respective relations (working your way down the tree for each recursion). The recursion stops when Id is ParentId, i.e. when you've come "full circle", or when there is nothing left to recurse.

Finally, the returned resultset is filtered for only those records where Id=ParentID. You might want to add a filter to exclude the 0-member as well, not sure how you wanted it.

Edit: I've LEFT JOIN'ed rcte again (as "b") to catch any branches leading up to a self-recursion. The query still doesn't precisely return the data in the question, but perhaps it's close enough. :)

A quick intro to recursive CTEs from a blog post of mine.


It really depends on how much data is going to flow through the transaction log.  Look at how big the log gets today.  You need to configure the log to be at least that size when SQL starts up.  For most of my clients they end up with a 3-4 Gig transaction log for the tempdb, which contains just a few VLFs and everything works nice and smoothly.


Numeric data type with variable significant digits

I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words:

CREATE TABLE MyData
(
  IntegerPart INT NOT NULL
  , FractionPart INT NULL /* Null of no fraction */
  , HumanFormat AS CAST(IntegerPart AS VARCHAR)
      + '.' + COALESCE('.'+CAST(FractionPart AS VARCHAR), '')
)

Optionally, add more computed columns to store as float or other formats that you might need to calculate.


Query to find the "Number of days" a customer has an active card between a specified interval

I think I have found a good solution for this.
Could you tell me if there are better ways other than this one?
select a.customer_num, count(distinct b.datekey) num_of_days
  from (select t.customer_num,
               t.card_number,
               t.card_issue_date,
               t.card_expire_date
          from test_table t
         where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
           and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')) a
 inner join dimdate b
    on b.datekey between a.card_issue_date and a.card_expire_date
   and b.datekey between to_date('1/1/2020', 'mm/dd/yyyy') and
       to_date('1/31/2020', 'mm/dd/yyyy')
 group by a.customer_num


Is it possible to have mysql treat row based updates as "upserts"?

Consider using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE statements on the master server.

Just note that:

These are unsafe statements for replication, so statement based replication will produce a warning and divergence is possible - consider using MIXED bin log.
These only work on tables with a single unique key or primary key
If there are differences in auto-increment primary key columns, the statements will update divergent records (hence the unsafe classification) (see the manual)


Are there any risks in running 'flush hosts'?

No harm. No connection termination. The cache is used only when clients are trying to connect; apparently something is out-of-date in the cache.

(Follup:  The OP tried it and found no side effects.)


Grant execute on specific type in Oracle

You need to grant EXECUTE on package itself. If security is your main concern you may create a new package that has just types, but you cannot specify that a given user can access only some objects defined in package specification - either all or none.


How do you setup a new oracle instance from the commandline after having done a database software only install?

Creating a new instance

The following command line call will create a new database + instance

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass

because I have a dedicated oracle user for my install I also had to use -serviceUserPassword.  With regards to the passwords, despite being in silent mode if you miss these out, then you will be prompted for them rather than the program failing so while technically the minimum is

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid

you wont be able to script that - the program will just hang indefinitely.

this command's usage is slightly mysterious and if you get it wrong it provides the amazingly useful message:

'Not all required options provided for the command "createDatabase".Please refer to the manual for details.'

Thankfully if you trawl through the reams and reams of online documentation eventually you can find what information it needs here along with what all the options are and what they are for.

A further thing I found here is that it can fail but it doesnt tell you from the command prompt - you should check the log files that it makes (it will tell you where these go).  Just because it says 100% complete doesnt mean it has actually worked!  You could just have got 100% of the way to failure!  In particular if it finishes very quickly then this is a warning sign - expect it to take at least a couple of minutes.  I found I needed to run this as administrator the second time around - I suspect this is always the case but on the first run the UAC was disabled.

Starting the listener

This bit is once per-server, so doesnt need scripting for my needs.  I thought it was worth mentioning though as a few more things were required to make my instance usable from other machines.

I found that after doing the above I couldnt connect to the database remotely but could from sqlplus locally.  In someways this local access was a false positive as it turned out there was no listener running on the server (probably because of the software only install).  You can determine if this is the case by looking to see if TNSLSNR.exe is running on the server.

Thankfully starting the listener is easy if it isnt already running.  You use a command line program called lsnrctl.  This is probably the nicest oracle command line program I've used.  It tells you how to use it and just worked without any messing about.  You can just use it interactively and type 'start' when it comes up.

Note you may need to run the above from an administrator shell  If you see something like the following:

LSNRCTL> start
Starting tnslsnr: please wait...

Unable to OpenSCManager: err=5
 TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error

then try doing it as admin.

Automatic registration

After it started I still couldnt connect to the database externally.  You can use lsnrctl status to determine if the listener knows about the database instance.  If you get a message somewhere in the output that says

The listener supports no services

then it isnt working.  I wanted to use automatic registration because it sounded like the way to go and it turned out from a bit of reading, that instances register with listeners when they start.

To restart an instance you do the following (docs here):

Use the sqlplus command line. You will need to login as a user with SYSDBA priveledges (eg system) syntax:

sqlplus name/pass as SYSDBA

then run 'shutdown' and wait for it to finish. then run 'startup' and wait for it to finish.

There is a good post here that also suggests another way of doing it

note if you haven't got a tnsnames.ora file you will need to specify the host in the connection string  - this wont work:

sqlplus name/pass@serviceName

do this instead:

sqlplus name/pass@//localhost/serviceName

Hey presto you should have your instance registered with the listener.  You can verify this by running lsnrctl status again.

You should see something like this:

Service "horse" has 1 instance(s).
 Instance "horse", status READY, has 1 handler(s) for this service...

Opening a firewall port

The final step in making the instances on the server accessible is to open a port on your firewall.   You can determine the port you need to open from the lsnrctl status message - there should be a section that looks like this:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))

1521 is the default port if you don't have a listener.ora file as mentioned here

To open a port on the firewall on our windows 7 server I used the following instructions.

After doing all of the above I could connect to the instance from my local machine.  Sucess!  Hopefully deploying more instances is just going to involve repeating the dbca command.  Since the listener will already be up it should just register with it immediately.  I'll try this out and post any further complications.


Your analysis is correct. You don't need the trigger because the constraint is doing the same job. Anyway, to drop the constraint use:

Alter table train drop constraint train_len;


On Oracle 12c. is there any way to enable the audit trail on SELECT on whole schema of some users?

Please try the following:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
ON <Schema_name>
BY ACCESS;

Using Oracle fine grain auditing we can achieve auditing of all statements of entire schema:

begin
    for c1 in (select * from all_tables where owner = '&schema') loop
        dbms_fga.add_policy (
            object_schema   => c1.owner,
            object_name     => c1.table_name,
            statement_types => 'SELECT,UPDATE,DELETE,INSERT',
            policy_name     => c1.table_name
        );
    end loop;
end;
/


pg_restore importing single table to a file

You need to read the documentation for the version you are using.
Since v12, -f - is mandatory to get output to go to stdout.  Having that behavior be obtained just by omitting -d was considered to be confusing.  But since you want the output to go to a file, just name that file:
pg_restore -Fd mydirectory -t sometable -f table.sql


Why do Indexed Views not permit non-unique clustered indexes?

The following explanation is given in this Microsoft Technical Article:

  Why does the first index on a view have to be CLUSTERED and UNIQUE?
  It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.

SQL Server uses a system of delta algebra to keep indexed views in step with the base data. It also automatically incorporates view-maintenance query plan operators for each DML query that affects one or more indexed views. Having a unique clustered index on the view greatly simplifies the implementation details.

The current arrangement allows for fixed-shape maintenance operator tree shapes to be incorporated in the base DML query tree, providing orthogonality that also simplifies testing. Ultimately, indexed views could be enhanced one day to support non-unique clustered indexes, but then again all things are possible given unlimited time and boundless resources (neither of which apply to the SQL Server development team as of the time of writing).

For an example showing how complex update query plan building can get, and how easily subtle bugs can creep in, see this example of a bug that occurs with MERGE and filtered indexes (a feature that has a close connection to indexed views).


Some suggested reading for you:

http://highscalability.com/  The greatest blog ever in regards to scaling websites.  Tons of fantastic information there.
Read about sharding.  http://en.wikipedia.org/wiki/Shard_(database_architecture)


Is it possible to return a custom error for a logon trigger?

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.)

If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified exists. If it told you why, this makes penetrating the system much easier because there's less guesswork involved.

So really if there is a way to do this using built-ins, I would have no hesitation to report it as a bug.

That of course doesn't solve what you're trying to do, which, IMO, is questionable for the same reasons.

Since login failures should be rare, what I would suggest is that all the restrictions be published internally in a predictable location (e.g., SharePoint). The main things with this are to make sure the list stays up-to-date (it could be data-driven), and also that it's visible to everyone who has access to the server (as opposed to everyone who is able to attempt to connect to the server, which is very different).


You can use LAST_INSERT_ID() to get the inserted id for example if you insert a row in your photos table and you need that auto incremented id for the detail table you can do so

INSERT INTO photos (purl, uid,`time`,title)
VALUES ( 'test',1,1111,'test');
INSERT INTO photodetail (pid, ptext, location)
VALUES (LAST_INSERT_ID(), 'test', 'test');

Fiddle Demo


psql \watch implementation for MariaDB? Repeatedly execute the current query

Not quite what you want, but this shell command might suffice:

$ mysql ... -e "SELECT * FROM TABLE WHERE state = retry"


Read from large table without causing disk queue length issues

Disk queue length isn't a problem, by itself: SQL Server will batch off IO requests. It's a question of whether your storage subsystem can deliver data fast enough to keep up with your queries.

While the report runs, gather wait statistics - the list of bottlenecks on the server. My favorite way to grab it is with sp_BlitzFirst in the open source First Responder Kit (disclaimer: I'm one of the authors). To gather wait stats for a period of time, run:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30

To capture wait stats for 30 seconds. Then, you can tune based on your top wait type, and how much of it you're actually seeing.

For example, if your top wait type is PAGEIOLATCH, that means reading data pages from a data file. To reduce that wait, here's the fixes that I'd recommend, in order:

Tune the query
Tune indexes in the execution plan
Add memory to the SQL Server (to cache more data, giving the storage subsystem more time to pull data from disk)
Make the storage go faster

I order 'em that way because that's usually cheapest/easiest up to hardest/most-expensive.

If your wait type is something else, then don't worry about disk queue length. (Actually, don't worry about disk queue length, regardless.)


Your log file is created with the 'Windows End-of-Line' way. When you check it with Vi or Vim then the ^M (= CR = Carridge Return) is show because it is not part of the 'Linux End-of-Line' way. You can ignore it.


Updating to the latest version of mongodb available for ubuntu fixed the issue.  This time, running db.bar.ensureIndex({"_id": "hashed"}, {"background": 1}), gave me:

Inserted 1 record(s) in 97564ms

and when I ran sh.shardCollection("foo.bar", {"_id": "hashed"}), I got:

{
  "collectionsharded": "foo.bar",
  "ok": 1
}

Here's the bash script that I used to update the servers:

#!/bin/bash

# for installing the latest and greatest version of MongoDB on Ubuntu Servers
if [[ "$UID" != 0 ]]; then # if not root, then exit non-zero
  printf "$(basename $0): err: %s\n" "must be root"
  exit 1
fi

apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10 # import the MonboDB public GPG key
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | tee /etc/apt/sources.list.d/mongodb.list # create `/etc/apt/sources.list.d/mongodb.list` containing the ubuntu repo for mongodb.
apt-get update # refresh the apt repos

for each in mongodb-clients mongodb-dev mongodb-server mongodb; do # remove the old version of mongodb
  apt-get remove "$each"
done

apt-get install mongodb-10gen # install mongodb from the newly added repo


You're on the right track.  Look closely at your 2NF result - this is where the 3NF result will come from.  Your next task would be to take the 2NF result and eliminate the fields that do not depend on the key.

There are numerous sources for how and why to do this, but I prefer Microsoft's explanation the best:

http://support.microsoft.com/kb/283878


Mongo Cluster sharded replica cluster design question

From comments:

mongoS doesn't need much memory/CPU (or even its own machine) and can be located (installed) on the config servers.
config servers don't need that much memory, even when mongoS is there; 1-2GB is enough.

– JJussi


How to get query runtime in PL/pgSQL to be as fast as console query runtime?

The elephant in the room is function overhead. When calling a function instead of raw SQL, Postgres needs to look up the function in the system catalogs (possibly picking the best match in case of function overloading) and consider settings of the function. And then there is the overhead for the function call itself. This adds a tiny overhead cost.

That only really matters for very simple queries like your example - which should much rather be implemented with a plain SQL function. You still encounter some (tiny) function overhead with an SQL function, but it can be "inlined" if it's a simple SELECT and some preconditions are met.

Like always, repeated calls profit from the populated cache. And in the case of plpgsql functions, it may also start to use a generic plan after some (typically 5) calls, if that seems promising.

Consider this related discussion on pgsql-general.

Query plans for code inside plpgsql functions

You are wondering:

  explain analyze even shows the usage of indexes in the first case but not on the second [plpgsql function].

plpgsql functions are black boxes to the query planner, which does not examine the contents of the function. They act as optimization barriers. Unlike with SQL functions their content cannot be inlined in outer queries.

This is the reason why EXPLAIN ANALYZE doesn't show what's happening inside the plpgsql function and you therefore see no index scan. The index (most probably) is still used. You can use the additional module auto_explain to have a peek on the query plans for SQL code inside the function. Consider the answers to this related question for details:

Postgres query plan of a UDF invocation written in pgpsql


Upgrading SQL Server 2008 R2 Standard edition to SQL Server 2008 R2 Enterprise edition what is the process and the impact?

The process is simply to launch SQL Server setup from your DVD or ISO, go to the Maintenance tab, and perform an "Edition Upgrade" (please ignore the fact that this screen shot is from SQL Server 2014):

You'll need to enter a different product key.

Please also make sure you install the latest service pack (SP3; 10.50.6000).

As for impact, you shouldn't expect any negative impact at all, except that perhaps you will need to bounce the service as a part of installation.

More info on edition upgrades.

If this doesn't work out, you could always install a brand new instance of SQL Server with a different instance name, then backup and restore your user databases (and copy linked servers, job, and logins). But that really should be a last resort - if you have an issue with an edition upgrade, come back with a more specific question.


What database to choose for 1TB of time series?

Any relational database will work.  1TB is huge, but feasible.

Don't use ticker LIKE Y unless you need to use wildcards.  Instead use `ticker = 'Y'.  Are you saying that the resultset is 20GB?  Then the real problem is network time.  It takes a lot of time to shovel that much data.

And have an index starting with ticker.  It may be better to have the PRIMARY KEY start with ticker.  If you would like more advice, I need to see more information.

A single row, properly indexed, will take only milliseconds to fetch.


Predefined rows conflicting with user entered rows

Reflection, not an answer.

  define a color whose R,G,B value are already used in a predefined color

I cannot find a reason to forbid for a user to create his own color name for some color. So (R,G,B) must NOT be unique.

  use a name that is already in use by a predefined color

I cannot find a reason to forbid for a user to create his own color for some color name. So (color_name) must NOT be unique.

As I see there is to exist additional field user, which allows each user to create his own colors collection. user field is nullable, Null value means this is a color available to all users (predefined colors, for example). But:

  hide predefined colors he's not interested into

means that there must be another method - while creating user you must copy all predefined colors to him personally. To mark any color as not interesting for current user you may add additional field or use any another method (for example, add some char - asterisk or minus sign, etc. - in front of color's name which is not allowed as first char for active color name).

Alternatively you can create a table with base colors and a table with color aliases personal for each user. Each new color created by some user is inserted in both tables whereas any existing color is inserted in second table only.


Are there any professional fulltime PostgreSQL DBA's?

Judging from the activity on the PostgreSQL jobs mailing list I'd say it's not all that uncommon to find full-time PostgreSQL DBAs.

http://www.google.com/search?q=full+time+site:postgresql.org/pgsql-jobs


How is a T-SQL Trigger that only fires on real changes made?

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
           EXCEPT
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
          )
BEGIN...


Why doesn't this query use an index spool?

As you know, the optimizer's search is not exhaustive. It tries things that make sense in context, and which frequently pay dividends on real queries. Forcing a loop join between two single-column unindexed heap tables is not such a scenario. That said, here are some details:

SQL Server likes to transform applies to joins early, because it knows more tricks with joins. Later on, it may explore converting the join back to an apply. The difference between the two being correlated parameters (outer references). Applies make sense when there is a suitable index on the inner side. Your example has no indexes, so the optimizer is not persuaded to explore translation to an apply.

A simple (non-apply) join has the join predicate on the join operator instead of outer references. The spool optimization for a non-apply is typically a lazy table spool, since there is no predicate on the inner side, only at the join.

The optimizer does not consider building an index on the fly to enable an apply; rather the sequence of events is usually the reverse: transform to apply because a good index exists.

You can sometimes encourage an apply rather than a join by using APPLY syntax in your query. The undocumented trace flag 9114 can assist in this by dissuading the optimizer from translating a logical apply to a join up front. For example:

SELECT *
FROM dbo.X_1000 AS a
CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b
OPTION (QUERYTRACEON 9114);

An index spool is favoured for apply because the outer reference means the selection is applied on the inner side of the join. You will often see this via SelToIndexOnTheFly but other paths exist. See my article The Eager Index Spool and The Optimizer.


SQL Server Create Access Denied even after adding MSSQLSERVER user permissions

The actual problem is that the SYSTEM or other container group had not had the NTFS permissions set for the SQL db folder on the SQL data drive, in my case, 'E:\SQLDATA`

The root E:\ drive was not accessible, so a specific NTFS permission had to be created for the SQLDATA.


I don't think there is a need for denormalizing the table. Self-joining will work fine if you have indexes that will be used effectively. For the specific query, I'd add an index on (year, dataID, countryID, data) and not use any derived tables:

SELECT
    c.countryName AS Country,
    em01.data AS data01,
    em02.data AS data02,
    ...
    emXX.data AS dataXX
FROM
    Countries AS c
      JOIN CountryData AS em01
        ON  em01.year = 2017
        AND em01.dataID = 523
        AND em01.countryID = c.countryID
      JOIN CountryData AS em02
        ON  em02.year = 2017
        AND em02.dataID = 524
        AND em02.countryID = c.countryID
      ...
      JOIN CountryData AS emXX
        ON  emXX.year = 2017
        AND emXX.dataID = YYY
        AND emXX.countryID = c.countryID
 ;

The only issue you'll have is that MySQL has a hard limit of maximum 61 joins in a query. So, you won't be able to have 90 columns with the above query.

Another observation is that you don't seem to need any aggregated data but just a tiny subset of the (small or big table, doesn't matter). With the suggested index, you could just write a query like this:

SELECT
    cd.countryID,
    c.countryName AS Country,
    cd.dataID,
    cd.data
FROM
    Countries AS c
      JOIN CountryData AS cd
        ON  cd.countryID = c.countryID
WHERE
      cd.year = 2017
  AND cd.dataID IN (522, 523, ..., YYY)
ORDER BY
    cd.countryID,
    cd.dataID ;

and have the pivot transformation done in your application.


OK, so further testing seems to indicate that what happens is this:

If the database specified for isql's connection is just a local file path (possibly indirectly via an alias), isql will first attempt to connect by opening the file directly. This would be the kind of connection used for classic (and supeclassic?) mode. So it's not exclusive per se, but incompatible with superserver. If the file is already locked, as would be the case if the superserver has a connection to it, isql will try to connect via the superserver. In this case, the connection will obviously not block any other connections in any way.

So, to make sure other (superserver) connections are never blocked when using isql, isql's command line should specify a TCP/IP connection string, something like this:

isql -u usr -p pwd localhost/3050:MyDBAlias

If a connection string like that is used, other (superserver) connections work just fine.


Use utl_match.jaro_winkler_similarity to select distinct rows using Oracle

I'm not overly familiar with the UTL_MATCH.JARO_WINKLER_SIMILARITY function, but using it in a UNION query, in conjunction with the ROW_NUMBER analytic function, will give you the result you desire:

WITH
    temp AS
    (
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
    UNION
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
    )
SELECT log_message
FROM temp
WHERE rn = 1
;

Result (screenshot from test DB at apex.oracle.com):

If you just need any value from each group (as opposed to the "first" value in each sorted group), you could use ROWNUM criteria instead of the ROW_NUMBER functions, which would return a similar result:

WITH
    temp AS
    (
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
      AND ROWNUM = 1
    UNION
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
      AND ROWNUM = 1
    )
SELECT log_message
FROM temp
;

However, unless there is a specific reason why you need to use UTL_MATCH.JARO_WINKLER_SIMILARITY, there's a chance you may be falling victim to the XY Problem. You could achieve identical results by replacing the two individual WHERE-clause lines (in either query above) with these:

WHERE log_message LIKE 'Unable to make thread %'
WHERE log_message LIKE 'FEED\_%' ESCAPE '\'

Or these (e.g. if there is a need for more fine-grained filtering):

WHERE REGEXP_LIKE(log_message, 'Unable\sto\smake\sthread\s\d+\sa\srealtime\sprocess')
WHERE REGEXP_LIKE(log_message, 'FEED_[A-Z]{3}\sSecondary\sInstrument\sLines\snot\sconfigured')


set right permission to get connection ip for a user mapped to login

sys.dm_exec_connections will require the permissions you've stated of VIEW SERVER STATE. However, by looking at the code you only ever want to get the IP of the session the user is already in, so there is no need for the over-provisioning. Using CONNECTIONPROPERTY will fulfill the same request and NOT require any extra permissions.

I'd make it something as such:

CREATE PROCEDURE TestProc
AS
BEGIN
 SELECT CONNECTIONPROPERTY('client_net_address')
END

GRANT EXECUTE ON dbo.TestProc TO TestUser

You can then test:

USE YourDatabase
GO

EXECUTE AS LOGIN = 'TestLogin'
 EXEC dbo.TestProc
REVERT


How to unify two records with same field?

Use GROUP BY to get one output row per cod, and use aggregate functions to select one value to output. For the doc values, use CASE to select only one of them (it returns NULL in a row where it does not match):

SELECT cod,
       name,
       max(CASE date_row WHEN 1 THEN doc1 END) AS doc1,
       max(CASE date_row WHEN 2 THEN doc1 END) AS doc2,
       max(cpf) AS cpf
FROM my_table
GROUP BY cod,
         name
ORDER BY cod;


So it seems that the issue was actually related to certificates. Once the certificate was backed up and shared to the mirrored server, everything worked as intended. I just wanted to swing back around and let those who were reaching out to help know where I landed.


How to use a MySQL database both remotely and locally

Remote access can be had if you provide suitable GRANTs for hosts other than "localhost".


MongoDB Balancer:move data inside replication set or across replication set?

1 - Yes, the balancing operation can affect the performance, that's why you can schedule the balancing window. By default, the balancer is enabled and may run at any time.

2 - The balancer moves data chunks between shards.

Just to clarify, each shard is a replica set. All members of the replica set (the primary and N secondaries) have the same data (replicated). Therefore, each shard has a different subset of the total data.


MySQL/InnoDB - Clustering Index with UUID

Face it, wherever you have the UUID as a KEY, it will be in a fragmented BTree.  But, the BTree is kept reasonably clean.  That is, when a BTree block is too full to accept another row, it splits into two blocks, each about half full.  As time goes on, any new inserts into either of those blocks will simply add to the blocks without immediately splitting.  The end result of 'random' inserts is blocks that average about 69% full.  This is only slightly worse than 100% full.

InnoDB uses only BTrees.  The data is 'clustered' with the PRIMARY KEY.  There is always a PRIMARY KEY:

an explicit PK, or
the first UNIQUE key with non-null column(s), or
a fabricated, hidden, 6-byte PK.

If your UUID is the PK, then you are doing splits/fragmentation on the data.  If your UUID is a secondary key, then that BTree suffers from splits/fragmentation.  There is (almost) no escaping it.

I say 'almost' because if you are using Type-1 UUIDs, you can shuffle the bits to make them approximately time oriented.  This makes them much like AUTO_INCREMENT ids.  I discuss that in my blog.

Yes, you could rebuild whatever index contains the UUID. This is via the OPTIMIZE TABLE, which rebuilds the table, blocking access during the process.  And, as I say, you won't gain much from it.  Ordinary blocks splits are not costly; OPTIMIZE is.  I often tell people to 'never' use OPTIMIZE.

Is your entire table small enough to be cached in the buffer_pool?  If so, UUIDs are not too much of a performance problem.  On the other hand, once the table (or the UUID index) gets much bigger than the buffer pool, processing becomes I/O-bound.  This is because of the random nature of UUIDs.  As the table grows, each INSERT and each SELECT (using UUID) becomes more and more likely to require a disk hit.

If you cannot avoid UUIDs (which would be my first recommendation), you can at least shrink them from taking 110 bytes via a casual implementation (VARCHAR(36) w/utf8) or 36 bytes (CHAR(36) w/ascii) and shrink it down to 16 bytes (BINARY(16)).  See my blog for the pair of Stored Functions.  Smaller --> More cacheable --> Less I/O --> Faster.

If you would like to discuss further what you are doing, I would be happy to elaborate further.


MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

MySQL Cluster checkpoints and logs in-memory data to disk (assuming you leave the default of checkpointing on). As these are streaming writes, fast HDDs are very efficient to handle this activity. Also, all updates are synchronously replicated between data nodes in a node group, and these can be split across data centers using multi-site clustering:
https://blogs.oracle.com/MySQL/entry/synchronously_replicating_databases_across_data

So there are multiple mechanisms available to protect data

HTH!
Mat


How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date?

somewhat old but still ...

https://stackoverflow.com/questions/10735447/is-there-a-way-to-convert-a-varchar-to-datetime-in-sql-server-2008

convert(datetime,'24/05/2012 09:56:06',103)

set your column containing the varchar as the second parameter - 103 is for the --/--/---- format ..

I tested above command against a SQL Server 2014 and it works when you omit leading zeroes - and also with AM / PM addition so perfect for your case

The only thing that needs some more work is your desired addition of the timezone, but I highly suggest adding that only for display, not in the actual database


CHECK constraint not implemented in MySQL, what about other DBMS?

Every R (relational) DBMS I've used has.  MySQL is peculiar in so many ways I struggle to put it head to head with any of the "normal" RDBMS for anything other than a basic data dump for a simple application.

There are plenty of DBs that are not relational that don't have the concept.  As far as I know though, there are none other of either sort that support the syntax without the functionality.


Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

The order you insert data into a table does not guarantee the order of the data on this disk.

Even if the order happens to be as you wish, it's not structured in a way that the optimiser will know that it's searchable by that order.  (It will be treated as un-ordered.)

When you have a non-clustered index, and you need to use fields not covered by the index itself, the index only contains a pointer to the data.  This is similar to introducing an extra join; a quick one, but a definite extra step in loading that data.

In short, it should nearly always be advantageous to have a clustered index (excepting circumstances like repeatedly inserting data at random positions in the table).

Which is contrary to your experience...

So, what are the queries that you are running?  It may be that something counter intuitive is happening, but it may still be explainable.

You've given us the schema, thanks.
Could you also include the test queries you are using?
And could you also include the methodology and results for measuring performance?
And, finally, if possible, the execution plans for each query?


Error "#1118 - row size too large" on the first row of the table only

As suggested from this bug report, you may need to look into the following:

File Format

Run SHOW VARIABLES LIKE 'innodb_file_format%';

innodb_file_format
innodb_file_format_max

You might see values Antelope, Barracuda

Row Format

Run ALTER TABLE tblname ROW_FORMAT=DYNAMIC; to make the row format Dynamic
Run ALTER TABLE tblname ROW_FORMAT=COMPRESSED; to make the row format Compressed

Suggestions

Workaround: use new InnoDB file format Barracuda and row_format=DYNAMIC

This was added to the change log in MySQL 5.5.29

Perhaps an upgrade to the latest MySQL 5.5. is in order

Give it a Try !!!


Yes, a bridge table (also known as junction table, mapping table etc.) is a perfectly sensible solution in this case, and it is common for a bridge table to have other attributes beside the references to the tables being connected.

As a stationary object, a stop is just a place, which has coordinates and may also have a fixed name as well as some other properties. As a part of a route, though, it can be assigned the additional attributes you have mentioned that would depend on the route, i.e. on the specific bus number. Those would need to be stored in a different table, the one that would link the stop and the route.

So, if by "bus" you mean "bus number" (or "bus route", "bus line"), then by all means create a dedicated table with references to Bus and Stop and with whatever additional attributes you deem necessary. Note, though, that if Bus is meant to store information about specific buses (vehicles), then I would consider creating another entity (called Route perhaps) that I would link to in the bridge table. Bus would then probably reference that new entity, either directly or, again, through another bridge table (in case vehicles were allowed to work alternately on different lines).


Checksum of data imported from mysqldump not equal to the original checksum?

In the file slave.sql, modify the top line, delete -log. Then re-diff your files. If that's the only difference (and it appears to be), then there's no problem - the data part of your mysqldump is identical! (The small "difference" appears to be in the header generated by mysqldump).

A checksum will vary greatly even if the file contents differ by only one bit! A discrepancy of 4 characters is, to all intents and purposes, enormous in this context.

From here:

  Depending on its design goals, a good checksum algorithm will usually
  output a significantly different value, even for small changes made to
  the input. This is especially true of cryptographic hash functions,
  which may be used to detect many data corruption errors and verify
  overall data integrity;

(and the really important bit)

  if the computed checksum for the current data
  input matches the stored value of a previously computed checksum,
  there is a very high probability the data has not been accidentally
  altered or corrupted.

This is a facinating topic all in its own right and the maths gets very difficult very quickly. Just one thing worthy of note is the "Birthday attack".

This means that even if you have matching checksums, there is a small  (in reality, infinitesimal) possibility that your data does not match. Day-today, this is not a problem - included for completeness.


Conditional loop date lookup SQL Server 2008

Here is example of solution, which give you expected result:

SELECT EmpNAME,[Hours/Day],Date0
FROM (SELECT DISTINCT Date0 FROM #WorkTime) D
CROSS JOIN (SELECT EmpNAME FROM #EmpName) EN
OUTER APPLY (SELECT [Hours/Day] FROM #WorkTime wt
             WHERE EN.EmpNAME=wt.EmpNAME AND D.Date0=wt.Date0) HD
ORDER BY Date0

At this moment, I don't see other way.


Copy of a database with an existing database with schema and data in sql server

Create two template databases - once - one with the schema, and one with schema + data - populated with the manual generate scripts method.

Take a backup of each.

When you want to create a new database, instead of a manual CREATE DATABASE and then manual population, restore the backups as new names and the WITH MOVE options to move the data/log files.

Refresh the templates, obviously, every time you make meaningful changes to the schema and/or data that you want used for future, new databases.

You could reduce this work by putting just the schema in model, then CREATE DATABASE would create an empty database with your schema, and you'd only need to do something for the one with the data. Or vice-versa. But note that this will affect all future databases.


High CPU usage from MySQL with no queries at all running

had the same problem, here is a solution:

http://blog.mozilla.org/it/2012/06/30/mysql-and-the-leap-second-high-cpu-and-the-fix/

Everything happens because of "1" second.


What User Level is needed for Transactional Replication

The Snapshot Agent process account used to connect to the Distributor must be db_owner in the distribution database and have read, write, and modify permissions on the snapshot share.  The account used to connect to the Publisher must be db_owner in the publication database.

The Log Reader Agent process account used to connect to the Distributor must be db_owner in the distribution database.  The account used to connect to the Publisher must be db_owner in the publication database.

The permissions required by the Distribution Agent process account depend on whether you are using push or pull subscriptions.

For push subscriptions, the Distribution Agent process account used to connect to the Distributor must be db_owner in the distribution database, be a member of the PAL, and have read permissions on the snapshot share.  The account used to connect to the Subscriber must be db_owner in the subscription database.

For pull subscriptions, the Distribution Agent process account used to connect to the Subscriber must be db_owner in the subscription database.  The account used to connect to the Distributor must be a member of the PAL and have read permissions on the snapshot share.

The is all covered in the section Permissions That Are Required by Agents in Replication Agent Security Model.


The tutorial refers on how to setup a sharded cluster but not on how to shard collections.

The next step is to enable sharding for a database and shard one or more collections in order to distribute the data.

From the mongos first use sh.enableSharding("dbname") https://docs.mongodb.org/v2.6/reference/method/sh.enableSharding/

and then sh.shardCollection("dbname.collectionname",{shardkey:1}) https://docs.mongodb.org/v2.6/reference/method/sh.shardCollection/

You need to choose what the shard key will be. A tutorial on how to choose a shard key can be found here  https://docs.mongodb.org/v2.6/tutorial/choose-a-shard-key/


MySQL similar name for status or type column

As a_horse_with_no_name commented:

  I typically include the entities name with that, e.g. person_status or element_type. That also makes reading queries easier (at least in my eyes)

...it is better to include more information with those column names in order to make them more easily understandable.

For example you in a user table you could have a user_status and user_type column and those names would clearly define exactly what you are referring to with status and type. It would make it even more useful if those columns where using reference tables such as user_status and user_type.

Those are just examples. The table name could be modified to indicate that it is a reference table such as r_user_status or user_status_ri. Or one can work on making it more generic (but still more descriptive than status), so that it can be used for more tables. This would also allow you to remove repeated data that might need updating, and move the update to a single table for when you might need to change the text of an existing status.

In the end avoiding generic names such as status and type will make it much easier for developers who join the project later on to easily understand the data and its relationships.


Table is dropped but ibd file still exists

You can go to the OS and drop the file because the data dictionary in the system tablespace (ibdata1) does not know of the .ibd file's existence any more.
I wrote a post 2 months ago about deleting temporary .ibd files : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave. It applies in your case as well.
Give it a Try !!!


Optimize Oracle 11g SQL*Plus connection for large remote SELECTs

Instead of the 11g client, you should use at least 12.2, because:

11g is outdated
12.2 has builtin support for generating CSV output (MARKUP), sqlplus -M "CSV ON" user/password@address
you can simply start 12.2 with the -fast option (-F[ast]), that sets (which you can set and tune further manually as well):

ARRAYSIZE = 100
LOBPREFETCH = 16384
PAGESIZE = 50000
ROWPREFETCH = 2
STATEMENTCACHE = 20


Does AWS ever `vacuumlo` PostgreSQL RDS instances?

Probably not, because blindly executing vacuumlo on random databases would be foolish.

Unlike vacuum that is pretty much unavoidable on a normal live database, vacuumlo fixes an oversight, a situation that should not arise in the first place, since applications should unlink the large objects that they no longer use, at about the same time that they delete the references to these large objects.

In a database with a large number of large objects that are properly handled, vacuumlo would burn CPU cycles and I/O at each invocation, just to compute every single time that there is nothing to remove.

Moreover, there are two assumptions that vacuumlo must do that are debatable:

the fact that any column of type oid should be taken as a candidate to a large object reference. oid are used for other purposes, especially if you want to refer to objects in the catalog.
the fact that, if a large object doesn't have it oid refered in some table in the same database , it should be removed. If the reference is in another database, or in a file, or in the same database but embedded in a text column, that logic doesn't work and is in fact dangerous.


Filter rows if value exists in different columns

WITH cte AS (your query)
SELECT t1.*
FROM cte t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM cte t2
                   WHERE t1.StockCode = t2.Component )

This works by wrapping your original query up into a CTE, then only returning rows from it if there are no other rows where the Component column is equal to the StockCode column.


Is Log Shipping or Mirroring a better solution for Disaster Recovery?

I think this is kind of the same question as the other one you just posted. there are a couple of new points you address:

Bulk logged mode doesn't make the logs shipped over the wire smaller. It just makes writing to the local log file less IO intensive. Once the log has to be backupped, alle changed pages haved to be included in the backup. The end result is that your trans log backup will be just as big.

If you already have High latency, changing to mirroring will not change that. the only way to spread the load is: stop doing rebuild and change to a spread reorganize strategy (as I mentioned in your other question.)

Or as somebody else there mentioned, start doing more frequent log backups and ship smaller log backups in a higher frequency.

Just based on what you are telling us now. (the high latency) I personally would not advise to change to mirroring. But the choice between mirroring and log shipping should be based on many more items then only latency.


Export Data Pump all data from few schemas except one table structure only?

You probably can try something like,

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
query=SYS_HISTORY:"where 1=2"

This way, the metadata about table SYS_HISTORY will be copied but no data.
Note: since you are not prefixing table name (to exclude data from), with schema name, this filter will apply to this table in each schema specified.


There are 2 types of database directories in Db2 for LUW: system and local.
When you create a database on some path /some_path, the following directory structure is created for this local database directory (if it doesn't exist): /some_path/${DB2INSTANCE}/NODE0000/sqldbdir and the database is cataloged (or registered) in the system database directory automatically.
You may have many local directories in the system, but only one system directory.
You have an ability to uncatalog ("deregister") a database manually afterwards, which removes the corresponding entry from the system directory, but not from the local one. The only way to remove a database from its local directory is to drop it.

So, the problem described in the doc is about such uncataloged databases, which Db2 instance is unaware of. If you uncataloged your databases previously, and forgot to catalog them again, you should find such databases and catalog them. If you don't remember their local database directory paths, you may use unix find /some_path -type d -name sqldbdir command, and db2 list db directory on /some_path, if you found the directory structure like /some_path/${DB2INSTANCE}/NODE0000/sqldbdir.


Cannot execute inserts querys after upgrade to mysql 5.7

If your question is about #1055...

1055: sql_mode includes ONLY_FULL_GROUP_BY.  When doing a GROUP BY, you should include all the columns in the SELECT other than aggregates.

If your question is about INSERT, I don't see any supporting info.


Error in setting a value in sql server trigger

You have a derived table in the from clause without an alias.

This will give you the same error:

select *
from (
     select *
     from YourTable
     );

Add an alias to the derived table.

select *
from (
     select *
     from YourTable
     ) as T;

Note: Your trigger will fail if you insert more than one row at a time. The trigger is executed once per insert statement, not once per row.


RMAN - Fail To Restore Oracle DB with CDB and PDB

The error message is a bit confusing. Normally this is solved by feeding a few extra archives to the recovery process and issue a cancel.
Do this using sqlplus.
Let it recover an archive and when it asks for the next archive issue 'cancel'. Most of the times the database will open.


Query optimization with conditional group by and where

e and wp are not used, so remove the `LEFT JOINs to them.

Some composite indexes needed:

letture:  INDEX(id_event, entrata, qrcode, id_sala, datalectura)
ep:  INDEX(qrcode, id_event)  -- in either order

My suggested index for letture helps with all 3 uses of that table, and in different ways.  The order of the columns is critical, except that the first two can be swapped.

If AND  i2.datalettura > i1.datalettura is part of doing a "groupwise max", then it is especially inefficient.  This discusses much faster ways.

AND  i3.datalettura IS NULL and its LEFT JOIN can probably be replaced by NOT EXISTS ( SELECT 1 ... )

The CROSS JOIN is technically an INNER JOIN, but since MySQL ignores CROSS and INNER, either of those works the same.  Ditto for plain JOIN.

The index recommendations come from looking at the WHERE clauses, and noting that nothing can be done about the GROUP BY.  More discussion here.

If you need to discuss this further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....


if set global transaction, how can I commit?

BEGIN or START TRANSACTION initiates a transaction.  It is ended by COMMIT or ROLLBACK in the same connection.

SET TRANSACTION provides optional attributes for the transaction; it is not often used.

If you need a long-running transaction, or something that spans multiple web page invocations, you need to do it some other way.  (This is probably a common question on this forum.)


What would be most elegant way to replicate data contents periodically into a MySQL table?

If you don't need to retain the data from the previous day when you update the data, you could do this without downtime:

Day 1:

insert data from API into Table1

Day 2+:

create a new table with the same structure as Table1

create NewTable like Table1;

insert API data into NewTable (note: app is still using Table1 at this point)
switch out the tables in one statement

rename Table1 to OldTable, NewTable to Table1;

drop OldTable now if you want to, or keep some archives


Select data with the latest table partition that actually holds data

Try something like this (replace schema.tablename with your object name).  It worked for me in my limited testing.

DECLARE @PartitionValue INT

SET @PartitionValue = (
        SELECT TOP 1 partition_number
        FROM sys.dm_db_partition_stats
        WHERE object_id = object_id('schema.table_name')
            AND row_count > 0
        ORDER BY partition_number DESC
        )

SELECT count(*)
FROM schema.table_name
WHERE $PARTITION.pf_schema_table_name_data(expression) = @PartitionValue


"Waiting for initial communication packet" error 0 in MySQL

Adding the following database parameter to my.ini solved my problem:

skip_name_resolve

This parameter skips host name resolution during connection establishment with the MySQL database server.


Grant Windows logins to read a secondary database of log shipping?

Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server.

You can use this script - How to transfer logins and passwords between instances of SQL Server to script logins - both SQL and Windows.

If you are using SQL Server 2014, then you can just create a ROLE and then grant that role connect any database and select all user securables. This works on readonly databases in logshipping configuration as well.

Note that above is not that elegant if you keep security in mind, but still is an alternative that can be considered.


Creating a PostgreSQL SERIAL column using pgAdmin3

This is not a pgAdmin feature but that of PostgreSQL.  If you check your table from an other client (eg. psql) you will see the exact same column definition.  Why is it so?

  The data types serial and bigserial are not true types, but merely a
  notational convenience for creating unique identifier columns (similar
  to the AUTO_INCREMENT property supported by some other databases). In
  the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

It is always useful to consult the documentation.


WHERE clause on a TIMESTAMP column causes serious performance penalty

It looks like with the WHERE condition the system is searching by the index on 'created_at' and when you eliminate this condition it searches by 'page_id'.
if you add a new composed index on 'page_id' and 'created_at' or in the other order, the optimizer possibly will find the correct execution plan.


How can I generate an Entity-Relationship Diagram and SQL DDL statements from natural language text?

There are many ways to do this, but the most important thing to know is that there is a name for what you want to do: Object Role Modeling. It's a process for creating conceptual models using natural-language semantics.

This is a pretty good (if old) overview of the process.

The Wikipedia page for ORM has a pretty complete list of the available tools, many of which are free.

The most up-to-date solution is NORMA, since it is actively maintained by the ORM Foundation, but that's a plug-in that requires Visual Studio.

If you don't have Visual Studio, Infagon looks like it is still pretty actively developed and may be your best bet.

CaseTalk is also worth a look. Free to students and educators, but commercial otherwise.


How to assign CTRL+F1 to sp_whoisactive in ssms 2012?

Click on Tools > Options.  Then navigate to Environment > Keyboard > Query Shortcuts. In the stored procedure column next to Ctrl+F1, type Sp_whoisactive.

Next navigate to Tools > Options > Environment > Keyboard > Keyboard, and find the command Query.CustomSP1. Assign the shortcut Ctrl+F1. Then search for the command Help.ViewHelp and remove the mapping for Ctrl+F1 there.


You should separate the tests into tests that can execute inside a transaction and rollback and tests that require a full restore. Execute accordingly :)

If you have any DDL in the test then don't bother with transactions, the only safe option is to restore. Most single threaded DML tests will work if wrapped in a transaction and rolled back, but not all.

For speeding up restore consider snapshots, see Revert a Database to a Database Snapshot but keep in mind that snapshot feature requires Enterprise edition (or whatever its current name for 'Enterprise').


Is there any point in undersizing VARCHAR columns?

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.


Unique, Non-Clustered Index and Unique Clustered Index on Same Column

In SQL Server the PK index will, by default, be a clustered index, containing all of the data rows at the leaf level of the index.

Queries of the form

SELECT COUNT(*) FROM T

or

SELECT ... FROM T ORDER BY ID OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY

Would normally use the clustered index.  But scanning the whole clustered index to count the rows, or to fetch a page of rows from deep in the sort order is expensive.

So it's a separate non-clustered index on the same key is sometimes created to support these queries.


Is there a way to get instance_id from msdb.dbo.sysjobhistory during the execution of a SQL Job step?

If you create dummy steps between each real step, will that work?

So if step1 is Start Import, ans step2 is Run Import, you'll see step1 completed and you'll know step2 is running.

Alternatively, just insert all the steps into your own own audit table?


What is the optimum method of finding the per-item earliest date from a clustered index

This is related to "Index Skip Scan" optimization (see the Connect item below, from 2011). Unfortunately it has been closed as "Won't Fix".

Some related enhancements are already there but only for partitioned tables: Query Processing Enhancements on Partitioned Tables and Indexes.

Various workarounds exist though:

Workaround / solution 1: CROSS APPLY to subquery with TOP 1

If there is a table with the (10K) distinct ID values, we can use it to do a CROSS APPLY.

-- if you don't have a table already
CREATE TABLE MyTableIDs
  ( ID int NOT NULL,
    PRIMARY KEY (id)
  ) ;

-- we only do this once
INSERT INTO MyTableIDs (ID)
SELECT ID
FROM MyTable
GROUP BY ID ;

Then the query will do (10K) seeks into the index:

SELECT  i.ID, a.EventDate
FROM    MyTableIDs AS i
    CROSS APPLY
        ( SELECT TOP (1) t.EventDate
          FROM     MyTable AS t
          WHERE    t.ID = i.ID
          ORDER BY EventDate
        ) AS a ;

Workaround / solution 2: Implement "Skip Scan" with Recursive CTE

Another option is a recursive CTE to implement the skip scan. Here is a demo script, posted by @PaulWhite on SSC in October 2010, demonstrating how much faster an rCTE can be: Calculating interest query

In your case, it would be something like this:

WITH    RecursiveCTE
AS      (
        SELECT  a.*
        FROM    ( SELECT TOP (1)  ID, EventDate
                  FROM     MyTable
                  ORDER BY ID, EventDATE
                ) AS a
        UNION   ALL
        SELECT  r.ID, r.EventDate
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE ;)
                SELECT  t.ID, t.EventDate,
                        rn = ROW_NUMBER() OVER (ORDER BY t.EventDate)
                FROM    MyTable AS t
                JOIN    RecursiveCTE AS r
                        ON  r.ID < t.ID
                ) AS r
        WHERE   r.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0) ;

Workaround / solution 3: Partitioned Table

Available if you have Enterprise edition. More details about this possibility in the linked article: Query Processing Enhancements on Partitioned Tables and Indexes

The main disadvantage is that the skip scan will work only if ID is the partitioning column.

Workaround / solution 4: Additional Index

Adding an NCI index on (ID, EventDate) allows an index scan on the much smaller index. See @Daniel Hutmacher's answer for an explanation.

It's still a scan though and not (many) seeks so I'm not sure if it will scale as good as options 1 and 2. Of course everything depends on details (column sizes, number of distinct IDs vs number of repeated values, etc).

Workaround / solution (no, not working) 5: Indexed View

Nice idea, if it worked. Unfortunately a view cannot be indexed if it has GROUP BY and MIN/MAX, see restrictions of Indexed Views.

I wonder why, since it is allowed (and be indexed) if it has GROUP BY and COUNT_BIG(). There may be a Connect item about it, too!

Workaround / solution 6: "Do It Yourself" Indexed View

Implement an equivalent to an indexed view yourself. You could for example, add a MinEventDate column in MyTableIDs (the table with the distinct ID values, see option 1) and add triggers in MyTable that update this column accordingly. Then your query would be a simple SELECT from MyTableIDs.

Links to related web pages

Connect item: Implement Index Skip Scan
Implemented feature: Query Processing Enhancements on Partitioned Tables and Indexes
Indexed Views and restrictions: Create Indexed Views


Is there a way to tell your hostname within a stored procedure?

I can see my hostname on my PC

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql>

This works in MySQL 5.x

SELECT variable_value INTO @local_hostname
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

The query works directly in the mysql client:

mysql> SELECT variable_value INTO @local_hostname
    -> FROM information_schema.global_variables
    -> WHERE variable_name = 'hostname';
Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

mysql>

Here is quick SP I wrote in MySQL 5.5.12 on my PC

DELIMITER $$

DROP PROCEDURE IF EXISTS `junk`.`ShowHostName` $$
CREATE PROCEDURE `junk`.`ShowHostName` ()
BEGIN

    SELECT variable_value INTO @local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';
    SELECT @local_hostname;

END $$

DELIMITER ;

Then run CALL junk.ShowHostName();

mysql> CALL junk.ShowHostName();
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here it is as a function

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetHostName` $$
CREATE FUNCTION `junk`.`GetHostName` () RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN

    DECLARE local_hostname VARCHAR(64);

    SELECT variable_value INTO local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';

    RETURN local_hostname;

END $$

DELIMITER ;

Now, just SELECT it:

mysql> SELECT junk.GetHostName();
+--------------------+
| junk.GetHostName() |
+--------------------+
| LW-REDWARDS2       |
+--------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


Performance Monitor % Processor Time metric difference between _Total and sqlservr.exe

The Process: % Processor Time (Instance - sqlservr.exe) counter takes into account all of your cores. If you divide by the cores you get an answer much similar to what you expected.

This TechNet article explains it in far more detail but it uses this calculation to get the number you see:
(No of Logical Cores * 100),  So this is going to be a calculated over a baseline of more than 100.


Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have.

Start with this:

SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout
FROM employees
LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno
LEFT JOIN attendanceOut ON employees.eno=attendanceOut.eno
WHERE attendanceIn.puchtime >= '2016-07-01 00:00:00'
AND attendanceIn.puchtime < '2016-07-02 00:00:00'
AND attendanceOut.puchtime >= '2016-07-01 00:00:00'
AND attendanceOut.puchtime < '2016-07-02 00:00:00'
GROUP BY eno
ORDER BY eno ASC

If you still need performance improvements, provide SHOW CREATE TABLE tablename statements for all your tables in this query.


Database Design for Airline Reservation System

It seems you are looking for a tool, that verifies the normalization. There is no such tool (at least I can't think of any since it would be almost impossible do write checks for the noralization).

If you want to see if your schema verifies, use simple logic by observing it. If you are not sure, look at the examples in WIkipedia for 1NF, 2NF and 3NF. Does your model have the same structure as these examples? If yes, than you adhere to normalization.

Your model may look like this (showing only PKs and FKs and example)

flight
-------------------
flight_id (PK)
destination
[....]

passengers
-----------------
passenger_id
surname
lastname
nr_of_seats
[....]

payment
--------------------
payment_id
date
price
[....]

-- Your join table, that lets you connect all the data. I assume that the a passenger may book a flight more than once, so I join them together with a certain flight and payment.
reservation
----------------------
flight_id
passenger_id
payment_id
[....]

If you want tools to visuallize your database, pick one from the list that applies to your DBMS. It lets you check how your entities are connected together.


how to generate the creation script for partition function and partition schema?

I see from your comment on Terry C's answer that you are looking for a T-SQL solution to script partition functions and schemes. Although I think SMO or SSDT (via SSMS, Powershell, Visual Studio) is the right tool for scripting DDL, it is possible accomplish via some ugly T-SQL. Below are sample scripts for this particular task.

SELECT
      N'CREATE PARTITION FUNCTION '
    + QUOTENAME(pf.name)
    + N'(' + t.name  + N')'
    + N' AS RANGE '
    + CASE WHEN pf.boundary_value_on_right = 1 THEN N'RIGHT' ELSE N'LEFT' END
    + ' FOR VALUES('
    +
    (SELECT
        STUFF((SELECT
            N','
            + CASE
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'char', N'varchar')
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'nchar', N'nvarchar')
                    THEN N'N' + QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'date'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS date), 'yyyy-MM-dd'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'datetime', N'smalldatetime')
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss.fff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime2'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime2), 'yyyy-MM-ddTHH:mm:ss.fffffff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetimeoffset'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetimeoffset), 'yyyy-MM-dd HH:mm:ss.fffffff K'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'time'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS time), 'hh\:mm\:ss\.fffffff'),'''') --'HH\:mm\:ss\.fffffff'
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'uniqueidentifier'
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN (N'binary', N'varbinary')
                    THEN CONVERT(nvarchar(4000), r.value, 1)
                  ELSE CAST(r.value AS nvarchar(4000))
              END
    FROM sys.partition_range_values AS r
    WHERE pf.[function_id] = r.[function_id]
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_functions pf
JOIN sys.partition_parameters AS pp ON
    pp.function_id = pf.function_id
JOIN sys.types AS t ON
    t.system_type_id = pp.system_type_id
    AND t.user_type_id = pp.user_type_id
WHERE pf.name = N'PF_Year';

SELECT
      N'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name)
    + N' AS PARTTITION ' + QUOTENAME(pf.name)
    + N' TO ('
    +
    (SELECT
        STUFF((SELECT
            N',' + QUOTENAME(fg.name)
    FROM sys.data_spaces ds
    JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
    JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
    WHERE ps.data_space_id = ds.data_space_id
    ORDER BY dds.destination_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_schemes AS ps
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
WHERE ps.name = N'PSC_Year';


Getting a query to work on SQLite as well as on MySQL

SELECT * FROM
  (SELECT
  discounts.*,
  (SELECT COUNT(id)
   FROM payments
   WHERE payments.discount_id = discounts.id
   GROUP BY payments.discount_id) AS usage_count
FROM discounts
WHERE rate > 10) table1
WHERE usage_count > 1


Get into SQL Server 2008 with no management studio and no sqlcmd

You can download SSMS 2012 or 2014 which is a full seperate download and then use it to access tables, etc

link: ssms 2012

can't comment on what/ how your plugin is.


As @Max Vernon suggested, you can create sub queries in MS Access, so perhaps I jumped the gun on that one! I couldn't understand why my sub query wasn't working; I still don't understand fully Why this solution works but the other one didn't.

I followed these steps:

Create sql subquery as a saved query in Access. Test. - Success
In a new query, added the saved query, added my Modification_Log table. Test. - Success
Added the Employee table, linked this. Test. - Success
Added my query criteria. Test. - Success
I finally replaced my reference to the saved query with my sub query. Test. - Success

What I end up with is this:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email, Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM
         (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
         FROM Modification_Log
         GROUP BY Modification_Log.RecordID) As LogMax
INNER JOIN (Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID) ON (LogMax.NewestModificationID = Modification_Log.ModificationID) AND (LogMax.RecordID = Employee.EmployeeID)
WHERE Employee.EmployeeID=3 AND Modification_Log.TableName='Employee';

Which is almost identical apart from the fact the sub query immediately follows the FROM clause, I didn't think the order of joins would make a difference but this is MS Access we're talking about.


TempDB Autogrowth - Does SQL need a restart?

As far as a restart goes, no you don't need to.  However there's several things to think about before growing TempDB, some of the main ones are:

-Resize all of them to the same size.  TempDB uses a round robin proporitional fill algorithm which will create hotspots on physical files which are smaller in size.  Paul Randal goes through how to properly do this here.

-There's logical and physical contention.  Logical usually arises when you have to few physical files to spread the IO requests too.  Check your metrics to see how fast your current disks are performing and benchmark it again after you get new disks.  Then you'll have an idea what your current workload threshold is.  Review the DMVs to use here.  Read more about detecting logical contention here.

If you don't care about all this nit picky performance info and this is a environment where root cause analysis, very high uptime, perf. expectations are not critical; you could just throw hardware at it but make sure you review the info around tempdb.
Edit: Aaron Bertrand gave some great things to check in the of another answer here, which I will copy here:
You should set trace flag 1117 globally but also add it to the startup parameters so it is persisted.


mysql auto adjusting max connections values

It seems OK to me. You cannot have max_connections larger than that, because you open_files_limit is too low. For every connection MySQL opens several files. Take a look here.

So, you will have to increase open files limit on your OS, and afterwards, you can put higher max_connections value.


How to optimise very simple query with subquery that only needs to execute once

Have you tried with a join?

SELECT a.products_id, a.products_model, a.products_iban_no
     , a.products_allocation_number
FROM products a
JOIN (
    SELECT products_model
    FROM products
    GROUP BY products_model
    HAVING COUNT(products_model) > 1
) as b
    on a.products_model = b.products_model


Where should I look for details about 'access denied' errors?

You need to look in SQL Server's error log, which is kind of a catch-22, since you need access to the SQL Server in order to see the error log.

You don't need direct access to SQL Server to read the log; if you have physical access to the machine, you can find the log in something like:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG

Right-click, open with Notepad, and scroll to the bottom, you'll see things like:

  2013-05-23 16:34:56.29 Logon       Error: 18456, Severity: 14, State: 5.
  2013-05-23 16:34:56.29 Logon       Login failed for user 'whatever'. Reason: Could not find a login matching the name provided. [CLIENT: ]

This is, of course, for security purposes. Similar to the reason why these details are not revealed to the end user when they hit the login failure.


What are my locking options for a Merge statement?

You need to give the query processor a more efficient access path to locate StudentTotalMarks records.  As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId] applied to each row:

The engine takes U (update) locks when reading as a basic defence against a common cause of conversion deadlocks.  This behaviour means the second execution blocks when trying to obtain a U lock on the row already locked with an X (exclusive) lock by the first execution.

The following index provides a better access path, avoiding taking unnecessary U locks:

CREATE UNIQUE INDEX uq1
ON dbo.StudentTotalMarks (StudentID)
INCLUDE (StudentMarks);

The query plan now includes a seek operation on StudentID = [@StudentId], so U locks are only requested on target rows:

The index is not required to be UNIQUE to solve the issue at hand (though the INCLUDE is required to make it a covering index for this query).

Making StudentID the PRIMARY KEY of the StudentTotalMarks table would also solve the access path problem (and the apparently redundant Id column could be removed).  You should always enforce alternate keys with a UNIQUE or PRIMARY KEY constraint (and avoid adding meaningless surrogate keys without good reason).


Running SHOW PARAMETERS LIKE 'TIMEZONE'; returns in my case:

key         value               default
TIMEZONE    America/Los_Angeles America/Los_Angeles

SELECT CURRENT_TIMESTAMP() results in 2019-11-04 07:48:47.289 -0800.

Then I can alter the session parameter like this ALTER SESSION SET TIMEZONE = 'Europe/Amsterdam';

Now SHOW PARAMETERS LIKE 'TIMEZONE'; gives:

key         value               default
TIMEZONE    Europe/Amsterdam    America/Los_Angeles

And SELECT CURRENT_TIMESTAMP() results in 2019-11-04 16:49:45.987 +0100

That hopefully shows the logic behind CURRENT_TIMESTAMP.

Answer to actual question: To get the timepart simply use TIMESTAMP::TIME

SELECT
CURRENT_TIMESTAMP() :: TIME AS Amsterdam_Time,
CONVERT_TIMEZONE('America/Los_Angeles', CURRENT_TIMESTAMP)::TIME AS LA_Time,
CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)::TIME AS UTC_Time;

Results in (with have Amsterdam as default):

AMSTERDAM_TIME  LA_TIME     UTC_TIME
16:55:37        07:55:37    15:55:37


mysql innodb index: better one big or many compact?

danblack's post answers the main question about the best index strategy for you queries.

However, I would add a sometimes forgotten optimization of the index strategy, which is implemented in most recent versions of RDBMs (MySQL, MariaDB, PostgreSQL...): covering indexes

Definition of covering index: (from MySQL documentation)

  An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O

That means that your third query:

select distinct attraction from table where country='X'

would benefit more from a (country, attraction) index than a simple (country) index.


How can I query a remote server using a local function?

Seems (based on the name, anyway) like a pretty basic scalar UDF that just massages strings, won't be subject to a lot of changes over time, and doesn't rely on any data. So, I suggest you make a copy of the function on the source server, and fight whatever rule is preventing you from doing so (it shouldn't be hard if you can justify the use of the function, and describe what kind of hack you're trying to use as a workaround to the rule). Note that the function doesn't have to live in the same database, it just needs to be on the same server.

(Even if the function does rely on data, and it's clear in your case it doesn't, the local copy of the function could reference the data in the other server, using 4-part name(s), without referencing any functions.)

DRY is not always worth pursuing.


Your query has

WHERE
    ST_Distance_Sphere (
        geo_point,
    ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34
)

In MySQL you must use the MBR functions to use the index.

So don't use ST_Distance_Sphere instead,

MBRContains( ST_Buffer( ST_SRID(POINT(51.85 -0.79), 4326), 100 * 1609.34), geog )


The problem
Here is a very similar case discussed on pgsql.general. It's about the limitation in a b-tree index, but it's all the same because a GIN index uses a b-tree index for keys internally and therefore runs into the same limitation for key size (instead of item size in a plain b-tree index).
I quote the manual about GIN index implementation:

Internally, a GIN index contains a B-tree index constructed over keys,
where each key is an element of one or more indexed items

Either way, at least one array element in your column data is too big to be indexed. If this is just a singular freak value or some kind of accident you may be able to truncate the value and be done with it.
For the purpose of the following demo I'll assume otherwise: lots of long text values in the array.
Simple solution
You could replace elements in your array data with according hash values. And send look-up values through the same hash function. Of course, you probably want to store your originals in addition somewhere. With that, we almost arrive at my second variant ...
Advanced solution
You could create a look-up table for array elements with a serial column as surrogate primary key (effectively a radical kind of hash value) - which is all the more interesting if involved element values are not unique:
CREATE TABLE elem (
  elem_id serial NOT NULL PRIMARY KEY
, elem    text UNIQUE NOT NULL
);

Since we want to look up elem, we add an index - but an index on an expression this time, with only the first 10 characters of the long text. That's should be enough in most cases to narrow a search down to one or a few hits. Adapt the size to your data distribution. Or use a more sophisticated hash function.
CREATE INDEX elem_elem_left10_idx ON elem(left(elem,10));

Your column data would then be of type int[]. I renamed the table to data and got rid of the ominous varchar(50) you had in your example:
CREATE TEMP TABLE data(
  data_id serial PRIMARY KEY
, data int[]
);

Each array element in data refers to a elem.elem_id. At this point, you may consider to replace the array column with an n:m table, thereby normalizing your schema and allowing Postgres to enforce referential integrity. Indexing and general handling becomes easier ...
However, for performance reasons, the int[] column in combination with a GIN index may be superior. Storage size is a lot smaller. In this case we need the GIN index:
CREATE INDEX data_data_gin_idx ON data USING GIN (data);

Now, each key of the GIN index (= array element) is an integer instead of a longish text. The index will be smaller by several orders of magnitude, searches will consequently be much faster.
The downside: before you can actually perform a search you have to look up the elem_id from the table elem. Using my newly introduced functional index elem_elem_left10_idx, this, too, will be much faster.
You can do it all in one simple query:
SELECT d.*, e.*
FROM   elem e
JOIN   data d ON ARRAY[e.elem_id] <@ d.data
WHERE  left(e.elem, 10) = left('word1234word', 10) -- match index condition
AND    e.elem = 'word1234word';  -- need to recheck, functional index is lossy

You may be interested in the extension intarray, that supplies additional operators and operator classes.
Fully functional live demo on sqlfiddle.


Formatting Tabular mysql results in mail on unix

The formatting is down to the client in which you are reading the result. If the client is set to fixed width font then the display formatting will be as you expect.


SET CONTEXT_INFO for tracking specific SQL requests

I've now got this in production and it's working beautifully.  We found the underlying issue before this went out, but it may be very useful for future issues.

To any future readers, SET CONTEXT_INFO can indeed be used for correlating SQL requests to the code and/or requests that run them, and I've seen no noticeable performance hit.

For anyone curious about the specifics of this locking issue, it turns out that in this case, we had several places in our code where we opened a transaction and then during the transaction, we called a library function that on the surface appeared to just retrieve a cached value, but in reality did some non-database work that should not have been done inside the transaction and then proceeded to open its own database connection.  Under certain high-load situations, this caused a deadlock because the transaction locked a highly-contented item, then while the improper processing was occurring, every other available connection became blocked waiting for the same highly-contented item.  Once the processing was complete, the errant code then attempted to open the separate database connection, which deadlocked because every available connection was in use and waiting for the transaction to complete.  Adding code to detect that situation and log the call stacks allowed us to find not only the case that had begun triggering multiple times per week, but five other cases as well.

Lesson: let the database do everything involved with every transaction (never use SqlConnection.BeginTransaction).  When you think you need to do something client side during the transaction, redesign the system so that you don't need to do that--you may think you're fine with the one thing you're doing, and you might be, but you never know how the code may subtly change in the future and break you just like this.


Index Seek scanning whole table dependent on parameter value

When there is a mismatch between the data types of the column and variable, SQL Server cannot directly use the seeking ability of a b-tree index to locate the correct range of values.

When the rules of data type precedence mean that the column data would have to be converted to the data type of the variable, this would mean scanning the whole table or index, converting each value and testing it against the variable as a residual predicate.

This is obviously not ideal, but so common (unfortunately) that SQL Server has a built-in way to achieve an index seek in these cases. It takes the supplied value and computes the range of values it maps to, accounting for the type conversion and collation.

This feature is known as a dynamic seek and the internal method that computes the mapped range is called GetRangeThroughConvert.

For example, when the nvarchar variable contains 'a', the mapped range of values for data type varchar might be 'a' to 'B' (the exact range depends on the collation). This means SQL Server can seek the varchar index between 'a' and 'B', testing only the matches for equality with 'a' (as nvarchar) as a residual predicate.

When the supplied value is the empty string, the computed range is infinite, so the whole index is effectively scanned.

For example:

DROP TABLE IF EXISTS dbo.MyTable;
GO
CREATE TABLE dbo.MyTable
(
    ColumnA varchar(22) COLLATE Latin1_General_CI_AS NOT NULL
);
GO
INSERT dbo.MyTable
    WITH (TABLOCKX)
    (ColumnA)
SELECT TOP (1000)
    REPLICATE(CHAR(65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 26), 22)
FROM master.sys.all_columns AS AC1
CROSS JOIN master.sys.all_columns AS AC2;
GO
CREATE INDEX IX_MyIndex_A ON dbo.MyTable(ColumnA);

The following query uses a dynamic seek with a range of 'a' to 'B' and a residual predicate of CONVERT_IMPLICIT(nvarchar(22),[dbo].[MyTable].[ColumnA],0)=[@varA]:

DECLARE @varA nvarchar(22) = N'a';

SELECT MT.ColumnA
FROM dbo.MyTable AS MT
WHERE MT.ColumnA = @varA;

The execution plan shows the dynamic seek shape with 38 rows qualified by the seek, but all ultimately rejected by the residual:

The 38 rows are those counted by the query:

SELECT COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE MT.ColumnA > 'a'
AND MT.ColumnA < 'B';

When the variable contains an empty string, the calculated range is unbounded so the seek effectively scans the whole index:

DECLARE @varA nvarchar(22) = N''; -- empty string

SELECT MT.ColumnA
FROM dbo.MyTable AS MT
WHERE MT.ColumnA = @varA;

The execution plan shows all 1000 rows being read from the index (but again, discarded by the residual):

The empty string is a special case where GetRangeThroughConvert cannot produce a useful range. A single space character does produce a narrow seek range (plan).

Anyway, the message is to pay careful attention to data types.

db<>fiddle


MySQL performance tuning + queries stuck on "Copying to tmp table"

This query definitely needs to be optimized since the "DISTINCT" part is forcing creation of temporary tables.

If you really don't want to touch the query (which is a mistake) - then your other option would be to create a RAM disk (tmpfs) for your tmp files. The query will still be creating temp tables, however it will avoid the disk I/O part.

Hope this helps.


Select one name per person where spelling variations exist

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names:

INSERT INTO TargetTable
  (Emp_ID, Name)
SELECT Emp_ID, MIN(Name)
FROM SourceTable
GROUP BY Emp_ID ;

And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on the disk but that is no guarantee for the order of retrieval).

You can get the data from the target table with a query afterwards and if you want them ordered, you can (and should) define the order you like (and a different one if you like, every time you query it):

SELECT Emp_ID, Name
FROM TargetTable
ORDER BY Name ;


Primary Key issue in MemSQL doesn't support case sensitive

In order to make primary key to support case sensitive, binary character set helps in this.

To set binary character set at database level, following command can be used:
SET collation_connection = 'utf8_bin';

To set at column level, following command can be used:
create table CaseSensitive ( name varchar(5) binary, age int, primary key(name));


Integration Services License requirements

If users are not accessing the 2016 Instance itself, there's no need to licence them for CALs to it, just need a licence for itself for production purposes - Server or (in this case, avoid) Core.

It is unusual that you would use a Server licence without CALs, Microsoft would probably be interested on an audit... but as you note, your users are on 2014 CALs accessing their 2014 Server Instance, so would be covered for that (assuming no pass-through or multiplexing going on). However, as at least one user will need to use SSMS or VS etc to manage the 2016 SSIS packages or indeed server, I'd suggest a 2016 CAL for them.

Disclaimer: I'm a Microsoft Licensing Sales Specialist and sell MS Licencing online in UK, any advice may be given freely but ultimate responsibility is of the licencee.


CONVERT to datetime fails on Dynamic SQL - "Conversion failed when converting date and/or time from character string"

Your second example is using the /b switch which only returns file names, but not any date information.  Removing the /b should fix the problem.


Combine results of Multi-Server Query from Registered Servers

No, there is no built-in way to accomplish this due to each query in a Multi-Server query being executed in a separate connection to each individual server. The results only exist together within SSMS. I always had to copy and paste the results grid into Excel so that I could do further aggregations and sorting.

Fortunately, I am very close to releasing a new product that does exactly what you are wanting to do. There will be a free version that has some limitations. A paid version allows for multithreading. It currently comes in two forms: command-line utility and SQLCLR Stored Procedure.

If you are interested in beta-testing, please contact me. I will update this answer with more details once it is officially released.


Should I have a column for every data type or 1 column that stores everything

This question (in various guises) crops up regularly. This type of "solution" is known as EAV (Entity-Attribute-Value) and is not a good idea. Take a look here or here for tips (or links to tips) on the problems it can cause and how to properly leverage the data types that your RDBMS offers.

Not using the correct data type is a sure fire way to confuse the
query optimiser and slow your system down. Joe Celko is a world renowned expert in all matters SQL and his opinions merit attention.

The fact that he uses the acronym "MUCK" (*) to describe this system should tell you all you need to know :-)

(*) Massively Unified Code Key


MySQL query using alias to count certain years from DATETIME

This query will work for any given year based on today

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT ID,GROUP_CONCAT(orderDate ORDER BY orderDate) orderDates,COUNT(1) orderCount
FROM t1
WHERE orderDate >= MAKEDATE(YEAR(NOW()) - 1,1)
AND   orderDate <= MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY;
GROUP BY ID;

Running this query now will give you all of 2015.

Running this query next year will give you all of 2016.

NOTES

MAKEDATE(YEAR(NOW()) - 1,1) gives you the first day of last year

MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY gives you the last day of last year

The reason I use

SET group_concat_max_len = 1024 * 1024 * 100;

The default for group_concat_max_len is 1024 (1K). I set it to 100M in case the list is long.

This query will work faster if you have a comound index on (ID,orderDate)

If you do not have such an index, then create one:

ALTER TABLE t1 ADD INDEX ID_orderDate_ndx (ID,orderDate);

COMMENTARY ON YOUR QUESTION

With regard to your original query

You cannot use HAVING without a GROUP BY in the strictest sense (See MySQL Documentation on MySQL Handling of GROUP BY)
There is no need for HAVING

HAVING is the same as a WHERE except it is against an aggregated set of data.
Using WHERE just gets the data before aggregation

Using HAVING complicates the query because you would have to structure the query for counting multiple years and then select just 2015. Using WHERE gets data only from 2015. Then, you can aggregate by ID.


Enable Job instantly after AOAG Role change

Not sure if you can build a "trigger" to capture role changes.

I would probably set up a SQL Server Agent job that runs pretty frequently. Check

SELECT primary_replica
FROM sys.dm_hadr_availability_group_states;

and compare this value to a local table to detect when the primary replica changes.


Replication pull job on subscriber stopped running when removing article from publication

I don't know how your drop the article from publication, but you can assign again
just execute this

exec sp_addsubscription @publication='your_pub',@article='all',@subscriber='your_server'


many 'unauthenticated user', 'connecting host', in show full processlist (Mysql 5.1.5)

Setting skip_name_resolve disables the MySQL server's ability to perform DNS lookups. DNS servers can be unavailable and for a production server it is usually better to specify and ip and not a hostname as the 'host' specifier of a username. However users must then be created as 'user'@’ip’ and not as 'user'@'host' (except for ‘localhost’ what is accepted with this setting ON)

Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost

http://dev.mysql.com/doc/refman/5.0/en/host-cache.html


Its necessary to relate the ComplaintMessages entity with the User and Admin entities or is enough to relate with the Complaint entity? (ER Model)

Depends if a different admin or a different user could join in on the followup conversation, other than those who started it in the originating Complaint.

If a complaint's message thread is forever tied to the single, originating user, then both the connection (foreign key) and idUser column should be excised from ComplaintMessages. Same with the admin if it's fixed, and there's no chance a different admin might have to answer the followups (this side might be less true).

Consider if it even makes sense to have this many separate tables. Most would keep all users, regular and admin alike, in the same table, and have a field - or a connected rights table - to determine who has what permissions.

Similarly, there doesn't seem to be much reason to keep  Complaint and ComplaintMessages separate. Considering it sounds like there could be multiple followup messages, you are currently missing some kind of mechanism to keep the followup messages in chronological order (other than possibly relying on the primary key id field providing that ordering).
Instead of the two, you could have a single Messages table, with a "thread ID" field added, referencing the first entry in the same table. And probably a date/timestamp field to keep everything in order, or a reference back to the previous message's ID.