texts: Distributor latency - slow sp_MSget_repl_commands performance

texts:
One of our publishers is subscribed to by three subscribers, each subscriber is hosted on its own physical server. Two of those subscribers are 2008R2 and the third 2012. The 2012 subscriber is experiencing latency daily during peak times.

After running some extended event tracing for a couple of days I can see that the latency co-insides with slow sp_MSget_repl_commands execution times at the distributor, and more specifically the final select query found at line 724 "-- Mini join along. Only agent_id and article_id columns in dbo.MSsubscriptions". The execution time for this statement can run into >= 20 minutes.

Does the distributor reader thread wait for the entire execution of sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffers? Or does it start writing replicated commands to the command buffers as soon as they are returned by the SP? I see that the problematic statement is hinted with OPTION (FAST 1) which might suggest that this is the case. If the reader thread does wait for sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffer then that might explain the latency, otherwise I am unsure. Is there anything else obvious I can monitor?

The MSdistribution_history.comments suggest that the holdup is at the subscriber. However no replicated subscriber stored procedure are completing in >= 100 ms and there is no obvious physical server resource contention. I’ve ruled out the network because there are also other subscriber databases on the same physical server being populated across the same network which are not experiencing latency.


texts:
I've been looking around now, reading the mysql site and I still can't see exactly how it works.

I want to select and row lock the result for writing, write the change and release the lock. audocommit is on.

scheme

id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime)

Select an item with a status of pending, and update it to working. Use an exclusive write to make sure that the same item is not picked up twice.

so;

"SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR WRITE"

get the id from the result

"UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>

Do I need to do anything to release the lock, and does it work like I've done above?


texts: Is it possible to CREATE EVENT to another server?

texts: I have serverA and ServerB. I need ServerA to copy a database to ServerB and UPDATE/INSERT/DELETE as it changes. I think this can be done with replication but I don't want to continually open a connection. Is it possible to use CREATE EVENT or TRIGGERS? If so, where do I start?


texts: Which MySQL table to use for read-only, bulk-loaded tables?

texts:
So I have a workload on 2 key tables in my application with the following characteristics:

READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, there are no deletes nor updates.  I am using  INSERT IGNORE ...
Very Large Tables
I am using temporal partitioning, but even with that my expected worse case on a single paritition is 10M-100M records per paritition (older partitions are eventually deleted).
No need for FK, transactions, consistent reads
Theses table are used to seed very targeted time-sensitive queries and reporting views.  There is no need for enforced FK constraints (there are no explicit FKs) and certainly no transactions are needed.  There is also no need for clients issuing selects to have a fully consistent view of the data -- so if I am in the middle of a bulkload and they only see 1/2 of the in-progress updates, that's totally cool.

I initially was leaning towards InnoDB and then went back to MyISAM after reading about concurrent inserts, which sounds like a perfect description of my use case (high number of inserts with no intervening updates) I am thinking of going back to MyISAM esp given how many other features that I just don't care about (e.g. Foreign Keys, transactions).

Could someone with real-world experience using MySQL for similar workloads offer a suggestion on what storage engine is the right choice here.  Plan on doing some serious testing early next week but just would like to get a rough idea of what to expect/look out for here.


texts: Sort results by number of matching words

texts:
I have a table (called sentences) in which I have some sentences. I want people to be able to search it with multiple words, which would make the SQL query look like this:
SELECT content FROM sentences WHERE content LIKE "%<word1>%"
                                 OR content LIKE "%<word2>%"
                                 OR content LIKE "%<word3>%" etc... ;

How can I sort the results by the number of occurrences of different words, the most matching sentence comes first, etc...?


texts: ~/.my.cnf settings for a custom .mysql_history file

texts:
In my development environment I have a lot of databases, and I run the same query a lot of times. Long time ago I saw a post how to have a separated mysql_history per database. That allowed me to easily have in my history file only the queries that are relevant to that database.
My laptop died, and I haven't been able to find how to do that again.
I tried searching in the documentation, here, and in stackoverflow, but the only documentation I found was how to filter queries NOT to be stored in the history file, or how to disabled it completely.
I remember this setting was to be stored in the ~/.my.cnf file.
Any ideas how to do this?
Thank you.


texts: How to insert multiple returns of the select statement?

texts:
I have 3 tables. ClassList, Student & Faculty. I'm trying to assign the faculty to a specific class, e.g. I'm assigning "John Doe" to the section "Section1" where this "Section1" exists in the Student table column.

Outcome:

Student table

 StudentID       Name    Section
    1            user1    Section1
    2            user2    Section1
    3            user3    Section2

Faculty table

 FacultyID       Name       Subject
    1            Faculty1   Subject ABC
    2            Faculty2   Subject DEF

ClassList table

  ClassListID        StudentID      FacultyID    ModifiedDate

My insert statement is like this:

INSERT INTO ClassList
VALUES
(
    (
        SELECT Student.StudentID
        FROM Student
        WHERE Student.Section = 'Section1'
    ),
    (
        SELECT Faculty.FacultyID
        FROM Faculty
        WHERE Faculty.FirstName = @FacultyName
        OR Faculty.LastName = @FacultyName
    ),
    GETDATE()
);

I know that the select statement in the Student table returning a lot so I'm wondering if there's a way to add all the return query of it into ClassList table?


texts: SQL Database Mirroring Number Restriction

texts:
We are thinking of incorporating Database Mirroring. Company does not have time/budget for AlwaysOn Availability Groups. We currently conduct 24hr backup/restore from one server to another secondary server in geographical location. We want to reduce 24hr gap. Will utilize Asynchronous High Performance Mode.

We have around 50 databases, that we will move from Principal Server A to Mirror Server B.
Does SQL Server have any limitations on number of databases we can incorporate?

Company only consumes 1-2TB a yr, 50 million rows max in table, 200GB memory, 50 Cores on Principles,  same with Mirror server

Thank you,


texts: byte vs char length semantics in sqlplus

texts:
The following code works fine in SQLDeveloper

declare
x varchar2(5 char);
begin
select 'üüüü' into x from dual;
end;
/

But when I run it in sqlplus I get

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

I get a similar error when I in sqldeveloper changes the variable declaration to

x varchar2(5 BYTE)

The question is: why do I get the error in sqlplus and not in sqldeveloper?


texts:
If I have some T-SQL code like this:

While @done<1
Begin
WAITFOR DELAY '00:00:30';
Set @done=<some select statement>
End

Is this likely to increase waits for CXPACKET? Presumably the other processor(s) will be waiting whilst WAITFOR is running?


texts:
I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server.

I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it to a new 9.3 cluster at /srv/home/postgres/data using:

/usr/pgsql-9.3/bin/pg_upgrade -d /srv/data/postgres/ -D /srv/home/postgres/data/ -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ -u postgres --check

But I get an error:

SELECT c.oid, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM info_rels i JOIN pg_catalog.pg_class c ON i.reloid = c.oid   JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
ERROR:  could not create temporary file "base/pgsql_tmp/pgsql_tmp13803.0": No such file or directory

If I create an empty 9.2 cluster on the new server and run the same check, I get no errors.

Permissions seem to be the same for both the old, copied 9.2 cluster and the new initialized 9.2 test cluster (test 9.2 cluster on top; copied 9.2 cluster on bottom):

It's not clear to me what else I should try or what the problem is. Thanks!


texts: CASE Statement - 1st case is being ignored

texts:
I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users that are no longer on the database.

The table looks like this:

| username | db_instance | forename | surname | ...... |
|----------|-------------|----------|---------|--------|
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB2      |   xxxxx  |   xxxx  | ...... |

Here is the statement to create the view:

CREATE OR REPLACE FORCE VIEW usr_v_user_not_reg AS
 SELECT username "User", db_instance "Instance",
 (
  CASE
    WHEN username IN
    (
      SELECT username
      FROM SYS.dba_users
      MINUS
      SELECT username
      FROM usr_t_user_reg
    ) THEN 'not registered'
    WHEN username IN
    (
      SELECT username
      FROM usr_t_user_reg
      MINUS
      SELECT username
      FROM SYS.dba_users
    ) THEN 'no longer present'
  END
 ) "Status"
 FROM usr_t_user_reg
 WHERE db_instance = 'DB1'
 AND username NOT IN
 (
  SELECT username
  FROM usr_t_user_reg
  INTERSECT
  SELECT username
  FROM SYS.dba_users
 );

At first I thought it would work fine and then I noticed that the first case is kind of being ignored. I have some users who are not registered yet and they are not displayed in the output. If I use the first case as a standalone query

SELECT username
FROM SYS.dba_users
MINUS
SELECT username
FROM usr_t_user_reg
WHERE db_instance = 'DB1';

then it works. As soon as I want to use it in the whole query (even without the CREATE command) it doesn't work. The second case works without any problems.

I think I'm missing something, but I don't know what.

Additional considerations

I think I forgot to mention something. On the database are multiple instances and with the view I want to know on which instance there is a user who isn't registered yet or who isn't available anymore. The view I want to create should have an output similiar to this:

| username | instance |     status     |
|----------|----------|----------------|
|   XYA    |   DB1    | not registered |
|   XYB    |   DB2    | not registered |
|   XYC    |   DB3    | non-existend   |


texts: Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?

texts:
I develop an application that uses MySQL and explicitly uses InnoDB table engine.  In a few cases we have clients using this application with MariaDB or Percona.

I understand that MariaDB and Percona is a "drop in replacement", but my understanding is that this only means you do not have to migrate your data to switch from MySQL to MariaDB or Percona.  It says nothing about the SQL syntax being identical for applications that interface with these databases.

I am aware that the DDL for virtual columns are not identical between MySQL and MariaDB, but I'm not clear if this is a difference between InnoDB and Xtradb, or MySQL and MariaDB.

