SQL Server stored procedure working when run manually, not running from SQL Server Agent I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error.

  line 9, character 9, unexpected end of input

The code takes a very long XML string in UTF-8 encoding and puts it into a single nvarchar(max) cell. Then puts this string into a XML cell in a different table, allowing me to query the individual parts of the XML code using the nodes function. I cannot put the data directly into a nvarchar cell due to encoding differences.

I can't reproduce the string here as it is very very long.

I'm just looking for ideas really as to where it might be going wrong.

Here is what I know so far:

The procedure runs without issue when executed manually
I have checked permission issues, and that doesn't seem to be the problem. The agent runs under my own account and I am a sysadmin on the database
I split the procedure into seperate parts to locate exactly where the problem is occuring. Once again the seperate procedures run fine when executed manually but an error occurs when run through SQL Server agent.
When the query is run seperately through SQL Server Agent it gives a slightly different error. This leads me to believe it is an encoding issue. However I am getting the XML from a webpage and I can't change the encoding on the webpage.

  line 1, character 38, unable to switch the encoding

I know this is a long shot since you can't replicate the issue but if anyone could give an idea as to where to start looking for an answer, it would be greatly appreciated.

EDIT 1: The code for obtaining the XML is as follows:

Select @url = 'http://....'

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

INSERT #TextData(HTML)
EXEC @hr=sp_OAGetProperty @win,'ResponseText'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

line 1, character 38, unable to switch the encoding

I ran in to this a while back sql server wants the source string type to match the utf code.

    SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO

You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.

  line 9, character 9, unexpected end of input

I would run this problem from a different direction.

have the procedure output the received xml
check for permissions issues with the service accounts (difference between sql server and agent services)


Is this an example of an Enum (Enumerated Type) I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm correct in this thinking, and if not then what this is, and how I find out more about it.

SELECT fp.id AS fund_part_id, fp.fund_id, fp.principle_amount, fp.detail
FROM finance.fund_part fp
WHERE fp.status = 'live'::finance.fund_part_status;

I can see if the pg_enum catalog (table) that there are a series of records that belong to one enumtypid oid. Some of the entries in the enumlabel name column are ['live', 'for sale', 'pre-agrement']

Yes, it looks like finance.fund_part_status is an enum type.

Assuming you're using the psql command-line interpreter, try this command:

\dT+ finance.fund_part_status;


Do I need to update an index include columns every time I add a new column I work on a team where we have millions of records of data, but not 100s of millions anything crazy.

We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is loading data from disk into memory. This is crashing out DBs, and this has happened a few times in the past year.

A little bit about that way we have our data:

We have a "Carton" table and "Items" table. Items are contained inside cartons, so the carton is the parent entity and items are child (one to many relationship). These tables both have many attributes associated with them.

Okay, now moving forward. We look at the query optimizer and it tells us to create missing indexes. Here is an example:

CREATE INDEX missing_index_69 ON dbo.items (carton_number) INCLUDE
(item_number, entered_upc, item_desc, department, item_qty, item_cost_amt, cost_multi_qty,
item_retail_amt, retail_multi_qty, item_sell_amt, sell_multi_qty, mdse_div_nbr, sams_msde_cat_nbr,
base_retail_uom_cd, item_sale_type_co, scan_cnt, donation_item_id, price_embedded, weighted, cvp, gtin,
wm_discretion, category, sub_category, created_date, last_change_date, created_user_id,
last_change_user_id, replenished_by_unit, uom, deleted, qty_prev, qty_delta, bucket_id, bucket_color,
hazmat, hazwaste, cvp_value, claim_value, wmtd_claim_eligible, smart_label_id)

This issue arose when we did a outer fetch to get the items associated with the carton, and we looked for the items via the carton_number (Carton_number is primary key for Carton table, and is a Foreign-Key for the items, also with an index on it!)

Yes, we do Select All so that is why it is requesting to add all those fields, but I digress.

My problem is, this assumes that every single time I add a new column to the Item table, that I will have to update the index to add it in the include column. We have many queries where we search by different criteria (by smart_label_id, created_date, carton_state, etc.) For each one of those queries with indexes on them, do I really have to include all the columns, and update EACH ONE of those queries anytime I add a new column?? That seems extremely excessive, especially since we have many queries and adding a new column would require a LOT of refactoring.

Is this normal? It is my understanding that SQL server can handle billions of records with a lot of attributes, and I am surprised that we are running into this issue with the amount of data we have.

The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity.

The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to include bunch and bunch of columns. I.e., skip the included columns and just focus on the WHERE cause and the index keys.

So, don't give those "missing index" recommendations much credit. At all. But they can serve as starting points for your own analysis.

To cluster the "child table" om the FK is another often forgotten trick. As already mentioned by Laughing Vergil.


What is the meaning of this anomaly warning message? I have run an analysis with SchemaSpy.

The tool gave me a warning message for two tables. The warning message is:

  Tables with incrementing column names, potentially indicating denormalization

What is meaning of the above error?

For reference :

http://schemaspy.sourceforge.net/sample/anomalies.html

Based on the sample table in your link, I'd suggest it's taking issue with fields such as:

phone1
phone2

or

notes1
notes2
notes3

This may indicate a design flaw (but not always), and if so, one that can probably be corrected by more descriptive field names or perhaps a child table.

For example, someone might have more than one phone number... what if someone has three? Seven? Maybe data like this should be in a subtable, and perhaps even with some sort of indicator of what type of phone number it is, whether it's a preferred phone number, etc. Alternatively, maybe just renaming the fields to phone_home and phone_cell will be enough to eliminate confusion and allow you to collect all of the data you need.


SQL Server 2019 UTF-8 Support Benefits I'm already quite comfortable with using  COMPRESS() and DECOMPRESS() in an internal forum software for our company (Currently in SQL Server 2017), but trying to make the database as efficient as possible, is there an advantage to adding _UTF-8 to my current collation as in Latin1_General_100_CI_AS_SC_UTF8 upon future migration to SQL Server 2019?

trying to make the database as efficient as possible

There are at least two different types of efficiency that are really at play here:

space (disk and memory)
speed

