texts: Is it useful to index a column if it is already the first column in a composite index?

texts:
Suppose I have this table (simplified from my project):

create table BillTax
(
    BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID),
    TaxCode varchar(20) not null,
    constraint PK_BillTax primary key clustered (BillID, TaxCode),
    TaxRate decimal (10, 9) not null,
    TaxAmount decimal (12, 4) not null,
);

As you can see, the primary key is a composite of the BillID and TaxCode columns and uses a clustered index for speed. All queries against the table will seek on BillID, but some queries will seek on both BillID and TaxCode. Also, all queries will include other columns (e.g. TaxRate, TaxAmount) as result data. Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?

Cons:

Waste of space
Added complexity
Decreased performance on inserts

Pros:

Index on a single column should be pretty small
Query optimizer should be able to pick the best query either way (but we know the query optimizer is not always as smart as we'd want)

My understanding is that if I query this table on just the BillID column, the query optimizer will still use the clustered index because that index has the BillID column as its first column. And it will be quite fast because even though the index will be a little bigger as it includes the TaxCode column, being clustered will make up for that. On the other hand, one could argue that a single non-clustered index could be faster, if the data coming back is not that heavy. See Nonclustered index is faster than clustered index? for a related question.

I know, I know, try both and measure it yourself. I guess I'm just wondering if there is a major theoretical consideration I'm missing that renders one approach or the other moot.

Note: I found a possible duplicate here, but it deals specifically with PostgreSQL, and the accepted answer relies on some DBMS-specific implementation details like data type storage sizes:
Is a composite index also good for queries on the first field? (sic).


texts:
I am trying to run this update statement, but it keeps giving me the error of

  (3 row(s) affected)
  Msg 157, Level 15, State 1, Line 9
  An aggregate may not appear in the set list of an UPDATE statement.

What do I need to do diff in order to be able to have the sum added to the second field?

Create Table #ABCD
(
  time1 decimal(16,4)
  ,time2 decimal(16,4)
)
Insert Into #ABCD(time1) Values
('12.01'), ('13.04'), ('22.22')

Update #ABCD
SET time2 = SUM(time1/60/60)


texts: How can I force the MySQL client to output to stdout or a log file when importing a SQL file?

texts:
Let's say I am importing a small SQL file using the MySQL client like so:
mysql < 10k-file.sql

The mysql client does not output anything. How can I force it to show how many rows are affected for each query, errors, so on?


texts: If I am not mistaken, SQL Server caches pages in order not to hit the disk each and every time. How does this caching work in the case of shared storage and multiple active SQL servers in a cluster? Since there is more than one active SQL server, how does the server know if the page is still valid? Do we need to disable this caching behavior for shared storage case? If so, how?


texts: Shreded system_health ExEvent not returning all node information

texts:
SQL Server is not returning all node information when I shred the deadlock XML from the 2012 system_health Extended event.

When I run the following query

SELECT  CAST(event_data.value('(event/data/value)[1]','varchar(max)') AS XML) AS DeadlockGraph
FROM    (
        SELECT  XEvent.query('.') AS event_data
        FROM    (    -- Cast the target_data to XML
                    SELECT    CAST(target_data AS XML) AS TargetData
                    FROM      sys.dm_xe_session_targets st
                    INNER JOIN
                            sys.dm_xe_sessions s
                            ON s.address = st.event_session_address
                    WHERE   name = 'system_health'
                    AND     target_name = 'ring_buffer'
                ) AS Data -- Split out the Event Nodes
        CROSS APPLY
                TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent )
        ) AS tab ( event_data )

at our Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 instance all node information is returned along with descriptions i.e.

<deadlock>
  <victim-list>
    <victimProcess id="process10e26bc8" />
  </victim-list>
<process-list>
<inputbuf>
Proc [Database Id = 26 Object Id = 1641213047]
</inputbuf>
</process-list>
  ...
...
...
</deadlock>

However when I run it against our Microsoft SQL Server 2012 - 11.0.5548.0 instance all node information and descriptions are stripped out and only the text is returned i.e

Proc [Database Id = 26 Object Id = 1641213047]