Or to put this question another way, is my applications guaranteed to run on MySQL, MariaDB and Percona so long as they are at equivalent versions and all tables are InnoDB?


texts: Difference between return next and return record

texts: What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?


texts: Is it possible to change recursion level of a CTE in a function?

texts:
I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a string passed in is longer than that. I know I can use OPTION (MAXRECURSION [value]) to change the limit, but this does not appear to work when defining the function.

My function:

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(Pos,Digit)
    AS(
        SELECT 1 AS Pos, LEFT(@String, 1) AS Digit
        UNION ALL
        SELECT Pos + 1, RIGHT(LEFT(@String, Pos + 1), 1)
            FROM Split
            WHERE Pos < LEN(@String)
    )
    SELECT Pos, Digit FROM Split
    ---OPTION (MAXRECURSION 0) -- Unable to create function if this line is UnCommented)
)

Thus the following sql code will fail due to hitting the 100 default limit:

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')

Now if I add the option, it will work

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')
OPTION (MAXRECURSION 0)

However, it is highly unlikely that those using this function will remember to add this extra bit to their queries so I am hoping to add this in the function it self. Is this possible? (And yes, I do ramble wildly when I need to come up with some string for testing)


texts: Is EAV a good choice for scenario when the number of fields may differ client to client?

texts:
Suppose that you are developing an application that could be used by multiple clients.

You have to store "basic details" of an employee of a company (your client) into your database.

The first 15 fields are going to be common among all the employees across all the clients.

But there is a possibility that Microsoft (your client) has employee basic details to have 15 + 3 fields.

Your client Apple could have basic details with 15+5 fields.
The additional fields may or may not be common among the clients.

Would you use EAV for this scenario?

Or would you create maximum number of columns to suffice the needs of both your clients. In which case the columns that are not used by individual client will remain null.
Of course this calls for the need to have alter and add more columns if you sell your application to third customer.

The drawbacks that I see are :

Complexity
Performance hit
Varchar(n) for everything
No foreign key constraints etc.

Is there a 3rd solution?


texts: Can you rename or name Reporting Services jobs?

texts: In Report Manager you can create schedules, either shared or one-of's.  When you create these schedules a job is created in SQL Server with names that look like guids.  IS it safe to rename these jobs to something readable and understandable, or will renaming them break something?  Or, is there to give these schedules a name in Report Mnager that will be reflected in the job?


texts:
Assuming a simple table, e.g.

\d+ task

      Column       |           Type           |                     Modifiers                     | Storage  |
-------------------+--------------------------+---------------------------------------------------+----------+
 id                | integer                  | not null default nextval('task_id_seq'::regclass) | plain    |
 parent_task_id    | integer                  |                                                   | plain    |
 cinema_id         | integer                  | not null                                          | plain    |
 venue_id          | integer                  |                                                   | plain    |
 movie_id          | integer                  |                                                   | plain    |
 event_id          | integer                  |                                                   | plain    |
 result            | json                     |                                                   | extended |
 context           | json                     |                                                   | extended |
 guide             | json                     |                                                   | extended |
 started_at        | timestamp with time zone |                                                   | plain    |
 ended_at          | timestamp with time zone |                                                   | plain    |
 created_at        | timestamp with time zone | not null default now()                            | plain    |
 updated_at        | timestamp with time zone | not null default now()                            | plain    |
Indexes:
    "task_pkey" PRIMARY KEY, btree (id)
    "public_task_cinema_id1_idx" btree (cinema_id)
    "public_task_event_id4_idx" btree (event_id)
    "public_task_movie_id3_idx" btree (movie_id)
    "public_task_parent_task_id0_idx" btree (parent_task_id)
    "public_task_started_at6_idx" btree (started_at)
    "public_task_venue_id2_idx" btree (venue_id)
Foreign-key constraints:
    "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
    "task_cinema_id_fkey" FOREIGN KEY (cinema_id) REFERENCES cinema(id) ON DELETE CASCADE
    "task_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id) ON DELETE CASCADE
    "task_movie_id_fkey" FOREIGN KEY (movie_id) REFERENCES movie(id) ON DELETE CASCADE
    "task_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES venue(id) ON DELETE CASCADE
Referenced by:
    TABLE "task" CONSTRAINT "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
Triggers:
    update_task_updated_at BEFORE UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column()

Assuming a simple view that selects tasks that either do not have parent task or the parent task is complete:

CREATE VIEW outstanding_task AS
  SELECT
    t1.*
  FROM task t1
  LEFT JOIN task t2 ON t2.id = t1.parent_task_id
  WHERE
    t2.id IS NULL OR
    t2.result IS NOT NULL;

I am attempting to use this view with FOR UPDATE:

SELECT *
FROM outstanding_task
LIMIT 1
FOR UPDATE SKIP LOCKED;

However, in postgres v10.4 this produces an error:

  ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join

How to use FOR UPDATE with a view that is built using LEFT JOIN?

Note 1: The question is specific to views. Without a view I could use FOR UPDATE OF t1.


texts: Can MySQL walk through the files of a directory?

texts:
Like the title says: can MySQL (alone, i.e. without C or Python) walk through the files of a directory? Or can you only do this through an API?

Sorry for the dumb question. Total noob here and I swear I couldn't find the answer on Google or this site.


texts: Execution plan percentages don't add up to 100%

texts:
I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan.

But the total of individual query costs adds up to more than 100%.

This is my query:

DECLARE @date SMALLDATETIME

SELECT Reffd AS NAME
    ,(
        SELECT (
                (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[customer]
                    WHERE upper(Reffd) = upper(main.reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    ) + (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[rep]
                    WHERE upper(Reffd) = upper(main.Reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    )
                )
        ) AS Completed
    ,(
        SELECT (
                (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[customer]
                    WHERE upper([call Attnd]) = upper(main.[Reffd])
                        AND cast(DATE AS SMALLDATETIME) = @date
                    ) + (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[rep]
                    WHERE upper([Call Attnd]) = upper(main.reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    )
                )
        ) AS Attended
    ,(
        SELECT (
                (
                    SELECT (
                            (
                                SELECT count(*)
                                FROM [cal_reg].[dbo].[customer]
                                WHERE upper(Reffd) = upper(main.reffd)
                                    AND cast(DATE AS SMALLDATETIME) = @date
                                ) + (
                                SELECT count(*)
                                FROM [cal_reg].[dbo].[rep]
                                WHERE upper(Reffd) = upper(main.Reffd)
                                    AND cast(DATE AS SMALLDATETIME) = @date
                                )
                            )
                    )
                ) + (
                SELECT (
                        (
                            SELECT count(*)
                            FROM [cal_reg].[dbo].[customer]
                            WHERE upper([call Attnd]) = upper(main.[Reffd])
                                AND cast(DATE AS SMALLDATETIME) = @date
                            ) + (
                            SELECT count(*)
                            FROM [cal_reg].[dbo].[rep]
                            WHERE upper([Call Attnd]) = upper(main.reffd)
                                AND cast(DATE AS SMALLDATETIME) = @date
                            )
                        )
                )
        ) AS Total
    ,'' AS f6row
    ,'' AS f8row
FROM [cal_reg].[dbo].[customer] AS main
WHERE cast(DATE AS SMALLDATETIME) = @date
    AND upper(reffd) IN (
        SELECT upper(shname)
        FROM common.dbo.Password_table
        )
GROUP BY reffd

And for that I am getting total of 104% as shown below:

+

My question is it possible to solve that more than 100% cost error or are there any other ways from which I can tell my query is running efficiently ?


texts:
Not sure if this is the right place to ask.
I am working on an ETL procedure that gathers the data about medication consumption and I have something like this:
Dim_Patient, the dimension for all the patients in the system
Dim_Doctor, all the doctors in the hospital
Dim_Medication, all the medications available
Dim_DateTime
F_Consumption:
    Id(PK)
    PatientId(FK)
    DoctorId(FK)
    MedicationId(FK)
    DateTimeId(FK)
    TreatmentId(DD)
    Quantity
    Current

Say that there was a record:

ID
PatientId
DoctorId
MeditacionId
DateTimeId
TreatmentId
Quantity
Current

1
1
1
1
202105031250
1
10
TRUE

And that for any reason there was a mistake, say, in quantity, which has been noticed the next day. For example, there were not 10 units administered but 5. After the ETL, I would end up with something like this:

ID
PatientId
DoctorId
MeditacionId
DateTimeId
TreatmentId
Quantity
Current

1
1
1
1
202105031250
1
10
FALSE

2
1
1
1
202105041250
1
5
TRUE

How do you set up the CUBE with this data? The quantity here is clearly non-additive.
Given that I work with Postgres, I would have created a MATERIALIZED VIEW with GROUP BY CUBE(...), filtering only Current = TRUE, but then I would lose track of history.
Do I even need to include historical data in the CUBE?
I am looking forward to hearing any thoughts on this.


texts: Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

texts:
Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes.

This question is NOT about the amount of space reported by the file system.

We have a log table with four VARCHAR(max) columns. The table currently holds roughly 180 GB of data.  The table grows at a rate of roughly 10 GB per month.

In an attempt to free up space for future growth, I recently deleted (in chunks over a 36 hour period) close to 30% of the data.

Based on the size of the table and the number of rows being removed, I expected the size of the table to be reduced by roughly 55-60 GB.  I verified this by taking a sample and calculating the average row size, and then multiplying that by the number of rows to be removed.  However, after finishing the delete, the table size has not changed.

While I am not looking to shrink the size of the .mdf file, I did check to see if the database had any free space that could be shrunk, but there is 0% free space.

So - I am confused.  I've verified the data that I deleted from the table is no longer there. I even compared it against a backup to get a before & after.  The number of rows in the database has changed, but the data space for the table has not decreased.

My questions then are:
1) Why did deleting data from the table not reduce the size of the table?
2) What can I do to actually reduce the size of the table?

EDIT:

I am determining the space used by the table by calling sp_spaceused 'tablename'.
I have used dbcc updateusage (0, tablename) prior to calling sp_spaceused.
I am NOT trying to free up disk space - although an analysis indicates that there is 0% space available to be reclaimed.
I have run an index reorganize with ( LOB_COMPACTION = ON ). Fragmentation is below 1%.
The table is not a heap. Table definition below.

 CREATE TABLE [dbo].[LARGE_LOG_TABLE](
  [ID] [varchar](36) NOT NULL,
  [DOC_ID] [varchar](36) NOT NULL,
  [REQUEST_DATA] [varchar](max) NULL,
  [REQUEST_DATA_XSLT] [varchar](max) NULL,
  [RESPONSE_DATA] [varchar](max) NULL,
  [RESPONSE_DATA_XSLT] [varchar](max) NULL,
  [ACTIVE_FLAG] [varchar](1) NULL,
  [CREATED_BY] [varchar](100) NULL,
  [DATE_CREATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_CREATE_DT]  DEFAULT (getdate()),
  [USER_LAST_UPDATED] [varchar](100) NULL,
  [DATE_LAST_UPDATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_LAST_DT]  DEFAULT (getdate()),
 CONSTRAINT [PK_LARGE_LOG_TABLE] PRIMARY KEY CLUSTERED
 ( [ID] ASC ) ON [PRIMARY] )
 ON [PRIMARY]
 TEXTIMAGE_ON [PRIMARY]

EDIT 2:

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), OBJECT_ID('database..LARGE_LOG_TABLE'), default, default, default)

