texts: How to prevent seek pagination from doing sequential scan over entire table

texts:
I have 3 tables and a materialized view:

resource_categories contains all category names and metadata

create table if not exists resource_categories (
    category_id INT,
    title VARCHAR(255),
    content TEXT,
    icon VARCHAR(50)
);

resources contains all resources under each category

create table if not exists resources (
    resource_id INT,
    title VARCHAR(255),
    content TEXT,
    link VARCHAR(1000),
    category_id INT REFERENCES resource_categories(resource_id),
    icon VARCHAR(255),
    created_at DATE,
    updated_at DATE
);

resource_votes contains user liking a resource or disliking it

create table if not exists resource_votes (
    resource_id INT REFERENCES resources(resource_id),
    user_id INT,
    vote BOOLEAN
);

resource_votes_aggregate a materialized view with likes per resource_id

CREATE materialized view resource_votes_aggregate AS
SELECT
   resource_id,
   COUNT(
   CASE
      WHEN
         vote = TRUE
      THEN
         1
   END
) AS likes
FROM
   resource_votes
GROUP BY
   resource_id;

I want to execute the following queries
Find all resources sorted in descending order of likes (find most liked resources)
Find all resources sorted in descending alphabetical order of their titles
I want to use SEEK / KEYSET pagination for efficiency

Query to find resources in descending order of their likes page 1

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   likes DESC,
   resource_id DESC LIMIT 5;

Execution Plan

QUERY PLAN
Limit (cost=74.50..74.52 rows=5 width=157) (actual time=0.058..0.060 rows=5 loops=1)
-> Sort (cost=74.50..76.80 rows=918 width=157) (actual time=0.058..0.058 rows=5 loops=1)
Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC, r.resource_id DESC
Sort Method: top-N heapsort Memory: 25kB
-> Hash Right Join (cost=12.03..59.25 rows=918 width=157) (actual time=0.032..0.046 rows=50 loops=1)
Hash Cond: (a.resource_id = r.resource_id)
-> Seq Scan on resource_votes_aggregate a (cost=0.00..30.40 rows=2040 width=12) (actual time=0.001..0.004 rows=38 loops=1)
-> Hash (cost=10.90..10.90 rows=90 width=149) (actual time=0.021..0.021 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on resources r (cost=0.00..10.90 rows=90 width=149) (actual time=0.003..0.010 rows=50 loops=1)
Planning Time: 0.050 ms
Execution Time: 0.075 ms

This and all the other queries generate a sequential scan which beats the entire purpose of seek pagination

How do I fix this. Help is super appreciated

LINK TO DB FIDDLE


texts: How can I obtain the last inserted row with INSERT ... SELECT in PostgreSQL?

texts:
I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id).

In order to determine where to start for the next batch, I'd like to start off from the last inserted row (WHERE user_id >= last_user_id AND object_id > last_object_id).

Starting off with this:

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...)
FROM source_table
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... returns a table of 1000 tuples. I'd like to obtain the last inserted tuple from it.

I've tried to do a SELECT around it, like this:

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...)
    FROM source_table
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

But that returns a syntax error:

ERROR:  syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
               ^

I've also attempted RETURNING ... INTO variable as described here, but that fails too:

ERROR:  syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
                                      ^

Do I need to create a function for this (e.g. plpgsql) or am I missing something obvious in plain SQL that let me do this? That would be highly favorable.


texts: How can I better model the relationships in this project?

texts:
I'm developing an internal tool (as an intern). We deal with a lot of hardware, which is all custom and managers have a hard time knowing who is developing on what machine.
The three main things to track for this project will be Hardware, Projects and the Employees. I'm struggling with how to deal with the managers.

I was struggling with databases and of course I have to design one in my first internship. Any and all suggestions are greatly appreciated.

Here is my current ERD:

Here is an excerpt from the design document for context:

  Tool Features

  Track gaming hardware:
  Sort or search by serial no, vendor, platform, type.
  Track project pool:
  See employees and VLT’s assigned by project. Emails are directly in the web >page.
  Track lab hardware:
  Sort or search by serial no, vendor platform and type.


texts: remove duplicate values in the same table

texts:
My table looks like:

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
|  5 | uska    |
+----+---------+

I want it to change it to

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
+----+---------+

modify changes in the same table without switching to another table.

I tried many ways, many hrs to delete or select but doesn't work, I did use code to resolve this, but I will appreciate for any explanation, thank you.

I tried in two ways like

delete from t where id<>(select id from t group by name);

Here I am getting an error as

  Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data"

delete t1.* from t t1,t t2 where 1.id<>t2.id and t1.name=t2.name;

2 duplicate rows are getting deleted instead of one.


texts: Data modelling for specific units of measurement

texts:
I am currently adding a feature in our software where we save a timeout in the database to initialize cronjobs which get active after that timeout.

To model this I see different ways but can't say which of them is the cleanest:

Create 2 columns "timeout" and "timeout_measure", where timeout_measure is a MySQL Enum with seconds, minutes, hours, etc.
Create 1 column "timeout" where the timeout is always stored in seconds

Even though I tended to the 1. way at first I saw the problem that additionally to this second column a MySQL trigger would be needed to check that if a timeout is set, the timeout_measure cannot be NULL , which would be an unnecessary performance hit as far as I am concerned, because we can implement the same functionality with only one column where we save the seconds of the timeout.

So I am now tending to the 2. way but I don't like the readability of this. Having a column like so with entries in seconds, one might not understand but only guess what this means without reading a documentation of this table.

| timeout |
| 3600    |
| 25200   |
| 86400   |

So I thought about saving the unit of measurement in the column name like timeout_in_secs. But then I am curious if this wouldn't be kind of boilerplate because if there is a documentation this additional info would not be needed in each query made and whenever the column is referenced at in the code. So whenever one wants to reference to the column of this entity in the sourcecode (we use NodeJS and Sequelize as ORM) he would need to write obj.timeoutInSecs instead of just obj.timeout.

What to do? I don't want to change the whole table structure once this feature is productive, since this could end up in a big mess. Do you think it suffices to have this documented in the database documentation, or would you still go to choose timeout_in_secs as the column name?

EDIT: I know this might be opinion based but in this case different opinions and experiences might help a lot. I am on my own in this decision and simply can't decide which solution is the best/cleanest.


texts:
So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag.

E.g for tag: photographer

I'd want to add 'photography, photographers, photo' as synonyms for 'photographer'.

The way I've proposed is that all synoynms are created as tags & that I associate a tag with a synonym by doing a 1<->1 relationship on a second table.

Here is what I'm proposing:

Tag:

tag_id
title
More meta data fields here...

Synonyms:

In this table we can attach a tag as a synonym to another tag (e.g photo can be a synonym for photography)

tag_id (the tag which we're attaching a synonym to)
synonym_tag_id (the tag_id which will be a synonym)

So I can add many synonyms to one tag.

Is this solution viable? Could it be improved somehow? I will be writing this schema in postgres. Appreciate any tips :)

Use cases:

Users of our app will be able to attach tags to there articles. If the user adds photopgraphy as the article title, our search will also show articles that have the photopgraphy synonyms attached as a tag.
Note that we aren't using SQL for search, we're using elaticsearch. The sql will just be used for storing the data so it can later be imported into elasticsearch.


texts: Mysql 8 accidentally lost ROOT user all privileges in localhost wampserver 3.2

texts:
Mysql 8 root user all privileges lost.I changed some permissions from phpmyadmin.

. After that all tables disappeared.
I found this answer Lost All Privileges in MYSQL.but did'nt help me.

I am using Wampserver 3.2 on windows 7 and mysql version is 8.0.18
Now showing only information_schema table.
I login as root user and run all below commands.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql;

mysql> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table '
user';