Under certain conditions (as described in Outman's answer, which is a copy/paste of the "Recommended Uses / Guidance" section of my blog post, linked at the top of that answer) you can save space, but that is entirely dependent on the type and per-row quantity of characters.

However, at least in its current implementation, you are more likely than not to have a decrease in speed. This could be due to how they are handling the UTF-8 data internally. I know that when comparing UTF-8 data to non-UTF-8 VARCHAR data, both values are converted to UTF-16 LE (i.e. NVARCHAR). I wouldn't be surprised if other (perhaps even most) operations needed to convert the UTF-8 data into NVARCHAR given that is how Windows / SQL Server / .NET have always handled Unicode.

So, assuming that you have a scenario that could possibly benefit from using UTF-8, you need to choose which efficiency is more important.

Now, whether or not UTF-8 will benefit scenarios where the environment itself is naturally UTF-8 (e.g. Linux) remains to be seen. Typically the database driver (ODBC, SQL Native Client, etc) handles the translation between client and server. I suppose there could be a performance / efficiency gain here if doing this would result in the driver software skipping the additional steps (and CPU cycles) it takes to do those encoding translations. So far this is just a theory as I have not tested it.

Just keep in mind the following:

UTF-8 was designed to achieve ASCII compatibility for easier implementation. This allows systems that are Standard ASCII-based (values 0 - 127; values 128 - 255 are Extended ASCII and not covered by this) to enable Unicode without having to re-save anything in a new encoding.

For SQL Server, the goal was that existing apps that are currently using VARCHAR can start supporting Unicode without needing to do much re-coding (i.e. adding N prefixes to string literals) or updating datatypes from VARCHAR to NVARCHAR.

It was not designed to be a form of compression. If you have data that has a reduced footprint in UTF-8, then great. But when working with data that is not Standard ASCII, then either won't have any savings, or even worse, you might increase the data size by going to UTF-8 (given that 63k of the 65k BMP characters are 3 bytes in UTF-8, which is 1 byte more than the 2 bytes they require in UTF-16).

And, if UTF-8 gives a performance gain, or at least you don't see a drop in performance, then great. But, don't expect it. In fact, don't be surprised if you happen to see a decrease in performance.
If you decide to implement the UTF-8 collations in SQL Server, you need to be aware of some potential data "issues":

Data loss from mixing UTF-8 string literals and/or variables (due to the current database having a UTF-8 default collation) and non-UTF-8 VARCHAR columns. This is caused by collation precedence effectively downgrading the collation from UTF-16 to whatever code page the column is using.
Minor truncation from mixing non-UTF-8 string literals and/or variables with UTF-8 columns (and, in some cases, variables). This is caused by certain characters
requiring more bytes in UTF-8 than they did in their original encoding.
Invalid byte sequences in UTF-8 can throw an error instead of returning the default replacement character "�". This is a different approach than has been taken so far with invalid sequences in any other 8-bit encoding or in UTF-16.

For more details and examples, please see the "Things to Keep in Mind: Operational" section of my post: Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?


DBCC CheckDB causing high values for resource_semaphore wait type On one of our production server, where SQL Server 2008R2 (Service Pack 2) is installed with 24 GB memory and 24 CPU cores grouped into two NUMA nodes, we are facing high counts for resource_semaphore waits WHEN we execute DBCC CheckDB for one of our database which is 125 GB in size.
Instance is up for last 6 months and working fine, but this start suddenly while no size change in database and no configuration changes performed.
While
Hard drives are performing up to the mark.
BUT
Strange thing i found is "ideal_memory_kb" values i.e. 5299767576 (5TB) for DBCC Check DB Session

select top 5 ideal_memory_kb,* from sys.dm_exec_query_memory_grants

Is it TempDB issue ?
Is there anything wrong on memory side?

There is nothing wrong! This is how DBCC CHECKDB works. You can read Jonathan's post explaining this behavior and a workaround if you're on the Enterprise Edition -- DBCC CHECKDB Execution Memory Grants – Not Quite What You Expect

The only strange thing is that usually this happens with servers with larger amount of RAM!

Here is also a really good article on Understanding SQL server memory grant

  Memory grant uses "RESOURCE_SEMAHORE" wait type. If you see significant waits on this wait type, you may have an issue with big queries.

Perhaps, if you see a big IO Queue on your SAN, you have a lot of IO bound queries on top of your memory pressure. Check the PLE and Memory Grants Pending counters on this server. If you PLE is very low and you have lots of pending memory grants, your server might benefit from additional memory. Also look if you need to add any indexes (review missing indexes DMV) -- Are you using SQL's Missing Index DMVs?


SQL Server 2012, Network for: AlwaysOn Availability Group with AlwaysOn AlwaysOn Failover Cluster Instances Setup: I have two data centers. In site1, my primary site, I'd like to deploy an AlwaysOn Failover Cluster Instance (FCI). In site2, my data recovery site, I'd like to deploy a single-node instance of SQL. I'd then like to use AlwaysOn Availability Groups (AG) to replicate data between site1 and site2.

I've read a lot of blog posts and TechNet articles. It's clear to me that automatic fail over is not supported for AGs with FCI members [1]. It's also clear that FCIs support nodes on multiple subnets [2]. Apparently this is achieved by updating the Virtual Network Name (VNN) in DNS.

Question: Do AGs support multiple subnets, or do I need a spanned subnet between site1 and site2? I haven't been able to find any sources that explicitly make that clear.

References:

[1] Failover Clustering and AlwaysOn Availability Groups (SQL Server)
http://msdn.microsoft.com/en-us/library/ff929171.aspx

[2] AlwaysOn Failover Cluster Instances (SQL Server)
http://msdn.microsoft.com/en-us/library/ms189134.aspx

Yes AG support multiple subnets as described here. Also make sure that your data provider supports MultiSubnetFailover .. .NET Framework 4 supports it.

To answer your question ...

IF you use .NET framework 4 or 3.5 then the provider will support it as described here.

Also, a good reference to SQL Server Multi-Subnet Clustering is well documented.

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.


New tables not showing up in MySQL Workbench Navigator I've created a database on a local MySQL server with MySQL Workbench version 8.0 on Windows 10, but when I make a table in said database with a manually typed query

CREATE TABLE newtable (column1 INTEGER NOT NULL, column2 VARCHAR(30), PRIMARY KEY column1);

I can't see the table in the Navigator panel on the left hand side, even after right-clicking the schema and clicking Refresh All. However, the query was completed successfully according to the output. If I right-click the database and open the Schema inspector, in the Tables column, I can see the new table. I can even work with the new table with typed queries - I just can't seem to open the GUI. That is, until I restart Workbench, and then it shows up.

I tried creating a new table using the GUI, that is, right-click on "Tables" in the Navigator and then creating a new table, and that works as expected and the new table is visible in the Navigator.

Is this a bug in MySQL Workbench or am I doing something wrong? (I am very new to this, I'm still learning!)

EDIT: After rebooting my computer, the problem is gone and refreshing schemas works as expected. ¯\_(ツ)_/¯


How to add a fixed string to a mongoDB result like we do we in SQL Server? We have this syntax with SQL server:

select name as 'database',
      'I_Love_SQL' as 'feelings'
from sys.databases

which will return results like this:

database    |   feelings
database1   |   I_Love_SQL
database2   |   I_Love_SQL
database3   |   I_Love_SQL
database4   |   I_Love_SQL

How can I achieve this with mongoDB? I have this query and I need to add some fixed fields for example ACTIVE, PT-BR, etc.

db.product.find({
    "company.cad": {
        $in: [" ", "14940"]
    },
    "tradeItemMeasurements.netContent.measurementUnitCode": {
        $ne: null
    }
}, {
    "_id": 1,
    "brandNameInformation.brandName": 1,
    "childTradeItems.gtin": 1,
    "company.cad": 1,
    "placeOfProductActivity.countryOfOrigin.countryCode": 1,
    "referencedFileInformations.uniformResourceIdentifier": 1,
    "tradeItemClassification.gpcCategoryCode": 1,
    "tradeItemDescriptionInformation": 1,
    "tradeItemMeasurements.netContent": 1,
    "tradeItemMeasurements.width.measurementUnitCode": 1
}).limit(1)

You can use MongoDB's Aggregation Pipeline
to add calculated or constant fields to a query.

The $addFields
stage appends new fields to existing result documents if you don't want to specify
a list of all fields. However, since your example projects a subset of fields you
can also add new fields
using expressions in a
$project stage.

Your find() query would become a $match stage in aggregation. Here's an example showing a few different options (with comments) in the $project stage:

db.product.aggregate([

    // The initial $match stage can take advantage of available indexes
    { $match: {
        "company.cad": {
            $in: [" ", "14940"]
        },
        "tradeItemMeasurements.netContent.measurementUnitCode": {
            $ne: null
        },
    }},

    // Project the desired result fields
    { $project: {
        // Fields that already exist
        "_id": 1,
        "brandNameInformation.brandName": 1,
        "childTradeItems.gtin": 1,
        "company.cad": 1,
        "placeOfProductActivity.countryOfOrigin.countryCode": 1,
        "referencedFileInformations.uniformResourceIdentifier": 1,
        "tradeItemClassification.gpcCategoryCode": 1,

        // constant values
        "ACTIVE": true,
        "language.code": "PT-BR",

        // calculated values (in this case, size of an array)
        "itemCount": { $size: "$tradeItems" },

        // literal values (that might otherwise be interpreted as expressions)
        "batch.batchNumber": { $literal: 1 },
    }}

])


Is it possible to implement minimally logged reads from a disk table My code uses a table that links SQL Server SPIDs with CompanyIDs.  For example

Spid|CompanyID
1   | 123
2   | 412

Every time a request reaches the database, the request first checks this table to get the current user's company ID, and if there isn't one, it inserts it.

Naturally, this is a very high traffic table.  My current implementation is using a non-durable in memory table using SQL Server 2016 in memory OLTP.  However, we have discovered a bug with memory pressure (msft currently looking at it) and need to move away from this implementation.

This table only contains ~300 records at any given time, and rarely do users WRITE to this table.  But there are hundreds of reads a second, potentially.

We're going to have to move back to a disk table for now, and I'd like to not log anything to the transaction log for reads on this table.  Is there a way to NOT log anything to the transaction log OR minimally log to the transaction log?

I looked into global temp table and also using TABLOCK and Minimally Logging keywords, but these all seem to be concerned only with high traffic WRITES.

Is there a way to accomplish what I want here?

We're going to have to move back to a disk table for now, and I'd like to not log anything to the transaction log for reads on this table.

Reads aren't logged because they don't change anything, so you already get what you want.


Can i restore mysql database to point of time when it was just installed/factory default? I have an application on Centos VM that uses MySQL DB.I took a MySQL backup from another such application installed on another VM and loaded the backup on my current VM. I did not take a backup before doing the restore. Is there any way for me to go back to the factory default DB like when i first installed it? Does DROP TABLE actually delete tables or drop data from the table?

For PIT recovery, see if log_bin was turned on.

DROP table/database/etc promptly and irrevocably removes the data and schema.  DROP cannot be in a transaction (until Version 8.0).

If you are hoping for a "recycle bin", be aware that COMMITting a transaction effectively flushes what you might think of as a recycle bin.


Trying to create PostGIS database I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation.

In the short version, i executed,

createdb yourdatabase
createlang plpgsql yourdatabase

But I did not execute the last three commands,

psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f postgis_comments.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Is my database still spatially enabled? If not, then can you tell me where these .sql files are located so I can execute those last commands, since I am getting the following error,

postgis.sql: No such file or directory

I am using Ubuntu 12.04.

You must install the PostGIS functions, data types, etc. before you can use PostGIS. This can be found in these three SQL-files. Set the correct path to the files and execute them in PostgreSQL by using psql.


mysqldump can write to /tmp and /var/tmp but nowhere else I have various mysql databases on different servers and at different versions. I'm using the same mysqldump command across Linux versions without issue except for this new Red Hat 7.6 server.

Command:

mysqldump --tab=/var/tmp/mysql/mysql.20190808/wikidb --opt --user=root --password=[password] wikidb

Prior to the mysqldump command:

mkdir -p /var/tmp/mysql/mysql.20190808/wikidb
chown -R mysql:mysql /var/tmp/mysql/mysql.20190808
chmod 777 /var/tmp/mysql/mysql.20190808

This is all scripted so no chance for a typo.

On all the older servers, and especially the older wiki servers this server is replacing, the command works successfully. I get a list of all the tables and .txt files for the database in the directory.

On the new server, I get the very first table, back_archive.sql, and then an error:

mysqldump: Got error: 1: "Can't create/write to file '/var/tmp/mysql/mysql.20190808/wikidb/back_archive.txt' (Errcode: 2)" when executing 'SELECT INTO OUTFILE'

I've tried various permissions including 1777 to give it a sticky bit just in case and it still fails. Ownership is confirmed as mysql:mysql and the database owner is mysql.

I'd done quite a bit of searching and reading of the man pages and everyone pretty much blames it on ownership or directory permissions but as noted above, I believe I've done it correctly, especially since it's a script and is working on every other server.

I will note this is my first master/master replication server. I didn't find anything specific regarding this sort of configuration but I thought I'd mention it.

[Lots of] Other things can get in the way.

SELinux can cause all sorts of problems, making really basic things fail for no apparent [or announced] reason.

ACL's in the file system can cause confusion, sneakily "tweaking" permissions without you realising it.

Is it possible that the MySQL configuration is different?
These days, a clean install of MySQL might well come with --secure-file-priv turned on.  If that doesn't match where you're trying to put the file, you'll hit another wall.

Regards,   Phill  W.


Specify order of attributes in crosstable I'm trying to create pivot tables on PostgreSQL.

I successfully created my first example. But I have on problem:

I use the two-parameter form of crosstab, which takes a query for the data and a second query for the categories.

I also have to provide a list of category names in the AS statement.

But how can I make sure that they are in the same order?

The documentation uses SELECT DISTINCT category FROM table. But that can't guarantee me any order of the categories, right?

I have three test objects/experiments X_E001-3 with three fields/categories/attributes:

MR_Type (out of: Tw2, T2w, MRx)
groundTruth (out of: head, liver,
abdomen)
origin (out of: Hamburg, Cambridge, Paris)

CREATE TABLE x_experiment_fields (
    x_experiment_id text NOT NULL,
    field text NOT NULL,
    value text NOT NULL
);

INSERT INTO x_experiment_fields VALUES ('X_E001', 'MR_Type', 'T2w');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'organ', 'liver');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'MR_Type', 'Tw2');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'organ', 'abdomen');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'origin', 'Hamburg');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'origin', 'Paris');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'origin', 'Cambridge');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'organ', 'head');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'MR_Type', 'MRx');