EDIT 3:

The answers provided to the question at Delete query does not appear to open space on the SQL Server refer to freeing up space on disk / shrinking the physical size of the database. This question refers to removing rows containing LOB data and yet the database (internally) shows the LOB data has not been removed.  We removed 600000 rows of data, but the space used on the table level (not the file system level) was only reduced by a few bytes - not the expected 60 GB. This question is about removing the LOB data.


texts:
I'm working a query and I wasn't sure if my current approach is the "best" approach.
I got a table filled with values and a table with buckets that I use to group those values.

There are 2 types of buckets:

A priority bucket, if items apply to the conditions they should fall in this bucket and be excluded for any following bucket.
A simple range bucket, the values should fall within a range.

I set up a simplified version that can be used to test.
the [Filter] column in #tempBUcket here is used for the priority buckets

The table definitions

create table #TempData([Id] int, [value] int);
create table #TempBucket([Id] int, [FromVal] int,[ToVal] int,[Filter] int);

Some test data

INSERT INTO #TempData (Id,value)
    VALUES  (1,1),(2,2),(3,3),(4,4),(5,5),
            (6,6),(7,7),(8,8),(9,9),(10,1),
            (11,2),(12,3),(13,4),(14,5),(15,6),
            (16,7),(17,8),(18,9),(19,10),(20,11);
INSERT INTO #TempBucket(Id,FromVal,ToVal,Filter)
    VALUES  (1,0,3,0),(2,3,6,0),
            (3,6,9,0),(4,9,12,0),
            (5,NULL,NULL,1),(6,NULL,NULL,2);

Right now I have this query which works and gives the results I want but I feel this could be done better and more efficiently.

WITH filteredData as
    (SELECT tv.*,COALESCE(tb1.Id,tb2.Id) as BucketId FROM #TempData tv
    LEFT JOIN #TempBucket tb1 --prio bucket join
        ON tb1.Filter IS NOT NULL AND tb1.Filter = tv.Value
    LEFT JOIN #TempBucket tb2 --range bucket join
        ON tb2.Fromval < tv.value AND tv.value <= tb2.ToVal
    )
SELECT Count(fd.value) as count ,BucketId FROM filteredData fd GROUP BY BucketId;

dbfiddle.uk


texts: REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT for Readscale AG failover

texts:
I have 2 node  Readscale AG setup where each node is on a different data center. I want to know if  SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1); needs to be run even after setting both the replicas to Synchronous mode.

Also, Application services were shutdown before fail-over took place, so this is where my ambiguity lies. I used the following Microsoft documentation for the process..

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-2017


texts: SSIS keeps force changing excel source string to float

texts:
There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].

I can change it manually in the Output branch's External and Output columns, but the Error Output simply refuses to let me change the respective column.

