texts: Authoritative source that  and != are identical in performance in SQL Server

texts:
Consider this answer on SO that reassures the asker about the <> operator that:

<> is ... the same as !=.

But then a commenter pipes up and says:

It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very different. =/!= are simply evaluated as true/false whereas <> means the engine has to look and see if the value is greater than or less than, meaning more performance overhead. Just something to consider when writing queries that may be expensive.

I am confident this is false, but in order to address potential skeptics, I wonder if anyone can provide an authoritative or canonical source to prove that these operators are not just functionally the same, but identical in all aspects?


texts: How to manage primary key value while multiple secondary server sync with primary server?

texts:
we have three secondary database server in different cities and one primary server.
my question is i want asynchronous transaction commit to all three database but  primary key data conflict occur when commit to primary database...because same id can generate on all three server.
then which technology is available for this kind of scenario for maintain primary key conflict issue...

Note-all three secondary server work as a 'replication publisher' and primary server work as a subscriber(primary replica of alwayson).

solution from my side- i commit the records on secondary server and i use some tricks on all secondary server like..server 1 (seed value = 1 increment by = 3),server 2 (seed value = 2 increment by = 3), server 3 (seed value = 3 increment by = 3)...according to this setting my primary key value never conflict....so please advice am i going right way or not ???


texts: How to convert memory optimized table into disk based table in sql server

texts:
Could anyone please explain the process to convert memory optimized table into disk based table in sql server?

Thanks & Regards,
Phanindhra S


texts: Is it possible to replicate different Galera MAIN-MAIN clusters (MariaDB) with another Galera MAIN-MAIN cluster but as MAIN-SECONDARY asynchronously?