I then make may query as proposed:

SELECT * FROM crosstab(
    'SELECT x_experiment_id, field, value FROM x_experiment_fields ORDER BY 1',
    'SELECT DISTINCT field FROM x_experiment_fields')
AS (x_experiment_id text, MR_Type text, organ text, origin text);

which outputs:

 x_experiment_id | mr_type | organ     | origin
-----------------+---------+-----------+--------
 X_E001          | liver   | Paris     | T2w
 X_E002          | abdomen | Hamburg   | Tw2
 X_E003          | head    | Cambridge | MRx

which is wrong (or better not intended).

The reason is, that SELECT DISTINCT field FROM x_experiment_fields gives me

 field
-------
organ
origin
MR_Type

whereas I want them in order MR_Type, groundTruth, origin.

Even if I change my order, I can't be sure the query always gives the identical order, right?

I could order the query and my list. But then the order might not me as wanted (and it makes it harder to extend the attributes list later on).

Is there any common use?

Robin

Ok, I might have found an answer myself.
I learned that I can "emulate" the table generated by the second query by this:

$$VALUES ('MR_Type'), ('groundTruth'), ('origin')$$

This way I have control over the order. And if anything changes I have to add it into the AS statement nevertheless.

I don't want to eliminate the possibility to use maybe another special purpose table to control this mechanism, but for now this would be ok.


Should I consolidate my SQL instances? I'm trying to find an answer to this one with not much luck.

I have an MS DB server with our main databases on it. Over time, someone has installed SharePoint and Microsoft Online (365) services which both created SQL Express instances.

My question is, SHOULD I move the databases off the SQL Express instances on to my main one and uninstall Express - and if so, why?

SQL Express has many disadvantages. If you've already paid for a real license you should use it. You don't have to consolidate everything in one instance, but I prefer not using Express...

See http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.110%29.aspx

Max Compute Capacity
Max memory utilized
Max Database size
AND
No SQL Agent!


MSSQL Server - Mirror Between Different Versions I'm planning a SQL server upgrade and trying to get mirroring working between 2 different versions of SQL server, same edition - standard.

Is it possible to mirror from SQL Server 2008 std to SQL Server 2016 std edition? I know it wouldn't be possible the other way round.

Technically it can work, although it's not usually a good idea to do so as part of a DR strategy, as after you've failed over to the newest version, you can't easily fall-back to the older version. The only reason it is supported is for doing rolling upgrades and minimising downtime.

This MSDN article explains all about it.

Some additional thoughts not strictly in answer to your question, but that come to mind:

I can't think of a good reason for keeping a system on an old version of SQL Server when you have access to a newer version. You're not going to be able to use any of the new features of the newer version, and you could run into development problems (particularly if you ever failed over to the newer version, made changes only available in 2016 and then wanted to go back to the older version - which would be tricky to do with mirroring like that anyway, but impossible if you've used something not available in 2008, and it could cause you to end up in hot water). I suggest just upgrading your main system to the most recent version you have access to.
Bear in mind that SQL Server 2008 is coming to it's end of life, and is in the process of being put out to pasture/sent to the glue factory.
Mainstream support ended two years ago, and it'll be totally out of support in 2019 (and they're not releasing any more updates anyway).


Easy Postgresql master/slave replication with automatic failover on Linux? I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the slave as a hot standby for read-only queries. All good so far. However how do I automatically promote a slave to master when the master fails (or the slave thinks the master has failed)? I know I can write a file on the slave to make it become master but am looking for scripts et al to do that (and make sure the master stays down etc.).

Anyone know of a nice tutorial on how to do this on Ubuntu 12.04? Hosted at Hetzner (for bonus points)? Chef recipes or something?

Have a look at repmgr. It's a free software product that is meant to manage failovers and switchovers with PostgreSQL built-in replication.


How to most effectively get the first preceding/subsequent entry outside a given time interval? We have a SQL Server DB that contains a large table (>100M rows) with archive values, basically in the form of [Id], [StationId], [DatapointId], [Timestamp], [Value]. As the stations do not get polled, there aren't any regular intervals at which a station sends a datapoint, so the timestamps are set to arbitrary times.

For our web visualization, I need to extract entries from this table for a pannable chart that I'm building with D3.js. I need to query by StationId, DatapointId, StartDate and EndDate. This is simple and works as expected, but for the line chart that it feeds, I do need additional data, namely the first value before the chosen time interval, and (if there is one) the first value after the interval. This is neccessary to draw the line from the left resp. right boundary of the chart to the first/last value within the interval (otherwise the data within the chart would look somewhat "isolated" because the line stops before the charts boundaries, like there's no data before or after the values in the chosen interval).

At the moment I need to run 3 queries: one to get the data within the interval, one to retrieve the first matching row before StartDate, and anotherone to get the first matching row after EndDate.

It works, and after creating a covering index the speed is ok, but I think there might be a way to reduce the number of roundtrips to the DB. Do you have any idea?

I assume you query for all data (from all stations) in a specific time range.

You can probably write this with window functions (and it might be more efficient) but here is one method, with OUTER APPLY.

(The ds subquery is only used to get a list of distinct StationID. It should be replaced with the Stations table you likely have):

SELECT
    ds.[StationID],                   -- station
    st.ExtendedStartDate,             -- extended start and
    en.ExtendedEndDate                -- and end dates

    tt.[Timestamp],                   -- timestamps and data
    tt.[DatapointId],
    tt.[Value]
FROM
    ( SELECT DISTINCT [StationID]
      FROM ArchiveTable
    ) AS ds
  OUTER APPLY
    ( SELECT TOP (1) s.[Timestamp] AS ExtendedStartDate
      FROM ArchiveTable AS s
      WHERE s.[StationId] = ds.[StationId]
        AND s.[Timestamp] < @StartDate
      ORDER BY s.[Timestamp] DESC
    ) AS st
  OUTER APPLY
    ( SELECT TOP (1) e.[Timestamp] AS ExtendedEndDate
      FROM ArchiveTable AS e
      WHERE e.[StationId] = ds.[StationId]
        AND e.[Timestamp] > @EndDate
      ORDER BY e.[Timestamp] ASC
    ) AS en
  OUTER APPLY
    ( SELECT t.*
      FROM ArchiveTable AS t
      WHERE t.[StationId] = ds.[StationId]
        AND t.[Timestamp] >= COALESCE(st.ExtendedStartDate, @StartDate)
        AND t.[Timestamp] <= COALESCE(en.ExtendedEndDate, @EndDate)
    ) AS tt ;


Exclude rows from SELECT I have a massive table where a row is defined by two ids (Tid,Bid) that looks like this:

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  1  |  T  |   Insert |     NULL   |   50  |  10:11 |
|  1  |  T  |   Update |   Executed |   50  |  10:12 |
|  1  |  T  |   Remove |   Executed |   50  |  10:50 |
|  1  |  S  |   Insert |     NULL   |   10  |  10:10 |
|  1  |  S  |   Update |   Executed |   10  |  10:11 |
|  2  |  T  |   Insert |   Executed |   22  |  12:20 |
|  2  |  T  |   Remove |   Executed |   22  |  12:44 |
|  3  |  B  |   Insert |   Executed |   44  |  15:21 |
|  3  |  B  |   Update |   Executed |   48  |  15:25 |
------------------------------------------------------

From this table I want to select only rows with the latest time and have the status Executed and exclude all rows with the same ids if one of those rows contained the Action Remove, so the final resoult should look like this :

------------------------------------------------------
| Tid | Bid |  Action  | Status     | Value |  Time  |
|  1  |  S  |   Update |   Executed |   10  |  10:11 |
|  3  |  B  |   Update |   Executed |   48  |  15:25 |
------------------------------------------------------

Currently my solution is built with multiple nested Selects and it dosent run very fast, is there a more elegant way to solve this?

What are you using to measure the speed of your query? Without a baseline, how will we know if the way that we do it will be faster?

It could also be useful to add in maybe a snippet of your query, even if it's just to check that we are not doing the same that you are.

Creating and populating a test table with the information that you have given us, on SQL Server 2012:

if object_id('dbo.TwoIds', 'U') is not null
begin
    drop table dbo.TwoIds;
end;

create table TwoIds
(
    Tid int
    , Bid char(1)
    , [Action] varchar(25)
    , [Status] varchar(25)
    , [Value] int
    , [Time] time
);

insert TwoIds
(Tid, Bid, [Action], [Status], [Value], [Time])
select
    *
from
(
    VALUES
    (1, 'T', 'Insert', NULL, 50, '10:11')
    , (1, 'T', 'Update', 'Executed', 50, '10:12')
    , (1, 'T', 'Remove', 'Executed', 50, '10:50')
    , (1, 'S', 'Insert', NULL, 10, '10:10')
    , (1, 'S', 'Update', 'Executed', 10, '10:11')
    , (2, 'T', 'Insert', 'Executed', 22, '12:20')
    , (2, 'T', 'Remove', 'Executed', 22, '12:44')
    , (3, 'B', 'Insert', 'Executed', 44, '15:21')
    , (3, 'B', 'Update', 'Executed', 48, '15:25')
) as data(Tid, Bid, [Action], [Status], Value, [Time]);