mysql> SHOW GRANTS;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost`                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+

  How to revoke all privileges for 'ROOT' user

Please help


texts: Join conditions and Where conditions in Left Join

texts:
STUDENT table contains a list of students in my school Database

LIBRARY_BOOK table contains the information of each Book that is with currently with any student.

each student is allowed only 1 book at a time.

I want to know

Library Book with each Class 7 Student.

I am using Microsoft SQL Server 2008 R2

I have few queries which looks almost similar but don't behave in same way.

Query 1 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
WHERE ST.Class = 7

Query 2 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7

Query 3 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7
WHERE ST.Class = 7

Can someone explain the difference between these queries in terms of both Output and Performance.


texts: Create view using tables from differing databases on a server

texts:
I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to be available to run on multiple servers that have 1 or more databases. The database and table structures are all the same.

I know I can get the data using a cursor and dynamic SQL, but that does not result in the view I need for reporting in another software program.

If it makes more sense, I want to Union data from the same table in multiple databases into one view, however, the number and name of different databases will be different on different servers. Can this be done without hard coding the views on each server?


texts: LEFT JOIN Producing Inflated COUNT() numbers

texts:
I have 3 tables that I am using a Right Join on.  The data is setup like below -
and my issue with it is that numbers being returned are inaccurate, such that when I execute the query I get this returned (which is double and triple the accurate value)

vendor     TotalSales   TotalCases
Vendor 1    61.40          6

but if you manually do the math it should be

vendor     TotalSales   TotalCases
Vendor 1    30.70          2

What must I change in my query so that the above results are returned?

  Declare @BBC Table
  (
    vendor varchar(250)
    ,vendorcasenum varchar(100)
    ,casenumdate date
  )

  Declare @AllVendor Table
  (
    vendor varchar(250)
  )

  Declare @TotalSalesAmt Table
  (
    vendor varchar(250)
    ,saleamt decimal(10,2)
  )

  Insert Into @TotalSalesAmt (vendor, saleamt) VALUES
  ('Vendor 1', '10.20'), ('Vendor 2', '10.10'), ('Vendor 1', '.40')
  ,('Vendor 1', '20.10'), ('Vendor 2', '20.10'), ('Vendor 3', '20.00')

  Insert Into @AllVendor (vendor) Values
  ('Vendor 1'), ('Vendor 2'), ('Vendor 3'), ('Vendor 4')
  ,('Vendor 5'), ('Vendor 6'), ('Vendor 7'), ('Vendor 8')
  ,('Vendor 9'), ('Vendor 10'), ('Vendor 11'), ('Vendor 12')
  ,('Vendor 13'), ('Vendor 14'), ('Vendor 15'), ('Vendor 16')
  ,('Vendor 17'), ('Vendor 18'), ('Vendor 19'), ('Vendor 20')

 Insert Into @BBC (vendor, vendorcasenum, casenumdate) VALUES
('Vendor 11',   'A12344',    '2017-01-19')
,('Vendor 10',  'A12311',    '2014-05-12')
,('Vendor 9',   'A12889',    '2015-07-10')
,('Vendor 8',   'A12988',    '2016-07-01')
,('Vendor 7',   'A12931',    '2012-03-07')
,('Vendor 6',   'A12199',    '2011-10-05')
,('Vendor 5',   'E12331',    '2011-10-11')
,('Vendor 4',   'E12391',    '2014-12-16')
,('Vendor 3',   'E12300',    '2011-07-15')
,('Vendor 2',   'E11001',    '2011-06-15')
,('Vendor 1',   'E12301',    '2013-11-06')
,('Vendor 1',   'E12221',    '2013-11-06')

Select
av.vendor
,TotalSales = SUM(ISNULL(tsa.saleamt,0))
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN @BBC bbc
ON av.vendor = bbc.vendor
LEFT JOIN @TotalSalesAmt tsa
ON tsa.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor ASC

EDIT
I also tried using a CTE to achieve my desired outcome, but came out with the same incorrect results:

WITH tsa As
(
    Select
    vendor
    ,saleamt
    FROM @TotalSalesAmt
)
,BBC As
(
    Select
    vendor
    ,vendorcasenum
    FROM @BBC
)
Select
av.vendor
,TotalSales = ISNULL(SUM(tsa.saleamt),0)
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN TSA tsa
ON tsa.vendor = av.vendor
LEFT JOIN BBC bbc
ON bbc.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor


texts:
I have looked into articles to get the size of SSISDB down.  One good way to do this was set the retention window for logging lower, currently down to 150 from the default of 365 days.

I would like to reduce this to 14 days but would like to be able to keep it at 150 days for packages that log an error.

I think I will have to dig into the SP that runs under the SSIS maint job.  Before I do that I wanted to see if anyone has a better idea.


texts: Is there any way to determine how many rows a transaction has modified (before the transaction ends)?

texts:
I have a ~320 million row table on which I run the following query:

UPDATE my_table SET state = TRIM(state)

Needless to say this has been running for 90+ hours (since thursday afternoon) and has not finished yet.

I'd like to know, more or less, how many rows it has modified until now.
Is there any way to obtain this information from the running transaction?

Trying to search I have found how to determine a rough estimate of how many new rows are being added to a table using the pgstattuple function:

select dead_tuple_count from pgstattuple('my_table');

But this does not seem to show any interesting information for how many rows have been modified.


texts: Difficulty with nested query order & grouping

texts:
I'm trying to identify the count of objects a user updated during a given day, but only where the user was the final update.

Each object can have multiple updates in a day from any user, but the final update is the one that should be counted.

Each object only has 1 status though--always as a result of the most recent update.

Object_Update_Table

|UpdateID| UserID |timestamp| ObjectID |
| 1      |   3    | today   |    1     |
| 2      |   3    | today   |    2     |
| 3      |   1    | today   |    3     |
| 4      |   1    | today   |    3     |
| 5      |   1    | today   |    1     |

Object_Table

|ObjectID| Status  |
| 1      | failed  |
| 2      | failed  |
| 3      | progress|

Final desired output of query

|User |count "failed"|
| 1   |  1           |
| 2   |  0           |
| 3   |  1           |


texts: Filter rows on data saved as JSON arrays


texts: What is the right time to use an ENUM versus a normalized types table in MySQL?

texts:
Possible Duplicate:
  Advantages and Disadvantages to using ENUM vs Integer types?

Considering MySQL only...

I'm curious to know what the community thinks the proper use-case for an ENUM type vs. a FK to a types table.

I've read this question: Advantages and Disadvantages to using ENUM vs Integer types?, however the use-cases aren't really covered well enough for me.


texts: SQL Query optimization - Long running query

texts:
I have a database with the following tables :

CREATE TABLE [Document](
    [IdDocument] [int] NOT NULL,
    [DocumentType] [int] NOT NULL
    CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
    [IdExternalKey] [int] NOT NULL,
    [RefDocument] [int] NOT NULL,
    [EntityType] [int] NOT NULL,
    [Value] [int] NOT NULL,
    CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey)
    CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
    CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

Documents are mapped to physical files on a drive. Each document has a type (eg : 2 = IDENTITY_CARD)
Those documents are linked with external entities with a one to many relation.
Each external key has an entity type (eg : 50 = PERSON) and a ID (eg : 213235)

Database is quite big : Document is 10M records. ExternalKey 40M.

Example of data inside ExternalKey:

IdExternalKey  RefDocument EntityType  Value
1              1           50          3421
2              1           50          9524
3              1           60          7893
4              2           50          1752
5              2           50          8979

I want to filter documents based on their type and the entities they are linked with (simplified query) :

WHERE (DocumentType IN (10, 20, ...)
       AND ExternalKeys ANY (EntityType = 50 AND Value IN (4,5,6,7,8,9,...)))
   OR (DocumentType IN (80, 90, ...)
       AND ExternalKeys ANY (EntityType = 60 AND Value IN (110,120,130,...)))

The best i could come up is this:

SELECT TOP 50 IdDocument
FROM Document d
WHERE
    (d.DocumentType IN (SELECT Id FROM @listA1) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 60 AND ex.Value IN (SELECT Id FROM @listB1))) OR
    (d.DocumentType IN (SELECT Id FROM @listA2) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 61 AND ex.Value IN (SELECT Id FROM @listB2))) OR
    ...
    (d.DocumentType IN (SELECT Id FROM @listA3) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 59 AND ex.Value IN (SELECT Id FROM @listB3)))
ORDER BY IdDocument

Table value parameters contains a lot of IDs (about 100K in total)
Table definition is:

CREATE TYPE int_list_type AS TABLE(Id int NOT NULL PRIMARY KEY)

The query is slow and performance vary a lot depending the sort (from a dozen of seconds to several minutes). For example, sorting documents on IdDocument is way faster than sorting them on DocumentType.
What I have tried so far:

Creating indexes on RefDocument, EntityType, Value (mulitple column index). I have tried different column orders.
Replacing the table value parameters (eg : @listA1) by hardcoded values in the query : parsing time explode (as expected).
Replacing IN with EXISTS : performance is similar.  Replace IN with JOIN : a  lot slower

Is there some more efficient ways to perform the filter, any tips ?

Here is the query plan for 2 OR clauses (each OR clause is in RED)


texts: Is there any way to get original query based on lsn

texts:
Is there any way or tool to get original query against the lsn.

For example, I have a lsn range which cause the data set modified. Can I get the actual query against the lsn. Is there any mapping between them in SQL Server ?


texts: Restoring ANY database to Azure MI fails

texts:
Our freshly provisioned Azure SQL managed instance fails restoring backups from URL.

According to the server log the restore itself seems to finish after a certain amount of time but errors in following processes lead to a termination of the whole process...

The error message prompted by SSMS is

  Msg 22003, Level 16, State 1, Line 7
      Stale/aborted version cleanup was aborted for database id '5' due to database shutdown.
      Msg 3013, Level 16, State 1, Line 7
      RESTORE DATABASE is terminating abnormally.

The relevant part of the server log can be found here.

I tried backup files from different SQL Server versions (2012, 2016, 2019) -- even the backup of an empty database causes this error...

Did anybody experience something similar? Google does not seem to help at all.

The command used is:

RESTORE DATABASE Test FROM  URL = N'https://STORAGEACCOUNT.blob.core.windows.net/STORAGECONTAINER/Test.bak'

The necessary credential got created via:

CREATE CREDENTIAL [https://STORAGEACCOUNT.blob.core.windows.net/STORAGECONTAINER]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxxxxxxxxxxxxxxxxxxxxx';

I removed STORAGECACCOUNT and STORAGECONTAINER as well as most of the SECRET.


texts:
I get this error earch time:

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup cleanup done, waiting for required WAL segments to be archived
WARNING:  pg_stop_backup still waiting for all required WAL segments to be archived (60 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup can be canceled safely, but the database backup will not be usable without all the WAL segments.

These are the steps I have done:

made sure postgresql is running
connected to postgres server by

[root@localhost pgsql]# pgsql postgres postgres
then
[root@localhost pgsql]# psql postgres postgres
psql (9.3.4)
Type "help" for help.

next:

postgres=# select pg_start_backup('varlibpgsql', true);
pg_start_backup
-----------------
0/F5000028
(1 row)

Then in separate terminal (as root) I:

  tar cfP /var/lib/pgsql/db_files_backup4me.tar /var/lib/pgsql/9.3/data --exclude "/pg_log"
 (I excluded the pg_log folder as I kept getting errors
 tar: /var/lib/pgsql/9.3/data/pg_log/postgresql-Tue.log: file changed as we read it
 tar: /var/lib/pgsql/9.3/data/pg_stat_tmp: file changed as we read it  )

Next, after successfully creating the tar, I execute in the postgres terminal:

 postgres=# select pg_stop_backup();

This is where I get the errors mentioned at the top.

Can someone explain why this is happening?


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

texts:
When I go to Tools > Options > Environment > Keyboard and
Type in Query.Sp_whoisactive
Press shortcut keys: Ctrl+F1
Click Assign.

When I press Ctrl+F1, It still calls Help.


texts: Problem using sample schemas by Oracle in Oracle APEX

texts:
I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas "scott", "pm", "sh" - so that I can work using them later.

Now I am trying to make an application using "scott" schema. The schema is already unlocked, so I can connect to this schema using sqlplus or TOAD.

C:\Users\srl13>sqlplus scott/scott

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 7 15:39:23 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to create a workspace, I cannot select "scott" as the active schema for the workspace - as the workspace is not shown in the list of existing schemas.

If I manually input "scott" in the "Schema Name" and try to proceed, I get the error

Schema is reserved or restricted

I don't know what went wrong and what's the problem - as I already did same things before making APEX application on "hr" schema. Can you suggest me what to do so that I can make the workspace? I also noticed that although I unlocked a number of schemas, no schema except "hr" and "bi" is shown in the schema selection list; so I basically can't make any workspace on any of the unlocked schemas except "hr" and "bi" - and I presume that I am going to face same error if I try to use those schemas "manually".

How should I solve this issue, so that I can create the workspace(s)?


texts: character varying vs. text as function parameter types in PostgreSQL

texts:
Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:

Example 1:

CREATE OR REPLACE FUNCTION func1(table_name <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Example 2:

CREATE OR REPLACE FUNCTION func2(filter_value <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  SELECT COUNT(*) INTO RESULT FROM <some_table> WHERE <some_column> = filter_value;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

NOTE in the 2nd example the some_column type is text if filter_value is of type character varying and vice versa


texts: SQL Azure VM Usecase for each backup type

texts:
It seems that within the Virtual Machine category in Azure the solutions for backups become increasingly complex when compared to the PaaS options.
What is the use case for each backup solution, and what is the best practice recommendation by Microsoft when we are running a marketplace image of SQL Server within a Virtual Machine.

Managed Backups
Automated Backups
Azure Recovery Services Vault
File Snapshot backups
Backup to URL

I am currently within an environment where a vendor has implemented their own backups into the file system, but the VM itself is being backed up into a Recovery Services Vault and I'm looking to optimise the current configuration.


texts: How to automatically back up local MySQL database to a remote MySQL database

texts:
I have a local instance of a mysql database and a remote mysql database.

I want to back up the localhost server to the remote server automatically every night.

My applications will be running from the localhost, but management also wants the data to be housed by our IT company.

I believe they want the servers to be fail safe, because if one of the servers are down they still want to be able to perform their daily task like normal.

Edit: After more research I believe a Master-Master replication will make the servers fail-safe.


texts: Cannot TNSPING from VirtualBox Client to Oracle Database On Host

texts:
I am new to Oracle, attempting to learn about connecting remote clients to an Oracle 12c Database server instance.

SUMMARY: I cannot get TNSPING to work from a client machine to a known good database on a host machine.

DETAILS:

I am hosting the Oracle 12c database on my Windows 7 x64 development machine. I've done numerous tests of connecting to the database from that machine - TNSPING, SQL*Plus, ASP.Net web page, and they all connect to the database no problem. Great!

So I figured a next logical step is to try to connect from another client inside my network. I created a Win7 client using VirtualBox running on the same host (RM08) as the Oracle Database. I completely disabled Windows Firewall on that client machine. I then installed the Oracle Client on that virtual machine. That gives me SQL*Plus and various other tools, and led me through a configuration wizard to create a tnsnames.ora.

When I tnsping from that virtual/client machine to the Oracle host I get "ORA-12541: TNS: no listener". But I am confident the listener is in fact running, because the database works fine when I access from the host machine itself.

Further information:

-- The host machine has IP address 192.168.1.6

-- The virtual/client machine has IP address 10.0.2.15

-- From the client machine I get success when I ping the host

-- From the client machine I can successfully open up various ASP.Net web pages at http://192.168.1.6

-- Windows firewall is completely disabled on BOTH the client and host machines.

On the client machine, the tnsnames.ora file has contents:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

On the client machine, when I issue:

tnsping orcl

I get:

C:\Users\rmvirt>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:11:37

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.6)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
TNS-12541: TNS:no listener

If I ping by IP address, I get a similar message, but it mentions EZCONNECT, I assume because I including that option when installing Oracle client:

C:\Users\rmvirt>tnsping 192.168.1.6

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:13:26

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=192.168.1.6)(PORT=1521)))
TNS-12541: TNS:no listener

Finally, if I issue

tnsping badname, I get:

C:\Users\rmvirt>tnsping badname

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:15:15

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

That at least gives me confidence that my tnsnames.ora file is syntactically correct and being referenced.

I have read this somewhat similar question, with no luck: Using Oracle client to connect to remote database

I have also read the "Testing Connections" section from the "Database Net Services Administrators Guide" at Using Oracle client to connect to remote database.

But I am still stumped.

Can anyone suggest other troubleshooting steps?

Thanks!


texts:
Why these two queries have different result?

SELECT lf_transit_new.trayNo
FROM lf_transit_new
WHERE lf_transit_new.trayNo NOT IN
(
    SELECT lf_transit_new.trayNo
    FROM lf_transit_new
    WHERE lf_transit_new.trayNo IN
        (SELECT lf_transit_cage.trayNo
         FROM lf_transit_cage)
)

SELECT lf_transit_new.trayNo
FROM lf_transit_new
WHERE lf_transit_new.trayNo NOT IN
(SELECT lf_transit_cage.trayNo
FROM lf_transit_cage)

First query show 561 rows but second query show none.


texts:
I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.

I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead of the end.

In reference of http://sql-4-life.blogspot.com/2009/03/order-of-inner-joins.html


texts: sqlplus, command line parameters and ORACLE_HOME with spaces

texts:
I have noticed strange interaction between ORACLE_HOME and parameter substitution.
Here is my test.sql

prompt &1
prompt &2
prompt &3
prompt &4
exit

The expected behavior:

C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:19:16 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

one
two
three
four

C:\cygwin64\home\rejap\subst>

however, when ORACLE_HOME is set, things get interesting

C:\cygwin64\home\rejap\subst>set ORACLE_HOME=c:\Program Files (x86)\Oracle\product\12.2.0\client_1

C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:21:25 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Files
(x86)\Oracle\product\12.2.0\client_1\sqlplus\admin\glogin.sql
three
four

C:\cygwin64\home\rejap\subst>

Am I missing something fundamental here?

Are there any negative effects of not setting ORACLE_HOME at all?

Are there any negative effects of pointing ORACLE_HOME to symlink?

c:
cd \
mklink /D oracle c:\Program Files (x86)\Oracle
set ORACLE_HOME=c:\oracle\product\12.2.0\client_1

And of course I am interested in explanation of what is going on.


texts: postgres: Upgraded RDS Postgres from 9.4 - 9.6, id fields went from SERIAL to INT

texts:
So, on my RDS postgres, that is currently running as a DMS CDC from our previous DB, our ID fields lost the serial type, and it got moved to INT without a NEXTVAL. So, using the example of our table public.pageviews, I tried

ALTER TABLE public.pageviews ALTER COLUMN id TYPE SERIAL;

and it is giving me that SERIAL is not a valid data type.

When I run

ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval('pageviews_id_seq');

I am getting the following:

  ERROR:  null value in column "c_key" violates not-null constraint
  DETAIL:  Failing row contains (null, 2018-01-03 14:17:28.952708,
  master, 1528336, ALTER TABLE, 0, , public, ALTER TABLE
  public.pageviews ALTER COLUMN id SET DEFAULT nextval...). CONTEXT:
  SQL statement "insert into public.awsdms_ddl_audit
        values
        (
        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
        )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

  ********** Error **********

  ERROR: null value in column "c_key" violates not-null constraint SQL
  state: 23502 Detail: Failing row contains (null, 2018-01-03
  14:17:28.952708, master, 1528336, ALTER TABLE, 0, , public, ALTER
  TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval...).
  Context: SQL statement "insert into public.awsdms_ddl_audit
        values
        (
        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
        )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

On that, I am not a PG DBA by trade (MSSQL that got lucked into this job) so I am not familiar enough with how to really make my way around things, on pgadmin, I have tried to set the default value for ID to a nextval, but I am not getting anywhere with that.

I'm on windows and have really no experience with psql commands (learning them, but the curve is steep)


texts: Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value

texts:
I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each  day is divided into 24hrs/2 = 48. There is a time slot.

Now I want output like this :-


texts: Should we periodically 'refresh' the mirrored copy of a SQL Server database?

texts:
When you have SQL Server mirroring set up, do you trust that your mirror database will stay the same as the primary, even after months and months of mirroring?  I know we all check the status, and of course it says the database is synchronized...  but I worry that somehow the mirror might fall out of sync due to network glitches, software bugs etc. and the fault won't be detected.  Then one day we need the mirror and we're horrified to learn that it's corrupted.

One thing I could do is periodically refresh the mirror from the primary, since our database is not terribly large.  But perhaps I'm being overly paranoid.

I would like to hear from DBAs, either of SQL Server or any other platform, as to whether you share this concern about the integrity of mirror DBs, and if so, what you do to mitigate that concern.  Do you occasionally re-copy your primary DB over to the mirror?  Do you take other measures to ensure consistency between the two?  Thanks for any advice you can provide.


texts: SQL AlwaysOn for application running on workgroup servers

texts:
I'm running a SQL 2012R2 with AlwaysOn setup in an isolated cloud environment. We have 2 VMs running AD and 2 VMs running SQL 2012R2 with AlwaysOn configured. All these have been configured and works perfectly fine.

My problem is that the application servers in this environment (web, app and etc) are all non-domain servers(workgroup). Because of this, they are unable to resolve the active IP address of the AlwaysOn AG listener's FQDN. Instead, they can only connect to the database via the physical IP of the SQL server.

My question is, is it possible to configure the workgroup computers to some how use the DNS service provided by AD so that they can then resolve the IP of the AG Listener and yet remain as a workgroup computer?


texts:
I've got a mongo cluster with three nodes, running 2.6, but probably going to 3.2
What is the best method to back-up a database in a replica set of three mongodb servers?
Should I use mongodump --db [dbname] --out [path]?
And do I back-up from the primary node, or the slaves?
The database is about 30GB, so will the performance decrease during the backup of the other nodes?
I looked at the documentation here and here, but could not find my answer.


texts: What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

texts:
According to the docs:

  CONCURRENTLY
  Refresh the materialized view without locking out
  concurrent selects on the materialized view. (...)

  ... OTHER CONTENTS ...

  Even with this option only one REFRESH at a time may run against any
  one materialized view.

I had a function that checked the last refresh time for a MATERIALIZED VIEW and, if more than 60 seconds had passed, it would to refresh it.

However, what would happen if I try to refresh a materialized view from two separate processes at the same time? would they queue or would they raise an error?

Is there a way to detect when a MATERIALIZED VIEW is being refreshed and therefore avoid touching it?

Currently, I have resorted to populate a table record before refreshing (setting refreshing to true) and then setting it to false when the process has finished.

EXECUTE 'INSERT INTO refresh_status (last_update, refreshing)
         VALUES (clock_timestamp(), true) RETURNING id') INTO refresh_id;
EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_mat_view';
EXECUTE 'UPDATE refresh_status SET refreshing=false WHERE id=$1' USING refresh_id;

Then, whenever I call this procedure, I check the most recent last_update and its refreshing value. If refreshing is true, then don't try to refresh the materialized view.

EXECUTE 'SELECT
           extract(epoch FROM now() - (last_update))::integer,
           refreshing
         FROM refresh_status
         ORDER BY last_update DESC
         LIMIT 1' INTO update_seconds_ago, refreshing;

IF(updated_seconds_ago > 60 AND refreshing = FALSE) THEN
  -- the refresh block above
END IF;

However, I'm not sure the refreshing flag is being updated synchronously (I mean, it really waits for the refresh to actually be complete)

Is this approach rational or am I missing something here?


texts: Why does adding a simple CHECK constraint take so long on Postgres

texts:
I'm using PostgreSQL 9.6.1 on Amazon RDS

I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows.

ALTER TABLE event_click_2016_8
ADD CONSTRAINT click_2016_8_chk CHECK ( timestamp_ >= '2016-8-01' AND timestamp_ < '2016-9-01' );

The above takes 15 minutes to complete. Why is it so slow? What specifically is holding up this operation? There is nothing else running on the DB and no locks being held by other processes.

r3.large instance (2 vCPU, 15 GiB RAM)
maintenance_work_mem = 256147
work_mem = 1024000


texts: when selecting from multiple tables I get a multiplication of the records

texts:
When executing this query below it takes too long. And the result is too much records,
more than possibly in the database.
The query should return around 117.000 records. That is the amount of records enterred into the database during a period of half a year.

Why is this query multiplying, resulting in millions of records?
If someone could give me the clu where I'm stuck, I'll be very happy.

/* Formatted on 2011/08/05 17:09 (Formatter Plus v4.8.7) */
SELECT   t2.flag, t3.reg_number, t1.reportdate, t1.latitude, t1.longitude,
     t1.value2 course, ((t1.measspeed) * 3.6) / 1.852, t1.status alarms
FROM traffic t1, details t2, registernumbers t3
WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)
ORDER BY reportdate DESC

Thanks in advance,

Maarten


texts: SQL Server - Select most recent record from each group when performance is critical

texts:
I run a SQL Server 2016 database where I have the following table with 100+ millions rows:

StationId | ParameterId |       DateTime       | Value
    1     |      2      | 2020-02-04 15:00:000 |  5.20
    1     |      2      | 2020-02-04 14:00:000 |  5.20
    1     |      2      | 2020-02-04 13:00:000 |  5.20
    1     |      3      | 2020-02-04 15:00:000 |  2.81
    1     |      3      | 2020-02-04 14:00:000 |  2.81
    1     |      4      | 2020-02-04 15:00:000 |  5.23
    2     |      2      | 2020-02-04 15:00:000 |  3.70
    2     |      4      | 2020-02-04 15:00:000 |  12.20
    3     |      2      | 2020-02-04 15:00:000 |  1.10

This table has a clustered index for StationId, ParameterId and DateTime, in this order, all ascending.

What I need is, for each unique pair StationId - ParameterId, return the most recent value from DateTime column:

StationId | ParameterId |       LastDate
    1     |      2      | 2020-02-04 15:00:000
    1     |      3      | 2020-02-04 15:00:000
    1     |      4      | 2020-02-04 15:00:000
    2     |      2      | 2020-02-04 15:00:000
    2     |      4      | 2020-02-04 15:00:000
    3     |      2      | 2020-02-04 15:00:000

What I'm doing now is the following query, which takes around 90 to 120 seconds to run:

    SELECT StationId, ParameterId, MAX(DateTime) AS LastDate
    FROM  MyTable WITH (NOLOCK)
    GROUP BY StationId, ParameterId

I've also seen many posts suggesting the following, which takes 10+ minutes to run:

    SELECT StationId, ParameterId, DateTime AS LastDate
    FROM
    (
       SELECT StationId, ParameterId, DateTime
       ,ROW_NUMBER() OVER (PARTITION BY StationId,ParameterIdORDER BY DateTime DESC) as row_num
       FROM  MyTable WITH (NOLOCK)
    )
    WHERE row_num = 1

Even in the best case (using GROUP BY clause and MAX aggregate funcition), the execution plan doesn't indicate an Index Seek:

I wonder if there's a better way to perform this query (or to build the index) in order to achieve better execution time.


texts: Pros and Cons of creating a new table to store matches vs frequently running query to find matches

texts:
I apologize if this type of theoretic question is not allow.
The application I am building will return a list of best matching venues from my database given an array of user selections. Typically we are returning around 50 venues. Users can also like or dislike a venue which will need to get stored in the database.  I am trying to decide if I should create a new table in my database to store best matches for each user or if I should run my search algorithm with the user selections to get this list.
Initially I thought storing the data would be best.  However, the user may want to change some of the answers on the questionnaire in which case I would need to rewrite the list of best matches.  Also this table will grow quite large if I need to store 50 entries per user. The pros of storing the data are that it is quite easy to query and find the user's matches.  Also it is easy to add a column into the matching table to indicate if a user liked or disliked the venue.
The other option is to resubmit the questionnaire each time.  The obvious con of this is that I will be searching through a large database frequently which may be expensive. On the pros side I would eliminate the large table of user matches from my database and it would be easy to handle changes to the questionnaire.
Any help would be appreciated as I am just getting into the database side of things and am looking to learn as much theory as possible.


texts:
I have three servers with own databases, and database schemas are same. Let's call them servers A, B and C.

All three servers will be operational, which means that all three servers will serve for its region.

Data from servers B and C need to be replicated on server A, so that server A will contains data from all servers, but other two servers contain just own data.

I need to implement some replication solution to achieve this goal, and I am asking if it is possible to have two publishers (server B and C) and one server with two subscription (server A)? Is there any better solution? Which replication model will be the best?


texts: How can we clean up/backup of .DBF files in Oradata folder in ORACLE on windows

texts:
We have very less space on our C Drive where the DBF files are stored in windows server.
Can we clean up some space any logs can we removed on observation could see that DBF files are taking huge sapce


texts: SQL Server database level audit filtering does not work

texts:
I have SQL Server 2014 Enterprise Edition installed. Due to privacy considerations, SELECT statements on private data need to be logged, except for a specific domain user, used by a service which writes to the database.

Creating a database level audit specification works well. E.g.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [public])
WITH (STATE = OFF)
GO

causes all SELECT statements from everyone to be written to the log file. When I replaced SCHEMA::[dbo] with [MyTable], the log was correctly filtered for SELECTs on MyTable only.

But when I replaced public by specific user name MYDOMAIN\myuser, the log entries are not filtered for the given user name as I would have expected.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [MYDOMAIN\myuser])
WITH (STATE = OFF)
GO

Every SELECT issued by any user was logged instead.

I queried sys.server_principals to check whether the given user was a valid principal name, but it actually showed up in the result.

What is wrong with my second audit specification?

Edit: It seems like if the CREATE statement in question has been silently discarded. When issuing Script Database Audit Specification as ... in SQL management studio, the original statement is written to the query editor window.
However, changing the value to others than user names (e.g., replacing public by dbo) worked well.


texts: Does Grant on MySQL overwrite previous permissions?

texts:
I ran the following command per AWS Aurora's documentation to give my user permissions to write to S3: GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'. Unfortunately, it revoked permissions to do anything, so I couldn't even run a basic select statement on any table.

So what would be the proper statement to append or add the additional permission to run select into s3 for a user?


texts: Installing Oracle 12c on Red Hat Enterprise Linux 6

texts:
When I try to ./runInstaller I get this error message:

  Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives.

I have two database unzip folders, database and database(0).

I copied database(0)/install/.oui to database/install, but the error message does not disappear.

This is the part of runInstaller:

# Abort the install, if install/.oui file is not found in the stage area
#
if [ ! -f $CMDDIR/install/.oui ]
then
        echo "Source location is incomplete. Make sure you have downloaded and extracted
all the relevant archives."
        exit 1
fi

#

This is the list of my database directory:

Certainly I have the .oui in database/install.

What can I do to resolve this error?


texts: preventing FullTextSearch from using wildcards

texts:
I am using FTS in a sql2008 sql server and had a user complain about some searches breaking. after root cause analysis we found only searches like U.S.A. fail because FTS tries to run wildcard matches on the periods.

whats the correct way of solving for this when the UI is a web page?

Is swapping FTS for a select using like '%search%' an option? and will it maintain performance of FTS?


texts: Why is there a writable instance inside a secondary region for MySQL AWS Aurora Replica?

texts:
I have created two Aurora MySQL clusters with two nodes in two regions using terraform. The secondary region is set up to replicate from primary region using binlog. This seems to be set up correctly, describe-db-clusters shows replication info and Replication source is displayed on the secondary cluster in the Console.
What confuses me is that in the secondary region, there is a write node. I can connect to it, add data to table and it gets propagated back to primary region—and vice versa. I am specifically not using multimaster cluster (which is single-region only anyway) and I am not using global database as I want to scale to more regions. Based on AWS docs, only global database supports write forwarding.
I only tried with manually connecting to the instances using mysql CLI as I want to understand the characteristics before using this with an application.
How is this possible? Is there any documentation? How does this setup handle update conflicts?


texts: Foreign key in one table have high read performance or another table with two primary key?

texts:
I want to know which one is better for high read performance. Foreign key in one table have high read performance or another table with composite key?

In Figure1 "idInvoice" is nullable some Sanads come without Factor

Q1- For example I want to list all Notes Invoice or Find is there any Invoice for special Note.
Q2- Which one is better if "idInvoice" can not be null.

Figure1

Table Invoice:
idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:
idNote //Primary key
type
liable
credit
idInvoice // Foreign key (Nullable)

OR

Figure 2

Table Invoice:
idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:
idNote //Primary key
type
liable
credit

Table InvoiceNote:
idNote //composite key - Foreign key
idInvoice //composite key - Foreign key


texts:
I am using MongoDB 3.0.10 with 10 databases in it,

also has implemented 3 member replica Set for it,

the question is it possible to replicate only selected databases from Primary to Secondary? If yes, then How?


texts: Combining two event tables into one postgres view


texts: Count total number of impression views and clicks for same ad id for diffrent ip


texts:
I have master-slave replication topology in MySql. I have taken the database dump from slave server and restore it on new slave. i just want to know that while pointing it to master what could be MASTER_LOG_POS= in below command.
CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='', MASTER_LOG_FILE='',MASTER_LOG_POS=;
i have two option

SHOW MASTER STATUS where i will get the MASTER_LOG_FILE name and MASTER_LOG_POS=
show slave status \G; where also i will get Master_Log_File and Read_Master_Log_Pos

so which one is correct. I have taken the dump from slave server.


texts: DateTime value has additional hour after insert

texts:
I developed a Windows Service (with a fixed culture of "de-DE") in C#. This service is getting data from an API and copies it over to a Navision Database.

My problem is following: After I've copied the data, every DateTime value got an additional hour. So if the DateTime value from API was 13:25:00, in the Navision database it is now 14:25:00.

When retrieving the DateTime value from API it is of kind "Unspecified".

To insert it into the Navision database I'm doing a simple
INSERT INTO [TABLE] (..., [Date_Time], ...) VALUES (..., [DATETIME VALUE], ...).

This just happens on customer side. In my test environment I can confirm that the same DateTime value from the source is inserted into the database.

I used the Database Creation Script provided by the customer to create the database in my test environment.

I cannot see all sources of failure, because I'm not yet so deep into this.


texts:
I'm moving database from Oracle to SQLServer (2012 if that matters). The process of migrating tables/views is more or less straightforward and automated. Triggers are also not an issue since I don't have too many of them.

However, I wonder what is the best practice for moving Oracle packages. As far as I know, SQLServer doesn't have anything similar. My idea is to map each package to SQLServer schema (thus, for instance, I'll have 'package1' , 'package2', etc schemas in SQLServer, each of them has no tables, just functions/procedures/types). I believe it will preserve original purpose of packages and at the same time reduce number of changes required on application side.

Does it make sense or I'm on a wrong way?

Thank you.


texts:
I am a computer science student and yesterday in my class on database management systems and talking about distributed systems and good practices he said that we can reference other instances through something he called 'connetion stream' or 'connetion String 'I really did not quite understand what he said. But I raised the following question:

I theoretically own a license db, application X and application Y, are totally different applications and only share the access user. In the first database are stored company information, license and access users; In database Y and X are different data that need to share the same user that is in the license database.

I tried in SQL Server as follows:

CREATE TABLE Licences;
USE Licenses;
CREATE TABLE Persons(
 Id int identity(1, 1),
 Name varchar(100) unique not null,
 //Other user information
 primary key(Id)
);

CREATE TABLE X;
USE X;
CREATE TABLE XInformation(
 Id int identity(1, 1),
 //Other X information
 PersonId int not null references Licenses..Persons(Id)
);

CREATE TABLE Y;
USE Y;
CREATE TABLE YInformation(
 Id int identity(1, 1),
 //Other Y information
 PersonId int not null references Licenses..Persons(Id)
);

But I get an error saying that sql server does not support foreign keys between databases.

But if I create these tables without referencing I can query them like this:

SELECT * from Persons as p inner join X..XInformation as xi on p.Id = xi.PersonId;

What is the correct way to do this referencing?

How would a dba solve this case?

Can I only reference other databases in Views?

Thanks in advance.


texts: What are some common and useful fields for a users table in a database?

texts: I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, real name, email, etc..., but what are some other things that might be useful to know about the user, in a general sense? (I've been tossing around things like last login, login location)


texts: Slow queries related to subqueries using aggregation

texts:
Here's a simplified version of the queries I am running:

select
    ...
from
    editions
    join edition_events on edition_events.edition_id = editions.id
    join events on events.id = edition_events.event_id
    join (
        select
            event_id,
            array_agg(date_start),
            array_agg(date_end)
        from
            event_dates
        group by
            event_id
        ) as dates on dates.event_id = events.id
WHERE
    editions.id = ?;

These queries ran great with my test data (~1500 events).  Now that I'm starting to use a copy of the real data (~400,000 events, ~500,000 event_dates), my application is timing out before I get results back from the database.  I have added indexes on all relevant columns, which only helped a little bit.  I've narrowed down the problem to the subqueries that use aggregation.

Now the real version of these subqueries does things like time zone manipulation or a count instead of array_agg, so using a trigger to update a counter column is not practical in many instances.  The dates associated with events must be individual rows rather than stored as an array since they have a foreign key relationship with other tables.

What would be my best bet for improving performance here?  I understand that the subquery is being evaluated first, but I don't know which dates I actually need to look at until I've done the joins.  I have looked at lateral joins, but I am uncertain if they would help here (and most information I can find is along the lines of "look, new feature!").  Since I am still using 9.2, I don't want to ask to upgrade to 9.3+ if it won't actually solve my problem.


texts: MSSQL - Primary Key Clustered specifying multiple columns

texts:
I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:

 ALTER TABLE [dbo].[Distribution_Batch] ADD  CONSTRAINT
 [PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
 (
    [BATCH_ID] ASC,
    [DATE_CREATED] ASC,
    [CONTACT_ID] ASC,
    [LAB_CODE] ASC,
    [DISTRIBUTION_TYPE] ASC,
    [CREATOR] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Here is the table itself, these are the columns:

 SELECT TOP (1000) [BATCH_ID]  --- this is a varchar(100) field
       ,[LAB_CODE]
       ,[DISTRIBUTION_TYPE]
       ,[CONTACT_ID]
       ,[CREATOR]
       ,[DESCRIPTION]
       ,[DATE_CREATED]
       ,[DATE_COMPLETED]
       ,[DATE_DEADLINE]
       ,[DATE_CONFIRMED]
       ,[ERROR_CODE]
       ,[CONTENT]
       ,[ADDED_BY]
       ,[SOURCE]
       ,[STATUS]
       ,[DELIVERY_METHOD]
       ,[FILE_SIZE]
       ,[DISTRIBUTION_FORMAT]
       ,[CONTACT_ID_ORIGINAL]
   FROM [dbo].[Distribution_Batch]

All this table does is queue up distribution jobs to be run by our application.  Nothing fancy.

They have indexes on numerous columns:

 [DistributionBatch-ContactId-DistributionType-20161108-121538]
 [idx_date_completed]
 [idx_date_created]
 [idx_DistribBatch_BatchId]
 [idx_Distribution_Contacts]
 [IX_Distribution_Batch]
 [IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
 [IX_Distribution_Batch_LAB_CODE_STATUS]
 [IX_Distribution_Batch_STATUS]
 [IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
 [PK_Distribution_Batch_1__23]

The system overall is slow and the admins response has always been to add more indexes to each table.   This table spits out data sequentially based on whether it is completed or not.  I can't understand why they would have so many indexes on columns that are never sorted on.  Am I missing something?

My question is 2 parts:

Part 1) Does that primary key make any sense?  Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?

Part 2) I need confirmation that none of those indexes make sense and are not necessary.   There are a lot more tables that have this same issue.

This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.


texts: How do I access a result set from a stored procedure in another stored procedure?

texts:
I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like this:

CREATE PROCEDURE consumerProcedureA()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE consumerProcedureB()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE supplierProcedure()
BEGIN
    SELECT field1, field2, field3, MESSYEXPR AS field4
    FROM (complicated sub-SQL query) -- blah blah blah
    ;
END;

Grant you, I could create a view to capture the common query, but is there a way to have a calling stored procedure access and process the result set returned by a called stored procedure?


texts: What is the "Chaos" Isolation level and when should it be used?

texts:
ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use.

The SET TRANSACTION ISOLATION LEVEL command in BOL (ah! see, I can use google and BOL) nothing seems to be named "chaos" and ADO.NET does have 5 modes that match up nicely to documented levels in addition to "chaos"

What or who is this Chaos level for? (And why does it have an unfriendly name?)

Refs:
The ADO.NET enum


texts: Can sys.database_permissions contain REVOKE?

texts:
The Microsoft documentation for sys.database_permissions mentions that the valid values for state are "D", "R", "G", "W". The corresponding values for state_desc are "DENY", "REVOKE", "GRANT", "GRANT_WITH_GRANT_OPTIONS".
The value of "R" / "REVOKE" is a surprise to me. Isn't "REVOKE" just part of the syntax for eliminating a previously GRANT'ed or DENY'ed permission?
Is it possible for the values or "R" / "REVOKE" to be present in this system view? If so, under what circumstances would it appear?
I know that if you REVOKE a DENY'd permission, the DENY'd permission goes away; and if you REVOKE a GRANTed permission the GRANTed permission gets removed. Under what circumstance would SQL Server register the REVOKE'ing in sys.database_permissions instead of simply removing the existing GRANT/REVOKE/GRANT_WITH_GRANT_OPTIONS row?


texts: Replace special characters in a column with space

texts:
I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid):

SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

How can I integrate the replace function into the select statement so that all characters other than alphanumeric, comma and space in the result set are  replaced by ' ' (space). This one won't work:

SELECT replace(columnA,'%[^a-Z0-9, ]%',' ')
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'


texts: SQL Server: How can I read server-side traces with limited permission?

texts:
I am trying to set up a trace on a remote server.  I am able to generate the trace, set filters, and even stop and delete the trace through sql server without an issue.  However, the file created with sp_trace_create seems to be owned by the SQL Service and I do not have administrative permission to grant myself read-access.

Is there something similar to Oracle's trace_files_public for SQL Server 2008 R2?

  "There is an undocumented parameter _trace_files_public that if set to
  true changes the file permissions in the user_dump_dest directory when
  trace files are created to allow everyone to read them."
  (http://www.petefinnigan.com/ramblings/how_to_set_trace.htm)

Is there a way I can view the contents through SMSS (since I seem to have adequate permission there, if not to the file itself?)
Something else that would be even better I haven't considered?

Sorry if my terminologies are rough--the whole dba thing is new to me.

Thanks in advance!  My sys-admin will appreciate not being bugged to set permissions (again.)


texts:
I'm running mysql diff on a reference db (created from scratch) and a dump of the current DB.

However, the compare dbs option only compares the names of the databases, which in my case are different. Because I'm running on the same server.

mysqldiff --server1=jaap@localhost --difftype=sql current:reference
# server1 on localhost: ... connected.
# Comparing `current` to `reference`                                   [FAIL]

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `current`
+++ `reference`
@@ -1 +1 @@
-CREATE DATABASE `current` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `reference` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

I there a way to compare all tables in 1 statement?


texts: MySQL Get first and last day of multiple records

texts:
I have a table HC_RSPI_data:

| RADIO_TYPE |   HOP | TOWARDS | RSPI |       TP_NAME |          DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max |
|------------|-------|---------|------|---------------|------------------------|-------------|----------|----------|
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 1 Radio 1 | June, 08 2015 00:00:00 |       86400 |       65 |       37 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 1 Radio 1 | June, 09 2015 00:00:00 |       86400 |       70 |       37 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 1 Radio 1 | June, 08 2015 00:00:00 |       86400 |       65 |       37 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 2 Radio 2 | June, 10 2015 00:00:00 |       86400 |       74 |       47 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 2 Radio 2 | June, 09 2015 00:00:00 |       86400 |       74 |       47 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 2 Radio 2 | June, 08 2015 00:00:00 |       86400 |       74 |       47 |

And i want to get the records with the first and last day like:

| RADIO_TYPE |   HOP | TOWARDS | RSPI |       TP_NAME |          DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max |
|------------|-------|---------|------|---------------|------------------------|-------------|----------|----------|
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 1 Radio 1 | June, 08 2015 00:00:00 |       86400 |       65 |       37 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 1 Radio 1 | June, 10 2015 00:00:00 |       86400 |       70 |       37 |

| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 2 Radio 2 | June, 08 2015 00:00:00 |       86400 |       74 |       47 |
| FlexiMetro | H3376 | RAPSANI | RSPI | ODU 2 Radio 2 | June, 10 2015 00:00:00 |       86400 |       74 |       47 |

OR

One record with  2 fileds Start date and End date like:
min(DATE_TIME_END) as Start date and  max(DATE_TIME_END) as End date

How can i achieve this?
I was trying something like this

SELECT DISTINCT rssi.*
FROM HC_RSPI_data as rssi
INNER JOIN HC_RSPI_data as j
on  rssi.RADIO_TYPE     =   j.RADIO_TYPE
and rssi.HOP            =   j.HOP
and rssi.TOWARDS        =   j.TOWARDS
and rssi.RSPI           =   j.RSPI
and rssi.TP_NAME        =   j.TP_NAME
and rssi.DATE_TIME_END = j.DATE_TIME_END or j.DATE_TIME_END = DATE_ADD(rssi.DATE_TIME_END, INTERVAL 6 DAY)

Any ideas?

Thank you.


texts: How to Perform Two Simple Queries using Select Value from First Query

texts:
I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select limit n id < selected.id and limit n id > selected.id

I am currently failing in getting the value from 1st query to perform union all queries. How can I fix it?

SELECT * FROM table WHERE column_b = 'a value' # this query returns ID.
union all
(select a.* from table as a where id < returned.id
order by id desc limit 5)
union all
(select a.* from table as a where id >= returned.id
order by id asc limit 15)


texts: Check and count if a column value is used in another column of same table

texts:
I have a table like this

and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0, also the 11/4/19 entry of 1 is ignored as the check is 0, so 9/4/19 entry is shown in result.

Next is the children count. it counts the number of times,the parent id is repeated. I really don't want the number of  times,the parent id is repeated,but just want to know if its has child or not.

I am confused how to get the children count. Remaining i did like this

SELECT id, modified,checks
FROM ( select *
       from test
       order by modified desc) as t1
where checks!=0  group by (id)

and i got this result

How to get children count too or just if the id got a child?
How to get a result like this


texts: How can I edit the content of a table in the data output of a query in pgAdmin III?

texts: I wonder whether I can edit the content of table in the data output of a query in pgAdmin III? The query is a select * from [...]. MySQL Workbench allows users to edit the content of table in the data output of a query, but I fail to see the same option in pgAdmin III.


texts: Proper indexes or anything else to optimize

texts:
I need help to optimize the query to avoid using "Using temporary", "Using filesort".

CREATE TABLE `target_bahrain_pepsi` (
    `id` int(11) DEFAULT NULL,
    `col_value` varchar(50) DEFAULT NULL,
    `source_file_id` int(11) DEFAULT NULL,
    KEY `id` (`id`)
) ENGINE=InnoDB

SELECT su.adbrand_id, sum(st.col_value) as amount
FROM statex_with_id su  INNER JOIN target_bahrain_pepsi st  ON st.id = su.id
GROUP BY su.adbrand_id
ORDER BY amount DESC ;

Table statex_with_id has also index.

mysql> EXPLAIN select su.adbrand_id, sum(st.col_value) as amount
    -> from statex_with_id su
    -> INNER JOIN target_bahrain_pepsi st
    -> ON st.id = su.id GROUP BY su.adbrand_id ORDER BY amount DESC ;
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | st    | index  | id            | id      | 58      | NULL             | 1804021 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | su    | eq_ref | PRIMARY       | PRIMARY | 4       | dashboard1.st.id |       1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+


texts: Windows Authentication fails with "Cannot generate SSPI context"

texts:
We have a Windows Server 2012 with SQL Server 2012 instance.

Its services used to run under an Active Directory account from an old domain, from Windows 2000 Server. A few years ago we replaced our AD to Windows Server 2012 and created a new domain.

Now we decided to replace the service account to the new domain. When that change was done, Windows Authentication failed to work. SQL Server shows message "Cannot generate SSPI context".

If I log in the server using MSTSC, Windows Authentication works. But if I try to log from my local PC, it fails.

I've read some support as https://support.microsoft.com/en-us/kb/811889, but it's kinda old and I'm unable to properly understand it.

Any idea what's causing this error, and how to solve it?


texts:
I have a mysql server running. Due to the uptime is not perfect, sometimes the HTTP server is blocked when mysql server is down intermittently.

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

Currently, I have to set up a cron job to run "mysql-admin flush-hosts" to ensure my HTTP server can connect to database after the block.

My question is, how can I get certain IP (eg my HTTP Server) from being blocked by MySQL server ? Is it possible?

https://dev.mysql.com/doc/refman/5.0/en/blocked-host.html


texts: SQL Database Design Relationship Choices

texts:
I'd like some advice about how to go about creating a database for the following situation:

I want to track my work hours.
I'll have three entities: Shifts, Category, Location (Example: 6am to 6pm, Nursing, Brisbane Hospital).

I understand the shifts can have one category and one location. So the main question is: Should Category and Location contain a list of shifts? Or I should let the front ent of my app deal with displaying things like "Shifts Per Category" or "Shifts per Location"?
Thank you!


texts: Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle

texts:
Well, I have a table with an Arabic column which may contain variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms:

with diacritics: "أَسْمَى";
with changing the last letter of "ى" into "ي" so it would be like "أسمي";
with kashida or "ـ" in some part of the word so it would possibly be "أسمــى";
with variant forms of alef hamza (أ - إ - ا - ء), so it might be "اسمى" or "إسمى";
any combination of the former cases, i.e diacritics and kashida.

I'm looking for a way to store these values in the database (actually I need a solution for SQL Server and another one for Oracle), and to retrieve them regardless of these differences.

I found that I should use an Arabic collation like this Arabic_CI_AI but this collation only helped me in sorting out the problems #1 and #2.

In addition I considered using a fulltext index on the column, but this has its drawbacks and it doesn't provide a full solution.

How can I resolve this?


texts: How to optimize MySQL query with more than 2 inner joins

texts:
select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )
    or id in ( select tcd.id
               from property_details as pd
                  , customer_details as tcd
               where pd.store_client_id = 2
                 and pd.customer_detail_id = tcd.customer_id
                 and pd.property_key = 'Accepts Marketing'
                 and pd.property_value = 'no' )
    and id IN ( select customer_detail_id
                from orders
                where store_client_id = 2
                group by customer_detail_id
                having count(customer_detail_id) > 0 )
    or id in ( select tor.customer_detail_id
               from ordered_products as top
                  , orders as tor
               where tor.id = top.order_id
                 and tor.store_client_id = 2
               group by tor.customer_detail_id
               having sum(top.price) = 1 )`