Error at Extract Stations [Excel Source [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Group No" (29)".
Error at Extract Stations [Excel Source [1]]: Failed to set property "DataType" on "output column "Group No" (29)".

I have tried modifying the package xml. I tried the IMEX=1 and typeguessrow=0 but none of that has fixed my problem. Does this have any fix at all? There are also articles suggesting you modify your registry so that excel reads more then the default 8 rows before guessing the data type. I do not want to go this route because even if it were to work, then I would have to modify the registry on the machine I plan to run the package on.

The excel field to be imported into the SQL nvarchar field reads for example

295.3
296.33

but they are being written to the SQL table as

295.30000000000001
296.32999999999998

I put in dataviewers and the fields show

295.3
296.33

all the way thru execution which is correct but I guess when it hits the OLE DB Destination source it somehow converts it to the

295.30000000000001
296.32999999999998

which is wrong.

I also have values such as V321.1 and V213.34 for example which are definitively not integers and need to be stored as varchar.


texts:
Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where:

DBApp_DEV shall access DBGen_DEV, and
DBApp_QA shall access DBGen_QA.

From DBApp_DEV, I would do this in an SP:

select Col1 from DBGen_DEV.dbo.TableName

However, from DBApp_QA I would have do this in the same SP:

select Col1 from DBGen_QA.dbo.TableName

Now, how can I synonymize the database name in that SP?


texts: Differential backup failing - Ola Hallengren Script

texts:
So I have installed Ola Hallengren's maintenance solution on our SQL 2016 servers.

I have set the differential backups to take place every evening 7pm and full backup is scheduled for once a week.

I've noticed that differential backups are not being taken.

All the correct permissions have been assigned to the backup drive/folders and having a look through the error logs I keep seeing the same error which is...

('Error creating directory.', 16, 1)

No Azure VM snapshots take place around that time and I've also had a look at VSS and that is disabled.

Any ideas as to why this is not working?

This is happening on all of our SQL servers where the script has been setup not just one specific server.

Thanks!


texts: Getting duplicate results in many to many query

texts:
I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the read table means that book has been read by the corresponding user.

Relation defined in SQLFiddle

I want to select the books with their author names and also keep a count of how many people have read a particular book. Here's my query :

Question : Count of the number of readers for a book is wrong (coincidentally equal to the number of authors). For a book with more than 1 reader, the author names are repeated.

  SELECT b.id, b.title, COUNT(r.user1_id) AS read_ct,
         array_agg(author.name)
  FROM book b
  LEFT OUTER JOIN reader r ON r.book_id = b.id
  LEFT OUTER JOIN book_author ba ON ba.book_id = b.id
  LEFT OUTER JOIN author ON author.id = ba.author_id
  GROUP BY b.id

Which of these solutions is better ?

Solution 1 : Use `DISTINCT clause i.e.

 SELECT b.id, b.title, COUNT(DISTINCT r.user1_id) AS read_ct,
     array_agg(DISTINCT author.name)

Solution 2 : Use subquery

 SELECT s.id, s.title, s.names, COUNT(r.used1_id) AS read_ct
 FROM (
   SELECT b.id, b.title, array_agg(author.name) AS names
   FROM book b
   LEFT OUTER JOIN book_author ba ON ba.book_id = b.id
   LEFT OUTER JOIN author ON author.id = ba.author_id
   GROUP BY b.id
 ) AS s
 LEFT OUTER JOIN reader r ON r.book_id = s.id
 GROUP BY s.id, s.title, s.names


texts: How can I prevent UNPIVOT from being transformed into UNION ALL?

texts:
I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot of the SQL Monitor report for the isolated query:

Here is the same logic when run as part of the full query:

The colors correspond to the same tables in both screenshots. For the slow query, Oracle is doing a MERGE JOIN between two tables that do not have an equality condition in the JOIN. As a result of that, about 150 million intermediate rows are unnecessarily processed.

I am able to work around this problem with query hints or rewrites, but I want to understand as much of the root cause as I can so that I can avoid this problem in the future and possibly submit a bug report to Oracle. Every time I get the bad plan the UNPIVOT in the query text is transformed into a UNION ALL in the plan. To further investigate I would like to prevent that query transformation from happening. I have been unable to find a name for this transformation. I also have not been able to find a query hint or underscore parameter that will prevent it. I'm testing on a development server so anything goes.

Is there anything that I can do to prevent the query transformation of the UNPIVOT to UNION ALL? I am on Oracle 12.1.0.2.

I am not able to share the query, table names, or data for IP reasons. I was not able to come up with a simple reproduction. With that said, it's unclear to me why that information is needed to answer the question. Here is an example of an UNPIVOT query along with the same query implemented as UNION ALL.


texts:
I got to the point where I want to separate the MySQL database from the server where the website resides. And keep those 2 parts separate so I can do load balancing of the web servers later.
How do I calculate what resources I would need for the MySQL db server? At the moment there are some slow queries in the app which I cannot avoid, so this is why I want to separate the DB and increase the resources to handle more connections and more queries.
Would be better to have 2 replicated db servers and implement load balancing? What would be the best practice for that?
What would be the best practices for my situation?


texts: Unable to start SQL Server Browser service in Sql server 2005

texts:
we are unable to start the SQL browser service on one of our machine with below error:

I believe this to be the reason, why we could not connect to the instance with below error:

I tried troubleshooting as mentioned here, by changing value in registry from 1 to 0, but still no luck.

SQLservices are also running.

Please help on how to resolve this!

@Mark- screenshot after running commands provided:


texts: Can't restore database with (apparently) same version

texts:
I'm trying to restore a SQL Server database instance in my local machine I've backed up from an Azure Cloud environment but I'm getting the following message:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.2000. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

enter image description here
The version seems to be the exactly the same, so it doesn't make any sense to me. Am I missing something? Or you just can't restore a backup from an Azure Cloud at all?
Thanks in advance.


texts: Does creating View cause an overhead to DB?

texts: As my subject suggests, I would like to know does creating views cause an overhead to DB in terms of space, storage, performance?


texts: MYSQL Return lines whose sum of one field equals another tables field

texts:
I have a table of job payments, and when matching to transactions need to match up payments on the same day for the same customer that equal a single transaction for that day. I have been able to match, but only return the aggregated total, not each row.

I understand a join will be required but haven't been able to get it right.

DB:

create table jobs(
  jobid int(7),
  datein date,
  total numeric(5,2),
  chargeto int(5)
);
create table payments(
  payid int(7),
  paymentamount numeric(5,2),
  paymentjobno int(5),
  paymentdate date,
  paymenttype int(1)
);

insert into jobs (jobid, total) values (1000, 100, 4);
insert into jobs (jobid, total) values (1001, 50, 4);
insert into jobs (jobid, total) values (1002, 25, 4);
insert into jobs (jobid, total) values (1003, 220, 6);
insert into jobs (jobid, total) values (1004, 200, 7);

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, 01/10/2019, 1);

Code I have:

$groupmatches = $con->query("
SELECT j1.*, j2.*
FROM jobs AS j1
JOIN (SELECT jobs.jobid, payments.paymentamount, payments.paymentjobno, COUNT(*) AS count
  FROM (jobs LEFT JOIN payments ON payments.paymentjobno = jobs.jobid)
  WHERE payments.paymentdate = '$dt' AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
  GROUP BY jobs.chargeto
  HAVING SUM(payments.paymentamount) = '$at' AND COUNT(*) > 1) AS j2
ON j1.jobid = j2.paymentjobno;
");

It is matching and returning a single line with a count value of how many lines added up to a match, but I can't return each of those lines. What am I doing wrong?


texts: Replication feature in SQL Server 2017 on Linux

texts:
We have a running production using SQL-Server 2014 in production. It has replication running and partial subscribers in several data centers.
I had a request to evaluate SQL Server 2017 on Linux so it could serve for a new application running on Linux.
The issue is that it should be a subscriber as well and seems that Microsoft doesn't have replication in development yet.
Have someone already handled this issue of replication in any way?
Is Replication supported in SQL Server on Linux?
I opened a connect and perhaps some of you would vote for it.
Thanks in advance


texts:
I am using PostgreSQL 9.4 and having tough time with slow queries. Some of these use "like" and others use "not like" searches. For like searches I could resolve by using trigram indexes, but those with not like are a headache.

e.g.

select * from mytable where user_code not like 'TE%'

EXPLAIN ANALYZE says "Seq scan". How can I make Postgres use index in such cases?


texts: SQL Server can't see subst/virtual drive

texts:
I have been having trouble getting SQL Server to see subst/virtual drives at all.

For example:
I've used the subst command to create a F:/ drive that maps to C:/FDrive. The stored procedures use this F:/ drive to generate various files. If I programmatically change the folder location within the stored procs from F:/ to C:/FDrive they execute successfully.

Is there a fix or workaround available?


texts: How do I dictate the order of preferred replica on a Always On cluster?

texts:
I have a problem, I look after a massive estate. the newer AO clusters I built myself and they work fine. I have inherited once AO cluster which is often problematic. However, migrating isn't an option ATM.
My current problem is I notice Netbackup wouldn't take backups from the secondary. After investigation, I found it was trying to take the backup from the DR instance. I've set it to take from the Primary for now.
Current behaviour
AUTOMATED_BACKUP_PREFERENCE = PRIMARY

preferred replica results
DR = 0
primary = 1
secondary = 0
AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY

preferred replica results
DR = 1
primary =  0
secondary = 0
The behaviour I need it to be is how can I get it to be,
AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY

preferred replica results
DR = 0
primary =  0
secondary = 1
However I can't find anything on Microsoft to point me in the right direct?


texts: Query does nothing except context switches

texts:
I run Adam Mechanic's WhoIsActive every so often on my server and log results to a table.  I noticed a peculiar case while looking at some of the results and would like help interpreting what is happening.

There was a query that had been executing for about 30 seconds, but it seems it was not doing any work.  There were no wait stats, no reads, no writes, no CPU usage, but there were several thousand context switches.

   wait_info  CPU    tempdb_allocations tempdb_current  blocking_session_id  reads  writes context_switches physical_io physical_reads  query_plan  used_memory
   NULL       0      0                  0               NULL                 0      0      8,404            0           0               NULL        4

The query responsible is a pretty simple Select ... Into a temp table. And unfortunately I cannot reproduce the scenario, because if I run the query now, it finishes in under a second.  But but my curiosity is killing me and I'd like to understand what could have been happening during the 30 seconds when it seems to have been doing nothing.


texts: how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

texts:
I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than 8000 characters in length. My table has some column as

PageID int,
PageTitleEnglish nvarchar(200),
PageTitleArabic nvarchar(200),
PageDescEnglish nvarchar(500),
PageDescArabic nvarchar(500),
PageBodyEnglish nvarchar(max)
PageBodyArabic nvarchar(max)

If i keep PageBody as nvarchar(4000) then i a limited to 4000 characters and if i have to store Arabic version then i need 16000 bytes (As Arabic is Unicode and take 3 time more space then ASCII).

So i am only left with option of defining PageBody as nVarchar(max), This will have it downside from performance point of view. My actual question is if some data in PageBody column is less than 4000 characters will it MS SQL Store than data in inline column or separately in the database.

I looked for this on Google also but didn't find any relevant answer and how i can improve performance in such scenario.

Any suggestions for best practice for such design of multilingual CMS are welcome.

I need to Support Only two languages Arabic & English


texts: Schema based replication in Oracle using Data Guard

texts:
We have active-passive database replication in our system cluster, so passive db belongs to DR class of nodes. We're trying to design zero-downtime deployment so I went out with few ideas.

Since we have 2 databases schemas, is it possible to replicate only one and second one (which is not replicated) to use in full read-write mode?

Data guard is used to replicate data. In previous company, we did such thing easily with MySQL (free edition), but not sure is it possible with Oracle. For me, if MySQL can do it, Oracle can as well, but internet is not providing me the exact answer on it.

I read somewhere that it's possible to do some EXCLUDE commands and to limit what is going to be replicated, but need to use that excluded schema in full read-write mode.

Thanks


texts: How to determine the time out setting of mirroring?

texts:
I know that the default TIMEOUT of database mirroring is 10 seconds if you do not change it. And I can use

ALTER DATABASE [DatabaseName] SET PARTNER TIMEOUT 30

to change it to avoid 'accident alerts'. But I want to know, How long time is good for a production database? The 30s, 40s, 60s, or even 120s? Which condition is the most consideration?
Thanks in advance.


texts: Does Oracle do a native conversion to numbers for varchar2 that are aggregated?

texts:
I have a table in an Oracle 11g database that's similar to the following:

CREATE TABLE BUDGET_DATA (
  BUDGET_MONTH varchar2(30)
  , BUDGET_CATEGORY varchar2(30)
  , BUDGET_AMOUNT varchar2(30)
)

All the values in BUDGET_AMOUNT are numbers. When I do select SUM, the values come out as I would have expected if BUDGET_AMOUNT was actually stored as a numeric.

SELECT BUDGET_MONTH, SUM(BUDGET_AMOUNT)
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

I'm curious, does Oracle perform a native conversion to number if you put a VARCHAR2 in an aggregate function? I would have expected that I'd have to do the following to get the data to sum correctly:

SELECT BUDGET_MONTH, SUM(TO_NUMBER(BUDGET_AMOUNT))
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

Can someone point me to some documentation about this behavior? I haven't been able to find anything to describe it.


texts: The column order of composite clustered primary key on a partitioned table?

texts:
I have the following table.

create table T (K1 date, K2 int references S(Id), ....) on partitionScheme(K1)

The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). Which of the following primary keys (the column order is different) is preferred?

alter table T add primary key clustered (K1, K2)
alter table T add primary key clustered (K2, K1)

Or should the PK be non-clustered and create another clustered index?

A lot of the queries look like:

select ....
from T join S on S.Id = T.K2
where ....


texts: How to generate a dynamic file name for a mysqldump on Linux?

texts:
I want to schedule my MySql backs to happen on an hourly basis, but the problem is I want to customize the name of the file that is outputs but not to sure how to?

This is the command I am using now

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > db-backup.sql

This works but I would to have it output a file something like this, but for it to happen dynamically, when the command is run

1-01-18.13:50.db-backup.sql

How could I do this on a single line of code


texts: How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

texts:
I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.

What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details.  The fact that http://pqxx.org/ has been down for the past few days isn't helping me...

How would I go about inserting the contents of somefile.bin into a table with a BYTEA column?

What I have is along these lines:

pqxx::work work( conn );
work.exec( "CREATE TABLE test ( name varchar(20), data BYTEA )" );
work.exec( "INSERT INTO test( name, data ) VALUES ( 'foo', <insert filename.bin here> )" );
work.commit();

If it makes a difference, I'd like to use the new hex format for BYTEA available in PostgreSQL 9.1.


texts:
I'm trying to understand NO REVERT option effect of the EXECUTE AS statement. The documentation says:

  When the EXECUTE AS statement includes the optional WITH NO REVERT
  clause, the execution context of a session cannot be reset using
  REVERT or by executing another EXECUTE AS statement.

As I understand there are two limitations:

  session cannot be reset using REVERT

and

  session cannot be reset by executing another EXECUTE AS statement

Ok, I have two database users. One of them can impersonate the other:

CREATE USER U1 WITHOUT LOGIN;
CREATE USER U2 WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::U2 TO U1;

The first limitation is clear and work according to expectation:

EXECUTE AS USER = 'U1' WITH NO REVERT;
REVERT; --> ERROR: Msg 15196 The current security context is non-revertible.

With the second limitation meant, I expected that the following commands sequence

EXECUTE AS USER = 'U1' WITH NO REVERT;
EXECUTE AS USER = 'U2'; --> OK

will fail once the the second EXECUTE AS is reached. However, it didn't failed.

So, the question is what does the second limitation meant in the documentation exactly mean? May be I understand it incorrectly or check it inappropriately.


texts: Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)

texts:
Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.

Let's assume the following statement update three rows:

UPDATE table_name SET a=1 WHERE id IN (1, 2, 3);

In mysql 5.7.21: The binary log receive only one log containing the 3 rows changes. (Which is my desired behaviour)

However, when tested in mysql 5.7.11 and mysql 5.7.26, the same statement produces three separated logs. Each one of them containing each changed row.

I have checked the system variables and, at least, the ones who are related to the binary log, seem to be exactly the same on 5.7.21 and 5.7.26.

We have checked the startup variable --binlog-row-event-max-size, which could be causing the problem. Since the rows in our tables could be bigger than the default 8KB. But, we are not able to set this variable. Since we are working in Amazon RDS. (How can we do it?)

I need that the rows that are changed on the same statement are always batched in the binary log. Is there some variable which could be causing this behaviour?

Notes: There is no replication involved in our setup.


texts: GRANT EXECUTE ON SomeProc TO SomeRole AS dbo

texts:
I have a role ExecSP which I use to manage who can execute stored procedures. EXECUTE permission is granted on all SPs to this role.

I also use SQL Server Data Tools (SSDT) to manage MS SQL Server database schema.
In the file where my SP is defined, I have this:

CREATE PROC SomeProc AS
.
.
.

GO

GRANT EXECUTE ON SomeProc TO ExecSP

However, when I apply the change to the target database, and do the schema comparison, it reports differences in SP permissions, where on the DB side I have this:

GRANT EXECUTE ON SomeProc TO ExecSP AS dbo

I've tried to understand what AS dbo part represents, but I couldn't.

My questions:

Does it mean that the SP will be executed with dbo rights, regardless of who is executing it? (I guess/hope it doesn't.)
Why is this added automatically?
I want this SP to be executed with the rights the caller has - how do I achieve that?


texts: What are the possible performance implications of dropping a not used ‘surrogate key’ column?

texts:
I have a very large (>10m rows) table in a Postgres 9.5.4 database that collects readings from sensors. This table handles many inserts (~2k/minute) but isn't queried too often--only during a cache miss or when rebuilding the cache. Said table is essentially ‘append-only’--there are no updates. However, the queries that I do run are often fairly complex, and I care about optimizing read performance as cache misses can be very slow to a user.

In the business context of relevance, there are sensors that collect energy and power quality data from buildings; there can be multiple sensors for buildings, and a common query is aggregating facility level data. The table schema looks like so:

                                   Table "public.measurement"
    Column     |            Type             |                        Modifiers
---------------+-----------------------------+----------------------------------------------------------
 id            | integer                     | not null default nextval('measurement_id_seq'::regclass)
 meter_id      | integer                     |
 timestamp     | timestamp without time zone |
 value         | double precision            |
 _reading_type | smallint                    |
 facility_id   | integer                     |
Indexes:
    "measurement_pkey" PRIMARY KEY, btree (id)
    "ix_measurement_timestamp_meter_id__reading_type" btree ("timestamp", meter_id, _reading_type)
    "ix_measurement_timestamp_facility_id__reading_type" btree ("timestamp", facility_id, _reading_type)
Foreign-key constraints:
    "measurement_meter_id_fkey" FOREIGN KEY (meter_id) REFERENCES meter(id)
    "measurement_facility_id_fkey" FOREIGN KEY (facility_id) REFERENCES facility(id)

I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

I essentially run two different types of queries on this table:

"Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
"Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id column denormalizes the table, but saves what was an expensive join).

Questions

What would happen if I do not declare a primary ke?
The combination of (timestamp, meter_id, _reading_type) is guaranteed to be unique, so: what would be the implication of dropping the multicolumn index on these columns and defining a composite primary key?


texts: Postgres 9.3 copy data from another table with relation

texts:
Hi I have two tables in my database which is related, which is forms and qa_forms.

forms
->id
->name
-address
-phone
-calldatetime

and then `

qa_forms
->id
->forms_id
->name
->address
->phone

So the qa_forms is just the replicate of forms I already have data on both however I forgot to add calldatetime in my qa_forms. So what I did is i add new column on qa_forms which is calldatetime. As expected all the values will be null since I already have data before and this one is newly added, how can I get their respective calldatetime on forms on the fastest way I can do it? They have relation which is forms.id = qa_forms.forms_id.

Any query on that?


texts: Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset?

texts: I want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?


texts: What is this upside-down silver key icon

texts:
I inherited a SQL Server 2005 3rd party server and database to look into to be able to generate reports and dashboards on.
On one table, I found a new icon: a blue-silver upside-down key, alongside my other primary and foreign keys.
When I right-click it, the context menu gives me the option to "Script Constraint as", but when I try to "Modify" it, it opens the column design form (like primary keys), and yet, it appears in the "Indexes" section, not constraints.
Someone knows what it is called and what it is used for?


texts: run job with uncommitted transaction - how to find the transaction? is it still active?

texts:
By mistake I have run the job I just put into production,
but the COMMIT TRANSACTION was commented out.

when I look at the job log, it printed that it did my updates with no problem.

when I run

dbcc opentran

it says:

  No active open transactions. DBCC execution completed. If DBCC printed
  error messages, contact your system administrator.

no locks or blocks are in place,
but where is my transaction?

the code for my job is here:
with the commit transaction commented out

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '

-- no updates for france
--UPDATE FRStock.DBO.ItemStock
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM FRStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating FRStock.DBO.ItemStock '

-- us is on a different schedule
--UPDATE USStock.DBO.ItemStock
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM USStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '

UPDATE EUStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM EUStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating EUStock.DBO.ItemStock '

--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    WHILE @@TRANCOUNT > 0
         ROLLBACK TRANSACTION

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

the table used in the join comes from here:

how to insert a list of varchar values, into a single column table?


texts: partitioning by datetime vs date vs int performance

texts:
I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server.

We know we want daily partitions. The data has a natural DATETIME field in it, so my gut instinct is to partition on that value. However, we have discussed the possibility of adding a DATE column (with just the time information stripped out), or possibly coding the date as an integer (YYYYMMDD, e.g., 20130930) and using that as the partitioning column.

All queries on this data will be on a specific date (WHERE ItemTime = '2013-09-30') or range of dates (WHERE ItemTime BETWEEN '2013-09-15' AND '2013-09-30'). For now, we always query on a date, but future requirements may include time details (WHERE ItemTime BETWEEN '2013-09-29 20:30:00' AND '2013-09-30 10:15:45').

I have tested performance of each strategy on a few hundred thousand rows of data, and seen no real difference. The production deployment, however, will be in the hundreds of millions up to maybe a couple billion rows.

Is one of these strategies going to lead to more efficient queries than the others? Why or why not?

Thanks for your help.

[EDIT] The queries will be formatted by application code, so I'm not concerned about how to translate between DATETIME, DATE, and INT. I can assume the query will be properly formatted based on whichever partitioning scheme is chosen. I'm just trying to find out if partition elimination will be faster using one of these data types.


texts: Backup to the null device on SQL Server for Linux

texts:
In SQL Server on Windows, it's a common enough thing to backup a database to the NULL device if you need to take a backup but don't actually care about that backup.  This is useful for changing the recovery model in a dev environment, as an example.

The backup statement is something like:

BACKUP DATABASE [xyz] TO DISK = 'NUL:';

In SQL Server for Linux, one might assume /dev/null would be the logical equivalent, however if you attempt that:

  Processed 125224 pages for database 'xyz', file 'xyz01' on file 1.
  Processed 2 pages for database 'xyz', file 'xyzLog' on file 1.
  Msg 3634, Level 16, State 2, Line 16
  The operating system returned the error '31(A device attached to the system is   not functioning.)' while attempting 'DiskChangeFileSize' on '/dev/null'.
  Msg 3013, Level 16, State 1, Line 16
  BACKUP DATABASE is terminating abnormally.

The above command does appear to reset the log_reuse_wait and log_reuse_wait_desc columns in sys.databases, so perhaps /dev/null really does work.

I'm using SQL Server on Linux v14.0.900.75 on RedHat Enterprise Linux 7.2.


texts: Is there any way to use different InnoDB settings for different databases on the same server?

texts:
From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used for non-critical data that uses InnoDB with more relaxed settings related to disk writes.

Is there a way to achieve this setup?


texts:
I have a project called social media file sharing. I have this database schema and I need your opinion about what I should remove or add or edit on my database schema.

Every user can upload media types like Text, Images, Audio and Video. I assume that every media type can be set into album (like photo album) each album has many images. With this I create a table called Holder and table Connector to connect media type and their holder.


texts:
One of our stored procedure was using Cursor to update rows. Total rows to be updated varies from 1000 to 2000. In our scenario, this stored procedure was taking 50 minutes to complete the update.

I replaced the Cursor and used Table Variable. At any point of time, the Table Variable will also contain 1000 to 3000 rows. When executed the update finished in 2 minutes.

Server is Windows Server 2012 64-bit with 64GB RAM.
SQL Server 2008

I want to know whether to replace Table Variable with #TempTable? In the scenario mentioned above, Table Variable is working fine.

I checked few posts on Table Variable. One post says SQL Server decides when to keep Table Variable in memory and when to move it to TempDB.

For the above configuration mentioned, when will Table Variable be moved to TempDB?


texts:
I'm trying to change field type to bool among all documents in collection. Here is the code

db.product.find().forEach(function(doc) {
if (doc.checked === "Y") {
doc.checked = true;
} else if (doc.checked === "N") {
doc.checked = false;
}
db.product.save(doc);
});

Mongodb returns no error but doesn't convert fields.


texts: Order a nested table with path index according to list_order in SQLite

texts:
I have a SQL table with this nested structure:
CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);

My SQLite version does not support window function, currently my query at link SQLite query are returning as follow the result, but it's not ordered according to list_order, another detail is that the list_order column could come with all values equal to zero.
   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   1        Pois           0          0     1           4
   2        Então          0          0     2           5
   3        Teste          0          0     3           3
   11       Teste 3        3          1     3.1         2
   12       Teste 2        3          1     3.2         0
   13       Teste 1        3          1     3.3         1
   4        Fundo          0          0     4           2
   7        Profundo       4          1     4.1         1
   8        Dhdhd          4          1     4.2         0
   9        Gagagaga       8          2     4.2.1       1
   10       Fsfsfsfs       8          2     4.2.2       0
   5        Profundo       0          0     5           1
   6        Gigante        0          0     6           6
   14       Teste          0          0     7           0

What I want the path_index column to follow list_order and level columns, like here:
   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   14       Teste          0          0     1           0
   5        Profundo       0          0     2           1
   4        Fundo          0          0     3           2
   8        Dhdhd          4          1     3.1         0
   10       Fsfsfsfs       8          2     3.1.1       0
   9        Gagagaga       8          2     3.1.2       1
   7        Profundo       4          1     3.2         1
   3        Teste          0          0     4           3
   12       Teste 2        3          1     4.1         0
   13       Teste 1        3          1     4.2         1
   11       Teste 3        3          1     4.3         2
   1        Pois           0          0     5           4
   2        Então          0          0     6           5
   6        Gigante        0          0     7           6

How can I align the path_index sorted according to list_order and level columns?


texts: Use Natively-Compiled Stored Procedures in disk-based tables

texts:
Is it completely worthless to use a Natively-Compiled Stored Procedure over disk-based tables? (SQL Server 2016)

Should all the queries inside a Natively-Compiled Stored Procedure point only to in-memory tables? I ask, because I am inserting and updating table T1, but the source for such tasks are disk-based tables.


texts: Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

texts:
I have 3 tables, users (user info), mghours (volunteer time), mgtraining (training time). I want a result that lists some user columns, sum of volunteer hours and sum of two types of training:

uid lastname firstname mgstatus trainingyear vol at ce

I use the following and as long as I have the two fields from the mgtraining table and the LEFT OUTER JOIN for those in the query, I get skewed results. If I take that out of the query, my user info and vol columns are fine.

SELECT users.uid AS 'uid',
users.lastname AS 'lastname',
users.firstname AS 'firstname',
users.mgstatus AS 'mgstatus',
users.trainingyear AS 'trainingyear',
sum(mghours.hourstime) AS 'vol',
SUM(IF(mgtraining.trainingtype = 'AT', mgtraining.trainingtime, 0)) AS 'at',
SUM(IF(mgtraining.trainingtype = 'CE', mgtraining.trainingtime, 0)) AS 'ce'
FROM users
LEFT OUTER JOIN mghours ON users.uid = mghours.uid AND year(mghours.hoursdate) = YEAR(CURDATE())
LEFT OUTER JOIN mgtraining ON users.uid = mgtraining.uid AND year(mgtraining.trainingdate) = YEAR(CURDATE())
WHERE users.coid = 33
GROUP BY users.uid
ORDER BY users.lastname ASC

The users.coid = 33 references a county ID.

I've been pouring over this for hours. What am I doing wrong?


texts:
I created MERGE statements to transfer differences with a minimal set of required columns from production db into a staging database. The merged tables are planned to be used by different reporting and analysis scenarios (readonly).
The whole process of getting the differences into the staging tables should be fast (at least a lot faster then the current way of getting the whole table contents entirely every day by doing a DELETE followed by INSERT INTO for all data regardless of the amount of indeed changed data that I estimate to be about 5%).

Here is a sample:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON

IF NOT exists(select 1 from StagingDB.sys.tables t join StagingDB.sys.schemas s on t.schema_id = s.schema_id WHERE t.name like 'A' and s.name like 'staging')
BEGIN
        SELECT Top 0 [ID],[OID]
        INTO [StagingDB].[staging].[A]
        From [LiveDB].[dbo].[A];
END;

MERGE INTO [StagingDB].[staging].[A] AS Target
USING (
    SELECT [ID], [OID] From [LiveDB].[dbo].[A] where X = 0
) AS Source ([ID],[OID])
ON (Target.[ID] = Source.[ID])
WHEN MATCHED AND (Target.[OID] <> Source.[OID]) THEN
    UPDATE SET [OID] = Source.[OID]
WHEN NOT MATCHED BY TARGET  THEN
    INSERT([ID],[OID]) VALUES(Source.[ID],Source.[OID])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

IF NOT EXISTS(
    SELECT * FROM sys.indexes ix Where ix.name = 'PK_A' AND ix.object_id = OBJECT_ID('[StagingDB].[staging].[A]')
)
BEGIN
        ALTER TABLE [StagingDB].[staging].[A] add constraint PK_A primary key CLUSTERED ([ID])
END

This runs VERY QUICKLY for most of my tables. Unfortunately there are two tables that have much more rows and additionaly many more columns of those two tables are being used lateron in the process of reporting, so I have to transfer much more data.

While I can run this kind of merge command for 71 tables in within less than a minute, the two problematic MERGE statements run for about 2 hours each.
I could not yet find out why.

I suspect the amount of date but also the way I compare the tables differences. To find out if I have to run an UPDATE statement I do a comparison for each column that is included in the Merge Statement. So in case I have 10 colums the UPDATE condition of the MERGE looks like that:

WHEN MATCHED AND (Target.[OID] <> Source.[OID] OR Target.[OID1] <> Source.[OID2]  OR Target.[Text1] <> Source.[Text1] OR Target.[varcharlong1] <> Source.[varcharlong1] )  etc...

So it turns out that this kind of update condition check includes all colums and will not have an index. The staging tables always have a clustered primary key in uniqueidentifier and (so far) no additional indexes.

My question is: While the MERGE statement is running very quickly and efficient for most of our tables, it looks like this procedure is not suitable for the two larger tables including multiple columns.
Am I using the MERGE statement correctly in this case using the OR clause to make sure the rows need to be updated or is there a better way?
Is there a better way to quickly get the delta (changes) for a subset of columns into a staging table?
all of my tables do have a ROWVERSION, maybe this could be used to figure out the rows that have been changed somehow?


texts: sqlserver 2014 DEFAULT in VALUES using MERGE

texts:
I'm trying to insert or update multiple rows in an SQL Server 2014 database. Some of the columns may need to be set to DEFAULT, however sqlserver barfs when there is a DEFAULT. Incorrect syntax near the keyword 'DEFAULT'.

Here is a small representative example of a query. The real query is actually being generated using clojure's hugsql library, using data generated in the clojure program.

MERGE INTO [dbo].[IMOS] [T]
USING (VALUES ('!Default_Materials_1', DEFAULT),
              ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

In case anyone is interested here is the hugsql query:

-- :name insert-or-update-values! :!
-- :doc Insert the values into the database or if it matches the supplied where columns update the row
/* :require [wcc-variables.db.sql-macros :refer [columns values merge-set-  columns merge-on-columns]] */
MERGE INTO :i:table [T]
  USING ( VALUES --~ (values params options)
        ) AS [S] --~ (columns params options)
        ON --~ (merge-on-columns params options "T" "S")
WHEN MATCHED THEN
 UPDATE
 SET --~ (merge-set-columns params options "S")
WHEN NOT MATCHED THEN
 INSERT --~ (columns params options)
 VALUES --~ (columns params options)

And the output of a simple query it creates in hugsql's queryvec format:

[MERGE INTO [dbo].[IMOS] [T]
  USING ( VALUES (?,?,?,?,?,?,DEFAULT,?,?,DEFAULT,DEFAULT,DEFAULT,?,DEFAULT,DEFAULT,?)
        ) AS [S] ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
        ON [T].[NAME] = [S].[NAME] AND [T].[ORDERID] = [S].[ORDERID] AND   [T].[TYP] = [S].[TYP] AND [T].[OPTNR] = [S].[OPTNR]
WHEN MATCHED THEN
 UPDATE
 SET [CATEGORY]=[S].[CATEGORY] AND [OPTINFO]=[S].[OPTINFO] AND [PRODUCER]=[S].[PRODUCER] AND [FAMILY]=[S].[FAMILY] AND [WERT]=[S].[WERT] AND [POS]=[S].[POS] AND [SOURCE]=[S].[SOURCE] AND [CATALOG_ID]=[S].[CATALOG_ID] AND [DATE_LASTCHANGE]=[S].[DATE_LASTCHANGE] AND [LENGTH]=[S].[LENGTH] AND [SYS]=[S].[SYS] AND [WORKPLAN_ID]=[S].[WORKPLAN_ID]
WHEN NOT MATCHED THEN
 INSERT ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
 VALUES ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP]) Wardrobe Set - 1 Wardrobe Trim Stile Rail Zone Reveal JEFFWCC 1_WRDB_TM_FAM_STIR 0 1_WRDB_TM_STIR_ZR  IMOSADMIN 0 100]