That should give us your table named TwoIds that we can test against.

The following query is what I would have used to get the results that you want.

with CTE_LatestWithExecuted as
(
    select
        *
        , [Ordering] = ROW_NUMBER() over (partition by Tid, Bid order by [Time] asc)
    from TwoIds
),
 CTE_Order as
(
    select
        LastId = max(Ordering)
        , Tid
        , Bid
    from CTE_LatestWithExecuted as le
    where [Status] = 'Executed'
    group by
        Tid
        , Bid
)
select
    cle.*
from CTE_Order as ceo
inner join CTE_LatestWithExecuted as cle on ceo.Tid = cle.Tid and ceo.Bid = cle.Bid and ceo.LastId = cle.Ordering
where not exists
(
    select 1
    from CTE_LatestWithExecuted as ne
    where ne.Tid = cle.Tid
    and ne.Bid = cle.bid
    and ne.[Action] = 'Remove'
);

From checking the statistics and time on the query, I'm getting the following results:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 13 ms.
Table 'TwoIds'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 0 ms.

It's just one possible way that could be done but hopefully it will give you another option on aa way to do this. With testing you could see if that is performs better for you.


SQL substitution feature in Oracle 10g Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle in way that every time that it received a certain query A it executed another query B instead. No application code change, no schema change, just a simple "execute query B instead of A" type of configuration.

Not that I am planning to use that feature (I can think of some undesirable consequences), but out of curiosity does it really exist? If yes, what is that feature called?

That sounds like the DBMS_ADVANCED_REWRITE package.  Tim Hall has an excellent walk-through of using that package to point an application's queries against a different table or view.

If you merely want to change the query plan but not point the query at a different table, you can use stored outlines or SQL profiles.

For example, I have tables FOO with 1 row and BAR with 2 rows

SQL> select * from foo;

      COL1
----------
         1

SQL> select * from bar;

      COL1
----------
        66
        77

I can declare a rewrite equivalence saying that queries against FOO should instead hit BAR

begin
  sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
    'Rewrite_Foo',
    'select col1 from foo',
    'select col1 from bar',
    false,
    'TEXT_MATCH' );
end;

Now, if I set query_rewrite_integrity to trusted, queries against FOO end up hitting a completely different table.

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select * from foo;

      COL1
----------
        66
        77

That can create some rather interesting query plans where the object you're querying is nowhere to be found in the plan

SQL> select * from foo;

      COL1
----------
        66
        77

Execution Plan
----------------------------------------------------------
Plan hash value: 4224476444

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BAR  |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed


How do I find an object that doesn't seem to exist in the objects table? I have an object called cot_ntn_pi_v. I was told this was a synonym.  It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can select from it, but I can't drop it as it 'doesn't exists' and I can't create a new table with the same name as 'the name is already used by another object.'

Am I going mad or doing something really stupid?

Object types in the same namespace as a table are:

Stand-alone procedures
Stand-alone stored functions
Packages
User-defined types
Sequences
Views
Private Synonyms
Materialized Views

Therefore it is probably one of those types. If you can select from it then it rules out the first five leaving it to be either a table, view, private synonym or materialized view.

When you searched in all_objects etc. you did use upper case? For example,

select *
  from ALL_OBJECTS
 where OBJECT_NAME = 'COT_NTN_PI_V';

If you use some tool like SQL Developer or Toad you can let it describe the object for you. Highlight the name in the tool and hit Shift-F4 in Developer or F4 in Toad. Toad provides a lot of description on the object while Developer, in the Details tab, will have a row with TABLE_NAME or MVIEW_NAME in it and that'll show you what it is.

Once you know what it is then it will make it easier to know how to drop it.


Unexpected index scan instead of seek on update We have a third party application which is performing badly. An update statement is performing a scan instead of a seek, and is not suggesting a missing index. Why is this happening?

I was expecting the execution plan to suggest a missing index like

create nonclustered index [ix_FluxInbox_tasklockedby] on Flux.dbo.FluxInbox (tasklockedby) include (tasklockedrole, tasklockedon)

Below is the execution plan

The details of the index scan

As requested, the DDL of the table

CREATE TABLE [dbo].[FluxInbox](
    [id] [numeric](19, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [appStatus] [varchar](20) NULL,
    [branchCode] [int] NULL,
    [groupName] [varchar](200) NULL,
    [inboxType] [varchar](20) NULL,
    [origin] [varchar](50) NULL,
    [parentPid] [numeric](19, 0) NULL,
    [pid] [numeric](19, 0) NULL,
    [priority] [int] NULL,
    [processCreatedOn] [datetime] NULL,
    [processStatus] [varchar](20) NULL,
    [processTarget] [varchar](50) NULL,
    [tag01] [varchar](100) NULL,
    [tag02] [varchar](100) NULL,
    [tag03] [varchar](100) NULL,
    [tag04] [varchar](100) NULL,
    [tag05] [varchar](100) NULL,
    [tag06] [varchar](100) NULL,
    [tag07] [varchar](100) NULL,
    [tag08] [varchar](100) NULL,
    [tag09] [varchar](100) NULL,
    [tag10] [varchar](100) NULL,
    [taskActivatedOn] [datetime] NULL,
    [taskCommand] [varchar](20) NULL,
    [taskCompletedOn] [datetime] NULL,
    [taskCreatedBy] [varchar](50) NULL,
    [taskCreatedOn] [datetime] NULL,
    [taskDescription] [varchar](100) NULL,
    [taskScheduledTime] [datetime] NULL,
    [taskStatus] [varchar](20) NULL,
    [taskTarget] [varchar](50) NULL,
    [tid] [numeric](19, 0) NULL,
    [userName] [varchar](50) NULL,
    [channel] [varchar](50) NULL,
    [processCompletedOn] [datetime] NULL,
    [taskLockedBy] [varchar](50) NULL,
    [taskLockedRole] [varchar](100) NULL,
    [taskLockedOn] [datetime] NULL,
 CONSTRAINT [PK__FluxInbo__3213E83F349C8E92] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO

There are 9 other indexes, but none cover even one of the required fields

Since there is no index on (tasklockedby) or any index that has that column in any column position, the only option for the optimizer to find which rows need to be updated is to do a table scan (so clustered index scan in this case).

I don't know why an index suggestion was not made.

I would suggest a simple index on (tasklockedby), without any INCLUDE columns, if you want to improve the efficiency of the UPDATE statement. If you have other (select) queries that need to get more columns in the result set, then you many be better with a different one.


Merge multiple rows in table setting one column based on value in rows I have some data that looks something like this:

key B
1   true
1   false
2   false
2   false

And I want to roll it up so that if column B has two false values then the resulting value for B is false; otherwise, if there is a true and false value the value for B is true. Like this:

key B
1   true
2   false

Is there a way that this can be done with (Snowflake) SQL? I first thought of a pivot table but this doesn't quite seem to be what pivots do.

According to your text if any of them are true is must be true, otherwise is false:

select
    key,
    case
      when sum(case when B is true then 1 else 0 end) > 0 then true
      else false
    end as result
from
   t
group by
    key;

db<>fiddle here


Finding changes for table-valued function definition on SQL Server I had a Table-Valued Function in SQL Server and someone from my colleagues has altered it unexpectedly. How can I restore previous version easily? Is there any way to find the previous version?

The answer is unfortunately no. Without some form of source control or a functional back-up to go to you are kind of out of luck. As soon as you push execute on that alter statement you have changed that table/function/view/StoredProc. For future precautions you should probably have nightly back-ups make sure only the appropriate people have the user permissions required to make database changes. Also you want to make sure you have source control and are following a proper procedure for testing and deployment to make sure this does not happen in the future.


New SQL instance and migration What are the best practices of creating new SQL Server instance on SQL Server Clustering 2008 R2 and migrating a database with high transactions?

You can start from this list from Jonathan Kehayias.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/03/22/sql-server-installation-checklist.aspx

Can you elaborate on "migrating a database with high transactions"? This is very open ended question and not sure how to address this. There are lot of good practices to follow here.

Making sure data, logs, tempdb are on separate disk and following best practices for Files i.e Instant file initialization, pre-allocating sizes so that autogrow won't happen, partition alignment, having good RAID http://blogs.msdn.com/b/jimmymay/archive/tags/disk+partition+alignment/
Good index creation & maintenance procedures
Good application + sql code
Getting a head start on http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
Having decent memory size so that disk access is limited
Looking for disk latency using virtual file stats DMV
Watching for WAIT STATISTICS

There are so many other things here but it takes a while to list them all and it depends.


use two condition in a trigger I have 3 tables (shipment, Supplier, Part).

create table Shipment (
    SID int,
    PID int,
    Qty int,
    );

create table Supplier (
    SID int,
    Sname nvarchar(10),
    Status int,
    );

create table Shipment (
    PID int,
    Pname nvarchar(10),
    city nvarchar(10),
    );

I want to make this condition as a trigger to avoid the transaction.
condition: prevent transactions over 10,000 qty by a supplier who has the status equal or bellow 30.

SELECT        Shipment.Qty, Supplier.Status
FROM            Shipment INNER JOIN
                         Supplier ON Shipment.SID = Supplier.SID
WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30)

I created something like this, but it doesn't work.

ALTER TRIGGER [dbo].[qytcontrolcheck]
   ON  [dbo].[Shipment]
   AFTER  INSERT,UPDATE
   AS
BEGIN
            if exists (SELECT        Shipment.Qty, Supplier.Status
                    FROM            Shipment INNER JOIN
                         Supplier ON Shipment.SID = Supplier.SID
                WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30) )
                                        BEGIN
                    print'NOT ENOUGH STATUS TO REGISTER THIS TRANSACTION'
                    rollback transaction;
                END