I have this MySQL query with inner joins so when it runs in MySQL server it slows down. What is the issue I can't find?

But after 4-5 minutes it returns 15,000 records. These records is not an issue may be.

In some tutorial suggest to use Inner join, Left join,... But I don't know how to convert this query in Join clause. Any help will be appreciated. Thanks in advance.


texts: Using the output from mysqldiff.exe to transform database2 to match database1

texts:
This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line.

I have recently been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. My goal is to spot any schema differences between database1 and database2, then going by what it says "on the label", I should be able to obtain statements that can transform either of the databases to match the other.

Here's example output I obtained by comparing database1.clients and database2.clients.

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database1.clients to database2.clients            [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- database1.clients
+++ database2.clients
@@ -61,4 +61,4 @@
   CONSTRAINT `clients_ibfk_6` FOREIGN KEY (`state_code`) REFERENCES `states` (`state_code`) ON DELETE
SET NULL ON UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_7` FOREIGN KEY (`rel_id`) REFERENCES `religions` (`rel_id`) ON DELETE SET NULL ON
UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_9` FOREIGN KEY (`nationality_id`) REFERENCES `country` (`Code`) ON DELETE SET NULL
 ON UPDATE CASCADE
-) ENGINE=InnoDB AUTO_INCREMENT=8041 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
+) ENGINE=InnoDB AUTO_INCREMENT=691802 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Compare failed. One or more differences found.