texts:
We are a small team of developers and we usually deal with single database access locally or remotely. We are working on a project that needs to have not only replication on the local network (we'll implement either a load balancer or a custom fallback for DB access) to try to achieve an SLA of 100% but also remote replication (on the cloud) for backup purposes.
I've been playing a little bit with Galera and managed to run a cluster locally (using some VMs) which run synchronously (emulating different nodes in a local network) and another cluster inside Azure. Everything works perfectly but we really don't know if we can connect asynchronously the nodes outside Azure (as slaves) to the cluster in Azure.
TL;DR: Here's what we have in mind in a graphic :)

Thanks in advance!


texts:
In a trigger we may refer to inserted/updated values use NEW alias as a table. But it is NOT a table.

I insert some data into some table, and I need to update some another table during this insert. There is a lof of fields in both tables which matches to each other absolutely (names, datatypes, sizes and so on). So the simplest trigger text (for example, for insert) would be

CREATE TRIGGER tr
AFTER INSERT
ON table1
FOR EACH ROW
    UPDATE table2 NATURAL JOIN NEW
    SET table2.field = SOMEFUNC(NEW.somefields);

However, NEW is not actually a table, and I expect to get an error message when the trigger is fired:

  Table 'DB_name.new' doesn't exist

Question: is there a way or trick to use NEW as a table for to use NATURAL JOIN strongly (not ON, WHERE or USING)?

Assume that the goal is: if the list of matched fields altered (synchronously in both tables) I do not need to edit the trigger.


texts: update table column if provided text is not matched

texts:
I want to update a huge table, I can do the below in PHP but it will take longer time I believe.

$string = "test string";
if($string!="Amazon.com"){
//set table column value as FBA
}

if($string=="Amazon.com"){
//set table column value as AMZ
}

Can I do this via MYSQL query ?

thanks


texts:
I am new to firebird and confused with SuperServer SuperClassic thing.

But what i wonder is how should i set up this scenario on Linux?

Single shared storage that store database file on SAN
4 firebird VPSes connect to the shared storage via NFS
a load balancer vps ( any suggestion?)

My system will be 50:50 read : write with transaction. What kind of server should i choose? SC, SS or mix?

Thank you!


texts: Oracle 11g database structural change doesn't autobackup controlfile/spfile

texts:
I have configured controlfile autobackup:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/%F';

Then added a test tablesapce into the database:

create tablespace test datafile '+DATA01' size 10m;

And there is no controlfile autobackup, and also no entry in alert log file about backing it up.

Why?


texts: Purge mysql log bins older than two weeks automatically

texts:
I have to manually write

purge binary logs bin to "mysql-bin.somenumbers" ;

Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary logs bins automatically every two weeks? probably calling a bash script in crontab?

I have MySQL 5.5 running on CentOS 6.5


texts:
I'm using a tsquery field in order to pre-compute batch searches, but I'm encountering this error while updating a tsquery field on a table:

ERROR:  row is too big: size 10704, maximum size 8160

The error is given when I'm updating the field with the resulting tsquery of a long string (2439 ASCII chars, with no multibyte char). My guess is that the tsquery it's a binary format and its weight is higher than the original string, maybe because of additional information stored by the format.

There seems to be no limit imposed by the to_tsquery() function itself, but the limit is imposed for the field size, though,

Here is the field definition:

ALTER TABLE public.tpl ADD content_ts_query TSQUERY NULL;

Is it possible to change this setting? If not, how long can be the string so it can fit it inside the field?

Pg version: 9.3


texts: T-SQL - How to split (char separated) string into rows and columns

texts:
What is the best way to split a char separated string into rows and columns?

Here is my input string:

!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5

And here is my desired output table:

Id  Value  Count
1   100    10
1   200    0
1   500    2
1   1000   30
2   100    3
2   500    1
2   2000   5

Best Regards,


texts: SQL Server 2014 Triggers and Merge Replication

texts:
I am trying to have a trigger fire when the Merge agent\user insert\delete\update data.
The trigger inserts data into a log table.
I don't want to use NFR (Not For Replication) because I need that the trigger will fire when merge agent inserts data.

At first, I thought it was the problem with the replication but when I change the triggers into NFR everything was working fine.
(which is not good because I need them to fire when the merge agent inserts data as well).

Then I thought it was a problem with the triggers but when I insert\update\delete data using t-sql the triggers are working fine.

The problem is that the data is not synchronized as it should be in merge replication. When I update a column, I can't see it on the other database unless I'm changing the trigger into Not For Replication or disabling them.

Any ideas?


texts: What is the Difference between Archiving and Purging in Oracle database?

texts: I would like to know the main difference between Archiving and Purging in any database we use.


texts: Trying to understand the use of a '!' character

texts:
I stumbled upon this query that someone wrote and was curious as to what '!' was being used for here.

SELECT ACC9.JPM_CAT_ITEM_ID,                          !a.accomplishment
ACCE9.JPM_DESCR90,                             !b.descr
ACC9.JPM_DATE_6,                               !a.dt_issued
TO_CHAR(ACC9.JPM_DATE_6,'YYYYMMDD') &ISSUE_DT_STD,
ACC9.jpm_date_3,                               !Expire dt
C9.XLATLONGNAME
from PS_JPM_PROFILE A9,
PS_JPM_JP_ITEMS ACC9,
PSXLATITEM c9,
PS_JPM_CAT_ITEMS ACCE9
  WHERE A9.JPM_PROFILE_ID = ACC9.JPM_PROFILE_ID;


texts: selecting from information_schema increases free disk space

texts:
Some background: There are 3 database servers - two in a master/master replication config with only one server accepting writes.  And one other slave replica replicating from the write-master.  The slave-master had low disk space recently (to the point where it ran entirely out of space), so I added an additional disk and moved only the datadir to the new disk.  Prior to doing this, the slave master was accepting writes.  But I moved the writes over to the other master before moving data.

The strange thing is that the datadir of the slave-master is approximately 175GB larger than the write-master.  That is until I run the query:

select table_schema,table_name,data_length from tables order by data_length desc limit 10;

As this query is running the OS reports that the amount of free space is increasing.  And ends up around 120GB extra.  This happens in the space of a minute.  Then immediately after the query finishes the free disk space begins to decrease again.

This is with MySQL v5.6.  The majority of tables are MyISAM with a minority of InnoDB tables.  A show slave status on the slave-master reports replication continues as normal.  The logs dir, which remains on the original disk, does not fluctuate free space wildly like this.

Has any seen this behaviour before?  This seems somewhat sinister to me, but I'm by no means an expert.  Running the same query on the current write-master doesn't produce this behaviour.


texts:
I run the following code, received a syntax error.

SQL Server 2012 Express (version 11)
  OS: Windows 7 home premium (x64 bit)

The server complains that I have a syntax error near ROWTERMINATOR='\n',
I couldn't figure what is the cause for the issue.
I don't understand how to quotation mark works in bulk insert weblog from '''+@datapath+'access1.log''

My understanding is I should use double quotes to represent a single quote.

This is my code:

declare @datapath varchar(128);
set @datapath =
    'C:\Users\admin121\sql-server-2014\sql2012_class3_20141028\WebLog\';

exec
('
  bulk insert weblog from '''+@datapath+'access1.log''
    with
    (
      firstrow = 1,
      FIELDTERMINATOR ='  ',
      ROWTERMINATOR='\n',
      MAXERRORS=99999999

    )
');


texts: Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

texts:
I have a function which returns:

TABLE (id TEXT, collected TIMESTAMPTZ)

In another function, I would like to be able to pass the result of that first function as a parameter. For example:

CREATE FUNCTION my_func(devices TABLE(id TEXT, collected TIMESTAMPTZ)) ....

That syntax gives me an error:

ERROR: syntax error at or near "TABLE"

Is there some way to accomplish this in PostgreSQL?


texts: Different behaviour between oracle and postgres regarding alter table primary key command

texts:
In Oracle 11g, when you write

alter table abc add constraint abc_pk primary key (one, two, three);

Automatically an index for one, two, three is added.

In PostgreSQL 9.5, when you do the same, no index is added.

Is there anything I can add to the command and have the same behaviour as with Oracle?


texts: Why is TOP operation in SQL execution plan

texts:
After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there.

When running the query below on two similarly set up SQL servers, we encounter different execution plans which affects performance and we need help figuring out the cause.

The query:

SELECT process_id
INTO #temp
FROM revrep_revenue_fact
WHERE process_id = 284
DROP TABLE #temp

Execution plan of server A

Execution plan of server B
Server B http://s2.postimg.org/z9fjrfv4n/server_B.png

You'll notice that server B has the TOP physical operation in the actual execution plan and we're trying to figure out why.  Both queries use the same index in the Index Seek.

Here are some details of server A and server B

Server A and B are both

Windows Server 2008 R2 Standard Service Pack 1

24GB RAM

64-bit operating system

SQL Server 2012 Versions obtained by using (SELECT SERVERPROPERTY('ProductVersion'))

Server A SQL version 11.0.3000.0

Server B SQL version 11.0.5058.0

What we've tried

Clearing procedure caches
Rebuilding indexes
Refreshing statistics
SET ROWCOUNT 0

Why does server B have the TOP in the execution plan?  In this simple query example there is no real issues but in a larger query the cost rises for the TOP and we see a performance hit.  Any help debugging this would be much appreciated and we can get you any additional information you may need to help.


texts: Advisable number of foreign keys in a single table

texts:
I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table?

At present, few of my tables are using 4 foreign keys. Would like to know, if number of foreign keys in a single table causes some performance or scalability issues?

I am using MySql


texts: Column Store Index over an clustered index (created when primary key is declared) - SQL Server

texts:
When a clustered index is created, the table itself becomes the index structure sorted by the index key.

Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and we create a column store index over table XPTO with columns (b,c).

What is the structure of this index? Is a different structure comparing to the clustered table? Or does the column store have pointers to the clustered table (like an other non clustered index).

Finally, the same scenario but creating the column index with all attributes, what is the structure?


texts: How can i limit the number of rows a user can update?

texts:
One of our Business Research Analysts has a habit of accidentally updating without a where clause.
Preferably the solution is better training and following a procedure like writing a SELECT before UPDATE. Or just security policies...

However, those things aren't going to happen soon. So I would also like to know if there is a way to limit a user to only being able to update X number of rows. i.e if they run an update that will do 10 rows, let it go. If it will be 1 millions rows (happened the other day) prompt them to double check or just stop that query.


texts: Table Design for user-specific and user-agnostic criteria

texts:
I want to design a table that is general enough to accommodate an increasing number of achievements as I come up with new accomplishments to reward.  Initially, I thought to design the table as follows

Name (of Milestone)
Description (of Milestone)
Metric

So for the last milestone listed above, I might say

Name: 1k All-Star
Description: Completed a total of 1000 tasks
Metric: 1000

The code would check the number of completed tasks against the metric to determine whether to award the milestone.  But there are two types of milestone examples I listed.  One in which the metric is universal for all users, and one in which the metric is subjective and depends on the user's previous actions.

Would I want to define universal metrics on one table and make a separate milestone table for the user-specific achievements?  I imagine there that I might create a secondary table of these user-specific metrics so as not to repeat fields:

Subjective Milestone Table

Name
Description
Metric - one-to-many connection to Subjective Metric Table

Subjective Metric Table

Name
User
Metric

So as an example

Name: Weekend Warrior
Description: Exceeded the record for most number of tasks completed on a Sunday
Metric: 5
Name: Weekend Warrior
User: Samson
Metric: 5

Or am I going about this all wrong?


texts: SQL Server uses more memory than maximum setting

texts:
Recently I have added more memory to my VM which has SQL Server installed (now it has 32 GB).

I have set the limit in SQL to 24 GB.

A few days later I see in my process window that the SQL Server process is using 26 GB.

Why is the limit overridden? Will this process increase even more?


texts: Is there a "best-practices" type process for developers to follow for database changes?

texts:
What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:

Script the change in a SQL file and attach that to a TFS work item.
The work is peer-reviewed
When the work is ready for testing then the SQL is run on QA.
The work is QA tested
When the work is ready for production then the SQL is run on the production databases.

The problem with this is that it is very manual. It relies on the developer remembering to attach the sql or the peer-reviewer catching it if the developer forgets. Sometimes, it ends up being the tester or QA deployer who discovers the problem.

A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

Our setup: Our development shop is full of developers with a lot of DB experience. Our projects are very DB oriented.  We are mainly a .NET and MS SQL shop. Currently we are using MS TFS Work Items to track our work. This is handy for code changes because it links the changesets to the work items so I can find out exactly what changes I need to include when migrating to QA and Production environments. We are not currently using a DB project but may switch to that in the future (maybe that is part of the answer).

I am very used to my source control system taking care of things like this for me and would like to have the same thing for my SQL.


texts: Schema design for storing item combination with order

texts:
What I need:

Three types of items, and user can add/delete items.
Use will select some items from those three packs, and arrange an order according to his/her preference.
The number of records is not big (generally below 20).
The tools I'm using are python, SQLAlchemy, and MySQL. (This might not be so important, anyway)

And this is my initial design idea:

One table is enough for those three types of items as they share the same fields and only need to use a 'item_type' field to differentiate. Let's call the table 'Item'.
Use another table called 'Preference' to save user's preference/priority on selected item. Cascade delete is set, so when user delete a row on Item table, it will also disappear from Preference table.

Example:

Use has added 5 items, and selected 4 of them into 'Preference'

[Item]
id  item_name  item_type  preference_id (foreign key reference on Preference.id)
1       xxx         A            1
2       ooo         B            2
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1
2      2
3      4
4      3

Now the sorting is ['xxx','ooo','ccc','bbb'], say the user delete 'ooo' from Item table, the status would become:

[Item]
id  item_name  item_type  preference_id
1       xxx         A            1
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1
3      4
4      3

and the sorting become ['xxx','ccc','bbb'] now.

It looks simple when delete or add new items to the Preference. However, if I need to put an item in the middle of sorting, I might need to re-update the priority field for many rows. For example, make 'aaa' become the top priority would require update the priority column of all other existing 3 items in Preference table, the design doesn't loo very elegant for this scenario.

I would be appreciated if anyone has better ideas, or notice some other flaws on this design. Thanks!


texts: Is it OK to Change System Locale after MSSQL2012 installation

texts:
I have a MS SQL Server 2012 instance preinstalled on a Windows 2012 R2 production server. The Windows locale was English(EN-US), and SQL Server Collation is SQL_Latin1_General_CP1_CI_AS. The system handles all Chinese data with nvarchar fields just fine.

Later for some reason (unrelated to sql server) I may have to change default system locale of the Windows 2012 R2 server to Chinese. Will this cause any undesired side effect on the SQL server instance ?


texts: If a DW fact table is not uniquely identified by all dimensions, what problems?

texts:
This is a bit of a thought problem I've been wrestling with.  I have a visceral antipathy to the notion of duplicate combinations of dimension values in a fact table.  I've read a lot about the existence of problems when the combination of dimensions in a fact table does not form a unique key.  However, I want to understand the exact types of analysis failures that can arise.

Note that I am going to stipulate, in advance, that the hypothesized ugly fact table has data that is all at the same grain.  Let's say that all sales are reported uniquely, but the finest granularity for the time of the sale is a day.  Obviously there will be transactions sharing the same combination of dimension values.  So this approach does not summarize the transactions for each day, the way good practice would ordinarily dictate.

I think simple DW queries with standard aggregation would still be correct.  By "simple," I mean that only a single fact table is referenced in the query.  In the usual form of aggregating/analyzing measures, I think the queries would produce correct results.

One failure case would arise from trying to select unique fact rows by combining all the dimensions. I believe these sorts of queries are practically unknown; I see little use for them, unless the user wants to really drill down to the finest level on all dimensions.  Am I correct in thinking this?

The only predictable and common failure case I can see arises from cross-fact queries.  Here the extra cardinality would presumably multiply any measures used in any fact table.

With my students (and in my corporate jobs), I often get asked "what bad will happen if I don't follow this rule?"  Right now I worry I don't have all the answer.

Thanks in advance for your thoughts and your answers.


texts:
Summary of Question

My question is for SQL Server 2017 is there a way you can re-attach the model database (without rebuilding master or restoring master) if it has been dropped given that both of the following fail

-- both of these do not work
sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'

CREATE DATABASE model
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf'),
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
FOR ATTACH;

Setup for my question

I followed an answer https://dba.stackexchange.com/a/39494/57571 for moving the model database which is not correct for SQL Server 2017 (I now know that the correct procedure to follow is https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017).

As part of it's instructions it had the following:

sp_detach_db 'model'
sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'

Problem was that every time I tried that it would stop the running sql server (since it was trying to access the now dropped model database)

I tried using

CREATE DATABASE model
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf'),
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
FOR ATTACH;

But would only get this error message

Msg 33401, Level 16, State 1, Server SQL01.TEST, Line 1
FILESTREAM database options cannot be set on system databases such as 'model'.

I ended up having to backup the databases and rebuild the master database using the instructions here https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2017

So rebuilt the master database with:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="NT AUTHORITY\SYSTEM" /SAPWD=StrongPa$$word

Then manually restored all the users and databases which existed beforehand.


texts: Is there a way to construct a PIVOT's IN clause using a SELECT?

texts:
I want to build a PIVOT table, but I may have to use dynamic SQL unless the IN clause can be constructed in regular SQL.  I want to do something like this:

SELECT *
FROM
(
    SELECT x, y, z...
) srcTable
PIVOT
(
    MIN(srcTable.TimesDownloaded) FOR OrganizationName IN (SELECT CONCAT('Download_', OrganizationName) FROM AdamUser GROUP BY OrganizationName)
) pivotTable

So when I SELECT unique OrganizationNames from AdamUser and prefix Download_, that gives me a table whose row values equal the names of the columns I want.  This is invalid syntax though.  Do I always have to hard-code the PIVOT column names?


texts:
I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential internal threat).

When I run the following, it returns one row as if the user ran the sp themselves with their current permissions.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_who] with execute as owner
AS
BEGIN
    SET NOCOUNT ON;
    exec master.dbo.sp_who;
END

Changing the "with execute as" to "self" (I'm a sysadmin) returns the same results. I've also tried the below instead of calling sp_who, and it only returns one row.

select * from sysprocesses

It seems that the context isn't switching, or persisting, throughout the execution of the procedure. And this is to say nothing of how I'm going to allow this person to "kill" processes.

Does anyone have a solution or some suggestions to this seemly unique problem?


texts: SQL Server logins and users -- assign multiple logins to a single user?

texts:
In SSMS, since I can't assign multiple login objects to a single user object, what is a best practice to achieve an equivalent security model?

For example, I have 20 logins and want to give them all the same permissions on a single database:

Do I have to create 20 user objects in that database?
Do I have to touch all 20 Login objects?


texts: Recommended model for application level event logging

texts:
I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption.

Below is an oversimplified model of the tables and their associations.

users
-------
id (PK)

event_type
-------
id (PK)
description

events
-------
id (PK)
user_id (FK)
event_type_id (FK)
created (timestamp)

The events will be reported in a timeline format (newest events at the top) for each user.

I'd also like to add custom uri parameters and the events context (User A updated "My Visa Card").

Am I overlooking any potential pitfalls in this design?


texts:
I read the  following information from "https://docs.mongodb.com/manual/core/backups/"

"mongodump and mongorestore are simple and efficient tools for backing up and restoring small MongoDB deployments, but are not ideal for capturing backups of larger systems."

I want to know the definition of small and larger deployment in the perspective of mongodb context.


texts: One field vs multiple fields to reference ID values of dependent information

texts:
This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether equipment's column to reference make-model.ID should be one field or two.
Say, there exists a table named make-model that stores dependent information.

ID
Name
Parent

1
Acme
NULL

2
1000
1

3
Anvil
1

4
Omni
NULL

5
1000
4

6
Photocell
4

This table is meant to represent a category/subcategory relationship that can be represented as:
Acme
- 1000
- Anvil
Omni
- 1000
- Photocell

Notice that there are two 1000 models, but they belong to different makes, this is why the ID column is the key.
There is also a table named equipment that stores information about the equipment, including the make/model.
Based on user input, the breed is set and could be specific to Anvil or stop at Acme. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)
In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (Acme) and one that would show the more specific level (Anvil).
As far as actual columns on the Equipment table, what is the benefit of one column storing the most specific id given (3 if Anvil, 1 if Acme) or two columns, one for each level?
So, for example
Table A:

ID
Name
Make-Model

1
Max
2

2
Spot
3

3
Fluffy
4

Vs. Table B:

ID
Name
Make
Model

1
Max
1
2

2
Spot
1
3

3
Fluffy
4
NULL

My question is about the benefit of Table A's design vs. Table B's design
As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.
The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.


texts: Move data from one table to other in batch's

texts:
I have a table 18 Million records I need to add append it to another table (with the same structure) I tried the import-export wizard, the problem is that the log gets very very big and eventually it took down the full server, I had to kill the process in middle.

I think if I'll do it in batches it'll be faster, but I don't know how to do it.

(I don't need the old table, a record that has been copied could be deleted)

EDIT: bulk insert block all operations, is there a way to prevent him from blocking everyone here?


texts: SQL Server and TFS - How to rename stored procedures

texts:
After a while one may wish to do some light refactoring, such as giving more appropriate names to stored procedures as their functionalities are better established.

For the life of me, I simply cannot rename anything in SQL in a multi-branch TFS environment without it being an onerous, manual process.

Without using some third-party tool, how are you supposed to do it?

P.S. This is just for renaming stuff in a way that doesn't require deployment scripting to migrate anything. That is its own giant can of worms.

When I try to rename a SQL entity (e.g. a table, stored procedure), I use the Visual Studio Find and Replace in Files utility and then rename the file, since the super-useful refactor tools that I use all day for C# do not exist for SQL.

After enabling the advanced SSDT Publish Option to drop objects in target but not in source and manually verifying everything works (since the validations done by the build and the publish are severely lacking) and convincing myself my rename was implemented correctly, I now have to merge changes to a couple other branches in TFS.

Apparently TFS Power Tools Migrate of rename is bugged (but since Microsoft retired Connect, I can't follow up on that post) because all my renames are now showing as ADD changes.

After undoing all those erroneous pending changes, the files are leftover on disk, so manually renaming the files throws file already existing errors. Upon deleting those orphaned files, I am back where I started with no automated tools to help, having to repeat all the changes in every target branch and having to manually detect then merge conflicts.

To add insult to injury, my %AppData%\Local\Microsoft\VisualStudio\14.0\ComponentModelCache folder keeps getting corrupted for some unknown reason (which persists through a complete repair reinstall) such that renaming files only under *.sqlproj throws a null reference error at me about a parameter name 'o' upon which the file is renamed on disk but not in the solution which then corrupts my TFS workspace.

I don't know what is wrong with me, but it seems that renaming SQL entities is unofficially blocked by Microsoft in every possible devious way.

P.S. There is no "rename" tag to put on this post, further suggesting that renaming stuff is not something people ever do in databases.


texts: MongoDB UserNotFound when starting mongod

texts:
I have a MongoDB version 2.4 that i wish to upgrade to 4.0. The official solution is to upgrade from 2.4 to 2.6, then to 3.0, and so on.
I am attempting to upgrade from 2.6 to 3.0, and also to change the storage engine to WiredTiger .
To upgrade from 2.6 to 3.0 i changed the bin folder's name of the 2.6executables and then changed the 3.0 bin folder's name to bin (so i will have a seamless transition to 3.0). Then as This official MongoDB upgrade manual says, i am trying to change the storage engine to WiredTiger by using This manual.
However, when i run this command:
mongod --storageEngine wiredTiger --dbpath ../dataDb_WiredTiger

as suggested by the manual, i get an error:

SCRAM-SHA-1 authentication failed for MyAdminUser on admin ;
UserNotFound Could not find user MyAdminUser@admin
Failed to authenticate MyAdminUser@admin with mechanism MONGODB-CR: AuthenticationFailed UserNotFound Could not find user MyAdminUser@admin

But using MongoVUE or shell, i am able to authenticate using the same user.
What am i missing here?
Thank you.


texts: SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key?

texts:
Since I'm new and I've never shared, and yet there are a few useful scripts that I need on a daily basis, I thought I should.

As a new DBA, some data fix DML operations and simple business logical analyses are much faster when I have a template with which I can check the constraints in a very large database with hundreds of tables. Pinal Dave's script (source posted in the answer below) was a great help as I started, but since then I expanded on it a bit to include the possibility to search for referenced ID's in related tables as well.

I hope this proves useful to some of you guys!


texts: MySQL - LOCK IN SHARE MODE for EXISTS query

texts:
I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid.

It's VERY unlikely of course that someone will delete at more or less the same time someone else tries to do what this transaction happens, but as with all things concurrent 'very unlikely' isn't good enough.

I don't want to experiment either - I want to know for certain it'll work. Will something like:

SELECT EXISTS(SELECT * FROM tbl1 WHERE ... LOCK IN SHARE MODE);

Work as I expect it to?

For

I would imagine that LOCK IN SHARE MODE is simply implemented as a flag at the parsing stage, telling MySQL to acquire certain locks, thus this isn't a special case.

Against

MySQL treats EXISTS differently to a normal SELECT for obvious reasons. As per it's query optimiser it often reads constants before it starts running the query. An over-eager optimisation could defeat this.

I'm leaning towards 'for' but I want to be certain.


texts: Why is MySQL database is having irregular performance issues?

texts:
I first thought it was because of some bad queries, but it wasnt. It took 90 secs to execute a select statement on a table with two rows one column while during other times it executed within ms. (Under similar query load)

We do a an average of 600 CRUD operations per sec and our server has following configuration:

Ubuntu 12.04 16 GB RAM 8 CORE CPU.

The output of vmstat 5 5 is:

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  0 293056 182544 182732 3999428    0    0     3    64    0    0  2  0 98  0
 0  0 293056 181296 182732 4000228    0    0    62  1951 3800 3523  7  1 92  0
 0  0 293056 181288 182740 4000356    0    0     3   456 3889 3924  2  1 97  0
 0  0 293056 180288 182744 4001372    0    0   166  1836 3050 3039  2  1 97  0
 0  0 293056 178984 182752 4002308    0    0   174   312 3158 3271  2  1 97  0

The output of ps aux | grep mysqld is:

mysql    28295 17.5 65.5 15203124 10765956 ?   Ssl   2012 157399:38 /usr/sbin/mysqld

Current innodb config is:

innodb_buffer_pool_size   = 8192M
innodb_thread_concurrency = 24
thread_cache_size         = 64
thread_concurrency        = 32

query_cache_limit         = 2M
query_cache_size          = 768M

Output of mysqladmin status:

Uptime: 53761928  Threads: 9  Questions: 18290906015  Slow queries: 694562  Opens: 12996174  Flush tables: 1  Open tables: 4096  Queries per second avg: 340.220

What I am understanding from ps aux is that current physical RAM usage is 12GB while total physical + virtual memory usage is 16GB

I am finding it right? Should I flush tables or increase RAM? And would 24GB RAM resolve the issue?


texts: Space consumed by a column in non clustered index

texts:
I m planning to add a key column index in one of the existing non clustered index in a big table which have millions of rows rather than create a new non clustered index. Is that any way for me to know how much the extra space consumed by the index by adding a new key column in existing non clustered index and is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table.
Just to be clear, I need to explain to storage team extra space that consumed by index after add the new column , then only I can add the column to existing index.


texts: MySQL/MariaDB Read only except for a single table

texts: I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?


texts: Query taking non-consistent time to execute

texts:
I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So there is no consistency with the time taken to execute.So i am not able to find why this happens?

Below is the query i used to test

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).

when i execute the same query(with cast) time required to execute is different even in same PC for different trials.It varies from 1 second to 14 second(i tried number of trials executing query). The variation in time may be due to network lateny,cache,table in high usage by other user etc.
But is there any tool or method by which i can find the reason for the time taken(may be slow or fast) to execute?

update
I just tried to check IO stalls by using the query

SELECT
    mf.physical_name,
    ( io_stall_read_ms / ( 1.0 + num_of_reads )) as [avg read wait],
    ( io_stall_write_ms / ( 1.0 + num_of_writes )) as [avg write wait],
    i.name,
    fs.io_stall
    FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
    INNER JOIN sys.databases as i on fs.database_id = i.database_id
order by i.name desc

In the output my database 'company' was taking high io stall.below is the values i got for that database.(The original output had iostall of all database files)

avg read wait-37.17162364    avg write wait -20.66666667  io_stall-4813081
So the database i used in query had high iostall .What is the reason for it?

I added Phone no as non clustered index and  just rewrite the query as

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [company].dbo.[customer]
  WHERE [company].dbo.[customer].[Phone no]  LIKE '%9000413237%'

Now the query executes within 1 second. I cant use LIKE '9000413237%' as i require phone number to be searched at any part of the column.So i hav to use LIKE '%9000413237%'.But in several articles it is mentioned that using like '%abc%' will not benefit indexing only 'abc%' can benefit from indexing. But in my case using LIKE '%9000413237%' helped me a lot with indexing.So i have doubt that why people say using LIKE '%9000413237%' does not benifit


texts: How to tune a stored procedure that is fast on test data but slow in production

texts:
If you have a SQL Server 2012 database with test data in development and preproduction phase and the real from the company's server take place only in the production phase. In other words, the real data take place only the the production phase and not in the test and pre production phase.

Production phase take place inside in the company's server.

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning.

This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily.

My questions are:

Is it recommended to do a performance tuning in the production phase in relation to real data?
Is it something that I should be aware of? Today, I'm a newbie in performance tuning.
What plugin or tool do you use in order to review the the database and its specific stored procedure's performance?

Thanks!


texts: How to safe delete (shred) a MySQL database?

texts: I want to store sensitive data in a MySQL database for a while, so I'd like to know a way to delete a MySQL database without having a chance of recovery. In Linux, we have a tool named shred which overwrites the content of the file repeatedly, so its content can never be recovered anymore. Problem is: I want to do it with a specific MySQL database. I know MySQL stores data in /var/lib/mysql, but there are several files there and it would destroy all databases and break MySQL. Any ideas?


texts: MySQL read/write ratio from information_schema.GLOBAL_STATUS

texts:
Could you please help me to construct a query to sum specific rows from a table, basically desired output is:

+-----------------+----------------+
| Total reads     | Total writes   |
+-----------------+----------------+
| 345436          | 345435         |
+-----------------+----------------+

Total reads = Com_select
Total writes = Com_delete, Com_insert, Com_update, Com_replace


texts:
I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner.
Let me first explain the problem. I have a series of events which happen on a certain point in time. To keep it simple, let's assume that each event happens on a distinct point in time. An event is represented by a number. Take for example the following data:
create table event
(
    time   time,
    status integer
);

insert into event
values
('12:00', 0),
('13:00', 8),
('14:00', 4),
('15:00', 2),
('16:00', 0),
('17:00', 9),
('18:00', 5),
('19:00', 8),
('20:00', 0),
('21:00', 1),
('22:00', 3),
('23:00', 0);

Now, a cycle is defined as a sequence of events happening between two events with status 0. So, for the data above, I have the following cycles:
cycle 1: 0 -> 8 -> 4 -> 2 -> 0
cycle 2: 0 -> 9 -> 5 -> 8 -> 0
cycle 3: 0 -> 1 -> 3 -> 0

The goal is to find these cycles.
I have a working solution (see fiddle), and it goes as following:
with
cycle_boundary(begin_time, end_time) as
(
    select begin_time, end_time
    from   (
             select    time, lead(time) over(order by time)
             from      event
             where     status = 0
           ) as cycle(begin_time, end_time)
    where  end_time is not null
)
select     begin_time, end_time, array_agg(row(time, status)) as events
from       cycle_boundary
cross join event
where      cycle_boundary.begin_time < event.time and
           cycle_boundary.end_time > event.time
group by   cycle_boundary.begin_time,
           cycle_boundary.end_time;

This outputs:
begin_time  end_time    events
12:00:00    16:00:00    {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00    20:00:00    {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00    23:00:00    {"(21:00:00,1)","(22:00:00,3)"}

The problem is that this solution is quite inefficient. First, I scan through the complete events to find the boundaries (which are two subsequent events with status 0), and then, I scan through these boundaries to find the containing events. This is basically a nested loop, so O(n^2).
In a procedural language, this can be easily solved in O(n) under the pre-condition that the events are sorted (which we can achieve in a database as well if there is an index on event(time)): loop through the ordered events and collect the events (in a temporary collection) as long we do not encounter an event with status 0; once we encounter such event, we output the collected events so far and clear this temporary collection.
So my question boils down to: how can we solve this in O(n) in SQL? I believe that one of the problems is that the FILTER clause for aggregate window functions is not implemented in PostgreSQL, but that might be irrelevant here.


texts: MySQL - Got an error reading communication packets - high open tables, server goes offline

texts:
I am currently testing a product with a couple of my friends before releasing to the public. There's now 10 of us who use the application daily. Yesterday, we noticed a performance error on the MySQL server, which currently runs on the smallest DigitalOcean droplet, with 1 GB Memory, 1 vCPU, 25 GB SSD Disk and 1 TB Transfer. Our plan is to increase this as soon as we go public, but we were hoping it would be sufficient for testing purposes.

The first thing I looked at was the MySQL error.log file. The output can be found here. This output was captured right after the server crashed for the first time, and I still don't know why.

I also had a look at the config file, which can be found on this link.

A couple more interesting things:

mysql> show global status like '%onn%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_connects                              | 19                  |
| Connection_errors_accept                      | 0                   |
| Connection_errors_internal                    | 0                   |
| Connection_errors_max_connections             | 0                   |
| Connection_errors_peer_address                | 0                   |
| Connection_errors_select                      | 0                   |
| Connection_errors_tcpwrap                     | 0                   |
| Connections                                   | 121                 |
| Locked_connects                               | 0                   |
| Max_used_connections                          | 10                  |
| Max_used_connections_time                     | 2019-06-27 16:52:29 |
| Performance_schema_session_connect_attrs_lost | 0                   |
| Ssl_client_connects                           | 0                   |
| Ssl_connect_renegotiates                      | 0                   |
| Ssl_finished_connects                         | 0                   |
| Threads_connected                             | 3                   |
+-----------------------------------------------+---------------------+
16 rows in set (0.01 sec)

Note: this was captured right after I restarted the server.

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1400  |
| table_open_cache           | 2000  |
| table_open_cache_instances | 16    |
+----------------------------+-------+
3 rows in set (0.01 sec)

Is there anything I can or should to do increase performance? Is there something I am doing wrong? This is the first time I am handling a MySQL server in combination with NodeJS, whereas my previous experience with MySQL was only with webhosting. If you need anything else, feel free to ask, I will be happy to provide anything I can find.

Per request from Wilson, I have uploaded a couple more files to pastebin which can be found here:

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;
htop
ulimit -a
iostat -xm 5 3
MySQLTuner
mysqld.cnf (my.cnf)
error.log
mysql.log
mysql-slow.log is empty

My specific concerns go towards, of course memory/cpu usage which caused the server to crash twice with almost no users (like 5 at most) and how I can prevent this and/or allocate the right resources to the right processes etc., but also about the timeout error in the error.log file. Of course if there are other suggestions to optimize my system, I would be happy to. I have this server run on a DigitalOcean droplet with 1 GB memory, 1 vCPU, 25 GB SSD Disk and 1 TB transfer - dedicated for MySQL so nothing else runs on there. Will upgrade to more memory/cpu/disk/transfer when we scale.

At this point I am a little afraid because I suspect a lot of changes would need to be made to make this small server be performant enough. Looking forward to your suggestions.


texts: Loading XML documents to Oracle 11g DB with control file

texts:
I am using Oracle 11g XML database and trying to load XML documents to this DB with a control file and the sqlldr utility.  All these XML files have an element that contains a date string with time stamp (and the letter T in the middle of it).  Oracle rejects this date string because of T in it and thus the XML file is not loaded to the DB.

I want to use the Oracle function TO_TIMESTAMP_TZ on the date string during the data load, but I do not know how to do it.  That's where I need help.  If there is any other way to import the XML (with date string with timestamp), I will try that also.

Here is the date entry in XML file:

<ns3:EntryDateTime cls="U">2013-04-20T21:02:52.468-04:00</ns3:EntryDateTime>

And here is entire code the control file:

load data infile 'filelist.dat'
  append into table STXP xmltype(XMLDATA)
  ( filename filler char(120), XMLDATA lobfile(filename) terminated by eof )

I believe that I can execute the above control file with the sqlldr utility on SQL*Plus command line also, but not sure about this option.  If this is possible, I guess I can ALTER SESSION (to somehow format date string) on command line before executing the control file.

The filelist.dat mentioned above contains entries for input XML file, with one line listing one XML file.  The above date entry is required in each XML file.  Each XML file has about 50 different elements, some required and some optional.  I would greatly appreciate your help.

UPDATE:
I successfully registered the schema, which contains definition for the date string, and 100 other schema, with a script.  Since this script is very large, I am posting only 2 registration portions of it:

DECLARE
SCHEMAURL VARCHAR2( 100 );
SCHEMADOC VARCHAR2( 100 );
BEGIN
SCHEMAURL := 'http://www.some.org/stxp/DataTypes.xsd';
SCHEMADOC := 'DataTypes.xsd';
DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL,
    BFILENAME( 'XSD_DIR', SCHEMADOC ),
    LOCAL => TRUE, -- local
    GENTYPES => TRUE,  -- generate object types
    GENBEAN => FALSE, -- no java beans
    GENTABLES => TRUE,  -- generate object tables
    OWNER => USER );
    SCHEMAURL := 'http://www.some.org/stxp/STXP.xsd';
    SCHEMADOC := 'STXP.xsd';
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL,
    BFILENAME( 'XSD_DIR', SCHEMADOC ),
    LOCAL => TRUE, -- local
    GENTYPES => TRUE,  -- generate object types
    GENBEAN => FALSE, -- no java beans
    GENTABLES => TRUE,  -- generate object tables
    OWNER => USER );
  END;
  /

The 2nd registration above is the last in the script, and this creates the table STXP, in which I am trying to load about 800 XML files.  Each XML file has a root element called stxp.

This is the relevant definition of date string:

 <xsd:simpleType name="DT" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
    <xsd:restriction base="xsd:dateTime"/>
</xsd:simpleType>

And this is how I am using the above definition:

<element name="EntryDateTime" type="oth:DT"/>

When I make the above element optional (for testing purpose) and remove the date string entry (mentioned near the top of this question) from my XML file, the XML file is loaded successfully to Oracle XML database.  When I put this entry back to XML file (because it is required), Oracle rejects it.

Because I let Oracle take care of population of STXP table with data from XML files, I am not sure if I can set a trigger to pre-process the date string from the input XML file before saving it in database.  i think there is a way to do it in the control file.


texts:
I have to delete larges amount of data by user request, ie, when a user press a button some PL/SQL will be executed to delete a certain set of data. Usually we can say that this set has approximately 10 million of rows. The table will have about 300 million of rows monthly and in this moment I don't know how the housekeeping will work on historic data.
What do you suggest in order to make this the most efficient possible and also to be able to not blow the transaction log files and put the database down?

The delete is something so simple like:

delete from <table> where <field> = <value>

Where this field is not a key in the table.

****** EDIT ******

This is a operation that a user triggers. And has to run after the moment that the user press the button.
The table is partitioned by date (interval of months).
I made an edit in the original description. There will be an estimated 300M of rows monthly, 10M per day. So you can look at this as 30 loadings of 10M of rows per day that will be in the same partition (partition for that month), and I want to delete one (or more) specific loadings for that month (partition).
We are just beginning so what I can say is just estimated values.

Thanks in advance.


texts:
For this question, please assume the following:

I am using MySQL
I have a Location table: Location(ID, Name, Address, TravelDetails, ...)
I have an Event table: Event(ID, Name, LocationID, StartTime, ...)

LocationID is a Foreign Key to Location

The system is an ASP.NET MVC Web Application
The system has multiple concurrent users.

Here is an example scenario that explains my confusion:

In my application, I select "Create New Event". I am given a Drop Down List to select a Location. I am shown the Location Name, but the LocationID of the selected item is what is stored in the Event.

While I am creating my Event, someone else opens a Location to edit some of its details. They could change the Location Name and all other details so that the Location is now effectively a completely different Location (basically, the editor created a new Location without deleting the location first). Those changes don't propagate to my Event's Location Drop Down List until I refresh the page, so I don't have the most up-to-date list of Locations.

With the changes made to the Location, the identity of the Location I wanted to use for my Event could be completely different - it might not represent the same Location as it did originally. Now my Event could be completely wrong because the Location is different than I had intended.

My question is this:

How should I handle this? Is this a problem worth worrying about? Who is to blame? Is it my fault as the developer for not locking down fields such as Location Name (but what if there was a typo in the name...?) or implementing safety checks to inform the user if a Location he is trying to use was changed since he started creating his Event? Or is it the fault of the editor for not being "smart" about completely changing the original meaning/purpose of a Location?

I want to assume users will be smart when making edits, but that is always a bad assumption.


texts:
I'm trying to store the following arrays into a database, but due to my inexperience, I don't know how to set it up.

It's for a scheduler. A day entry holds three arrays:

The 1st array holds the basic entry ['prefix', begin, increment, 'suffix'], that displays:

'prefix' begin - (begin + increment) 'suffix'
The 2nd array holds periodic entries that displays every number. For example, every 5 basic entries, display a new type.
The 3rd array holds the days to display on.

There will also be week entries, that can contain up to 7 day entries.

I organized it as the following:

Day entry:

'day' => [
    ['prefix', begin, increment, 'suffix'],
    [
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        (Haven't decided on limit)
    ],
    [list of days]
]

I thought of just sticking all of that data into one column as it is, but then I remembered that I also needed to store week entries.

'week' => [
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  (Possibly 5 more 'days')
]

I'll have an id column and a few other irrelevant columns for each entry, but that's the primary data. I haven't written much code yet, so I'm willing to change anything.

My question is, what's the most maintainable, efficient, etc. way to store this data in a mySQL database?

Edit: The data would look something like this:

'day' => [
    ['Lesson', 1, 2, ''],
    [
        [5, ['Test', 1, 0, '(study)']],
        [10, ['Investigation', 10, 0, '(study)']]
    ],
    [1, 2, 3, 4, 5]
]

And a week entry could hold up to 7 of these day entries.


texts: What is the nature of "LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2"?

texts:
I'm not sure when this started, but for the time being the primary database instances in a physical standby configuration logs this message on every redo log switch.

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 2 sequence 306
LGWR: Standby redo logfile selected for thread 2 sequence 306 for destination LOG_ARCHIVE_DEST_2

I'm not sure what this indicates, but I do associate this message with a standby database becoming available after downtime or network outage. E.g. the (optional) archive destination was deferred, but has now become active.

Similarly, on the standby instance, I'm seeing the following on every log switch:

Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 6 thread 2 sequence 308
RFS[212]: Assigned to RFS process 8304
RFS[212]: Selected log 8 for thread 2 sequence 309 dbid 18783000 branch 804771032

Again, this is a log message I associate with a disruption in the delivery of archived redo logs or a change of delivery mode (changing the log destination parameters or the protection mode).

Any idea what may be causing these to be logged repeatedly on every log switch?

Update:

I now  have a working theory: I think this will happen if real time apply have been used at all, and thus indicates that LGWR is waking up to do some archival work. Previously, I believe ARCH would take care of log transport and archival but activating real time apply may have forced a configuration change.

So far, I have found no evidence of this change being visible in data dictionary or parameter set, just the changes in alert log messages (which happened to coincide with a test involving real time apply on the standby).

If this is the case, any idea how to reverse the change now that real time log apply is disabled and no longer required?


texts: Two columns with and without index in where clause - How index works

texts:
I am executing a query on SQL Server 2008:

SELECT col1
FROM table1
WHERE col2=val2 AND col3=val3

Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. Query execution plan is similar to this.

I want to know how the query execution works here. From the execution plan I can see an index seek on 'col2' and Key-Lookup on 'col3' (shown parallelly).

Is it going to fetch all the rows that matches the 'col2=val2' condition and check for the other condition?
Why these two -Index seek and Key-Lookup, are shown parallel in the execution plan?
Will it always use the available index on 'col2', considering a large dataset and assuming almost all entries in 'col2' are unique?


texts:
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for database archiving in PostgreSQL.

Few solutions I can think of are:

Table partitioning
Separate tablespace and/or schema
Moving archived records/tables to a different harddisk

Any other suggestions/pointers/solutions are really welcome and appreciated.

NOTE: We're running PostgreSQL v9.1.3 on CentOS5.2


texts: How to turn off identity for a database column of a table

texts:
I have mistakenly deleted a table in one one my database server. I have same set of data in the other server.

What I have done is clicked on edit top 100 rows for the table, then copied it and pasted in the other server table.

But , here the problem is : one of the columns of the table is an identity. So, i was unable to copy that column here.

I need same identity values because these values are referred in the application, even if i copy the data and update it, new id values are being generated.

Is there any way to turn off identity for that column, paste these values and again turn on identity.


texts: MySql Moving from development to production into a different user

texts:
I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad practice I know).

Now, I need to move the whole thing to production and obviously NOT under the MySQL ROOT user.

I thought to do the following, but would appreciate an expert opinion before I engage into it:

Get a backup using mysqldump -u root -p -R MySB > dump.sql,
Edit the file and replace all instances of root with correct_user (where correct_user would be the MySQL user owning all the tables and routines),
Create the same user within the production DB,
Restore from the dump file using mysqldump -u correct_user -p -R MySB < dump.sql.

Can anyone tell me if my plan is correct or if there is a better (and more formal) way to do it?

Many thanks.


texts: Check Constraints on Decimals In SQL Server 2012

texts:
I have the following check constraint on an NVARCHAR(255) column:

([weekReviewWk10]='A' OR [weekReviewWk10]>=(0) AND [weekReviewWk10]<=(25))

However, when someone enters, say, 23.5, SQL Server rejects the input. How can I fix this? I know it is because it is testing things as ints, but how do I get it to test the entry as a decimal?

The column has to be NVARCHAR since, as you can see, I also need my users to be able to enter 'A'.


texts: Mysql How to update n rows using count function?

texts:
In the following table, I want to set delete = true if the total records for same orgid exceed 500
and I want to do it according to createdate such that if records exceed 500 the old records get deleted and make total records 500 for that orgid.
here is my table
Table A
+----+-------+------------------+--------+------------+
| id | orgid | transactionvalue | delete | createdate |
+----+-------+------------------+--------+------------+
|  1 |     1 |              123 | false  | 05-16-2020 |
|  2 |     1 |              412 | false  | 07-16-2020 |
|  3 |     2 |              762 | false  | 07-16-2020 |
+----+-------+------------------+--------+------------+

Here is the query I am trying
update A set delete = true where orgid = 1 and (select count(*) as records from (select * from A order by createdate) as pseudotable)) >500


texts: Is it possible to create a PostgreSQL RLS Policy that depends on values from the table the policy applied on?

texts:
I'd like to create a policy that depends on other records from the same table.
For example, let's say there's the following junction table:
 project_id | user_id
------------+----------
     1      |     1
------------+----------
     1      |     2
------------+----------
     2      |     2

I'd like to prevent a user from selecting records related to projects it's not related to, and allow the selection of the rest.
Something like:
CREATE POLICY project_user_read ON project_user FOR SELECT
USING(
      project_id IN(
      SELECT project_id
      FROM project_user
      WHERE user_id = current_setting('app.current_user_id')::bigint ---> I use runtime variables
   )
);

That will result this way:
SET app.current_user_id = 1;
SELECT * FROM project_user;

--  project_id | user_id
-- ------------+----------
--      1      |     1
-- ------------+----------
--      1      |     2

This obviously doesn't work because it's a circular condition.
Is there any way to implement such logic with RLS?


texts: In creating view use SQL Security definer or invoker?

texts:
CREATE VIEW syntax is

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

For SECURITY you can set DEFINER or INVOKER.  When does it make sense to select "definer"?  As far as I understand that means check the security permissions of me, the person who is creating the view.  If I have full permissions across our database, then I just created a view that gives any invoker full access.  That sounds wrong.  Wouldn't you always want the security to be the person who is actually using the view?


texts: DB2 DBM paramter NUMDB has value 8. Should I change it to 1 because I have only one database?

texts:
Hello I am using IBM Maximo application which is using DB2 database and I have created only one database. I heard that when paramtere NUMDB has value 8 it will divide all values in DB CONFIGURATION PARAMATERS because it will also get memory for other databases which does not exists. Is this true and should I change value from 8 to 1 because I have only one database? All I could find about this is on this link:

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0000278.html


texts: Bypass MS Access 2GB file size limitation

texts:
I am creating a MS Access *.accdb database with multiple tables and specific relationships.

This database will be used for a one time data transport to another system, containing order of a few million entries. A test with 2 million dummy entries already reaches about 1.8GB file size. Since MS Access has a built in limit of 2GB per database file I am searching for solutions to bypass this limit.

I came up with two ideas so far:

Split the database into two files, continuing the indexing during the transition from file1 to file2, taking care of independence for the entries in related tables.

file1_table1 (indices 1..1000)
file2_table1 (indices 1001..N)

Export the tables to different files, using one master interface file

file1_table1
file2_table2
file3_table3
masterfile (linking table1..3 and creating table relationships)

I would like to get some feedback on these ideas concerning concept, performance and usability or if there are any other ideas about this.

Please note that I want to stick to MS Access, building up a SQL Database would be overkill, since it will be used for a one-time transport only.

The data is distributed over several sources (MS Access .accdb, Excel Sheets *.xlsx, delimited files *.csv) and is analyzed, merged and prepared as a temporary one-time-use MS Access database, then transformed into a working database based on SQL, which includes a lot more than what I am preparing. So it's a classical ETL process.

The "Compact and Repair" command won't help, since it is really just the large amount of data, not a large amount of operations leaving behind a lot of junk. The "Split an Access database" procedure only seems to separate Queries/Forms/Reports from the backend file containing the tables, but I need to split the tables themselves, leaving me with the problem of consistency between the split files. This is not an issue with separating the interface (front end) and data back end; I need to split the data backend itself due to the large amount of data.


texts: SQL vs NoSQL: Fetching All records in DynamoDB vs SQL Database

texts: I am designing database for a project which requires frequent querying of all the records. The total number of records will be less than 500K. One of the databases I am considering is AWS DynamoDB where scan operation meant for this is very inefficient. What I fail to understand is whether it is inefficient in a SQL based database or there is one better than other for frequent calls for fetching all records.


texts: Why is one table taking up ~75% of time for DELETE

texts:
I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables.

Two of the tables (Result, Amount) have about 90 million rows. They are almost identical in nature. They both have:

A composite PK and a single decimal 'Value' column.
Both are joining tables where Period is one side of the relationship.
In addition to the PK clustered index, both have a NC index based on PeriodId and including the 'Value' column.

SELECTs that filter on PeriodId are very fast. I find however that DELETEs take too long (2-3 seconds) and that the main culprit is the Amount table.

I can't understand, given their identical setups, why one table takes up ~75% of the DELETE time. You can see from the picture of the query plan that the culprits are the two sorts. What would cause the Result table to DELETE so quickly, but the AMOUNT table to use these 'SORT's?

[Part of the query plan showing the RESULT delete on the top row and the AMOUNT delete on the bottom. Igore the middle part]


texts: mongo primary is showing behind the primary?

texts:
mongo primary is showing behind the primary ?? Its increasing as time passes . Please suggest ?

monRepl:PRIMARY> rs.printSlaveReplicationInfo()
source: xxxxxx:27017
    syncedTo: Mon Apr 27 2015 10:39:02 GMT+0530 (IST)
    **25835 secs (7.18 hrs) behind the primary**

monRepl:PRIMARY> rs.status();
{
    "set" : "monRepl",
    "date" : ISODate("2015-04-27T12:20:38Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 3,
            "name" : "xxxxx:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 329104,
            "optime" : Timestamp(1430111629, 90),
            "optimeDate" : ISODate("2015-04-27T05:13:49Z"),
            "lastHeartbeat" : ISODate("2015-04-27T12:20:36Z"),
            "lastHeartbeatRecv" : ISODate("2015-04-27T12:20:37Z"),
            "pingMs" : 0,
            "syncingTo" : "yyyyyy:27017"
        },
        {
            "_id" : 5,
            "name" : "xxxxxx:27017",
            "health" : 1,
            "state" : 7,
            "stateStr" : "ARBITER",
            "uptime" : 329104,
            "lastHeartbeat" : ISODate("2015-04-27T12:20:38Z"),
            "lastHeartbeatRecv" : ISODate("2015-04-27T12:20:37Z"),
            "pingMs" : 0
        },
        {
            "_id" : 6,
            "name" : "yyyyyyy:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 579120,
            "optime" : Timestamp(1430137238, 300),
            "optimeDate" : ISODate("2015-04-27T12:20:38Z"),
            "electionTime" : Timestamp(1429808169, 7),
            "electionDate" : ISODate("2015-04-23T16:56:09Z"),
            "self" : true
        }
    ],
    "ok" : 1
}


texts: Send email when certain Data is being inserted in MS SQL Server

texts:
I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server.

I want to use that to send email on the event that certain data is added, for example if I have a table with a column called 'error no' in it; I want to be notified via email when a certain error no. is being added.

e.g. if error no is 5, I want to be sent an email via the notification system. If the error no is 4, then insert it to table with no notification.

I would love to be able to do it without writing any stored procedure or T-SQL, but if I have to, then I have no problem doing it.

Thank you guys.


texts: The equivalent of Common Table Expressions (CTEs) in MySQL

texts:
I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

USE UNAMupdates;
with CTE as
( SELECT tbl_modules.module_code
FROM tbl_student
 JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
WHERE tbl_student.student_number='1002')

SELECT tbl_notifications.module_name,tbl_notifications.message,tbl_notifications.staff_username,tbl_notifications.date_time
FROM CTE JOIN tbl_notifications
ON cte.module_code = tbl_notifications.module_code
ORDER BY tbl_notifications.date_time DESC

What I am trying to achieve is to query all modules/subjects taken by a single student and then finding out if any notifications/news items are available for that subject.


texts:
Our database only has a couple of Users.Despite warnings of not opening to many tabs on MySQL Workbench (as performance tails off) one has succeeded in creating nearly 100 query tabs with the result that Workbench is so slow as to be virtually unusable. I am tempted to a complete removal of Workbench and a reinstall. However he says there a a number of complex queries he wants to get copies of. Is it possible to access these via another route?

He is running Workbench Version 5.2.38 under Linux Mint


texts:
What is the best setup strategy for TimescaleDB?
My initial thoughts based on the TimescaleDB FAQ was that it is indeed recommended and possible to basically have one database that contains both time series data and my other regular data.
I am just wondering what is the strategy when using multinodes. We can create a distributed hypertable, but what about the rest of my non-timeseries data? Can I distribute them?
Does it make sense to keep TimescaleDB data into its own "cluster" of nodes and have separate instances (perhaps even use other extension like Citus) for non-timeseries data ?


texts: Designing a Multiple Reflexive Relationship

texts:
I'm making a database diagram and I'm facing the situation explained below.

Business rules:

A device could have multiple connections with others devices (It's a graph).
A device could be a server, a switch or store unit (for the moment, could raise).
A device belong a one system.

So, I need it design this multiple reflexive relationship between devices. I believe that I achieved it with the next model, but I'm not sure if it's the best solution.

Reality and Problem:

A device could have multiple devices (links) and belong to multiple devices.

So my diagram is as follows:

Diagram Explanation:

The Device concept was created because the information of all devices is similar relative. So the properties that differ could be optional. I believe that the solution for the multiple reflexive relationship is to create other concept that has a copy of first concept ID (device) to apply the partition and get the link between multiple devices. This copy is generated when some inserts a device (with a trigger or with simple other insert, in chain from logic layer). The purpose of the ID copy is to allow creating relationships between all devices.

Any idea for this case? Thanks.


texts: Find continuous records with equal values

texts:
I have a table named "TOOLS" .

Sample data:

ID       TAG
1         0
2         1
3         1
4         1
5         0
6         0
7         1
8         1
9         0

So, the SQL query should select sum of TOOLS having continuous 1 in field TAG for a specified row, for example ID=2 then the query returns 3 in this sample. It also returns 2 for ID=7.

How can I do it?


texts: When does MySQL begin to start sending data to a client?

texts:
I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to the client.

Scenario 1:

If there is a select query on a very large data (say 1 GB), does MySQL pull the entire data from the disk (from .idb files) to the InnoDB Buffer Pool or does it send data to the client in batches without exhausting the server's memory?

Scenario 2:

A simple inner join of two tables without ORDER BY or GROUP BY clause (i.e the ordering of data doesn't matter). In such case, does MySQL send the data as and when the join data is accumulated (i.e in batches) or does it construct the entire JOIN result in the buffer pool and sends the data after? Is the entire data loaded in-memory?

The my.cnf configuration of my local machine is as follows:

[mysqld]
performance_schema=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_size=4294967296
secure-file-priv = ""

Note: I have disabled the buffer pool load on shutdown and startup so as to simulate the condition where none of data is cached when the server is started.


texts:
I am recovering a corrupted database, which may not really of had problems, but the server did.

The host has restored a backup of the VM image (Ubuntu 12.04 LTS), however, its 10 days old, which is pretty disappointing.

So what i decided to do is copy the old recovered database located in /var/lib/postgresql/9.1/main/base

I restarted the VM and postgresql, but the old data only showed!

So i have been repeatedly reindexing the database, the missing data is starting to appear.

Does anyone have in more tricks in this area?


texts: Resources on database design and data integrity enforcement best practices?

texts: I am a developer at a very small software company who has historically taken a very loose approach to database design and data integrity techniques. I'd like to turn that around and start using more in the way of keys, constraints, transactions, etc to prevent software defects and inconsistencies from causing data problems and also bring those causes to light more quickly. Are there any great/classic resources or books on best practices for database design and these sort of data integrity features, discussion of trade offs, etc?


texts: InnoDB: page_cleaner: settings might not be optimal (how to tune)

texts:
I am having similar issues to this question but I'm trying to understand how to tune the database correctly (rather than just lowering settings and hoping ;)
In the error log I have these entries (often days or weeks apart)
2020-12-19T11:54:53.640527Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12204ms. The settings might not be optimal. (flushed=27 and evicted=0, during the time.)
2020-12-19T23:14:04.901370Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6788ms. The settings might not be optimal. (flushed=10 and evicted=0, during the time.)

And corresponding entries in the mysql-slow log! down to the second. What I find weird is that I thought the cleaner was supposed to be some kind of background process... why does it have any impact on the queries themselves?
mysqltuner is happy, the db has 32 gigs ram and some relevant settings are
Settings:

innodb_page_cleaners is currently 4,
innodb_buffer_pool_instances is 25 !.
innodb_buffer_pool_chunk_size is 134217728
innodb_buffer_pool_size is 26843545600

Questions:

Should I just set the page cleaners to 25 as suggested in the other answer.
Should I try 1) first, see if it makes a difference, then lower the innodb_lru_scan_depth if I don't see an improvement?
Why doesn't MYSQL just default the page cleaners to be the same as the number of instances if it can have such a drastic affect?
t suggests setting the page cleaners to the same as the pool instances. Why doesn't MYSQL just default to that if it's so sensible?
Is it expected to see the cleaner have an impact in the slow-query log?

Thanks!


texts:
If I run the backup in a job this works:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "
EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\\backup\SQL\',
@BackupType = 'FULL', @ChangeBackupType = 'Y', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'" -b

If I stripe the backup like so:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\\backup\SQL\Drive1\, \\backup\SQL\Drive2\, \\backup\SQL\Drive3\,
\\backup\SQL\Drive4\, \\backup\SQL\Drive5\, \\backup\SQL\Drive6\,
\\backup\SQL\Drive7\, \\backup\SQL\Drive8\, \\backup\SQL\Drive9\, \\backup\SQL\Drive10\',
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y',
@LogToTable = 'Y'" -b

I get the following error:

  Msg 105, Level 15, State 1, Server SQLSERVER, Line 1
  Unclosed quotation mark after the character string 'EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
  '.

I can execute the same code as T-SQL and it works, also if I put the T-SQL in a job it works (although it gives me error 50000, but still completes!), I've been over it a few times and can't see what I might be missing, do I need to put extra quotation marks in the SQLCMD job?


texts:
In one of our environments we execute quite heavy query from python script using psycopg2. It takes more then 10 min to execute but we get a result and can consume received data.

When we move the same script to different environment then after ~5min we get an error: psycopg2.InterfaceError: connection already closed

What may be the reason for that?


texts: Handle 4 tables and to show 4 columns as result

texts:
i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns:

I want to show this 4 rows, each from a different table.

EDIT: Each company belongs to a Sector. And because of how the data it's submited and handle it inside the database, i can't have everything in only 2 tables. Every table has different uses.

So far, I have tried UNION/UNION ALL:

SELECT Companies.Symbol, CompanyData.strenght FROM Companies
INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anything'
UNION ALL
SELECT Sectors.SectorName, SectorTrend.Trend FROM
SectorTrend
INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID

I only get 2 columns: Symbol and Strenght

Several JOINS nesting the queries:

SELECT SectorTrend.Trend
FROM (
    SELECT Sectors.SectorName
    FROM (
        SELECT Companies.Symbol, CompanyData.strenght
        INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anyvalue'
         )
    INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
    WHERE Sectors.SectorID = 'anynumber'
     )
    INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

I get some sintax error on line 5 that can't figure out how to fix.

Using plain JOIN queries:

SELECT Companies.Symbol, Sectors.SectorName, CompanyData.strenght ,
SectorsTrend.Trend FROM Companies
INNER JOIN Companies ON Companies.SectorID = Sectors.SectorID
INNER JOIN CompanyData ON CompanyData.CompanyID = Companies.CompanyID
INNER JOIN SectorTrend ON SectorsTrend.SectorID = Sectors.SectorID

I get Error: Table/alias Companies is not unique. I have been playing switching the query order without any luck.

Is there anything that i'm  missing or can you point me out another way to handle this query?
Both queries handled separately works fine.
Should i handle it separately, store the results in any variable and then create a view to display the result?

Thanks in advance.

UPDATE #1
Following @Kondybas suggestions i rewrited the query, and got empty result.
It was an multi column index problem and when i executed EXPLAIN SELECT... there were NULLS keys and NULL possible keys, so I added indexes in several columns with: CREATE INDEX _indexname_ ON _tablename_ (_column1_, _column2_); and now the query it's running faster.

But managed to get results only if i remove the last JOIN: JOIN SectorTrend AS st ON st.SectorID = s.SectorID and without using any WHERE condition.
So it seems the problem it's that last JOIN.

Final query is (changed and included some columns names):

EXPLAIN SELECT c.Symbol , s.SectorName , cprs.strenght , ssbpi.ColumnType FROM Companies AS c JOIN Sectors AS s ON s.SectorID = c.SectorID JOIN CompanyPriceRS AS cprs ON cprs.CompanyID = c.CompanyID JOIN SubSectorsBPIsData AS ssbpi ON ssbpi.SectorID = s.SectorID WHERE cprs.strenght = 'strong' AND c.SectorID = '16'

And EXPLAIN SELECT... result:


texts: I am trying to install Oracle 19c, and It needs X-display so I typed xhost +, but it doesn't work. Any ideas to solve this problem? Also, I want to know that Ubuntu literally support Oracle 19c.


texts: Creating a Materialized view with fast refresh and on Demand option

texts:
create materialized view XXARG_GOKU
AS
select
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;

I need a query for creating fast refresh with on demand option. I have already created MLOG$_XXARG_DUMMY and MLOG$_XXARG_DRAGON mlog tables for it.

Please note: the above query is for creating a Materialised view and I'm using oracle 10g DB


texts: Insert values in table from front-end CategoryId

texts:
I want to add a value from front end into a table. I want to do it in asp.net. I have a table which is as below:-

CategoryId     int
CategoryName   nvarchar(50)   not null
ParentId       int            null

Also see the code:-

<asp:TextBox ID="txtCategory" runat="server"></asp:TextBox>

<asp:Button ID="btnCategoryUpload" runat="server" Text="Upload Category" Width="110" />

I tried with my code like this:-

<form id="form1" runat="server">
    <asp:TextBox ID="txtCategory" runat="server"></asp:TextBox>
    <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="110" OnClick="btnSubmit_Click" />
</form>

Code behind for the same:-

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {

    }
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant values (@CategoryName, @ParentId)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text);
    cmd.Parameters.AddWithValue("@ParentId", "");
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
}

But I got the below error, please help.

  The INSERT statement conflicted with the FOREIGN KEY SAME TABLE
  constraint "fk_subcategories". The conflict occurred in database
  "Test", table "dbo.CategoriesForMerchant", column 'CategoryId'. The
  statement has been terminated.

Please help

Table code:-

 create table [dbo].[categories](
    id int identity(1,1) not null,
    name nvarchar(50) not null,
    parent int null,
 constraint [pk_categories] primary key clustered ( id asc))

go

alter table [dbo].[categories]  with check add  constraint [fk_subcategories] foreign key(parent)
references [dbo].[categories] ([id])
go

alter table [dbo].[categories] check constraint [fk_subcategories]
go


texts: count multiple occurences of attribute values for each row in sql

texts:
I have a mytable structured as follows and I would like to count occurences of values for attribute in each row:

id | attribute
--------------
1  | spam
2  | egg
3  | spam

With

SELECT id, attribute, COUNT(attribute) FROM mytable GROUP BY attribute

I only get

id | attribute | count
----------------------
1  | spam      | 2
2  | egg       | 1

But what I would like as a result is

id | attribute | count
----------------------
1  | spam      | 2
2  | egg       | 1
3  | spam      | 2

How to achieve this?


texts: What are system columns used for in PostgreSQL?

texts:
I was following a PostgreSQL tutorial and the author just mentioned that there are system columns in PostgreSQL but did not mention when and why to use them.

Also, in PostgreSQL Documentation it just mentions that there are system columns but does not state when and why to use them?

What are the uses of system columns in PostgreSQL?


texts: Managing and Removing data from huge table

texts:
I have the following scenario:

A huge table, that is also the most use table by far in the system.
By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte array columns that saves files from our system.

The problem is when I need to remove a large number of rows from it.
First I tried just a single delete with in statement, but is froze the whole table (inutilizing the system with it), for about 45 minutes, when I decide to restart the database.

Then I tried deleting by chunks of 10 registries, and tried deleting 8k+ rows. It went really slow, and everywhere that got data from that table was slow too, and when it reached about 700 rows deleted, I started another process that used that same table, and got a deadlock.

How should I make these mass deletions from this table?
I thought about splitting the table in 2, one for the 2 byte arrays that contains the most part of table volume, and other with the rest of the informations (that are simple things like varchars, integers, datetimes and so on). Would doing this benefit me on these cases of deletion? Or the number of rows would still cause these locks on the table?

The version of SQL is Microsoft SQL Server Standard (64-bit) 12.0.5000.0.


texts: I pulled data out of a database and the apostrophe is showing up as "ÔÇÖ".  What character set is this from?


texts:
I'm trying to use the WITH clause and IF statement, but it does not seem to work and I cannot find any reason why it would not. Syntax should be correct and still it is complaining about the syntax.

The error I receive is "Incorrect syntax near ';'. I have tried removing ; characters, but then it says "Incorrect syntax near the keyword 'IF'.

Is it even possible to use the WITH and IF syntax like this? The query does work without the IF statements (with only one of the select queries).

With measurements as
        (
        (SELECT * from measurement_database1)
        UNION ALL
        (SELECT * from measurement_database2)
        UNION ALL
        (SELECT * from measurement_database3)
        );

    IF @device= 'en1' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en1' and op like 'yes'
            );
    IF @device= 'en2' AND @other_parameter= 'no'
        (
        select * from measurements where device like 'en2' and op like 'no'
            );
    IF @device= 'en3' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en3' and op like 'yes'
            );

I'm running Microsoft SQL Server 2012 11.0.6607.3.


texts:
I have a server with Postgresql. I don't have access to this server, just the endpoint to connect with psql. I want to backup data from this server to another server using pg_dump. However, my second server does not have postgresql and I don't have root privilege to install it either.
Is there any way to run pg_dump without install postgresql?

It is possible to install postgresql on my client and pg_dump from it but I'm afraid the database is too big for my laptop.


texts: Save data to variable and use it in stored procedure

texts:
I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this:

SELECT Text
FROM MyTable
WHERE Date>= @userDate;

This works, except when @userDate is bigger than the most recent Date in MyTable. In this case it returns nothing but I need it to return the Text for the most recent Date.

I have to change my query to use the smaller one of @userDate and MAX(Date). Any ideas how to do it?


texts: MySQL - Select Records that matches all values

texts:
If a table "XYZ" contains two columns as combine primary key, I need to select records that contain both Value1 and Value2 in COLUMN_B ( Result: ID4 )
I tried with "IN" query but it returns the records which contain VALUE1 OR VALUE2 in COLUMN_B (ID1, ID2, ID3, ID4).
Can anybody please help me to write a MySQL query that can do this.
COLUMN_A    COLUMN_B

ID1         Value1
ID2         Value1
ID3         Value2
ID4         Value1
ID4         Value2
ID5         Value3
ID3         Value3

Thanks in Advance.


texts: SQL Server prevents me from changing compatibility level

texts:
I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the GUI or T-SQL. If you view the options in the GUI (Right Click on DB, Select Properties, Select "Options" and then look at the "Compatibility level:", it shows options for only past versions 7(70), 2000 (80), and 2005 (90).

This is in production and I cannot simply drop and recreate it as it is constantly in use. When I backed up and restored it to another SQL Server instance (2014) all compatibility levels were available, but that's not an option without taking down all VM's that are tied to it. If all else fails, yes that is an option, but one that brings no joy.

When I run :

     ALTER DATABASE [VIM_VCDB] SET  COMPATIBILITY_LEVEL =  100

I get the following:

     Msg 102, Level 15, State 1, Line 1
     Incorrect syntax near '100'.

This is true for all system DB's on this box also... How to I raise the compatibility level for this? Is it a bad install of the SQL Server 2008 Express?


texts: Moving Replicated database in SQL Server Transactional Replication

texts:
I have a scenario where I need to move the published database (Transactional Replication) to another server, which is having upgraded Windows and SQL Server installed.
Current config is:
Windows 2012 R2 and SQL Server 2012

New config will be:
Windows 2016 and SQL Server 2016.

Just to let you know subscribers are already in SQL Server 2016.

Now the question is, is there any way to move the replicated databases from the old server to the new one without breaking the replication?

Edit:

Forgot to mention the reason behind "not breaking the replication" the thing is we have Temporal enabled at subscriber end, and we don't want to reinitialize the subscribers anyhow.

The main reason for moving is that we want minimum downtime that will be required for patching, so we are building another server with Win 2016 and SQL Server 2016 and thinking of moving all the databases without removing replication.


texts: What's the best way to pass a variable/valuable between steps in a SQL Server job

texts: For example, if I create a dtsx package that fills out an excel sheet, but only email it out if there is actual data?


texts: SQL Server Profiler Exception tracing - Error procedure and line number omitted

texts:
Is there a trace configuration or setting that will enable the profiler to output ERROR_PROCEDURE() and ERROR_LINE() information that is always available when writing manual error handling in CATCH blocks?

Doing a full trace of every statement being executed is very overwhelming on active systems (I got 45k entries trying it on my test system), and even then you have to guess where the error came from based on the preceding entries.


texts: Is there any way to concatenate a value in a column with different strings?

texts:
As you can see on the column 'Race Position' I have a set of values corresponding to the participants results. 1 being 1st Position and so on.

What I wanted was to be able to concat the values and give the first 3 strings as so; 1'st', 2'nd', 3'rd'. The position after will all finish with 'th' at the end for example 4th, 5th, 6th ,7th and so on.

Is there any way to implement this? I have used a cross join in order to make my positions work.