END

what am I doing wrong?
thank you in advance for helping me.

Replace , inside trigger code, the table Shipment with Inserted;

CREATE TRIGGER [dbo].[qytcontrolcheck]
   ON  [dbo].[Shipment]
   AFTER  INSERT,UPDATE
   AS
BEGIN
            if exists (SELECT        Shipment.Qty, Supplier.Status
                    FROM   Inserted as Shipment
                            INNER JOIN   Supplier ON Shipment.SID = Supplier.SID
                    WHERE        (Shipment.Qty >= 10000) AND (Supplier.Status <= 30) )
                                    BEGIN
                RAISERROR('NOT ENOUGH STATUS TO REGISTER THIS TRANSACTION',16,1);
                rollback transaction;
            END
END

dbfiddle here


Consideration for a little bit heavy database Environment information

OS : Windows Server 2012 R2 ( 64bit )
RAM : 16.00GB
CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz
SQL : Windows SQL Server 2012 Standard

Brief Database Data Information

Among more than 10 tables, one of them has varbinary(max) type column
The table has more than 1m records and each column has thumbnail data that takes up about 20k

Brief Table Spec

Table name : RegisteredThumbnail
Column name : UserId, ThumbData, Created, Updated
Column type : int, varbinarymax, datetime, datetime
Used space info : row : 1,034,300 | reserved : 34,092,160 KB | data : 34,054,872 KB | index_size : 31040 KB | unused : 6248 KB

Query
SELECT * FROM RegisteredThumbnail WHERE UserId = 512315

This query takes about 6:45 minutes to fetch the expected row.
To overcome this, is indexing the only option for me?
Changing the way image data is stored by replacing the binary data to image url as a string data will help a lot?
Since this is a currently operating system, changing the column is not a good idea though.
Any ideas would be greatly appreciated.
No indexes have been configured yet.

This table should have a clustered index on UserId. If there is no more than one thumbnail per user, then the clustered index can be the primary key.

Since you say you have a currently-running system on SQLServer 2012 standard edition, online index builds are not possible. You could wait for a low-activity period to create the index, or create a new table, migrate the data into it, then switch to using the new table.

The table should ideally look like this:

CREATE TABLE RegisteredThumbnail
(
    UserId       INT            NOT NULL,
    ThumbData    VARBINARY(MAX) NOT NULL,
    Created      DATETIME2(7)   NOT NULL,
    Updated      DATETIME2(7)   NULL,

    CONSTRAINT PK_RegisteredThumbnail PRIMARY KEY CLUSTERED (UserId ASC)
)

Since you cannot recreate the table, this is the index you need to create:

CREATE INDEX IX_RegisteredThumbnail_UserId
ON RegisteredThumbnail(UserID ASC)

As you're on 2012 Standard, this will be an "offline" operation - it'll block activity on this table while the index is being built. Given it's only a million rows, it should not take too long to build, however it does have to scan the table in order to do it, so you'll be limited by how fast it can read that data.


Oracle intermittent performance problem I have some complex queries that are usually fast (< 1 sec), but occasionally slow (> 10 sec, even minutes).

How do I track this down or identify what condition is causing it?

One theory was caching - perhaps the query is fast when the blocks cached in memory.  I tried flushing both the shared pool and the buffer cache ("alter system flush buffer_cache") but the query is still fast after doing that.

Is there a way to nail down precisely what was going on during a specific execution that was bad, and which part of query execution took the longest?

As you're on 11gR2 and suitably licensed (make sure you check!), I would recommend taking a look at the SQL monitor. This gives a detailed breakdown of all SQL statements that took more ~5s to execute in the past 24 hours.

Using this, you can see the duration of each step in the execution plan, along with I/O and wait details. This will enable you to see why it's taking so long.

The quick SQL executions won't appear by default, but you can add the /*+ MONITOR */ hint to force them in.

You can get a graphical view of the plan using Enterprise Manager or SQL developer. You can also get a text version using SQL. Have a read of this oracle-base article for more details on using the SQL monitor.

It's possible that the reason for the differences is because the SQL is flipping between two execution plans. You can spot this by looking in the AWR to see this. To do this you'll need to find the sql_id for the statement(s) that are causing you issues. You can do this with:

select sql_id
from   v$sql
where  sql_text like '%your query here%'
and    sql_text not like '%not this%';

To find if there's changes, have a read of Kerry Osborne's unstable plans article and look for the "awr_plan_change" script.

If you are switching between plans, you can force it to keep the good plan using SQL profiles. Again, Kerry Osborne has a detailed article on this.

Note that it's possible that the SQL you're interested in isn't stored in the AWR, as only the worst performing statements are kept. We can force details of it to be kept by "coloring" it however, as discussed by Dion Cho.


Database Design for Cars I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim:

This should handle data like this:

2017 Ford Mustang GT

However, it turns out that the GT trim can come in two body types: Coupe and Convertible. So, the data now looks like this:

2017 Ford Mustang GT (Coupe)
2017 Ford Mustang GT (Convertible)

In order to accommodate the body type, I added a FK in the trims table that references the body_types table:

So, whenever I need to reference a specific car, I do so via trims.id, which gives me the makes.name, models.name, trims.name, trims.year and the associated body type. This second design is prone to anomalies if I ever need to update trims.name, however.

In order to combat that, I added a trim_body_types table:

Now, if I want to refer to a specific car, I do so via trim_body_types.id. I think this 3rd design is probably normalized, but will also probably cost me in JOINs.

Assume that the appropriate UNIQUE constraints are in place.

QUESTIONS

Is my 3rd design the correct normalized design?
If so, should I accept the denormalized 2nd design instead in order to save on JOINs?
Any potential pitfalls with the designs I'm considering (specifically Design 2 and Design 3)?
Or am I completely off and need to consider a different solution?

I think trims can change through the year so you need a specific date start for a trim.
Model + body types is considered a 'sub model' AFAIK, and is not determined by 'trims-to-body types'. A Toyota Corolla Sedan may have different trims than the Coupe.
Also, how about you try this with natural keys and see what happens:

Manufacturers = (Manufacturer VARCHAR(20) PRIMARY KEY);

BodyTypes = (BodyType VARCHAR(10) PRIMARY KEY);

Models = (Manufacturer VARCHAR(10) NOT NULL REFERENCES Manufacturers(Manufacturer), Model VARCHAR(10) NOT NULL, PRIMARY KEY (Manufacturer, Model);

SubModels = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL REFERENCES BodyTypes (BodyType), FOREIGN KEY (Manufacturer, Model) REFERENCES Models(Manufacturer, Model), PRIMARY KEY(Manufacturer, Model, BodyType));

Trims = (Trim VARCHAR(10) PRIMARY KEY);

SubModelTrims = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL, Trim VARCHAR(10) NOT NULL REFERENCES Trims(Trim), SubModelTrimStartDate DATE NOT NULL, PRIMARY KEY(Manufacturer, Model, BodyType, Trim, TrimStartDate), FOREIGN KEY (Manufacturer, Model, BodyType) REFERENCES SubModels (Manufacturer, Model, BodyType));

What joins do you need now?

HTH


Using SQL Server 2016 R Services over a standalone installation of Microsoft R Server I've been experimenting with SQL Server 2016 and its R Services. I also have a standalone Microsoft R Server installed on my machine.
What is a good use-case to use SQL Server R Services over a standalone Microsoft R Server, since both feature properties of an enterprise scale R platform.

Possible a slightly subjective question but sometimes it's helpful to think of these things in scenarios, for example:

A predominantly SQL Server project, run by DBAs/devs (who may or may not know some R).  A data scientist or R professional writes some R for them or they do it themselves and test on their local machines, eg using R Studio or the Microsoft R IDE.  When they are happy with the R, they plug it into their data workflow using sp_execute_external_script.  They can easily incorporate this into other stored procedures, SSIS etc.  This might be a good candidate for SQL Server 2016 with R Services.
A secure project, the data is in your secured SQL Server and you want to keep the data close to the analytics and not use the cloud.  This might be a good candidate for an on-premise installation of SQL Server 2016 with R Services.
A predominantly data science project which requires data but does not require SQL Server access.  This might be a good candidate for Microsoft R Server only.
High performance is required for a big data project.  You do not want to share the box with the traditionally memory-hungry SQL Server.  This might be a good candidate for Microsoft R Server only.

Just a few examples there.  What examples can you think of?

HTH


Schema pg_dump failed due to a Lock on a table I'm running backup restore on a schema every day and get this every now and then:

pg_dump: Error message from server: ERROR:  relation not found (OID
86157003) DETAIL:  This can be validly caused by a concurrent delete
operation on this object. pg_dump: The command was: LOCK TABLE
myschema.products IN ACCESS SHARE MODE

How can this be avoided? It seems the table was being used at the time, or someone was running something aginst the table.
As far as I understand, pg_dump could run even if users are doing something with the table but it doesn't seem to be the case.
Thanks,

From pg_dump manual:

Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail. The reason is that the pg_dump master process requests shared locks on the objects that the worker processes are going to dump later in order to make sure that nobody deletes them and makes them go away while the dump is running. If another client then requests an exclusive lock on a table, that lock will not be granted but will be queued waiting for the shared lock of the master process to be released. Consequently any other access to the table will not be granted either and will queue after the exclusive lock request. This includes the worker process trying to dump the table. Without any precautions this would be a classic deadlock situation. To detect this conflict, the pg_dump worker process requests another shared lock using the NOWAIT option. If the worker process is not granted this shared lock, somebody else must have requested an exclusive lock in the meantime and there is no way to continue with the dump, so pg_dump has no choice but to abort the dump.

So pg_dump can run when other users use database, but not when tables are being dropped.
Your error message clearly states, that the object was not found and that's what caused locking to fail. It's also hinted in that message that it probably got deleted.
To avoid in the future, verify what could cause the object to be deleted during backup (maintenance? maybe some periodically run scripts/functions delete tables?) and schedule your backup after that.