Problem is, I was expecting the output to be usable SQL statements, so I can then run this on the relevant side of the compare. How can I use this tool to perform database schema synchronization as I described?


texts: How do I delete unique rows from a table with duplicate keys?

texts:
I have the following data:

      road        |  ID  | other column  | other column |
------------------+------+---------------+--------------+
MAIN ST           |  1   |     ...       |     ...      |
MAIN ST           |  2   |     ...       |     ...      |
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
BERRY LN          |  5   |     ...       |     ...      |
BERRY LN          |  6   |     ...       |     ...      |
JOHN RD           |  7   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:

      road        |  ID  | other column  | other column |
------------------+------+---------------+--------------+
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

where the id's are any value, as long as the road column is unique.

Is there a simple way to do this in SQL or T/SQL? I followed the guide here, but it only applies to duplicate keys with non-unique rows.


texts:
I want to have row limitation(for example, display only 10 rows in every page) to my table in the report. I could limit the rows with Ceiling method where I could find refer. However, I have to group based on two criteria before using row limit. One is I want to group by category by category name, after that group by based on Region. And have my row limit after that. How could I achieve this? And I want to do all of these in report level. Kindly share any insight on that matter.

Thanks.


texts: SQL Server 2005 encryption - What Grants should I give

texts:
I use Mixed mode Authentication and I want my database users (in the Public role membership) to have the ability to JUST execute procedures that encrypt/decrypt data (using Certificate and Symmteric keys).