texts: Calculating percentage of count and adding it to the count in MySQL table with the query

texts:
I have following basic MySQL query and I my aim is to artificially inflate the amount of today's orders by 75%.

Is there a more efficient way of doing this without using COUNT twice?

SELECT COUNT(order_id) + (COUNT(order_id) * 0.75) AS total
FROM orders
WHERE order_date BETWEEN BETWEEN CURDATE() + ' 00:00:00' AND NOW()

EDIT: I spotted an issue with my query (using + for concatenation when I should have used CONCAT)


texts: Log Shipping Monitor Stored Procedure returning Null

texts:
I have log shipping setup.  When I EXEC sp_log_shipping _monitor it returns all null values
even though log shipping appears to be working fine.

last_restored_file
NULL

last_copied_file
NULL


texts:
I have 2 tables:

Create table Files (
   FileId BigInt unsigned not NULL auto_increment,
   FileName varchar(255) not NULL,
   PRIMARY KEY (FileID),
   UNIQUE KEY FileName (FileName)
);
Create table FileRevisions(
   fileRevId BigInt unsigned not NULL auto_increment,
   FileID BigInt unsigned not NULL,
   RevisionName varchar(255) Not NULL,
   deletionFlag boolean default 0,
   PRIMARY KEY (FileID),
   KEY FileRev_FileID_FK_idx(FileId),
   CONSTRAINT FileRev_FileId_FK FOREIGN KEY (FileID)
     REFERENCE Files(FileID)
     ON DELETE CASCADE
     ON UPDATE CASCADE
);