What might cause this? The full XML hierarchy is outputted at our 2012 instance if I remove the SELECT  CAST(event_data.value('(event/data/value)[1]','varchar(max)') part from the query.


texts: Difference between full and level 0 rman backups?

texts:
Suppose we take a full backup, and after that we take a level 0 backup, both using RMAN and Oracle 10g.

I'm confused in the use of the level 0 backup and full backup. I found that the difference between both is only that level 0 backup is used for incremental backups but full backup isn't.

What are benefits of that full backup which we have take before the level 0 backup? How will Oracle use the full backup and the incremental backup?

When we perform recovery, will Oracle use that full backup which was taken before the level 0 backup?


texts: Partial local index on partitioned Oracle table

texts:
We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added.
We need to do selects on this table, over a period of the last five days, and including the accountid.

In order to keep things a bit performant, I suggested to partition the table on day level (done), and create a local index (prefixed or non-prefixed) on accountid.
Now, one of the teammembers says it will not be possible, since they need to create an index on only the last five partitions of the table, and that would require too much effort.

AFAIK, the creation of the index needs to be done only once, and then daily, the index on the new partition only needs to be created. Besides that, indexes on partitions older than 5 days will never be used anymore, so there's nothing to do there.

What are your ideas on this? How to best solve this issue, with performance in mind?


texts: About Global Temporary Table and Stored Procedure in Oracle

texts:
I am confusing with the global temporary table in Oracle:

For example, I want to write a SP MySP which can be used by 5 users at the same time.

CREATE GLOBAL TEMPORARY TABLE temp
     (ID NUMBER, COL1 VARCHAR2(4000), COL2 VARCHAR2(4000));

CREATE OR REPLACE PROCEDURE MySP
(
  id NUMBER
, input1 varchar2(4000)
, input2 varchar2(4000)
)
AUTHID CURRENT_USER IS

sql_stmt VARCHAR2(4000);
BEGIN
   sql_stmt := 'INSERT INTO temp VALUES ('
             + TOCHAR(ID,99) + ','
             + input1 + ','
             + input2
             + ')';
   execute sql_stmt;

END MySP;

My question is:
  When the five user run the SP at same time, will they  not interrupt with each other because they use the same global temp table?


texts: Microsoft SQL Server partitioning with on premise server

texts:
I'm planning for a small hosted ASP.NET application which uses a Microsoft SQL Server 2008 database. One of the users which uses the application doesn't want his data to be stored in a remote database and has requested to store his rows of the database on his on premise database server.

First, is this possible with SQL server? And if it is, is this even safer if the application is still running on a remote server and the data is used there (application has to load and transfer data from on premise server to remote server, so the data could potentially be loaded by anyone)?


texts: mysql workbench can't write to sql history file

texts:
I'm running mysql workbench on my local machine, connected to a remote sql server.

I am able to apply changes to the database, however, I see this error every time I apply a change

I can't seem to find any help online regarding this error, so I can't provide any more details

Error: There was an error while applying the SQL script to the database.

and in the message log
ERROR: Can't write to SQL history file


texts: Cassandra security - private IPs with some VPN solution or public IPs and open ports?

texts:
there are many different solutions to secure a cassandra cluster. One of them is to enter public IPs and open the 7000/9042 port on each sever.

Another one is to have everything in a private network with private IPs and connect the nodes with some kind of VPN solution. I have OPNsense with Wireguard for this, but I'm wondering if this makes any problems in production.

What are you guys using to prevent attacks on public ports?

Thank you so much.


texts: Is this update, select combination query thread safe?

texts:
I have a table called seats, which has a schema like this

  id,taken

For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am using

   update seats u inner join  (
        SELECT id from seats
        where taken is null limit 1) s
   on s.id = u.id set taken = 1;

This query takes a random seat with taken flag null, and for that seat it makes the flag 1. While this query is working fine, is this  thread safe?

Consider this scenario, I have two users in parallel. For user1, I select row X, and just before update query is ran user2 checks in, and for that user select query returns the same row as user1. So I will end updating the same row twice.

Is this scenario possible with this query?


texts:
I'm trying to run this code in sqlite:

sqlite> CREATE TABLE test(id integer primary key AUTOINCREMENT, page varchar(10), dp_record_id integer, foreign key (webserver_id) REFERENCES target_dp(id));

But I'm getting the following error message:

  Error: unknown column "webserver_id" in foreign key definition

Here's the parent table:

sqlite> .schema
CREATE TABLE target_dp(id integer primary key AUTOINCREMENT, server_name varchar(255), location_code varchar(10), active bit(1));
sqlite>

I can't see where my syntax error is.
Thanks in advance for your time.


texts: Migrate MySQL to another partition in same server

texts:
I have a instance of MySQL in a local server. To improve the performance, I put a SSD just for MySQL.

Now I need migrate the MySQL instance, with my databse (a data has 25GB) for the SSD.

What the best way to do that?
I thinked in export the data and after reinstall the MySQL in SSD and import the data again. But how is a big database, I will have a large downtime.

Are there a another way?


texts: Calculating a cumulative sum with an alias in postgresql

texts:
I have two tables - user credit and payments.
credit is just a total of the amount of credit a user has at the present.
Payments is a list of payments by date that a user needs to make.
I need to create a report showing the amount of credit available for each payment a user is to make - taking into account the credit used up on previous payments.
CREATE TABLE credit (
  user_id INT,
  amount INT
);

CREATE TABLE payments (
  user_id INT,
  due timestamp,
  amount INT
);

INSERT INTO credit values (1, 100);
INSERT INTO credit values (2, 200);

INSERT INTO payments values (1, '2021-04-01', 20);
INSERT INTO payments values (1, '2021-04-02', 20);
INSERT INTO payments values (1, '2021-04-03', 20);
INSERT INTO payments values (2, '2021-04-01', 100);
INSERT INTO payments values (2, '2021-04-02', 300);
INSERT INTO payments values (3, '2021-04-03', 20);

Result should look like this:

user_id
due
amount
credit_available
credit_used
credit_remaining

1
2021-04-01
20
100
20
80

1
2021-04-02
20
80
20
60

1
2021-04-02
20
60
20
40

2
2021-04-01
100
200
100
100

2
2021-04-01
200
100
100
0

3
2021-04-01
20
0
0
0

I have set up a fiddle here:
http://sqlfiddle.com/#!17/e812b/9/0
I thought I could do it pretty simply with a LAG() but I can't reference the previous row's calculated alias credit_remaining column within the select like so:
SELECT
    ...,
    least(0, payments.amount - LAG(credit_remaining, 1, credit.amount) OVER (PARTITION BY user_id ORDER BY user_id, due)) as credit_remaining
FROM ...


texts: How to check active connections in PostgreSQL?

texts:
All connection are in LAN.
I want to know IP Adresess and if it is possible toogether with ProcessID of All active connection (to all databases).
How to check active connections in PostgreSQL ?


texts: SQL Server 2008 R2 Express with management tools installation failure

texts:
I've been trying to install SQL Server 2008 R2 with tools.

I've downloaded thisn version and it just won't work!

I've had some problems installing Visual Studio SP1 due to silverlight4 being already installed, so I figured i needed to uninstall everything that had something to do with sql server, so I did.

Still the errors show up.

These 2 errors show up at the beginning of the installation during "Setup support files".

(I can't post more than 2 url's yet, so Ive put all the screenshots in 1 image, sorry)

Here

The first error is the top left one, and retry fails, and when I hit cancel a few secs later the second error pops up and the installation terminates.

My OS is W7 x64 with all updates installed.

I don't really know how to go on actually. I've searched for hours yesterday without any results.

Anybody got a clue? Before I reinstall every .net-related programs.

So far I've tried:

I checked if the first file shown in error 1 is actually there, and it is.
Tried to run the setup explicitly as administrator
I checked if the second file/folder was present, and it was not. So I manually created the folder and file, which then causes the
first error to say the same, but then it says can't create
\SqlSupport_Cpu64_1_ComponentUpdate_1.log, then I tried
to create an empty file with that name and extention. This makes the installer
append the filename with "_1". If I create that file too, it just
appends with a higher count.
Uninstalled every sql server related program (e.g sql server compact)
Reset all the permissions on the e: disk and formatted it.

Thanks in advance!


texts: DBCC CloneDatabase - sys.partitions rows value wrong

texts:
I'm using DBCC CLONEDATABASE to create a shell of a schema.
One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions.
as a matter of fact, the values of the row column are the ones I had in the source database.

Do you know if this a bug or "as designed"?
Do you know any other way to "refresh" the sys.partitions DMV without running TRUNCATE to all tables?

Code to reproduce -
USE [master];
GO
SET NOCOUNT ON;
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue')
BEGIN
    DROP DATABASE [CloneDBIssue];
END
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue_Clone')
BEGIN
    DROP DATABASE [CloneDBIssue_Clone];
END
GO
CREATE DATABASE [CloneDBIssue];
GO
USE [CloneDBIssue];
GO
--13213
SELECT  M.message_id,
        M.language_id,
        M.severity,
        M.is_event_logged,
        M.text
INTO    dbo.CDB_messages
FROM    sys.messages M
WHERE   M.language_id = 1033;
GO
CREATE CLUSTERED INDEX CIX ON dbo.CDB_messages(message_id);
GO
SELECT  'After Insert - Source DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
DBCC CLONEDATABASE (CloneDBIssue, CloneDBIssue_Clone);
GO
USE master
GO
DROP DATABASE CloneDBIssue;
GO
USE [master]
GO
ALTER DATABASE [CloneDBIssue_Clone] SET  READ_WRITE WITH NO_WAIT;
GO
USE [CloneDBIssue_Clone];
GO
SELECT  'dbo.CDB_messages' [Table],COUNT(*)[Rows]
FROM    dbo.CDB_messages;

SELECT  'After Clone - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
INSERT dbo.CDB_messages
VALUES (0,0,0,0,N'');
GO
SELECT  'After Insert - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);

GO
DELETE TOP(3214) FROM dbo.CDB_messages;
GO
SELECT  'After DELETE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
TRUNCATE TABLE dbo.CDB_messages;
GO
SELECT  'After TRUNCATE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO


texts:
I have a report where I need to add totals for a column and group them by company selected. I have to do this in T-SQL for 4 ranges:
0-3 Months, 3-6 months, 6-12 months, >12 months
I am confused about how this would be implemented in T-SQL.
There is a balance column on which I have to total.
The problem I am not able to understand is summary over those date ranges. How can this be done in T-SQL?

The user should input the company choice and a date and based on it, I have to generate summary of balance column within the ranges specified, which were
0-3 Months, 3-6 months, 6-12 months, >12 months

I am thinking of it this way: I am thinking of it this way:
Make ranges of date, so : suppose @date is user selected date
then:

DECLARE @daterange1start DATE = @date
DECLARE @daterange1end DATE = (SELECT DATEADD(m,3,@daterange1start))
DECLARE @daterange2start DATE = @daterange1end
DECLARE @daterange2end DATE = (SELECT DATEADD(m,3,@daterange2start))
DECLARE @daterange3start DATE = @daterange2end
DECLARE @daterange3end DATE = (SELECT DATEADD(m,6,@daterange3start)) '

And then using case

SUM (CASE WHEN  dueDate BETWEEN @daterange1start AND @daterange1end THEN    Balance ELSE 0 END) AS '0-3 Months',
SUM (CASE WHEN dueDate BETWEEN @daterange2start AND @daterange2end THEN Balance ELSE 0 END) AS '3-6 Months',
SUM (CASE WHEN dueDate BETWEEN @daterange3start AND @daterange3end THEN Balance ELSE 0 END) AS '6-12 Months''

Am I thinking of it the right way in terms of a correct solution?

Thank you.


texts: Relational schema and integrity constraints

texts:
May I ask if these integrity constraints can be created over this schema using create script?

club (klub) can have exactly one coach (trener)
club can have just one owner (majitel)
each member (člen) can be in just one club
and a coach can set tactics (taktika) only for his club?

Is it possible to solve this with integrity constraints in the create script at all or are triggers used for it? I am a part of my create script.
Schema

Create script
CREATE TABLE club (
id_club SERIAL NOT NULL,
nazev VARCHAR(256) NOT NULL,
datum_zalozeni VARCHAR(256) NOT NULL
);
ALTER TABLE club ADD CONSTRAINT pk_club PRIMARY KEY (id_club);
ALTER TABLE club ADD CONSTRAINT uc_club_nazev UNIQUE (nazev);

CREATE TABLE coach (
    id_member INTEGER NOT NULL
);
ALTER TABLE coach ADD CONSTRAINT pk_coach PRIMARY KEY (id_member);

CREATE TABLE member (
    id_member SERIAL NOT NULL,
    id_club INTEGER NOT NULL,
    jmeno VARCHAR(256) NOT NULL,
    prijmeni VARCHAR(256) NOT NULL,
    narodnost VARCHAR(256) NOT NULL,
    datum_narozeni VARCHAR(256) NOT NULL
);
ALTER TABLE member ADD CONSTRAINT pk_member PRIMARY KEY (id_member);

CREATE TABLE owner (
    id_member INTEGER NOT NULL
);
ALTER TABLE owner ADD CONSTRAINT pk_owner PRIMARY KEY (id_member);

CREATE TABLE player (
    id_member INTEGER NOT NULL,
    cislo VARCHAR(256) NOT NULL,
    post VARCHAR(256) NOT NULL
);
ALTER TABLE player ADD CONSTRAINT pk_player PRIMARY KEY (id_member);

ALTER TABLE coach ADD CONSTRAINT fk_coach_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE;

ALTER TABLE member ADD CONSTRAINT fk_member_club FOREIGN KEY (id_club) REFERENCES club (id_club) ON DELETE CASCADE;

ALTER TABLE owner ADD CONSTRAINT fk_owner_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE;

ALTER TABLE player ADD CONSTRAINT fk_player_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE;


texts: Oracle 11g setup closes without errors on pre-requisite check

texts:
I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.

I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free space
Windows 7 64bit
Oracle Administrator 64bit
No error in Event Viewer
No error in Installation log in Inventory\logs folder
Tried Run as Administrator also but didn't help

Any ideas?


texts: MariaDB: SQL query to return array of json objects and remove `\` from response

texts:
I am creating a sql query for api response in javascript.

But i stuck on one part that is creating a array of json objects of same group.

I have checked with the json_arrayagg and also json_objectagg but can not able to create an array of json objects.

Below is a sql query in which i have also tried with the JSON_ARRAY and JSON_OBJECT methods.

public async getProducts() {
  try {
  let productTbl = '`products`';
  let prodImgTbl = '`product_images_ids`';
  let productImg = '`product_images`';

  let query = `SELECT
  ${productTbl}.id AS productId,
  GROUP_CONCAT(JSON_ARRAY(JSON_OBJECT(
    "imageId", ${prodImgTbl}.id,
    "imageUrl", ${productImg}.thumbnail_image
  ))) imageData,

  FROM ${productTbl}
  JOIN ${prodImgTbl} ON ${prodImgTbl}.product_id = ${productTbl}.id
  JOIN ${productImg} ON ${productImg}.id = ${prodImgTbl}.product_image_id
  GROUP BY ${productTbl}.id `

  let records = await pool.query(query);
  return records
  } catch (error) {
  throw error;
  }
}

Using this query i am getting the result in postman as below which is adding the \ to the each key and value.

this.productList = [
  {
    "productId": 3,
    "imageData": "[{\"imageId\": 17, \"imageUrl\": \"/assets/images/321/1/1/1/image.jpg\"}],[{\"imageId\": 18, \"imageUrl\": \"/assets/images/322/image.jpg\"}]",
  }
]

So the result that i wanted to render is as below, so that i can loop through the records.

[ RowDataPacket {
    productId: 3,
    imageData [
      {
        imageId: 18,
        imageUrl: /assets/images/321/image.jpg
      },
      {
        imageId: 17,
        imageUrl: /assets/images/322/image.jpg
      }
    ]
  }
]

P.S: MySQL version: 5.5.5-10.3.22-MariaDB

Question Update:

SQL Query run in the adminer tool

SELECT
`products`.id AS productId,
GROUP_CONCAT(JSON_ARRAY(JSON_OBJECT(
  "imageId", `product_images_ids`.id,
  "imageUrl", `product_images`.thumbnail_image
))) imageData
FROM `products`
JOIN `product_images_ids` ON `product_images_ids`.campaign_id = `products`.id
JOIN `product_images` ON `product_images`.id = `product_images_ids`.product_image_id
GROUP BY `products`.id

And here is a result screenshot that i am getting.


texts: Does UPDATE without WHERE clause lock a table in PostgreSQL?

texts:
Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted?

E.g. if I run
UPDATE t1 SET key = 'value'
Can I expect no new rows will be inserted into t1 during the UPDATE execution?

If no, can I expect UPDATE will update even the rows appeared after its start? (key has no DEFAULT 'value' in its definition)


texts:
I have a Table which some nut head had created. The  data in it is not more than varchar(100) but he has created the column as CLOB.

Need to change it to Varchar (100). The below query does not seem to work.

ALTER TABLE test alter Column VARCHAR_CLOB set data type VARCHAR(100);

it gives the following error

ALTER TABLE "BASE.test" specified attributes for column "VARCHAR_CLOB" that are not compatible with the existing column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.18.60


texts: Loads of parallel inserts (OLTP) into heap table - Operation stops every now and then for 2-3 seconds

texts:
We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, i.e. add one to the click_events table, adds a row to the devices_seen table etc.

The main, large tables are those raw tables that store events (e.g. click_events). Those have 1-2 indexes but no clustered index and an auto-increment PK. They sed to have clustered indexes (hence the identity PK) but were later removed (many months ago).

The problems we are facing are as follows:

1) The processors that pick the events take 30-50 ms for each register (the merges). Often - every few seconds - the process stops / locks for 2-3 seconds and that event takes that amount of time to register. Why is that? Is there anything related to the database that we are missing? We are running the profiler but nothing really stands out.

2) Our heap tables get super fragmented often. I thought that on inserts only they won't get any fragmentation. Are we better off creating clustered indexes on those identity columns and let the DB do its job?

Would appreciate any help.

Thanks


texts:
How can I configure SQL Server 2012 to use multiple NICs?

I've a server with 4 NICs. I want to use 2 NICs to query my databases, and 2 NICs to retrieve results from them.

I want them to work in load balancing mode and still continue to work if one nick is down.

It is possible?


texts: Why do ASC and DESC come *after* a CASE clause?

texts:
This query works:

DECLARE @Ordering NVARCHAR(50)
SET @Ordering = 'aggregateid'

DECLARE @OrderDirectionInt INT
SET @OrderDirectionInt = 0 --1

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 1 THEN AggregateId END DESC,
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 0 THEN AggregateId END ASC
        ) AS RowNumber,
        MyField
    FROM
        MyTable
) AS TempResults
WHERE TempResults.RowNumber BETWEEN 12 AND 23

What I don't understand is why the ASC and DESC are after (or outside of) the CASE statements.  It looks like the ASC and DESC are being put in unconditionally, which doesn't make sense... it might resolve to:

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

Why doesn't the CASE statement contain the ASC or DESC (if you try to do that you get a syntax error)?


texts: MySQL - Creating a user and specifying a password

texts:
How do I create a user and specify a password in one line:

I thought CREATE USER 'foo'@'2.2.2.2' IDENTIFIED BY 'bar'; would do it but apparently it doesn't.

Any thoughts? I'm on version 5.1.49 of MySQL.


texts: MySQL 8.0.18 - ST_Area returns invalid value

texts:
I have a problem with ST_Area function in MySQL 8.0.16.

The result of the procedure call returns a different result than any library used to calculate the area of a polygon.

Example:

I have a GeoJSON with geometry like this:

{
        "type": "Polygon",
        "coordinates": [
            [
                [
                    16.91720977783202,
                    52.28469952907162
                ],
                [
                    16.910300407409657,
                    52.278608423688205
                ],
                [
                    16.919913444519032,
                    52.27753189128164
                ],
                [
                    16.91720977783202,
                    52.28469952907162
                ]
            ]
        ]
    }

The results of calulating area (in square meters) are:

Google Maps: 250165.80185391553
Turf library: 250152.24833417358
ST_Area(ST_GeomFromGeoJSON(@poly_json)): 202818.55768974536

First I was worried that GMaps (we got the coordinates from a map) is using some other projection, but it converts it into proper LatLng, so it shouldn't be a problem.

Do you have an idea why the result is so different between calculating area on the database and on the client?


texts: Inserts not propagated in transactional replication

texts:
I have a transactional replication configured which subscription fails to sync to often due to 20598 errors. I've enabled verbose logging on the Distribution agent, nothing more detailed captured than the message that the UPDATE fails due to the row missing at the Subscriber.
I've run profiler on the Subscriber side and verified that very often INSERT commands are not being replicated for the articles. But UPDATE commands do, and the 20598 error occurs to often. I can't just insert the missing rows all the time for to fix the process. Currently, I have an automated PowerShell solution that inserts these missing rows as 20598 errors appear. But in need to find the source of the problem.
The articles are configured properly, inserts are to be propagated by calling the procedure sp_MSIns_{article_name}.
I tried to extract all the commands that are being inserted in the distribution database for to be replicated by the Log Reader Agent, from the MSRepl_commands and MSRepl_transactions, but I had difficulties decoding the command from the MSRepl_commands without using sp_browsereplcmds (I am trying not to call this procedure all the time but run a job to capture what everything is being captured for replication and be able to search that result set just to confirm that the missing INSERT commands are really missing and are not even brought to the distributor server).
I am using a Remote Distributor, one Publisher server and one Subscriber server. The tables I am replicating are around 100 Gb in size and grow fast.
How can I debug this further?
Thanks in advance


texts: Log shipping large DBs - what about the log?

texts:
I'm currently setting up log shipping of a large DB (Approx 1.5TB) and am wondering what I can do about the log file.

As it stands, I want to do the following steps:

Change DB to FULL recovery
Take FULL backup (5-6 hours) on primary
Restore FULL backup to secondary (leaving in NORECOVERY)
Take DIFF backup on primary
Restore DIFF backup to secondary (still in NORECOVERY)
Initialize log shipping using 'The database is already initialized'

The issue is that while I am taking the full backup, the log file will fill up faster than the backup(s) can complete.

What options do I have to keep the log file from filling up?  Can I just take log backups as normal during the FULL backups as the DIFF restore will cover any transactions that take place during that timeframe?  Has anyone done this before with a DB of this size, any tips/tricks to make it easier?


texts: If the Database is in Readonly mode how can we drop/remove those unused roles

texts:
In SQL Server, if the Database is in Read_only mode, can we drop/remove unused roles? Please let me know.

Thanks in advance


texts: Aborted connection - Got an error reading communication packets

texts:
I'm using MySQL Community Server 5.7.15-log and MySQL Connector/ODBC 5.3.6 on Windows 2012 and getting a lot of these messages:

  [Note] Aborted connection X to db: 'Y' user: 'Z' host:
  'localhost' (Got an error reading communication packets)

I have max_allowed_packet set to 1G:

mysql> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.14 sec)

mysql>

Any advice?


texts: db2pd: Database mydb not activated on partition 0

texts:
As a user with DBADM privileges I enter

db2pd -db mydb -locks showdetail

from the DB2 CLP. The command then fails with message

Database mydb not activated on database partition 0.

Option -locks requires -db <database> or -alldbs option and active database.

I verified that the database is active:

C:\Windows\system32>db2 list active databases

                           Active Databases

Database name                              = MYDB
Applications connected currently           = 0
Database path                              = C:\DB2\NODE0000\SQL00002\

I also googled the error message and found an IBM support page for TSM which suggests my environment variable %db2instance% might not be set appropriately. However, everything appears to be in order:

C:\Windows\system32>set %db2instance%
DB2CLP=DB20FADE
DB2INSTANCE=DB2
DB2PATH=C:\Program Files\IBM\SQLLIB

What other reasons could there be, that db2pd is giving me the above error message?

I'm running DB2 v9.7.4 on Windows 7 Embedded.


texts: Can you insert a value from a range in mysql?

texts:
So basically I have one auto incrementing column which is increments every time a ticket has been sold. However I also have 50 chairs inside the room. since I can't use auto_increment 2 times is there another way to select one of the 50 chairs. basically something like

mysql_query(INSERT INTO table (`chair`) VALUE (Range(1, 50) as long as picked number not taken))


texts:
I'm trying to design the database model for my application and I'm finding it difficult to design the mappings for user access to various entities stored in the database.

If access was only controlled at a user level my diagram would look like this:

The user mapping table would contain the relevant information about the permissions which any particular user has to any particular McGuffin.

The problem is that the user belongs to an organisation and permissions can be granted at either level.  So for example there may be no user mappings, but a user may have access via their organisation which would lead me towards something like this:

But this doesn't feel great, the real case has more than 1 entity and possibly more than 2 hierarchies for user access (e.g. it may end up being Org -> Group -> User) so I feel like I'm going to land myself in a mess.  My current leaning is towards no explicit mapping in the relational model and instead using a general mapping table outside the model which specifies the entity, level and permissions. It would look a bit like this:

Accessor   AccessorID  Entity       EntityID    Permission
User       1234        McGuffin     678         ReadOnly
Org        321         McGuffin     456         ReadWrite

This also feels a bit iffy as I'll have no enforced integrity for the user, org or entity id's in the mapping table.

Whichever approach I take will rely heavily on program logic to determine the precedence and application of the rules so am I right to leave referential integrity out of this specific part of my database design?

NB. This isn't a question of database user access and security, it's about using the database to store information which happens to be about security.


texts:
I am trying to build a query for a report. The report will have a few fields that can be filtered, one of which is a jobStatus field, it is an enum.

Here is my query

SELECT * FROM jobs j
LEFT JOIN property p ON j.propertyID = p.propertyID
LEFT JOIN property_buildings b ON b.buildingID = j.buildingID
LEFT JOIN departments d ON j.dptID = d.dptID
LEFT JOIN jobs_timeframe jt ON j.jobTimeframeID = jt.jobTimeframeID
LEFT JOIN jobs_type t ON j.jobTypeID = t.jobTypeID
LEFT JOIN floors f ON f.floorID = j.floor
LEFT JOIN (
    SELECT group_concat(a.assetNo) AS assets, ja.jobID
    FROM jobs_assets ja
    INNER JOIN assets a ON a.assetID = ja.assetID
    GROUP BY jobID
    ORDER BY assetNo ASC
) ja ON ja.jobID = j.jobID
LEFT JOIN (
    SELECT group_concat(o.obsNo) AS observations, oj.jobID
    FROM observationJobs oj
    INNER JOIN observation o ON o.obsID = oj.obsID
    GROUP BY jobID
    ORDER BY obsNo ASC
) jo ON jo.jobID = j.jobID
WHERE j.ClientID = :clientID AND j.jobStatus = :status
LIMIT :limit OFFSET :offset

Here are my bound parameters:
:clientID => 14,
:status => 'Draft',
:limit => 25,
:offset => 0

If i exclude the j.jobStatus it works fine and shows 19 results with a mix of jobStatus' including Draft, Finished and Approved If i include try and filter by j.jobStatus at all using PDO i get a successful query but with zero rows (should be 14 rows for Draft, 1 for Approved, 3 for Finished and 1 null)

if i unsafely just put the variable for :status in the query
(like this WHERE j.ClientID = :clientID AND j.jobStatus = '.$status.' LIMIT :limit OFFSET :offset)
it works fine and shows the 14 expected results. I can not for the life of me figure out why.

The only thing I can think of is it is something to do with it being an enum, but i was under the impression that they are treated as strings for everything except the actual storage.

EDIT:
binding code:

$stmt->bindParam(':limit', $lim, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':status', $params[':status'], PDO::PARAM_STR);
$stmt->bindParam(':clientID', $params[':clientID'], PDO::PARAM_INT);


texts: How to make psql restore of a database ignore the owner information?

texts:
My database is associated with different usernames on the server and on my local machine. Whenever I try to restore it on my local machine from the backup downloaded from the server using the command
psql -d dbname -f backupfile.sql

I get the error output saying that
ERROR: role "myusername" does not exist

Despite the error message, the actual restore goes through. But it is annoying to see multiple error messages saying the same thing (once for each table). Is there a way I can suppress this error message. I searched quite a bit but could not find anything. There is no-owner option for pgrestore, but I could not find anything for psql.
Thank you for your help.


texts: dangers of using SHRINKFILE in sql server 2008

texts: I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are


texts:
I came across this issue while troubleshooting a problem that I described in another post, but I thought this deserves a post of its own.

I'm trying to reclaim some unused space in a table. This is the current disk usage report for the table:

This is the current disk usage report for the database:

I'm aware that shrinking a database is generally considered bad practice, but I wanted to see what kind of effect it would have in this situation.

I executed this command:

DBCC SHRINKFILE("MyDbFile", 0)

Here is the result of the command:

Now here are the disk usage reports after the shrink:

Why does it seem like the shrink operation had virtually no effect? I have read that this operation can't shrink a database below it's initial size, but the mimimum database size seems to be set to 640 KB.


texts: Shrinking the database partition if autogrow is turned off?

texts:
I want to know:

Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size instead of autogrow?
If the above are recommended.


texts: How can I improve a simple join on a very small table? (postgres)

texts:
I have a large table with a foreign key to a smaller table:

CREATE TABLE cars (
  make_model_id INTEGER INDEXED REFERENCES models (make_model_id),
  color TEXT INDEXED,
  year INTEGER INDEXED,
  details TEXT
);

CREATE TABLE models (
  make_model_id SERIAL PRIMARY KEY,
  make TEXT,
  model TEXT
);

cars is hundreds of millions of rows, while models has about 30 rows total.  Most queries look like this:

SELECT * FROM cars JOIN make_model USING (make_model_id)
WHERE
  color = 'green' AND
  year = 2015 AND
  make = 'Honda';

Looking at EXPLAIN, the query planner does a nested loop to join the two tables rather than collecting the rows from models and using the index on cars.  If I manually run SELECT make_model_id FROM models WHERE make = 'Honda';, that result is instant, then if I use the result in the original query with an IN clause, it shortens the run time by more than half.  Is there a way to do this two-step process in a single query?

I've tried moving the query to an in-line clause (make_model_id IN (SELECT make_model_id FROM models...) and as a CTE, but both yield near-identical performance to the original query.


texts:
I have this code that sums up the qty for a certain item (itemid) and by its product date code (proddte).

select sum(qty), itemid, proddte
from testtable where ....
group by itemid, proddte

What I want to do is to get the total of all qty regardless of itemid/proddte. I have tried:

select sum(qty), itemid, proddte, sum(qty) over() as grandtotal
from testtable
where ....
group by itemid, proddte

But it says I should also have qty in the group by clause. If I did that, the result will not be equal to my expected result.

It does not absolutely need to be represented as a separate column, with the same value in every row. Any representation is accepted as long as I can display the overall total.


texts: Generic Trigger on MYSQL to replicate data on other table

texts:
I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed.

CREATE TABLE `competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `credit_hour_equivalent` double NOT NULL,
  `description` text,
  `book_name` varchar(255) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  `statement_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `competencies_domain_id_index` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;

CREATE TABLE `history_competencies` (
  `id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `credit_hour_equivalent` double DEFAULT NULL,
  `description` text,
  `book_name` varchar(255) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  `statement_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `author_action` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For every INSERT on competencies I want to copy the row into history_competencies.

So I created the following trigger

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW.`id`,
               NEW.`title`,
               NEW.`type`,
               NEW.`credit_hour_equivalent`,
               NEW.`description`,
               NEW.`book_name`,
               NEW.`domain_id`,
               NEW.`created_at`,
               NEW.`updated_at`,
               NEW.`slug`,
               NEW.`video_id`,
               NEW.`statement_id`,
               NEW.`author_id`,
               'CREATE');

My question aims to find a better way to write the trigger so I don't have to DROP and CREATE the trigger every time that I change the competencies table or reuse for other tables just changing the table names.

I was looking for something like

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW.*, 'CREATE');

Or

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
  INSERT INTO history_competencies
  VALUES      (NEW, 'CREATE');

But it didn't work.

Do you know if this is even possible?

Thank you!


texts: How to tackle a large undocumented database

texts:
I recently got hired as the only IT Guy in a certain Company X and I am tasked to fix their applications, and in my opinion, the best way to start is by understanding the database.

Their current database is a MySQL database with 186 tables(note that some tables are empty for god knows why). And the application is communicating with the database through an MS Access database interface. (I ask myself why the developers did that too)

Question is, How do I start tackling this large undocumented database? Yes, it is undocumented because the application's developers aren't willing to give me an ERD or data dictionary or any information at all about the database to make my life easy. How would you suggest to take on this perilous endeavor of understanding every nook and cranny of the rather large database?

Related Question: How to dive into an ugly database?


texts:
I'm using Oracle 11g database in a Windows environment. I've installed Oracle database software only, but now I'm in need of enterprise manager. Initially I tried to start them using the below commands.

C:\>set ORACLE_UNQNAME=testdb

C:\>emctl status dbconsole

This didn't work as my service for DB console was not up. Then, I tried starting the service which showed up the following error.

Can anyone please help me in resolving this error?


texts: MySQL - fastest way to ALTER TABLE for InnoDB

texts:
I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.

I want to change the name of one of the columns and add a few more indices.

What is the fastest way to do it (assuming I could suffer even downtime - the server is an unused slave)?
Is a "plain" alter table, the fastest solution?

At this time, all I care about is speed :)


texts: Export table from MS Access to Excel while retaining Field Name and disregarding Caption

texts:
I have a MS Access Database, with each table containing the data of a particular questionnaire. I am building data entry forms for each of these tables, but as the field names may prove to be confusing for end users to comprehend, I have used the Caption property to specify terms that are much more friendly (these terms are automatically added as labels while building a form).

However, once I have done that, I realized that when I export the tables from MS Access to Excel, the resultant Excel file contains column names that are based off the Caption property instead of the underlying Field Name.

Is there a way that I can use Caption for my forms and the underlying Field Name when exporting tables to Excel?

Thanks!


texts: Connecting to mysql using management tool

texts:
So far I have set up my MySQL database only in the terminal using a homebrew.

Now I want to connect to the database and access all of its tables using Sequel Pro, which is a database management tool.

I'm wondering how I can connect to my local database that I have created on my terminal; Sequel Pro gives three types of ways you can connect : standard, socket, and ssh.  I'm not sure which I should select.


texts:
My problem is for Postgresql 10 but it probably is also relevant for other DB systems.

I have several tables in which I need to identify groups based on several criteria (some of them are PostGIS-geospatial criteria, some other are shared values in certain columns, ... that don't matter).

I am going to take a simple example. Let's say I have I have first table with people:

CREATE TABLE employees
(
id serial PRIMARY KEY,
name varchar(255),
desk_number varchar(20)
)

and that I want to group all employees that share the same desk (field desk_number).
Illustration :

employees
--------------
id | name | desk_number
1  | Bill | 314
2  | Joe  | 200
3  | Bob  | 314
4  | Matt | 189
5  | Sam  | 314
6  | Anne | 150

In this example, what I want to have is the list of employees sharing desk number 314 : Bill, Bob and Sam (id: 1, 3, 5).

My current query for finding this is :

WITH findpairs AS
(   -- part 1 of the query : selecting pairs
    SELECT s1.id AS id1, s2.id AS id2
    FROM employees AS s1
    JOIN employees AS s2
    ON s1.desk_number = s2.desk_number
    WHERE s1.id < s2.id
)
-- part2 of the query : filtering
SELECT s1.id1
FROM findpairs AS s1
LEFT JOIN findpairs AS s2
ON s1.id1 = s2.id2
WHERE s2.id2 IS NULL
;

Explanations :
In the first part of the query, I find all pairs of rows that for employees that share the same desk.

Without the WHERE clause, I would obtain as a result:

id1 | id2
 1  | 3
 3  | 1
 1  | 5
 5  | 1
 1  | 1
 2  | 2
 3  | 3
 4  | 4
 5  | 5
 6  | 6

The WHERE clause "s1.id < s2.id" prevents returning matches for a row with itself and makes sure that for two rows matching each other, only one row will be returned.

With this WHERE clause, what the part 1 of the query returns is :

id1 | id2
 1  | 3
 1  | 5
 3  | 5

I only two pairs of rows (1-3 and 1-5) to define the group I need ; the row 3-5 is unnecessary. That is why the second part of the query also does some filtering : because of the previous "s1.id < s2.id" clause, I know the lowest of the id values is only present in s1.id and never in s2.id. By using a LEFT JOIN, I can identify these rows from the set returned by part 1 and discard the other ones.

The final result returned is :

id1 | id2
 1  | 3
 1  | 5

And I can store this as such in a specific table :

CREATE TABLE matched_employees
(  id1 integer, id2 integer )

Question 1 : is there a less tedious way to do this ?
This takes a hell of a time to run on my server with my dataset.

Question 2 : instead to store the result in a table with pairs ( matched_employees ). Is there a better way to store this information (and easily access it later) ?


texts:
I'm working on a project that is using the instagram key format.

TL;DR 64-bit integer ids.

They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort by creation time.

The values are between 2^63 and 2^64, so (just) too big to fit inside a BIGINT.

So it seems our options for storage are numeric(20) or varchar. varchar is not as ideal since we'd have to zero pad them for sorting to work, but would there be a performance hit to using a numeric for lookups?


texts: After I re-installed SSRS 2014 (with default config) I get HTTP 404 when I try to access the ReportServer or Reports pages

texts:
I have SQL Server 2014 Express with Advanced tools installed. I had a previous installation, but I uninstalled the SQL instance and Reporting Services and then re-installed them.

Right now, I'm unable to access http://localhost/Reports or http://localhost/ReportServer and I get a

  HTTP 404 Page not found error.

Initially, probably stupidly, I thought it was because IIS isn't installed, so I installed that, but that didn't do anything.

I also tried changing the virtual directory names then applying, but that also didn't work. What am I missing?


texts: Does my database need to connect to a server?

texts:
I'm very new to SQL and databases. I'm currently following this tutorial for creating a Magento website: https://www.tutorialspoint.com/magento/magento_installation.htm

and I'm on Step 4. It says to create a new database for my site (which I'm running on localhost/magento right now). I downloaded MySQLWorkbench for this, and when I click "Create new connection", I put localhost/magento into the "hostname" section.

I'm just not sure how I'm supposed to create a database for my specific website, and if it needs to be connected to the URL.

Hope somebody can help me out in simple terms :)
Thanks.


texts: What makes SQL Server prefer page locking over row locking?

texts:
I'm probably dumb for asking this, but I'm wondering WHY would SQL Server prefer page locking over row locking when running DML queries.

In my specific scenario, I'm seeing in various deadlock graphs that queries that should S lock 400-500 rows are choosing to page lock instead - entering the deadlock in the first place. Note that I have not disabled row locks in our indexes. Moreover, I don't really think I'm missing indexes as the only suggestion coming from SQL are of the "create a nonclustered index on every column in this table" sort.

To be clear: I'm not asking for help on solving our deadlocks (not yet at least), I just want to understand if I'm missing something to try and foresee how SQL Server will behave.


texts: Slow MySQL searching from millions record

texts:
My table looks like this.

id | hash | date

I've an index on hash which is in binary mode.

I have about 3 million rows right now.

But if I query with the following query, it takes about 4 seconds.

select HEX(hash) as hash
from animal
where hash in (UNHEX("19788EAB833C289CD7AB"),
    UNHEX("74FA22042B131511B6AB"), UNHEX("3F716CE5C8E383D2B657"),
    UNHEX("B2AE511D489990C13AD0"), UNHEX("FF6FD22DF8498BA74572"),
    UNHEX("3C4E02A76F0EEA51CB9D"), UNHEX("2A40966445E46A9B47BE"), ...)
    ... = around 100 length

What should I do to make it faster?

EDIT:

The result from using EXPLAIN

1 | SIMPLE | animal | range | hash | hash | 10 | NULL | 7 | Using index condition; Using MRR; Using filesort

EDIT 2:

I've mysql version 5.6.26.

CREATE TABLE `animal` (
 `id` mediumint(9) NOT NULL,
 `hash` binary(10) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Oracle autotrace - pstart and pstop always the same

texts:
I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions - however, when I execute autotrace against query in which WHERE clause uses partitioning key, the pstart is always 1, and pstop is 1048575. Does that mean that it doesn't utilize the partition pruning properly? Shouldn't it come back as something like pstart=1 and pstop=1 (or 2 and 2 etc) if I'm looking to retrieve data of a single month?
Statement used to create partitioning
PARTITION BY RANGE(event_time)
INTERVAL (numtomyinterval(1, 'month'))
(PARTITION part_01 values less than (to_date('2010-01-01', YYYY-MM-DD)));

SQL query issues that was analyzed by autotrace
SELECT * FROM EXAMPLE
WHERE TO_DATE(SUBSTR(event_time, 1, 10), 'dd-MM-yy' BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yy')
AND TO_DATE('06-JUL-2020', 'dd-MON-yy');

Autotrace results in


texts: Cleaning replication tables before snapshot applied to subscriber

texts:
We have a snapshot replication setup.
In some cases (like previous replications failed etc), some subscribers get the following error

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “<FK_constraint>”. The conflict occurred in database “”, table “”, column ‘’. (Source: MSSQLServer, Error number: 547)

I found out that executing following clean up SQL on the subscriber would solve the issue
delete dbo.MSsavedForeignKeys where constraint_name = N'<FK_constraint>'

Or simply, executing the above SQL without any where clause to clean the dbo.MSsavedForeignKeys table and all problems got fixed. But it was getting a problem when the number of subscribers increases.
What I wonder is,

Is it safe to add this delete statement (without where clause) to pre-execution scripts to automatize the process?
Are there any other replication tables that could cause such problems and could be deleted with pre-execution scripts?


texts: Setup replication between two Galera nodes

texts:
I've been using MariaDB 10.2 Galera with 3 nodes successfully on local servers. Now, I have a second 3-nodes Galera cluster on another location, joined by an Site-to-Site IPSec Tunnel.

I can't really merge it to one big cluster, as the performance drawback would be high (due to physical positions of those two datacenters).

I'd like to setup asynchronous replication between those two clusters but I can't make it two work. Using "old-school" master-master replication (which is master/slave in both ways, as I get it) between two nodes doesn't work as conflicts starts to rise.

Is there a way to setup asynchronous replication between those two Galera clusters ?

Thanks for your help.


texts: What is the valid string representation of tstzrange[]?

texts:
I am trying to find string representation of tstzrange[] type (array of timestamp ranges):

select '{[2014-01-01,2015-01-01]}'::tstzrange[];
select '{''[2014-01-01,2015-01-01]''}'::tstzrange[];
select '{[''2014-01-01'',''2015-01-01'']}'::tstzrange[];

But each of queries above throws malformed range literal error.

I know that I can use array[tstzrange('2014-01-01','2015-01-01','[]')] but what I need is a string representation.


texts: Create table if not exist each time API called: bad practice?

texts:
I'm writing a simple API in PHP with MySQL as database for a web application. I intend to put CREATE TABLE IF NOT EXIST for all tables in each API call.

I expect that I will have 5-6 tables.

I'm trying to do so for a simple reason: I will not need any extra script to run for initialization of the application - it will do it automatically on the first run.

As a drawback I expect that this can slow down API operation. However I tried to log API calls and it is still 2-3 milliseconds (in my case I did not notice any difference if there is CREATE TABLE or not).

Any thoughts?


texts: Replication with encrypted columns, does the 2nd server have to have the same master key and symmetric key or is it done automatically?

texts:
I have two questions actually:

If you set up a table with encrypted columns and replicate it to another server does the other server have to have the same master key and symmetric key or is it replicated automatically?

And where replication activity is logged? - Is that inside the windows event viewer?


texts:
Is there a way to convert a Postgres dump file to a text file?

I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text.

Here's what lastest.dump looks like:


texts: How to import a large .sql file to SQLServer via MSSQL?

texts: I need to import a table from a .sql with size of 55GB. But i cant open it in Server Management Studio because of memory, there is a way to import this table query to my database of another way?


texts: How to index dynamic attributes in MongoDB

texts:
I have following kind of data (simplified a bit from my real case) in MongoDB:

{
    "name":"some name",
    "attrs":[
        {"n":"subject","v":"Some subject"},
        {"n":"description","v":"Some great description"},
        {"n":"comments","v":"Comments are here!"},
        ]
}

The attrs array is a container for dynamic attributes, i.e. I don't beforehand know what kind of attributes are put there. n stands for name and v stands for value.

The MongoDB In Action book describes this as a solution for having dynamic attributes in the case where the attributes are completely upredictable. It also describes that you can index it like this:

db.mycollection.ensureIndex({"attrs.n":1, "attrs.v":1})

Queries can then be done like this:

db.mycollection.find({attrs: {$elemMatch: {n: "subject", v: "Some subject"}}})

When i test this, I get very poor performance. I tested with mycollection having 2 million documents and having no index seems to perform better.

So, the question goes, is there a way to index this kind of dynamic attribute setting so that the indexing gives good performance? In my case, it is not feasible to just have keys like "subject" and "description" and index them all...


texts: How do I use the POSIX character classes upper and lower to search text in MySQL?

texts:
I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get it to return any results.

Say I want to search a column for the string literal 'FREE' only in upper case.

Let's say the table structure is something like:

CREATE TABLE article_body (
id INT NOT NULL AUTO_INCREMENT,
article_id INT NOT NULL,
summary VARCHAR(255),
body_text TEXT,
PRIMARY KEY(id));

I want to find body_text columns where the word FREE is in all uppercase, e.g.

SELECT id, summary FROM article_body WHERE body_text REGEXP '[[:upper:]]+FREE';

But of course that syntax is invalid.

Help?


texts: SQL-Server for Linux WORKGROUP authentication handling

texts: I installed SQL-Server for Linux on a linux Centos 7 distro, it works fine, but I need a workgroup authentication. It's required by the client (SAGE Ligne 100) which runs on a Windows workstation. There is no domain controller, just these two machins.Can SQL-Server for Linux handle workgroup authentication, and how ? Thanks.NB : I already posted that question to https://unix.stackexchange.com/questions/397194/sql-server-for-linux-workgroup-authentication-handling


texts:
What is the difference between these joins?

a left join b left join c
(a left join b) left join c
a left join (b left join c)

Does the grouping only affect the order of the joins? Will the query engine decompose #1 into one of #2 or #3, i.e. pick an order for doing the joins?

I'm asking because h2 uses indexes on c for #1, but for #2 does a table scan of c.

Here b is a simple many-to-many join table with columns (a_id, c_id).


texts: Get rows only joined to certain types of row in another table

texts:
I have rows in a person table.  Each can be linked to multiple rows in a thing table via a link table.  Each thing has an objectType.  I want to get all rows in the person table which are ONLY linked to three specific objectTypes and I only want one row per person.  I'm a SQL newbie and I don't know where to start.  I've set up a sample:

http://sqlfiddle.com/#!18/56bd69/2

Just using INNER JOINS and WHERE doesn't work because it returns all the person rows.


texts:
With one table tblBids containing BidId field with the relationship bidID has multiple contractors from tblContractors, I am trying to create a query which will list each contractor and email address related to their BidID.

BidID      Contractor Name     Contractor Email
1          Contractor A        Contractor A Email
1          Contractor B        Contractor B Email
2          Contractor A        Contractor A Email
2          Contractor C        Contractor C Email

My goal is to create a Send Object Command to send a PDF Report out for say BidID 1 to all contractors selected to that Bid ID.

Any ideas?


texts: Oracle: Performance centric ways to notify user on reaching "logical" lock expiration event

texts:
Current Setup:

My application uses Java (Spring) and Oracle 11g and has functionality where logical locks are placed on an object before updates are made in the table.

For example there are 2 tables EMPLOYEE and EMPLOYEE_LOCK. When any update is made to employee, an entry is inserted into EMPLOYEE_LOCK table to indicate that for next 30 seconds a particular employee is locked.

So EMPLOYEE_LOCK table looks like below (as of 10AM)

EMPLOYEE_ID | LOCK_TYPE | LOCK_UNTIL
-------------------------------------
100         | mandatory | 10:00:30 AM (30 seconds from 10AM)

After 30 seconds a notification needs to be sent out to all clients that a lock has expired and this employee is available again for updates.

Now to identify if LOCK_UNTIL duration has reached, application makes a SQL call to database every 2 seconds to see if 10:00:30AM has reached.

Performance Issue:

This call every 2 seconds is causing lot of overhead on the database and on the application server.

I am looking for a better ways where Oracle itself initiates a notification to the server when lock expiration time has reached. Is there any way I can achieve this?

Possible solutions:

One solution could be to use DBMS_SCHEDULER package and create a scheduled job. But I could not find anywhere in the documentation, some way for the job to notify application server. It can send an email but that wont help me much.
Second option could be to use "Database Change Notification feature" but this is triggered on a DML or DDL change on the DB object which is not happening in my case.

Can some one suggest some approach where Oracle some how notifies application server when lock expiration time is reached?

Edit (To answer questions raised by Gil Shabtai)

Its probably my bad that I tried leaving some of the points from the discussion which I thought were irrelevant to the question I was asking.
Here are the answers to your issues / questions raised

Concern:App knows lock expiry time but still checks
Answer: A 3rd party CRM application can also add lock in the table. So application is the not the only way to add lock. So application does not always knows what lock expiration times are.
Concern:30 seconds locking period Performance issue
Answer: This was just an example, the actual locking period is configuration and default is 5 seconds.
Concern:Caller needs to go to sleep if entity is locked.
Answer: Caller also has a mechanism to request a notification when locks are released. All these requests go to a queue. So it is critical that the caller be notified (in the sequence they requested for a lock) when the lock is expired. So caller going to random sleep may not be an option.

My primary goal (with this question) was to see if Oracle can give me some way to identify this expiration time trigger and initiate an activity rather than Application server initiating one.


texts: postgresql copy comand with date in filename

texts:
I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12).
COPY
(SELECT * FROM mytable)
TO 'C:\Users\me\mylocation\filename_date_time.csv' CSV HEADER

where date_time is YYYY-MM-DD-h_m_s (or similar)
I have seen some solutions to this using bash. For various reasons I am on a Windows machine with no bash.
I would like to do this using psql please.


texts: Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?


texts: Installing Oracle Express Database 18c (XE) On Oracle Cloud Free Tier Fails With ORA-00821: Specified value of sga_target | minimum requirements

texts:
Scenario

Tried to install Oracle Express Database 18c (XE) on Oracle Cloud Free Tier ( Centos 7 machine ).
Followed steps suggested in : Oracle installation doc
As Oracle Cloud Free Tier machine is limited to 1000MB memory, installation fails with 2 different major issues :
Encountered 2 major issues :

Issue 1 : [SEVERE] Oracle Database 18c Express Edition requires a minimum of 1GB of physical memory (RAM).  This system has 971 MB of RAM and does not meet minimum requirements."
Issue 2 : [WARNING] ORA-00821: Specified value of sga_target 296M is too small, needs to be at least 400M
ORA-01078: failure in processing system parameters


texts: prioritize one query over the other in mysql

texts:
Is it possible to prioritize one thread over the other in mysql. The scenario that I'm trying to address is as follows :-

2 threads T1 and T2 are both trying to acquire a lock on a table.

T1 is a batch-job, which if fails, can be restarted where as T2 is a normal request from an end user. Say T1 has acquired a lock on the table and at that point T2 comes in with a request. In such a scenario T1 should fail and allow T2 to acquire the lock.

Is it possible to achieve something similar in mysql or is there an alternative way to address such issue ?


texts: How to create a SQL trigger to execute a stored procedure when a table is updated

texts:
i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an option.

The idea is for the trigger to run the stored procedure whenever the table is updated regardless of whether its a single row or all. As well am not sure whether to create it in triggers under the table or Database triggers?. i want the script to check if the trigger exist and if it doesn't then create it.

table name: qf.customer_working_hours
columns: WHours_id,DayOFW

Stored Procedure: qf.ServiveRefreshCustomer_WH

Am new to SQL scripting and SQL triggers, any help will be appreciated


texts: MySQL: LEFT JOIN not working as expected

texts:
I have 2 tables: Employees, attendanceIn

When I query using LEFT JOIN

SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees`
LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno
WHERE date(attIn) like '2016-07-02%'

What I got is,

Whats wrong with my Query?
How do I get my Expected result?


texts:
I am implementing long polling using mysql for a near realtime data update for clients.

Currently, this is what I have (and it works):
A messages table, that holds messages between users,there is a clustering index using timestamps. Client starts an ajax request to poll. The server then, runs a db query to check for messages that are greater than a certain timestamp value continuously(sleeps for one second,then continues).
Since the timestamp is indexed, the search times are very fast.

Aside of this, I now want to implement a todo list. (unlike messages, todolists, can be deleted/edited ).

To store todo lists, I wanted a separate table, with whatever logic. Assuming the read times are fast, is it good practice to poll for new data from multiple tables?

Aside of that , I would like to know if there is any difference between running two sql queries separately vs them being a single string, separated by a ';'

I want to ask that because I am using laravel, and I see no way to run multiple queries separated by ';'.


texts:
If I have a MySQL table which has content from the last 3 months I actually do the following:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
  and timeCreated > NOW() - INTERVAL 1 HOUR
group by `_campaign`, `session`

Now I think it first checks all elements where _campaign=2, is that right? Then it checks where session = ... and then it checks where _timeCreated > ....

How can I make the select faster?
Is it better to reduce the size by changing the order of the element like this:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where  timeCreated > NOW() - INTERVAL 1 HOUR
  and _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

Or is it better to:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
from (
  SELECT *
  FROM `mDelivered`
  where  timeCreated > NOW() - INTERVAL 1 HOUR
)
WHERE _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

or how should this be done to be faster?


texts:
We have to load a number of huge tables using Load From cursor. The query formed for the same is a dynamic query as the column names are to be specified. we do the same using the below code in a procedure

For Mcnt As Mcur Cursor For
  Select Di_tablename From file_mapping  Order By Id
  Do

   For Mcnt1 As Mcur1 Cursor For Select Column_name  From Sysibm.Columns  Where Table_name=di_tablename
   Do
       Set v_colquery = v_colquery || Column_name || ',' ;
   End For;

   Set v_colquery = Substr(v_colquery, 1, Length(v_colquery)-1);

 set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || Di_tablename  || ' ) OF CURSOR INSERT INTO ' || Di_tablename || '_LOG  ( ' || v_colquery || ') NONRECOVERABLE';
call admin_cmd(v_sql);
commit;

 END for;

The first round in loop executes just fine. But when it comes to the second it  gives the below error.

The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.18.60

All the cursors are implicit cursors(do correct me if  i am wrong). Unclear as to which cursor is being referenced and what exactly the issue is.


texts:
I am trying to design a database solution for the following:

A table containing all nutritional information regarding a given ingredient; ingredient_ID is the PK here but ingredient name has a 4 character unique constraint upon it too (so OATS for a record containing nutritional information on oats)

A 'join' table containing a meal name, say BRKFST, ingredient_ID, and the given quantity for that ingredient

Possibly a third table containing just meal name

The first table has already been created so if that could be borne in mind I would appreciate it.
The inclusion of the third table is where I am struggling. I have read through topics on related matters, here, here, and here, but none of these quite seem to answer my query or deal with different designs such that I cannot see how they would adapt to my own. I have also read through the post here and it led me to the current design I have sketched out.
As it stands, this is what I have:

with meal_name and ingredient_id forming a composite PK in the second table. This is due to each record referring to a meal (and potentially an ingredient) multiple times.
So BRKFST might have two records; one for OATS, and one for MILK; similarly, MILK may appear elsewhere, in another meal.
I just wonder then what the use of the third table is in my design and was considering just having the two but was not sure if I might be missing something here. I have no need to include a description and do not want any recipe instructions.


texts:
Friends, I would like help from you to find a solution to the following case:

A table with 2 fields: datetime, record (the record being any number that can be repeated a few times)

I need to count how many times each record appears in 12h, 24h, and 72h intervals.

Example, supposing record number 1: It appears 2 times in a 12h interval, plus 1 within 24h and another 3 in the 72h interval.

Thus, for the first record 1, in the 12-hour column there would be a value of 2, a 24-hour value would be 3, a 72-hour value would be 6. In the second row of record 1 the results would be: for 12h value 1, 24h value 2, 72h value 5 and so on.

Any suggestion?


texts: How to include the value of the query parameter in the email body using sp_send_dbmail

texts:
I am calling sp_send_dbmail and setting the query parameter. The resulting email includes the results of the query. I want the actual query (the value of the query parameter) to be appended to the email, so that the email will include the results and then the query itself at the end.

This will aid debugging.


texts: ORA-17627:invalid username/password;logon denied error message when trying to duplicate database in data broker

texts:
I'm trying to duplicate the database for the purpose of Data broker in windows platform. When I'm trying to duplicate using below command

DUPLICATE TARGET DATABASE TO LOCALDEV FROM ACTIVE DATABASE;

I'm getting error as :

ORA-17629:Cannot connect to the remote database server
ORA-17627:invalid username/password;logon denied
ORA-17629:Cannot connect to the remote database server

I copied the password file from primary to standby and used same password as that of primary in standby. Still I'm facing this issue.
I'm able tnsping from both primary and standby.
I tried all the ways. But nothing could help me.

Any help will be appreciated.


texts: Clustered Index Help, where did i go wrong?

texts:
I was asked to review a very busy table and identify any areas of improvement.
I have very limited power to change the table just the indexes.
Table information

240 columns
~5 million rows
Read and updated by about 30 applications, which range from websites to polling applications.
Each row represents a contract and its state based on three flags (varchars of size 5, 8 and 8).
A row's life cycle goes from start to end through these three flags, and eventually is done.
A row typically is updated or changed 10 to 30 times throughout this lifecycle.
The primary key is a combination of an id column, a guid column and a company column.
Table has 40+ indexes, the majority of which are duplicates and unused.  This is based on the sys.dm_db_index_usage_stats DMV, running twice a week for the past 7 weeks.

The current clustered index on this table has five columns:

Company column (50 distinct values)
Region column (21 distinct values)
FlagA column (8 distinct values)
FlagB column (24 distinct values)
FlagC column (5 distinct values)

My understanding is a clustered index should adhere to the following properties.  Source

Unique
Static
Narrow
Ever increasing.

The current clustered index is none of those.

There is no unique Id.
The three flags are updated constantly.
There can be 5000 rows any given moment that have the same values for all 5 columns.

So my assumption was to correct this with a clustered index on the Id column - an integer that is NOT an identity but maintained through a counter table (read value, add 1, update counter table).
I created a clustered index on the Id, not using the primary key because I believed adding the guid and company column would not give me any benefit.
I then created a nonclustered index which holds the Company, Region and 3 flags.
In a test environment the stats were looking good, user_updates were lower etc. But the overall performance of applications against this table was terrible. The most common queries against this table are:
SELECT *
FROM table
WHERE ID = 1234;

And
SELECT *
FROM table
WHERE Company = 'company'
AND Region = 'region'
AND flagA= 'A'
AND flagB = 'B'
AND flagC = 'C';

What I have missed? Are there exceptions to the above clustered index rules?
Would the clustered index benefit from adding the company and region to the id?

It is my understanding if the clustered index is on non-static values it needs to constantly reorganize itself and the other nonclustered indexes, which at this point we still have 40 plus...to be later removed. Wouldn't I have seen a gain there?
Added information.

Both environments are SQL Server 2008 R2
Have the same OS, memory etc.
The only difference was the clustered index.
I wrote the SELECT * to be concise in the above example.
Test and production environment are "the same", as best they can be: Production was facing a heavier load, but both were running same applications, load balanced etc. Test was significantly slower.
We ran with the new clustered for a week and a half before we rolled back to the original setup. Initially, stats were updated and specific stored procs were recompiled as slow performance started to crop up.
I do agree that with the multiple column index at least the table was organized better than just on Id. I want to reattempt with Company, Region, and Id as the CI, but leave the three flags out of it.

What have I missed?


texts: syntax error at or near TO (ROLLBACK TO SAVEPOINT)

texts:
The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT :

  for ptrEnt in entite loop
      -- positionnement du point de retour pour une entitÃƒÂ©
    savepoint rbk;
      -- purge de la table des redevances de l''entite
    test := pckg_activ.initredevanceentite(ptrEnt.cod_ent);
      -- calcul de la redevance
      test := pckg_activ.calculredevanceentite(ptrEnt.cod_ent);
    -- controle calcul de la bonne execution pour l''entitÃƒÂ©
    if (test = 0) then
        -- initialisation des departements associes a une entite
        test := pckg_activ.initdepartement(ptrEnt.cod_ent);
      -- validation des modifications
      commit;
      -- generation de l''etat recapitulatif
      test := pckg_activ.recapentitegestionnaire(ptrEnt.cod_ent,ficHisto);
    else
        rollback TO SAVEPOINT rbk;
      report := jour + 7;
      update sav_rdv_date
          set date_dem = report
          where cod_ent = ptrEnt.cod_ent;
        RAISE NOTICE '!!erreur de patrimoine => abandon du traitement pour cette entite';
      commit;
    end if;
  end loop;

entite is a cursor that has been declared and the for loop is in a BEGIN bloc.

I get the error ERROR:  syntax error at or near "TO". I think that the syntax is correct , it it related to the cursor ?


texts: Can I create alert on ora-20118 from Enterprise Manager?

texts:
Can I create an alert on a user-defined ORA- error?

I want EM to notify me when the following error happens:

begin
raise_application_error(-20118, 'myerror');
end;
/


texts: UPDATE with multiple WHERE and SELECT condition - MySql

texts:
So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the name that match to that ID but I think I need multiple WHERE conditions and SELECT, may anyone help me out? Thanks!
I have the following code to update

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?,
                           localizacao = ?, fabricante = ?, modelo = ?,
                           imei = ?, numero_serie = ?, ativo_sap = ?,
                           anexo_a = ?, evento = ?, data_evento = ?,
                         SELECT id_colaborador FROM colaboradores
                               WHERE nome = ? AND WHERE id_ativo = ?";


texts: Data Warehouse Kimball Interest Rate and Interest Payment Amount

texts:
I am modelling a bank account.

Should interest rate be in the DimCheckingAccountId (which shows ProductDimensions, or should it be in fact table, or both). Same with another LoanAccount, should interest rate be in dimension or fact?

I know Transaction Amount should be in the fact table.

create table dbo.FactCheckingAccountTransaction
(

    FactCheckingAccountTransactionId int primary key identity(1,1),
    Dim_CheckingAccountId int,
    Dim_CustomerId int,
    Dim_TransactionDate int,
    InterestRate float.
    TransactionAmount numeric(10,2)
)

create table Dim_CheckingAccount
(
    Dim_CheckingAccountId int primary key identity(1,1),
    CheckingAccountNumber int,
    ProductType varchar(255),
    Dateopened datetime,
    BeginDate datetime,
    EndDate datetime
)


texts: How to deal with a large database containing zipped files.

texts:
(I'm a PhD student in applied math, I know some programming but admittedly I know very little about databases.)

My question is: how do I tackle/handle this database?

The problem is as follows. I have a "large" database (1 Tb). The top level maps are 15 batches which are named BATCH1 ... BATCH15 as follows:

The only relevant map is the RawTAS map (actually the TAS map, but RawTAS will do for the sake of this question)

Going one level deeper we find the following directory structure

Each RawTAS map has a year folder going from 2006 to 2015 and then each year folder has monthly folders going from 01 to 12. Each monthly folder now contains a bunch of zipped csv files. The issue is that when I exctract these zip files they become insanely large so unzipping all of them is infeasible.

What would be the proper way of handling this kind of database?


texts: how to see multiple column values in new line

texts:
Possible Duplicate:
  how to query multiple row data in single column in oracle?

my table structure is given bellow

deptid  ename
------  ------
10  CLARK,KING,MILLER
20  ADAMS,FORD,JONES,SCOTT,SMITH

but i want the output sould be
like

deptid  ename
----------------
10      CLARK,
        KING,
        MILLER
------------------
20      ADAMS,
        FORD,
        JONES,
        SCOTT,
        SMITH

please tell me how to do it in sql


texts: Check what event called the trigger on PostgreSQL?

texts:
I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.

Oracle triggers can check this in a simple 'IF' statement:

IF INSERTING, IF UPDATING or even IF DELETING

Is there a similar statement on PostgreSQL?

Usually I just create a function for INSERT, an other one for UPDATE and one more for DELETE, but it's a waste of code if there's some way to create just one function.

Can someone help?


texts:
We have an SSRS instance that is widely used, and we use a DNS alias to access the reports.  However, when you create a subscription to a report that sends a link, the server name is given and not the DNS alias.  I never would have noticed this, but in an effort to keep branding consistent, someone recommended that we use the DNS alias here as well.

Anyone know of a way to change that, so that subscriptions use the alias?

Thanks in advance.


texts: Store Procedure to Copy One Column value of one table into another table row

texts:
I have two table tblDailyCheckIn and tblWeeklyTimeSheet. I need to copy working hours for every employee from tblDailyCheckIn and insert into Sunday, Monday, Tuesday, Wednesday etc of tblWeeklyTimeSheet. For Sunday I have done it, now i want to do it for All day. How Can I do it?

Delimiter $$
Create procedure usp_tblWeeklyTimesheet_SundayInsert(In P_EmplyeeID bigint,
P_WorkingDate Date)
Begin
Insert into tblWeeklyTimeSheet(`Sunday`)
Select workingHours from tblDailyCheckIn where EmployeeId = P_EmplyeeID and
WorkingDate = P_WorkingDate;
End $$

Call usp_tblWeeklyTimesheet_SundayInsert(1001,'2020-05-03');


texts: PostGIS trigger is fired without error, but the action seems not executed

texts:
Context
I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.
This trigger must compute a new geometry for each inserted or updated point. This new geometry must be calculated as the projection of the geometry of the currently inserted feature point onto the nearest line of another table containing line features.
What I've done
The trigger is as follow, it is correctly fired as the EXPLAIN ANALYZE shows after a new insertion:

no errors are thrown when I insert a new point, but the projected geometry column proj_geom stays empty:
CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := (
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

As I discovered triggers yesterday evening, and even if I've done my best, my code is probably full of mistakes.
I also noticed some SQL scripts that I already have and which are actually working, ... doesn't work anymore in this fancy(?) way of writing triggers (e.g. I don't know the meaning of the double dollar sign $$).
Notice:
If I take out the inner most SELECT statement, and replace the word NEW by the actual name of the points table, the query, when run directly in pgAdmin with a specified ID such as WHERE points.id = 41 returns the correct results:

In the trigger, my first comment relates to the fact I wish to be able to update at the same time the line_id, dist and proj_geom fields of the table points each time an observation is inserted.
Question
How could I fix my code, at least to be able to get the new geometry?
I'm pretty sure I'm missing some tiny details (probably where I left some comments in the code where I have that actual feeling) but I can't figure them out and I've done too much hit'n'try now with the trigger function (none of them worked as good as the one here above).
Environment
Ubuntu 18.04
PostgreSQL: 10.12
PostGIS: 2.4


texts:
How do I stop a rollback in progress. I killed a very long running INSERT INTO table1 SELECT * FROM table2 statement. I want to commit what's already there but I can't since it's rolling back.

This is a mySQL database.


texts:
How to sum the values of a json column filtered with regex?
Columns:
A.name: Varchar with name product
B.products: JSON with time active By product
Query:
select
    A.name as product,
    sum(((REGEXP_MATCH(B.products->>'status', 'Active:.(.*?)\"'))[1])::float) as metric
from
    tbl_accounts A
inner join tbl_products B on
    A.identifier = B.identifier
where
    B.products->>'status' like '%Active%'
group by
    A.name,
    B.products

Explanation data
JSON 1: {"times": ["Stoped: 49.05", "Active: 23.26"]}
JSON 2: {"times": ["Stoped: 59.05", "Active: 33.26"]}
Desired Output: 56.52
Output:

ERROR: could not identify an equality operator for type json


texts: What is preventing DROP TABLE from removing a Postgres table?

texts:
I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command:

DROP TABLE pnt

Unfortunately, this does not appear to drop the table. My best guess is that there is some sort of admin privileges that I have not configured properly. The following shows the database, and commands used to try and drop the table.

What is preventing me from dropping the pnt table?

There is a similar question here, although the user is running into a cache error, which is not happening in my case.


texts: Fast way to load a large amount of test data

texts:
I've trying to populate a table with a load of dummy data so I can about with optimisation etc.

I've got the following:

WHILE @RowCount < 3000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max)))

    SET @RowCount = @RowCount + 1