I have already given them Grant Execute on the procedures. What other Grants should I apply?

Thanx!


texts: Using IF ... PREPARE ... EXECUTE ... DEALLOC to create a temporary block?

texts:
What are the comparative benefits of these two statements,
SET @stmt = (SELECT IF(
  (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = DATABASE() AND
            TABLE_NAME = 'foo' AND
            COLUMN_NAME = 'bar'
  ) > 0,
  'DO 0',
  'ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;'
));
PREPARE sth FROM @stmt;
EXECUTE sth;
DEALLOCATE PREPARE sth;

vs..
DROP PROCEDURE IF EXISTS `proc`;
DELIMITER $$
CREATE PROCEDURE proc()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*)
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = DATABASE() AND
                            TABLE_NAME = 'foo' AND
                            COLUMN_NAME = 'bar');
    IF _count = 0 THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$
DELIMITER ;
CALL proc();
DROP PROCEDURE `proc`;

I'm just wondering if either approach is prefered over the other?


texts: How to increase the query performance on my fts query?

texts:
I'm pretty new to database world and I have the following table:
                        Table "public.so_rum"
  Column   |          Type           | Collation | Nullable | Default
-----------+-------------------------+-----------+----------+---------
 id        | integer                 |           |          |
 title     | character varying(1000) |           |          |
 posts     | text                    |           |          |
 body      | tsvector                |           |          |
 parent_id | integer                 |           |          |