I have the following code in ADD_FILE(File_Name, Revision_Name) procedure:

Create Procedure Add_File(File_Name varchar(255), Revision_Name varchar(255))
BEGIN
    declare file_id;
    Insert into files(FileName) values (File_Name)
                  ON DUPLICATE KEY UPDATE FileId=LAST_INSERT_ID(FileId);
    set file_id = LAST_INSERT_ID();
    set @rowNum = 0;
    update FileRevisions set deletionFlag=true where fileRevId in
    (
     SELECT fileRevId from
     (
       select @rowNum:=@rowNum+1 as rowNumber, fileRevId from FileRevisions
       Where FileId = file_id and deletionFlag=false order by fileRevId
     ) tmp where rowNumber>=5
    );
    Insert Into FileRevisions (fileId,RevisionName) values (file_id,Revision_Name);
END;

When I call the procedure and have approx. million records, it take about 1 second to run.
from tests I figured the update is the slowly parts, (the inserts are fast)
I tried to run the inner select queries and it was also fast, the updtae was the problem, even where the inner select return no rows...

Why is the update so slow?
is there a faster way to do the update?


texts: How many operations SQL Server support per connection

texts:
I am making a multi threaded application in which I will use a single SQL Server connection and perform select, insert, update and delete.

So once my application starts, it will open connection with SQL Server and then multiple threads will run each doing select, insert, update and delete (in no particular order) on multiple tables using the same connection.