END

However, this seems very slow.

Is there a better way to automate loading semi-random rows into a database table?

New Script

This one seems to be pretty quick:

USE [Test]
GO

/****** Object:  Table [dbo].[Test]    Script Date: 17/10/2016 21:22:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Test]
GO

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [OtherKey] [int] NOT NULL,
    [Description] [varchar](max) NOT NULL,
    [Time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Add sample data

DECLARE @RowCount INT
DECLARE @Random INT
DECLARE @Upper INT

SET @Upper = 1000
SET @RowCount = 0

WHILE @RowCount < 1000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description]
               ,[Time])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max))
               ,GETDATE())

    SET @RowCount = @RowCount + 1
END

GO

/****** Object:  Index [IX_ID]    Script Date: 17/10/2016 22:18:48 ******/
CREATE CLUSTERED INDEX [IX_ID] ON [dbo].[Test]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_OtherKey]    Script Date: 17/10/2016 21:22:46 ******/
CREATE NONCLUSTERED INDEX [IX_OtherKey] ON [dbo].[Test]
(
    [OtherKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If anyone has any faster way though then I'd be very happy to hear it.


texts:
I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email.  However, anytime I try to execute my statement I get an incorrect syntax error.  Is this possible to do?  SQL Server 2008

Declare @name varchar(100),@bodytext nvarchar(max), @subject varchar(200)
Create Table #info
(
    name varchar(100)
    ,itemsold varchar(100)
)
Insert Into #info Values
('ZZZZZZ', 'First'),
('CCCCCC', 'Last'),
('EEEEE', 'Green'),
('XXX', 'Blue'),
('QQQ', 'Red')

DECLARE mailcursor CURSOR FOR
SELECT DISTINCT(name) FROM #info

OPEN mailcursor
FETCH NEXT FROM mailcursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    set @bodytext = cast( (
    select td = '' + name + '	' + itemsold + '	 '
    from (
              'Select * from #info Where name = ''' +@name + ''' '
          ) as d
    for xml path( 'tr' ), type ) as varchar(max) )

    set @bodytext
    = '
'
    + '
name	itemsold
'
    + replace( replace( @bodytext, '&lt;', '<' ), '&gt;', '>' )
    + '
name	itemsold
'
    + '
'

    Set @subject = 'Sent Through Code'

    exec msdb.dbo.sp_send_dbmail
        @profile_name = 'DatabaseMail',
        @recipients = 'foxtrotalphamale12343124@gmail.com',
        @body_format = 'HTML',
        @from_address = 'redfirebluestonesgreeneyes16@gmail.com',
        @body = @bodytext,
        @subject = @subject;

    FETCH NEXT FROM mailcursor INTO @name

END

CLOSE mailcursor
DEALLOCATE mailcursor

--Drop Table #info

And my error message is:

  Msg 102, Level 15, State 1, Line 26
  Incorrect syntax near 'Select * from #info Where name = ''.


texts: Select number of times 2 specific values show up for one record id

texts:
I'm not sure my title above makes sense, so I'll try to clarify.  I'm using SQL Server.

I have a table called Orders that looks like this (truncated):

Order_Number | SKU
           1 | abc
           1 | def
           1 | ghi
           2 | abc
           2 | ghi
           3 | def
           4 | abc
           4 | def
           4 | ghi
           5 | abc

I need to find all Order Numbers that include SKUs abc and ghi.  So if I ran the query on the above table, it would return:

Order_Number
           1
           2
           4

If the Order Number only includes abc or ghi, not both, I don't want to return the Order Number.  If it includes extra SKUs, such as def, that is OK, I still want to return the record.  I don't need to see all the SKUs in the output, just the Order Number.  Does that make sense?

Thanks in advance!


texts: Are there any performance benefits to using a hash table with no clustered index?

texts:
I have this table:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL
)

It stores hash matches. Is there any benefit to having this as a heap? It has around 7 million rows, and the values are not unique in either column. I know heaps are generally never good for any large tables.  This table currently has no indexes.

I was thinking of altering the table to:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL,

   CONSTRAINT [pk_relatea] PRIMARY KEY CLUSTERED (
      [mid] ASC,
      [sid] ASC
   )
)

Table queries are generally but not always on sid, and the combination of mid and sid should always be unique.

Is it better to create a clustered index like this, as opposed to leaving the table as a heap?


texts: How to scale MongoDB/GridFS for fast ingestion on limited timescale

texts:
I'm working on a cloud-agnostic service which must allow the ingestion of a lot of media files in a very limited timeframe.

Some numbers:

3-4 hours total of workload: the service will be active during several distinct live events, with no traffic before and virtually no traffic after (I plan to tear-down the whole structure after each event)
I have all the time I want for a warm up before the event
it will be used to ingest a variable quantity of files, approx between 1k and 100k files
each file will be approx between 10MB and 200MB
the ingestion will be in waves, with up to approx 1k-5k files to be ingested in 5 minutes, then virtually no traffic for another 5-10 minutes

As for now I'm using:

Kubernetes (cloud agnostic)
MongoDB with GridFS sharded for scalability

The issue is that adding a new shard to MongoDB triggers the balancer which takes a lot of time, and during rebalancing the ingestion performance are even worse.

I wish to be able to:

start with a minimal setup with 2 shards
observe disk and CPU usage during each 5 minutes wave of peak load
decide whether to scale up (no scale down required)
add some shards (probably double them up)
having the rebalancing completed in 5 minutes before a new wave arrives.

My current setup scales good if I leave enough time for the balancer to complete balancing before the new wave arrives, which most of times is not the case.

Somebody can help me achieving the desired behavior?

I'll eventually consider alternatives to MongoDB if they still are cloud-agnostic.


texts: What is the Best Database Design for multiple form which have some similar column, but some of it does have extra column that other form does not have

texts:
I'm planning to develop a Shopfloor Database System. What are the best database design pattern should i apply to my system. Each of the process have their own form and own field, but most of the field set are similar with each other process, just some of the process may have additional extra field. In which, it was difficult for me to create just one table to keep all of the process data entry.

Below are the sample of my process and the field column that required for each of the process.

Which are the recommend database design should i apply to this? Should I just create one table to keep all those similar column field data and for the rest of the other different column name I just move it to other table with a foreign key?


texts: What is an effective way to wipe sensitive data on a data pages after dropping a table's column?

texts:
After dropping a column with sensitive data from a SQL Server table, the data still exists on the data and index pages.  To ensure the protection of this data, it needs to be removed from the pages as well.

For SQL Server (in particular, SQL Server 2012), what is an effective and efficient way to remove all of the data from the actual pages?

If the solution causes data to be moved to new/recycled pages, do the all the original pages get overwritten?  For pages that are recycled for rebuilding, this would obviously be the case.  For pages that were part of the table/indexes will they be wiped or have the sensitive data removed in some other manner?