How to optimize a very slow query? I have this table structure. id is primary key:

 id  post_id  email  telegram twitter  address  refer_link  referrer

Here is show create table:

CREATE TABLE `wpkx_airdrop_claim` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) NOT NULL,
 `email` varchar(100) NOT NULL,
 `telegram` varchar(60) NOT NULL,
 `twitter` varchar(60) NOT NULL,
 `facebook` varchar(250) NOT NULL,
 `discord` varchar(60) NOT NULL,
 `address` varchar(120) NOT NULL,
 `refer_link` varchar(8) NOT NULL,
 `referrer` varchar(8) NOT NULL,
 `ip` varchar(15) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `post_id_index` (`post_id`)
)

Table is now relatively big. When I run this query (it has around 9k rows) it takes more than 30 seconds to execute:

SELECT o.email
    ,telegram
    ,twitter
    ,address
    ,IFNULL((
            SELECT email
            FROM wpkx_airdrop_claim
            WHERE o.referrer = refer_link
            ), '') AS refer_email
    ,(
        SELECT count(referrer)
        FROM wpkx_airdrop_claim
        WHERE referrer = o.refer_link
        ) AS refer_count
FROM wpkx_airdrop_claim AS o
WHERE post_id = 8037
ORDER BY refer_count DESC

This query counts number of same values in referrer column, and group it with the value of refer_link column. It gets how many referrals user have.
It is explained in this post:
https://stackoverflow.com/questions/52538612/mysql-create-view-from-columns-from-same-table

Any idea how to optimize this query to execute it faster?

Thank you.

give me an example how to do it

Get something like

SELECT t1.email
     , t1.telegram
     , t1.twitter
     , t1.address
     , COALESCE(t2.email, '') AS refer_email
     , count(t3.referrer) AS refer_count
FROM wpkx_airdrop_claim AS t1
LEFT JOIN wpkx_airdrop_claim AS t2 ON t1.referrer = t2.refer_link
LEFT JOIN wpkx_airdrop_claim AS t3 ON t3.referrer = t1.refer_link
WHERE t1.post_id = 8037
GROUP BY t1.id
/* If ONLY_FULL_GROUP_BY, replace with
GROUP BY t1.email
       , t1.telegram
       , t1.twitter
       , t1.address
       , COALESCE(t2.email, '') AS refer_email
*/
ORDER BY refer_count DESC

And create indices by: (post_id, referrer, refer_link), (referrer) and (refer_link). Then test does they are used.


MySQL: Why is the `length` (byte length) of a string in a `varchar` field shorter than the actual string? I'm sure there's a really obvious answer but:

Why is the length (byte length) of a string in a varchar field shorter than the actual string?

e.g.

CREATE TABLE `test1` (
  `testdata` varchar(4) DEFAULT NULL
) CHARSET=latin1;

insert into test1 (`testdata`) VALUES('ÂÂÂÂ');

select
    testdata,
    length(testdata),
    char_length(testdata),
    length('ÂÂÂÂ'),
    char_length('ÂÂÂÂ')
from test1

**result:**
testdata: ÂÂÂÂ
length: 4
char_length: 4
length: 8
char_length: 4

The table character set is different to your client character set. So when you type 'ÂÂÂÂ' into your client, you are referring to a different string than the result of select testdata from test1; When you ran insert into test1 (testdata) VALUES('ÂÂÂÂ'); there was an implicit conversion to the latin1 charset, which only needs 1 byte per character for the string. No such conversion happens when you type length('ÂÂÂÂ') into your client.


How to set/get a field of a nested composite type variable? For example:

create type t1 as (a int2, b text, c boolean);
create type t2 as (a t1, b boolean);
create type t3 as (a t2, b int4);

Now, how can I do the following for a variable x of type t3:

x.a.a.c := true;
y := x.a.a.c;

The first gives me an error.

There's one (awful, in my opinion) workaround... you can access only one level down at a time. Use intermediate variables.

DO LANGUAGE PLPGSQL
$$
DECLARE
    x t3 ;
    x2 t2 ;
    x1 t1 ;
BEGIN
    -- x.a.a.c := true;   -- Syntax error

    x2 := x.a ;
    x1 := x2.a ;
    x1.c := true ;
    x2.a := x1 ;
    x.a := x2 ;
END ;
$$

dbfiddle here


Very slow query despite index being used I have this table, created to hold tons of modbus measurements that comes from my devices:

CREATE TABLE public.tv_smartdevicemeasurement_modbus (
    measurement_id serial NOT NULL,
    insert_time timestamp with time zone NOT NULL,
    data jsonb NOT NULL,
    smart_device_id integer NOT NULL,
    CONSTRAINT tv_smartdevicemeasurement_modbus_pkey PRIMARY KEY (measurement_id),
    CONSTRAINT tv_smartdevicemeasur_smart_device_id_62c12ed0_fk_tv_smartd
        FOREIGN KEY (smart_device_id)
        REFERENCES public.tv_smartdevice_modbus (device_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idxgin
    ON public.tv_smartdevicemeasurement_modbus USING gin (data);

CREATE INDEX tv_smartdev_insert__0ae03a_idx
    ON public.tv_smartdevicemeasurement_modbus USING btree (insert_time);

CREATE INDEX tv_smartdev_insert__1f0611_idx
    ON public.tv_smartdevicemeasurement_modbus USING btree (insert_time DESC);

CREATE INDEX tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0
    ON public.tv_smartdevicemeasurement_modbus USING btree (smart_device_id);

Now, the table has something like 200 million rows (and it's growing every day). I need to select with a simple datetime clause like this one:

SELECT data
FROM tv_smartdevicemeasurement_modbus
WHERE (tv_smartdevicemeasurement_modbus.insert_time > '2019-08-01'
AND tv_smartdevicemeasurement_modbus.insert_time < '2019-9-25')

But this simple and without hassle query takes up 8 seconds, as reported here with EXPLAIN ANALYZE VERBOSE.

The same with the track_io_timing enabled.

How to resolve this problem? Is there an effective long term solution?

There are two indices on (insert_time) and (insert_time DESC). B-tree indices can be scanned backwards at practically the same speed. And insert_time is NOT NULL, so there is no point whatsoever. Drop one of those in any case.

I made some assumptions where info is missing:

Current Postgres 12.
You are free to redesign the table and lock the table exclusively for some time.
The table is basically "append only".
New rows are added in order of insert_time.
The current table is physically out of order and/or bloated (leading to the index scan in your query plan instead of bitmap index scan)

I would rewrite the table like this:

BEGIN;
-- SET maintenance_work_mem = ???   -- set as high as you can afford temporarily

-- drop idx first to free space
DROP INDEX public.idxgin;     -- acquires exclusive lock on the table
DROP INDEX public.tv_smartdev_insert__0ae03a_idx;
DROP INDEX public.tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0;
-- DROP INDEX public.tv_smartdev_insert__1f0611_idx; -- might help SELECT, drop later

ALTER TABLE public.tv_smartdevicemeasurement_modbus ALTER measurement_id DROP DEFAULT;
DROP SEQUENCE public.tv_smartdevicemeasurement_modbus_measurement_id_seq;  -- drop owned sequence
ALTER TABLE public.tv_smartdevicemeasurement_modbus
         RENAME TO tv_smartdevicemeasurement_modbus_old;    -- free org. name

CREATE TABLE public.tv_smartdevicemeasurement_modbus (
   measurement_id   serial PRIMARY KEY    -- consider IDENTITY column instead, see below
 , smart_device_id  integer NOT NULL      -- reordering saves 4-8 bytes alignment padding per row
 , insert_time      timestamp with time zone NOT NULL
 , data             jsonb NOT NULL
 , CONSTRAINT tv_smartdevicemeasur_smart_device_id_62c12ed0_fk_tv_smartd
      FOREIGN KEY (smart_device_id)
      REFERENCES public.tv_smartdevice_modbus (device_id) DEFERRABLE INITIALLY DEFERRED  -- WHY deferrable / deferred? see below
);

INSERT INTO public.tv_smartdevicemeasurement_modbus
      (measurement_id, smart_device_id, insert_time, data)
SELECT measurement_id, smart_device_id, insert_time, data
FROM   public.tv_smartdevicemeasurement_modbus_old
ORDER  BY insert_time DESC;   -- CLUSTER while rewriting

DROP TABLE tv_smartdevicemeasurement_modbus_old;

CREATE INDEX tv_smartdev_insert__1f0611_idx ON public.tv_smartdevicemeasurement_modbus (insert_time DESC);
-- CREATE INDEX tv_smartdev_insert__0ae03a_idx ON public.tv_smartdevicemeasurement_modbus (insert_time);  -- nope!

CREATE INDEX idxgin ON public.tv_smartdevicemeasurement_modbus USING gin (data);  -- ?
CREATE INDEX tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0 ON public.tv_smartdevicemeasurement_modbus (smart_device_id); -- ?

COMMIT;

VACUUM ANALYZE public.tv_smartdevicemeasurement_modbus;

This rewrites the table saving some space (which also helps performance). Most importantly, it clusters the table according to your main index - and removes all possible bloat while being at it. This should help locality of data and make Postgres read fewer data pages. Unless your data column is big, you should see bitmap index scan in the query plan now. And if data is small, consider a covering index  to get index-only scans. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

How does the changed column order save space?

Calculating and saving space in PostgreSQL

DEFERRABLE INITIALLY DEFERRED? This is rarely necessary. And cheaper without. See:

SET CONSTRAINTS ALL DEFERRED not working as expected

serial vs. IDENTITY? See:

How to change a table ID from serial to identity?

Then query like this:

SELECT data
FROM   tv_smartdevicemeasurement_modbus
WHERE  insert_time >= '2019-08-01'  -- included
AND    insert_time <  '2019-09-25'; -- excluded

Be aware that these date literals are interpreted according to your local time zone setting. Consider true timestamptz input to be unambiguous. See:

Ignoring time zones altogether in Rails and PostgreSQL

These two indices are orthogonal to the query at hand: idxgin and tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0. Drop unless needed for unrelated stuff.


If date & time are identical and work as a unique identifier in both tables:

SELECT
    t1.date,
    t1.time,
    COALESCE(t1.windspeed,t2.windspeed) as windspeed,
    COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

This is a query for create table:

CREATE TABLE tblName AS

SELECT
      t1.date,
      t1.time,
      COALESCE(t1.windspeed,t2.windspeed) as windspeed,
      COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

To work properly (fast), create combined indexes on both tables on (date, time).


Sql server update query locking stages? Looking at

update MyTable
set Status = 1
where Status = 0

of course- the where is calculated before the actual update.

but does this filtering (where Status = 0) is also inside the lock ?

I mean where is the lock ?

here : update ...

or here :  filter and update...

There are two stages to the lock of updated data.  The first is an update lock (U) and the second, provided there is data that needs to be modified, is an exclusive lock (X).  It's a two stage operation in the sense that there is data that needs to be searched in order to determine what/if data needs to be modified.  The update lock will exist for that (or attempt to be attained for the duration of the operation).  Then when data needs to be modified, the update lock will convert into an exclusive lock.

The reason behind this locking mechanism is to prevent deadlocking from the updating data scenario.  Please see Kalen Delaney's post on more specifics surrounding this.

Take the below for example:

Execute this for connection 1...

-- connection 1, leaves an open tran to prevent connection 2 from progressing

use AdventureWorks2012;
go

begin tran

    update HumanResources.Department
    set name = 'This is a new name'
    where DepartmentID = 11;

--commit tran

Then in another session, attempt the following update:

use AdventureWorks2012;
go

update HumanResources.Department
set name = 'This is a conflicting tran'
where DepartmentID = 11;

What we have here is an update lock that is waiting because there is already an exclusive lock on the resource that the update lock is trying to get (an update lock is not compatible with an exclusive lock).  The above scenario is used to "halt" the update lock conversion to exclusive lock.

You can see this by looking at the sys.dm_tran_locks DMV:

use AdventureWorks2012;
go

select
    resource_type,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_status
from sys.dm_tran_locks
where resource_database_id = db_id();

You should have similar results for the output of the above query:


Get a list of Databases which were not modified with in a date range - SQL server can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

I have tried below is the query:
--identify when a user database was last accessed--

SELECT DatabaseName
      ,MAX(LastAccessDate) DatabaseLastAccessedOn
FROM    (
           SELECT  DB_NAME(database_id) DatabaseName
                 ,last_user_seek
                 ,last_user_scan
                 ,last_user_lookup
                 ,last_user_update
                   FROM sys.dm_db_index_usage_stats
       ) AS Pvt
       UNPIVOT
        (
           LastAccessDate FOR last_user_access IN  (
               last_user_seek
              ,last_user_scan
              ,last_user_lookup
              ,last_user_update
           )
       ) AS Unpvt
GROUP BY
       DatabaseName
HAVING DatabaseName NOT IN ('master' ,'tempdb' ,'model' ,'msdb')
ORDER BY
       2

Need a query to find DBs which were not accessed/modified for the last 2 months and delete them. i have SQL express edition.

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

Regardless of what you may find on the internet, the only way to be 100% sure is to run an audit/trace/etc. against the servers and inspect the traffic that each database is receiving as some system processes will update the databases and will need to be filtered out before continuing (which the audit/trace/etc. should be able to filter itself).

Possible Options:

SQL Audit
Server Side Trace
Extended Events

If you don't need to be absolutely 100% sure, then feel free to choose any of the other methods on the internet such as the index usage DMVs, pages in the buffer pool, last accessed date, etc.


Rescue truncated data from incorrect column type I have mistakenly inserted a variable 18-20 digit variable into an unsigned INT() column.

The data has been truncated to the max 32bit length so all my data is displayed as "4294967295"

Is there any way to retrieve the original data?

The basic answer is: No.

Do you have a record of the 18-20 digit value somewhere?  In another location in your database, on a piece of paper on you desk, etc?  If so then use the source information to correct your error.

You have not indicated which database software you are using, which can make a difference for many issues.


How to Implement a Retry Feature in SSIS 2012 When a Database Connection Fails? We have an SSIS 2012 package that supports our internal analytics reporting database by extracting data from an external database server.

Last night, for the first time, the package failed due to a database connection timeout error when attempting to connect to the external database.

It seems to me that it would make sense to enhance our SSIS package with some means of making multiple attempts to connect before failing completely.

For example, we would want the SSIS package to try 3 times, perhaps sleeping for 5 minutes before attempting a retry.  If there are 3 failures to connect, then return an error for the package.

I've searched for references on this, but I am not finding anything; it seems that it should be an obvious feature, and I must not be looking in the right places.

Here are some details:

Using SSIS 2012 packages in a local SQL Server 2012 server.
Extracting data from a remote Oracle database
Using Microsoft Connector for Oracle by Attunity
The SSIS package is called from a Scheduled Job using SQL Server Agent
The SSIS package has very simple components that consist of a truncate table step followed by a Data Flow task.
The Data Flow task uses the Oracle Data Source component connected to an OLE DB Destination (our local SQL server instance/database).
There is 1 intermediary Derived Column component, that adds some calculated data to the destination.
The package has successfully run many times in our production environment, so I already know it's not due to development/visual studio vs. SSIS catalog in SQL Server, etc.

I have looked at the Connection Manager for my external Oracle database, and I've looked at the Oracle Data Source component, but I don't see anything that looks like it would support a retry.

Should I be creating some type of retry loop using the on-error functionality?

It seems a bit like overkill, perhaps there is some property within the Oracle Source Component that I need to put in the configuration somewhere?

There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally but I am sure there is a blog that talks about it. This one might help. http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/


Standard Time to Military Time I am somewhat new to SQL and I have seemingly simple query but I can't seem to figure out the correct combination of functions. We receive time data from an external source as a five-character string, for example "0500p". I want to convert this string into military time. The format required for output is a five character string, for example "17:00".
I have tried

SELECT CONVERT(CHAR(5),STD_TM,108)

but I think it's not recognizing my "time" as a time since it just outputs the same thing I put in.

You shouldn't care about whether it's 17:00 or 5:00 PM. How SQL Server ends up storing the time is irrelevant (formatting is for the presentation tier) - you should be focusing on converting the data to the right time value and storing it as the right data type (in this case probably datetime, but in modern versions of SQL Server, just time).

DECLARE @s char(5) = '0500p', @x datetime;

-- first convert it to a datetime-friendly format
SELECT @x = STUFF(@s,3,0,':') + 'm';

-- now some options for output:
SELECT @x, CONVERT(CHAR(5), @x, 108);

Results:

1900-01-01 17:00:00.000        17:00

Be sure that you consider formatting the value as a string only in the presentation tier. It's not really SQL Server's job to handle formatting for display, and your client tools are better suited for that translation anyway.


Oracle 10gR2 - fix for SSL version following Poodle attack We use Oracle utl_http to send https requests from within our database (10gR2).
As SSLv3.0 is now considered insecure (POODLE) we need to upgrade to TLS.
How can we ensure TLS usage when sending requests from PL/SQL?
Is a patch to the DB the only solution or is there another way?
Thanks
Terry

I'd expect you to be able to set the SSL_VERSIONS to 3.1 (TLSv1) and successfully avoid the POODLE vulnerability.  https://docs.oracle.com/cd/B19306_01/network.102/b14268/asossl.htm#i1023429


Finding the Min value for a subgroup, filtering out preceding Min values within the same parent group How do I get the minimum value for a group (Acc, TranType), but filtering out any minimum values used in preceding rows for the Acc group. Preceding rows would be defined as Acc asc, TranType asc.

The PosCancelID should only appear once per Acc group. But the same PosCancelID could appear within another Acc Group within the data set.

So with the given data set:

Acc  | TranType | PosCancelID
100     1               2
808     1               5
808     1               4
808     2               5
808     2               4<--To be filtered from min calc as it min for (808,1)
813     2               3
813     4               3<--To be filtered from min calc as it min for (813,2)
809     1               3
809     1               4
809     2               3<--To be filtered from min calc as (809,1) uses it
809     2               4
809     3               4<--To be filtered from min calc as (809,2) uses it

I should get:

Acc  | TranType | PosCancelID
100     1               2
808     1               4
808     2               5
813     2               3
809     1               3
809     2               4

SELECT ACC, TranType, min(maxPreceeding) as ActualCancelID
FROM
(
   SELECT ACC, TranType,
          MAX(m.posCancelID) OVER (PARTITION BY m.ACC
                                  ORDER BY m.TranType, m.posCancelID
                                  ROWS UNBOUNDED PRECEDING) as maxPreceeding
    FROM MCancel as m
) AS x
GROUP BY ACC, TranType

The above query gives me almost what i want but isn't filtering for acc = 813. So I know there must be a better (actually applying a filter to remove previous minimum values) way.

Quite a hard problem. Here is a recursive solution:

WITH
  rcte AS
  ( SELECT TOP (1)
        Acc, TranType, posCancelID,
        CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
    FROM
        MCancel
    ORDER BY
        Acc, TranType, posCancelID

    UNION ALL

    SELECT
        Acc, TranType, posCancelID,
        CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
    FROM
      ( SELECT
            m.*,
            r.IDs,
            ROW_NUMBER() OVER (ORDER BY m.Acc, m. TranType, m.PosCancelID) AS rn
        FROM
            rcte AS r
            JOIN MCancel AS m
                ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
                OR  (m.Acc > r.Acc)
        WHERE
            r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
      ) AS mc
    WHERE
        rn = 1
  )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

The solution assumes that a posCancelID should not appear twice in the result set. If the requirements are that they should not appear twice  in the same Acc group, then the solution needs a slight adjustment:

WITH rcte AS
  ( SELECT
      Acc, TranType, posCancelID,
      CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
    FROM
      ( SELECT
          Acc, TranType, posCancelID,
          ROW_NUMBER() OVER (PARTITION BY Acc ORDER BY TranType, PosCancelID) AS rnk
        FROM MCancel
      ) AS f
    WHERE rnk = 1

    UNION ALL

    SELECT
      Acc, TranType, posCancelID,
      CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
    FROM
      ( SELECT
          m.*, r.IDs,
          ROW_NUMBER() OVER (PARTITION BY m.Acc
                             ORDER BY m.TranType, m.PosCancelID) AS rn
        FROM
          rcte AS r
          JOIN MCancel AS m
              ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
        WHERE
          r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
      ) AS mc
    WHERE rn = 1
  )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

Both are tested at dbfiddle.uk


Insert but Don't Commit MySql I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id column has skipped a number.

So I was wondering what happens when you insert and forget to commit is this bad for the Database? Or does is just skip because when you insert it assigned an id to it but didn't save it?

Thanks

A skipped id can happen in several situation.  Keep in mind that an INSERT generally starts by allocating all the ids that it may need.  Here's a few examples where AUTO_INCREMENT ids are 'burned':

BEGIN; INSERT; ROLLBACK;

INSERT IGNORE -- and some of them are ignored due to dup key

REPLACE -- DELETE, then INSERT

The one I dislike is:

SET autocommit = 0;
INSERT
forget to ever do the COMMIT

For that reason, I avoid autocommit=0.

In a multi-master (including Galera) setup, with auto_increment_increment > 1, the INSERTs on one master skip values.


Strange PostgreSQL behaviour on service start Centos 8.3, postgresql 12 (i tried even 10, same thing).
I have set
the permission of the data directory to postgres:postgres. If I start
the service from systemctl, it gives me the error message

"creation of lock file "postmaster.pid" failed: Permission denied".

If I copy the command from postgresql.service which is postmaster -D /data/pgdata and I run from the console as postgres user, it works, and the postmaster.pidfile is correctly created.
I have no ideas, because it's not a permission problem...
What am I missing?

Ok, i understood.
It was a "problem" of selinux.
I changed the data directory, and selinux "didn't know" it, so i
chcon -R -t postgresql_db_t /data/pgdata
restorecon -RFv /data/pgdata.


Find a string in a SQL Server database I have an application which has logged an error like

Object <obj id> generated an exception.

The application store all its information in a SQL Server database which contains 100+ tables.

For sure somewhere there must be a table which contains all the objects of this type and I'm sure obj id is the key column there.

How can I find this object? Do I have to create 100+ SELECT queries to query each table or maybe there is a more convenient solution?

If I had to guess it's the object id for sys.objects. So

SELECT * FROM sys.objects WHERE object_id = <obj_id>

Specifically look at the name column (object name) and the type column (type of the object, for example U for user table, P for stored procedure etc).


How do I change the DEFINER of a VIEW in Mysql? When I run mysqldump, I get an error:

mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES

This makes sense because foobar is a legacy machine that no longer exists.

How do I change the definer of all my tables to 'root'@'localhost'?

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql


Postgres not returning data on array_agg query as below The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table books
--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Now when I do query like below:
SELECT library.name, array_agg(b.title) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id

The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)
Required Output:
----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

I've even tried coalesce as below:
SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id

Postgres version: 12

A LEFT JOIN can solve it, like Laurenz provided.
But I suggest an ARRAY constructor in a LATERAL subquery instead:
SELECT l.name, b.book_list
FROM   library l
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT title
      FROM   books
      WHERE  library = l.id
      )
   ) b(book_list)