I want to ask how many operations can SQL Server handle for a single connection?


texts: MySQL: query to extract text from HTML tags of a column

texts:
I have HTML tags in a column, which are used to define multi-language content on a webserver.

I need to do a query to extract several data from the database (MySQL v5.5) directly. Using stored functions are allowed. Note, I can not change the database structure, I have to use this one.

The step where I stucked is, to extract the language string depending on column named lang from column category.

Category column has strings like the following:

'/Hungarian Level 1English Level ONE/Hungarian Level 2English Level TWO/Last level'

Not all rows has HTML tags, those are simply Hungarian texts. We don't need to do anything in this case.
Depth of levels are unknown, can be several level deep.

If lang column begins with 'hu', then we need to extract only those texts within  tags, same for English .

I found ExtractValue function, which seems to be helpful, but doesn't solve the problem.

SET @xml = '/Hungarian Level 1English Level ONE/Hungarian Level 2English Level TWO/Last level';
SET @result1 = ExtractValue(@xml, '//span[1]');
SET @result2 = ExtractValue(@xml, '/span[2]');

SELECT @result1;
SELECT @result2;

I have a stored function, like this:

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
,
    `lang` VARCHAR(50)
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE cnt INT UNSIGNED DEFAULT 2;
    DECLARE cat_id INT UNSIGNED;
    DECLARE cat_name VARCHAR(200) DEFAULT '';
    DECLARE decoded_path MEDIUMTEXT;
    SET decoded_path = '';

    WHILE cnt < LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
      SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
      SELECT `name` INTO cat_name FROM mdl_course_categories as c WHERE id = cat_id;
      SET decoded_path := CONCAT(decoded_path, '/', cat_name);
      SET cnt := cnt + 1;
    END WHILE;

    SET @path := decoded_path;
        IF(lang REGEXP 'hu%' ) THEN
        -- SET decoded_path := REPLACE(decoded_path,'','');
        SET @result := ExtractValue(@path, '/span[1]');
        ELSEIF (lang REGEXP 'en%' ) THEN
        -- SET decoded_path := REPLACE(decoded_path,'','');
            SET @result := ExtractValue(@path, '/span[2]');
        END IF;

    RETURN @result;
  END

How to parse the multi-language text to display only in language set in lang column?


texts:
I have requirement to store the some sensitive information in SQL Server 2016. Organization policy demands, it should be encrypted.

Business Requirement:

Data cannot be decrypted by DBA.
Data cannot be decrypted by Developers.
Only end users able to access the decrypted data.

I have looked into some of the encryption options:

Column Level Encryption
Always Encrypted

But both of these options, does not satisfy the requirements. In Column Level Encryption, data can be accessed by DBA and Developers.

Always Encrypted does not allow DBAs to decrypt data but still Developers can be able to view the decrypted data, since data decrypted at the client driver.

I am thinking there must be some other method or combination of techniques can be used to achieve this requirements.

Please suggest on this. Thank you!


texts: pg_dump does not return schema for repmgr objects

texts:
While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a quickie pg_dump...

# pg_dump -U postgres -d repmgr --table=repmgr.monitoring_history --schema-only

...which frustratingly returns only the boilerplate header/footer info...

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.8
-- Dumped by pg_dump version 10.8

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

...well darnit! Did I typo something?

# pg_dump -U postgres -d repmgr --table=foo.bar --schema-only
pg_dump: no matching tables were found

...no... I am connected to the host I thought I was, right?...

# psql -U postgres -d repmgr -c 'select count(*) from repmgr.monitoring_history;'
  count
----------
 43597932
(1 row)

... so what gives? Why can't I dump the schema definition for this table?

Possibly also worth noting that pg_dump -s -d repmgr returns only the CREATE EXTENSION... & CREATE SCHEMA... commands and none of the tables. The behaviour is consistent across a few different repmgr installations. Perhaps I'm having the same issue as this guy?

Also also worth noting that attempting to dump data only also comes up blank. And that I've tried sudo -u postgres pg_dump... as well. Dumping another database on the same cluster works just fine returning data & schema as expected.


texts: PostgreSQL pg_dump -E encoding option not working

texts:
I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-encoded plain SQL dump file, as this is not working:
C:\Program Files\PostgreSQL\12\bin> .\pg_dump.exe -U postgres -F p -O -c -C -E UTF8 qdb > d:\qdb.sql

The output dump file is encoded as UCS-2 LE BOM nonetheless, which makes some German accented letters display incorrectly. I am using Windows 10 and PostgreSQL 12.4. How can we make pg_dump actually dumps a plain SQL UTF8 file?


texts: Alternative ways to find tables with specific storage engine?

texts:
I have a VPS with three accounts and 5 db's. Recently I added MySQLTuner. All of my tables are MyISAM, but the results MySQLTuner are showing that I have 14 tables that use InnoDB.

[--] Data in MyISAM tables: 257M (Tables: 618)
[--] Data in InnoDB tables: 560K (Tables: 14)

I looked all of my databases on phpMyAdmin and I couldn't find those tables.

Is there any way to find them?


texts: what is oracle universal installer in oracle database products

texts:
I am new to oracle database products. I am planning to install oracle 11g express edition in my PC,it looks like a standard installer.But in many of the oracle database installation tutorial mentions oracle universal installer.oracle universal installer provides more open options,but those options and features not available in downloaded oracle 11g express edition.

difference between two https://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#XEINW151 and http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/windbinst2.htm

so anyone explain me exactly what is oracle universal installer??Thx in advance.


texts: how to join below to queries in single query

texts:
I have following query,

How can I join these two queries into one.

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
 -- SUM(I.IP_WT) as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE
ORDER BY 1,2,3;

and

select SUM(I.IP_WT) as "TOTAL WEIGHT" FROM HR.BAF1_PDI I GROUP BY I.ORD_NO;


texts:
I am trying to take backup of postgresql 9.0 by using pg_dump command.
command here is:
cd /opt/PostgresPlus/9.0AS/bin and hit the following command

pg_dump -h xxx.xxx.xxx.xxx -p 5432 -U superuser db_name>db_name.dump

But i am getting error like,

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  schema "dbms_sql" does not exist
pg_dump: The command was: SET search_path = dbms_sql, pg_catalog

please give me suggestion.


texts: Why is "select *" faster than "select top 500 *" in SQL Server?

texts:
I have a view, complicated_view -- there are a few joins and where clauses. Now,

select * from complicated_view (9000 records)

is faster, much faster, than

select top 500 * from complicated_view

We're talking 19 seconds vs. 5+ minutes.

The first query returns all 9000 records. How is only picking up the top 500 ridiculously longer?

Obviously, I'm going to look at the execution plans here ---- but once I figure out why SQL Server is running the "top 500" in suboptimal fashion, how do I actually tell it to run the plan the quick way, like taking the full table?