Indexes:
    "so_rum_body_idx" rum (body)

I have dumped around 27M record. And I wanted to perform a full text search on my posts. body is a tsvector for posts and it is rum indexed.
However, when I run FTS, it is taking a long time. For example:
 EXPLAIN ANALYZE select count(*) from so_rum
    where body @@ plainto_tsquery('english','not fast enough data is slow much')

give back:
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=188.01..188.02 rows=1 width=8) (actual time=42207.386..42207.387 rows=1 loops=1)
   ->  Index Scan using so_rum_body_idx on so_rum  (cost=180.00..188.01 rows=1 width=0) (actual time=34716.047..42206.555 rows=497 loops=1)
         Index Cond: (body @@ '''fast'' & ''enough'' & ''data'' & ''slow'' & ''much'''::tsquery)
 Planning Time: 0.247 ms
 Execution Time: 42208.211 ms
(5 rows)

Another similar query, but fetching post this time:
EXPLAIN ANALYZE select posts from so_rum
    where body @@ plainto_tsquery('english',' why java type casting is throwing the exception? How to solve it')
;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using so_rum_body_idx on so_rum  (cost=216.00..224.01 rows=1 width=614) (actual time=45155.186..45516.358 rows=31 loops=1)
   Index Cond: (body @@ '''java'' & ''type'' & ''cast'' & ''throw'' & ''except'' & ''solv'''::tsquery)
 Planning Time: 19.540 ms
 Execution Time: 45517.985 ms