WHERE  l.id = :library_no;

This way, you don't need to aggregate in the outer query level and don't need to GROUP BY there.
You also don't need COALESCE, since the ARRAY constructor over an empty result already produces an empty array ({}).
And it should be faster for a small selection in library - obviously the query gets the result for a single given library.
Aside, you only need the variable :library_no in a single place like demonstrated.
About LATERAL joins:

What is the difference between LATERAL and a subquery in PostgreSQL?

About the ARRAY constructor:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Basic about joining tables in the manual.


Database structure for a system with multisite The system I'm working is structured as below. Given that I'm planning to use Joomla as the base.

a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for reservation.

x(www.x.com),y(www.y.com),z(www.z.com) are hotels where booking are made by users.

www.a.com's user can only search for the booking which are in
www.x.com
www.b.com's user can only search for the booking which are in
www.x.com,www.y.com
www.c.com's user can search for all the booking which are in
www.x.com, www.y.com, www.z.com

All a,b,c,x,y,z runs the same system. But they should have separate domains. So according to my finding and research architecture should be as above where an API integrate all database calls.

Given that only 6 instance are shown here(a,b,c,x,y,z). There can be up to 100 with different search combinations.

My problems,

Should I maintain a single database for the whole system ? If so how can I unplug one instance if required(EG : removing www.a.com from the system or removing www.z.com from the system) ? Since I'm using mysql will it not be cumbersome for the system due to the number of records?

If I maintain separate database for each instance how can I do the search? How can I integrate required records into one and do the search?

Is there a different database approach to be used rather than mentioned above ?

I would recommend keeping everything in one database. It will be easier to manage and administer. If you need to add more domains/clients, you can continue to use the existing instance of the database (no need for new installations). The records that are specific to certain domains/clients will need some kind of indicator. It could be as simple as an client_id field. Installed instances of the application should have their client_id (or multiple values, if they're allowed to search more than one dataset) in their configuration so that every query where it matters contains the proper client_id.

A possible alternative to having applications configured so the know what to query would be to move that configuration into the database, maybe in a table like this:

portals_hotels
--------------
   portal_id
   client_id

sample data:

portal_id   | client_id
------------|----------
www.x.com   | www.a.com
www.y.com   | www.b.com
www.y.com   | www.a.com


I really can't get rid of these conditional statements in mysqldump? My mysqldump DDL file is filled with tons of this:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`...

I've searched to no avail for an option to silence this.

My understanding is that this kind of output is necessary so that the script can successfully recreate the schema in what may be another version of mysql server. For my scenario with a closed system of installations, I know that the version will be the same as the source of the dump.

Again, I need to be able to make edits (manual or scripted) in the dump, and these outputs make the task quite a bit more daunting.

Elsewhere at SO, I saw a couple of questions on this, but folks have concentrated on preaching about why the tokens should be left alone. Kinder people recommended parsing the output on the command line to filter them out. Note that when I dump with phpMyAdmin, these tokens are not there.

Yak!

So, there really isn't a neater option to skip this kind of output? I work on Windows and with PowerShell.

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this:

sed -r  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g

It will convert lines such as

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to

SET SQL_MODE=@OLD_SQL_MODE ;

Because this line must run on any MySQL >= 4.1.1

Note that this will not remove multi-line conditional-execution comments, such as when dumping a trigger.

Since it's impossible to predict the future, it's better to store the dump with the comments on, and only remove them when you want to visualize it.

mysqldump ... > dump.sql
cat dump.sql | sed -E  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g > dump.no-comments.sql


How do I open a MySQL Database Dump? I work for a doctor's office, and we are in the process of switching EMR systems.  The previous system has provided a physical 'dump' of the database.  I however, don't know how to open this database and view the contents therein.  The file is 1.2 gb with less then 1000 lines of SQL commands so I assume that all of the data is there.  Here's what I've tried thus far:

Opened in a text reader:  All it shows is a bunch of SQL commands to create the database tables
Installed MySQL Workbench and tried to create a "EER Model From SQL Script": This just creates the database structure.
Using MySQL Workbench/Setup a 'localhost' server: Tried a 'import' of the .sql file onto the newly created 'server' but it doesn't work and spits out an error

What are some steps to properly opening and obtaining the data found within a MySQL data dump?

I'm VERY new to the whole database management so if I've missed any details feel free to prompt.

Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler.

Make sure you're logged into the mysql client:

mysql -u <username> -p

The first step is to create a database in mysql that you want the data in:

CREATE DATABASE <database name>;

Then you need to open--or use--that new database, which is empty:

USE <database name>;

All that's left is to do the import:

\. /path/to/file (or if in Windows, \. C:\PATH\TO\FILE)

If all goes well, you'll see a bunch of output about "Query successful, N rows affected" and so on.


Multiple concurrent connections to same large SQLite file on a network share I have a 7GB SQLite file and I am running multiple queries on it from different virtual machines in the cloud. Some of the queries are running in Python using the built in sqlite3 package, and others originate from SQL queries running in DB Visualizer. The file itself is on a network share (and for policy reasons cannot be copied locally).

Will these concurrent queries slow each other down?

I could copy the SQLite file multiple times on the network share and connect each concurrent query to its own copy of the file. Would that speed things up?

I can imagine that both the sqlite3 Python driver and DBVisualizer copy the database file into local temporary storage first (or into memory) in which case there is no speed-up to be gained, or they don't.

Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and DBVisualized, but expect both smb, your os and any middleware to cache automatically at least parts of the file, because unless requested so, it is done automatically to speed up filesystem access. Details would depend on os configuration and access details.

I would like to comment on something you didn't ask about, which is the fit of your architecture:

You need to access a database over the network
You need to have certain level of concurrency
You need to control access, because policy reasons (and probably some of those policy reasons could be backups)
You have expectations about performance
You need to control in a more fine-grained way what is on memory and what is on disk
Your database has grown beyond a trivial size

(and those are only the ones that are perceived from your question)

SQLite is a great piece of software, but please consider if it is the right technology for you, as this is almost a paradigmatic case of when not to use it. Sure, you can create a loop device and copy it in memory, and you may get some improvements, but you could also migrate to a distributed system with an integrated cache system (like any other multi-user relational database) and let the technology solve all this issues for you.

Accessing sqlite using a network share is really a hack, and network filesystems are known to have caused data loss due to its limitations towards write consistency.