Of course, I may have to rewrite the view entirely --- but quite odd.

Basically, I'm connecting this data table to a 3rd party software that pre-checks tables with a default select top 500 * query that cannot be modified. So other than dumping this view into an actual table (quite sloppy) - I can't get around their "top 500" addendum either.

This is SQL Server 2012.

EDIT: Disagree on the duplicate flag. The other question, the top was FASTER than all. This would be the EXPECTED behavior, returning less rows. My case is the opposite. Also, my understanding is that Top 100 is a different algorithm than Top 100+. I don't even think the duplicate question has the correct answer. Which is, the TOP X query will SORT potentially massive tables very early on, not AFTER they are aggregated/ filtered/ etc. The why is a mystery, but the how is plainly there.


texts: Moving database file locations in SQL Server 2008

texts:
I have SQL Server 2008 Express edition installed on my machine. I had changed the location of some folders from one drive to another (from F: to E:).

Now when I connect to SQL Server using SSMS, it does not show any database. If I try to attach a database by selecting the file it show me the error that

  Cannot attach a database with the same name as an existing database

How can I clean up the no-existing (for SQL Server) databases and re-attach the moved files?

Edit:- In Event Log it is showing errors like following for moved databases:

FCB::Open failed: Could not open file F:\Code\EFTest\App_Data\eftest.mdf for
file number 0.  OS error: 2(failed to retrieve text for this error.
Reason: 15105).

master, model,msdb,tempdb databases are available as these are in default location. The unavailable databases were in custom locations (in moved folder).


texts: User creation and rights to import a dump

texts:
I'm having problems creating an instance of Oracle 10.2.0.4 and importing a dump from other environment. I'm using Oracle under AIX.

I used a ksh script and everything goes right except when creating the users.

After that I create the default user, used to connect to the database and other working modules. The problem comes when trying to import the dump.

export ORACLE_SID=HISTO;
sqlplus / as sysdba
CREATE USER ANC IDENTIFIED BY "xxxxxxx";
grant connect, create session, imp_full_database to ANC;
commit;

The error when trying to import de dump is the following.
Command used to load the dump: nohup impdp ANC/xxxxxx DIRECTORY=MIG11G  DUMPFILE=dump_histo.expdp LOGFILE=impdp_29102013.log schemas=HISTO &

Output from the nohup.out file:

Import: Release 10.2.0.4.0 - 64bit Production on Lundi, 23 Décembre, 2013 18:51:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "ANC.SYS_IMPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-01950: no privileges on tablespace 'TOOLS'

My question: Is it normal haven't got system user to load a dump file ?
Why the load of the dump file fails ?


texts:
I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest this transaction in a stored procedure I run into lots of syntax errors.

How can I encapsulate the following code so it can be easily called?

BEGIN TRANSACTION AssignUserToTicket
GO

DECLARE @updateAuthor varchar(100)
DECLARE @assignedUser varchar(100)
DECLARE @ticketID bigint

SET @updateAuthor = 'user1'
SET @assignedUser = 'user2'
SET @ticketID = 123456

    UPDATE tblTicket SET ticketAssignedUserSamAccountName = @assignedUser WHERE (ticketID = @ticketID);
    INSERT INTO [dbo].[tblTicketUpdate]
           ([ticketID]
           ,[updateDetail]
           ,[updateDateTime]
           ,[userSamAccountName]
           ,[activity])
     VALUES
           (@ticketID,
           'Assigned ticket to ' + @assignedUser,
           GetDate(),
           @updateAuthor,
           'Assign');
GO
COMMIT TRANSACTION AssignUserToTicket


texts: Select full record with minimum value for column grouped by joined value

texts:
I have a table images with various columns, and two join tables images_units and images_sites that contains pairs of ids (image_id, unit_id), (image_id, site_id). One of the columns in the images table is sort.
I want to return each distinct record from the images table that has the minimum value of sort for each unit_id, with all records returned matching a given site_id. (for every unit_id there will be multiple image records.)
I thought I would be able to do something like this:
select images.*, unit_id from images
join images_units on images.id = images_units.image_id
join images_sites on images.id = images_sites.image_id
join (select min(sort) as minSort, unit_idas matchID from images join images_units on images.id=images_units.image_id group by matchID) as sq
where images.sort = minSort and a.unit_id = matchID and site_id = xyz;

In order to get all the values that I need, but I'm getting a syntax error near where so I presume I can't use it like this.
I ultimately want to return distinct rows and only the columns from images and nothing else (i.e the columns 'select distinct images.* from images' would give) but I think I should just be able to select those from the results of the query I've given - I'm including this note in case it's relevant to an alternative solution.


texts: Columnstore index for Key/Value Pair table on SQL Server

texts:
I need to build a database for storing assessments.

Each assessment can have an indefinite number of questions, and an indefinite number of responses.

Responses for each assessment can grow up to 500k.

Questions for each assessment can be from 10 to 200.

While the Assessments and AssessmentResponses tables will be designed with the "normal" relational table, Questions and Answers should be stored as Key/Value pair.

Assessments
|AssessmentID|Name|JsonSchema|

Questions
|QuestionID|AssessmentID|QuestionValue|QuestionType|

AssessmentResponses
|ResponseID|AssessmentID|RespondentName|Date|JsonResponse|

Answers
|ResponseID|QuestionID|AnswerValueText|AnswerValueDecimal|

As you can see I store also the assessment schema and the responses in JSON format as I found it useful to quickly visualize on a web UI. JsonResponse, in particular, contains all the answers given in the related response, like the following:

{
  "interviewDate":"2001/12/28",
  "city":"Mombasa",
  "phone":"123456789",
  "name":"Marco",
  "age":16
}

Typical queries can be:

Extract all the answers (paged) of a particular AssesmentID.
Calculate the average on all the answers of a particular
    AssessmentID of the age key.
Extract all the responses where the key "age" has a value greater
than 25 and the key "city" is equal to "New York"

Please note that for query No.2, the age values will be stored in the column AnswerValueDecimal, where I will store all the numeric values.

I'm wondering if the column store index will increase the performance in this structure?

Please note that I know that an ElasticSearch instance will help me a lot but for a budget issue we can't implement at this stage.

Some more details about data

Answers to the questions in the assessments are generally chosen from a limited number of choices. In example:

Are you married? > [YES,NO]
Type of building? > [Concrete,Skeleton,Eternite Sheets,Iron Sheets,Other]
City Area? > [North,South,East,West,center]

The queries can be built by the end-user depending on the information they need from a particular assessment. Giving the above example, they want to know how many Eternite buildings are in the North and West area of the city, a count in the end.

But for example there is also a question "How old are you?", they want to filter the previous query to know how many Eternite buildings are in the North and West area where there's a person with an age lower than 25 years...

USING JSON FIELD TO STORE DATA

I've tried using the new feature in SQL Server 2016/2017 to store JSON document inside an NVARCHAR(MAX) field. It seems to work pretty well but maybe only once the whole table is loaded in memory.

Here is the dbfiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2ce135dc37d72f9db951bbb4e2708baa


texts: Does Amazon Web Services (AWS) RDS support locally redundant storage (LRS) for Microsoft SQL databases?

texts:
Looking at the documentation for Azure databases I see that they support LRS (local stored in three seperate location) (I'm not interested in GRS) and I was wondering if anyone knew if AWS offered the same functionality? Ideally I just want to prevent against data loss (I realise data corruption and zone availability are issues, but these are not my primary concern)

In essence, if the hardware running my database breaks, I don't loose any information?

Thanks

Gordon


texts: Moving data from one DB to another where the table/column structure is identical

texts:
Question about the import / export process.

I know is SQL Developer I can export all the columns from a table and import into the same table on another server.

My question is - what happens with the duplicate inserts? Will it just skip or what will happen?

I have these tables I need the data to be identical - one of the tables has data the other doesn't and I need to make them match without it messing up what is already in there.

My theory is that once I export all of the rows in the form of inserts and run it on the other DB-Table it will insert all the ones it's missing and error and skip the inserts where there already exsist the row, but want to make sure this is correct.

Thanks in advance!


texts:
I have the following query. It calculates the best match for each row in flight based on the the data in airport

SELECT flight.id,
       (
           SELECT id
           FROM   airport
           WHERE  SQRT(
                      POW(69.1 * (latitude - flight.lat), 2) + POW(69.1 * (flight.lon - longitude) * COS(latitude / 57.3), 2)
                  ) < 10
           ORDER BY
                  ABS(
                      (magnetic -180) -ABS(
                          atan2(TAN(longitude - flight.lon), TAN(latitude -flight.lat)) * (180 / (22 / 7))
                      )
                  ) ASC LIMIT 1
       ) AS airport_id
FROM   flight
WHERE  STATUS = 1

Is there a way to remove the DEPENDENT SUBQUERY and change it to a JOIN? I can even put all the lat/lon in the same table without much effort using UNION.

SELECT id,
       lat,
       lon,
       NULL AS latitude,
       NULL AS longitude,
       NULL AS magnetic
FROM   flight
    UNION
SELECT id,
       NULL,
       NULL,
       latitude,
       longitude,
       magnetic
FROM   airport


texts: migrate db's - best way to mirror or replicate a full database

texts:
I would like to mirror/replicate my databases to the cloud. The cloud is setup using VPN to my actual machine.
I am a bit confused by the options I have and would like to get some light into it.
My setup is a SQL Server 2012 (standard edition) Instance which should be mirrored/replicated to a SQL server 2014 instance. The instance contains 30 databases which are in full recovery mode.

One other option would be to take a full backup, restore in cloud and later restore a differential one. But this might take some time. I would love the minimize the downtime that's why I thought about the replica/mirror way.

What are my options and riks at this point?