(4 rows)

How to reduce the query time here? What mistake I'm doing here?
Edit:
SQL query with analyze buffers (this is after vacuum full analyze):
EXPLAIN (ANALYZE, BUFFERS) select id from so_rum
    where body @@ plainto_tsquery('english',' what is run time time exception in java')
;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using so_rum_body_idx on so_rum  (cost=144.00..300.67 rows=38 width=4) (actual time=462.304..103072.850 rows=9841 loops=1)
   Index Cond: (body @@ '''run'' & ''time'' & ''time'' & ''except'' & ''java'''::tsquery)
   Buffers: shared hit=14898 read=8345
 Planning Time: 0.204 ms
 Execution Time: 103084.358 ms
(5 rows)


texts: Query detailing differences between rows for a large amount of data

texts:
I have a number of large tables, each with >300 columns. The application I am using creates "archives" of changed rows by making a copy of the current row in a secondary table.

Consider a trivial example:

CREATE TABLE dbo.bigtable
(
  UpdateDate datetime,
  PK varchar(12) PRIMARY KEY,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Archive table:

CREATE TABLE dbo.bigtable_archive
(
  UpdateDate datetime,
  PK varchar(12) NOT NULL,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Before any updates are executed on dbo.bigtable, a copy of the row is created in dbo.bigtable_archive, then dbo.bigtable.UpdateDate is updated with the current date.

Therefore UNIONing the two tables together & grouping by PK creates a timeline of changes, when ordered by UpdateDate.

I wish to create a report detailing the differences between rows, ordered by UpdateDate, grouped by PK, in the following format:

PK,   UpdateDate,  ColumnName,  Old Value,   New Value

Old Value and New Value can be the relevant columns cast to a VARCHAR(MAX) (there are no TEXT or BYTE columns involved), as I do not need to do any post-processing of the values themselves.

At the moment I can't think of a sane way of doing this for a large amount of columns, without resorting to generating the queries programmatically - I may have to do this.

Open to lots of ideas, so I'll add a bounty to the question after 2 days.


texts:
Using T-SQL, I can get the last known good DBCC Checkdb through DBCC DBINFO().
My question is, can I find, somewhere in SQL, when the Last DBCC Checkdb Ran? (regardless of being successful or not)

I would need this info to compare the Last date it ran with the last known good DBCC checkdb so that I can take action if the dates don't match.

Since I'm on SQL Express, I can't use SQL Server Agent to run the DBCC checkdb so it can be run randomly.

Any help is greatly appreciated.
Thank you


texts: Optimizing the repeated use of the same if condition inside a VIEW?

texts:
CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField2) "uniqueFieldName2")
    --more of these if statements with the same condition ,but different field
FROM table1
--various joins with other tables
);

I was wondering if following principles of DRY (don't repeat yourself) there would be a means of storing the same boolean condition being use in the IF statements for reuse to clean up the code and possibly make the query run better.

I have tried setting a boolean variable

set booleanIfCondition = table1.foo or table2.foo IN ('State1', 'State2');

However I get syntax errors whether I place it before the if statement or try to sneak in somewhere after the select.


texts: Storing small array of many possibilities

texts:
Let's say I need to store the text of a series of books, line per line in a database, and present them in a (CMS-based) website. I thought the correct setup would be:

a 'library' table: containing the ISBN-type info (per book-ID: author, title, year, maybe blurb) plus an array with the empty pages of that book.
a 'lines-of-text' table for each book.

The 'array' question here is different from the 'Storing integers for efficient select' and various similar questions (like 'match elements to dates of week') in that the set of (integer) options is not limited... it's sparse in a large and unbounded range AND because the entire use is not within SQL, but within PHP.

I now have my doubts:
[1] Should I put all the book-lines-tables together in one single table (adding a column containing the book-ID)? Or does it not matter really? There's cleanliness/readability, there's performance (once you've chosen a 'book', you're not going to switch --- so you'd be searching in a smaller table if there is many, is my idea). [Further info: I have to add (and occasionally delete) books. I also need to assign an owner to each book (that can edit the 'lines'), but that is outside the scope of Mysql I think.]

[2] How should I save the 'empty pages' array of each book?
--a Convert a php array to string (serialize/unserialize) and store as varchar (or tinytext)? Or
--b Make another table of books:empty-pages matches? Or
--c Use SET('1','2','3', ..., '999') to foresee all possible empty pages [and swear/blaspheme/explode violently the day I have to add a 1001-page-long book with an empty 1000th page]? Or
--d Calculate on-the-fly from the database?

I guess the 'SET'-approach protects against impossible arrays, from badly-intentioned 'owners' of books (which is rather improbable in my specific case); but sparse it's a lot of overhead and can easily break by extra-long entry. I guess on-the-fly is needlessly slow as it's an unchanging list (calculate once per 'book', not once every 'page'-request!).  I have the feeling solution (a) is the best given the circumstance, as I then put it in PHP which will be what's used to create the whole structure and linking (in HTML).


texts: Can a username and password be hard-coded in a system DSN

texts:
We have a legacy CRM that uses a DSN with Windows Authentication to connect to an instance of SQL Server 2005 (part of SBS 2003 Premium). We will soon be doing a major migration leaving us without a Windows Domain - as I understand things, that will mean we need to use an alternative authentication method. We are also planning to migrate from the SQL Server 2005 to SQL Server Express 2008 R2 in the same timeframe.

Unfortunately no modifications to the CRM are possible.

Leaving aside the obvious security issues, is it possible to hard-code a username and password in a system DSN? Alternatively is that another obvious approach to authentication that I am missing?

--EDIT

The DSN is defined in the ODBC Data Source Administrator in the "Administrative Tools" section of the Control Panel on the Windows clients.

--EDIT 2

I gave up trying to do this in the DSN thanks to Jonathan's answer. Fortunately I discovered an .ini file where I could insert a fixed username/password replacing the placeholders - problem solved.


texts:
I create a game in which players will gain levels. The basic information is in the table "char" (nickname, lvl, exp, money ...).

I want to make a ranking that will be updated on a regular basis and works in real time. I think it makes no sense to perform the "ORDER BY lvl" operation on the "char" table every time someone enters the "Ranking" tab on the site.

My plan is this:

1) Make an additional table: "ranking" in which the data will be physically sorted.

2) Create a trigger that will update the ranking every time someone reaches a new level or someone creates a new character.
Does it make sense and is more efficient? If so, how can I do it? If not, how else to do it?


texts: SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error

texts:
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.

INSERT INTO Activity (empid, action, log_date) VALUES
('a1', 'ON', '2019-01-01T07:00:00.000Z'),
('a1', 'OFF', '2019-01-01T07:30:00.000Z'),
('a1', 'ON', '2019-01-01T08:30:00.000Z'),
('a1', 'OFF', '2019-01-01T11:30:00.000Z'),
('a1', 'ON', '2019-01-01T12:30:00.000Z'),
('a1', 'OFF', '2019-01-01T12:45:00.000Z'),
('a2', 'ON', '2019-01-01T08:45:00.000Z'),
('a2', 'OFF', '2019-01-01T09:15:00.000Z'),
('a2', 'ON', '2019-01-01T10:00:00.000Z'),
('a2', 'OFF', '2019-01-01T11:45:00.000Z');

Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.

SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
      FROM Activity
      WHERE action = 'ON') AS s
JOIN (SELECT empid
      FROM Activity
      WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
                    FROM Activity
                    LIMIT 1
                    WHERE empid = e.empid
                    AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);

What seems to be the problem? I want the query result to look something like this:

|---------------------|------------------|------------------------|
|         empid       |        date      |      work_minutes      |
|---------------------|------------------|------------------------|
|          a1         |    2019-01-01    |        100 minutes     |
|---------------------|------------------|------------------------|
|          b2         |    2019-01-01    |        150 minutes     |
|---------------------|------------------|------------------------|

(work_minutes values above are random)

Any help will be greatly appreciated.
Thank you!


texts: In SQL Server 2014 Is it normal for for maintenance job to pick up a table that does not exist in database

texts:
As above, we are hitting an error on the maintenance plan running on SQL Server 2014.

During update statistics when we got the error.

  "Msg 2706, Level 16, State 6 , Line 1" Table Flow Detail does not exist".

And when we searched against the database, the table does not exists.

What could be the reason?

I just reviewed it and realized it is failing for some weeks now. I just took over the administration, what can we do?

The following queries both return no rows:

select * from sys.objects where type = 'U' and name 'Flow Detail'

SELECT  ss.name as SchemaName, so.name as ObjectName
FROM    sys.objects so
   join sys.schemas ss on so.schema_id = ss.schema_id
where type = 'U' and so.name = 'FlowDetail'

Last ran checkdb on this database previous week but that checkdb passed successfully.

Unfortunately I am unable to run Ola Hallengren's maintenance scripts as it is is my customer db server.


texts: Select items that have never been ordered using the MINUS operator

texts:
I have the following two tables:

ITEM (Item#, Item_Name, Unit_Price)

ORDER_ITEM (Order#, Item#, Qty)

Now, how do I list the items that have never been ordered by using the MINUS operator?


texts: Displaying only the most recent activity of an item

texts:
I have made a database that tracks the shipping and receiving of the tools that my company has. I am now wanting to make a query that shows only the most recent activity of all the tools. I have a current query that works pretty well but does not do exactly what I would like.

This is currently what the query is showing:

This shows roughly what I want. The only problem is that as you can see bellow it allows Serial_Number to be listed twice. Once for when it was sent and once when it was received. I only want it to display the most recent of the sent and receive.

For example instead of having the serial number 050675 listed once on 2/25/2015 as sent and once on 3/3/2015 as received I would only have the activity on 3/3/2015. And I cannot just limit it to looking for when the item is received because if you look at Serial_Number 1 the most recent item is Sent.

So far I have tried nesting IIf statements and also tried the DMax function and i was unsuccessful on both of them.

This is what I am thinking it should look like. I know this is very very rough but I really do not know how to or what I should use in coding this.

If Serial_Number <> Unique Then Select Serial_Number Where Recent Date is greater

This is the table that the data is being pulled from:

Thank you for any help you might provide.


texts: NEWSEQUENTIALID() resets sequence on server restart

texts:
MSDN learns me that NEWSEQUENTIALID():

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

So a newly generated GUID can be lower than a previously generated GUID. Isn't this highly undesirable in terms of index fragmentation and such?
And if so: is there some way to provide NEWSEQUENTIALID with a seed of some sort, to prevent this?

Or isn't this as bad as it seems as seeing the servers won't restart that often en we can deploy maintenance scripts for rebuilding indexes?