texts: Generate script for all stored procedures in Azure SQL database

texts:
I'm trying to create a SQL script that will create all the stored procedures that were exported from another database.
Basically navigating in SSMS to the database > Generate Scripts > select stored procedures > ..
I found this query that lists all stored procedures CREATE statements, but the question is how to get all of these rows (result) in a single query/file?
select  mod.definition
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', '
             from sys.parameters p
             where p.object_id = obj.object_id
                   and p.parameter_id != 0
             for xml path ('') ) par (parameters)
where obj.type in ('P', 'X')

I tried via a PowerShell script, but the commands are cut short...
The PowerShell script:
Param
(
    [Parameter(Mandatory=$false)][string]$SqlServer = "sql_server_name",
    [Parameter(Mandatory=$False)][string]$SqlDatabase = "db_name",
    [Parameter(Mandatory=$false)][string]$destination = "C:\Users\userx\Desktop\",
    [Parameter(Mandatory=$false)][string]$SqlUser = "sqluser",
    [Parameter(Mandatory=$false)][string]$SqlPassword = "password"
)

function Invoke-Sql
{
    $ErrorActionPreference = "Stop"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$($SqlServer); Database=$($SqlDatabase); User ID=$($SqlUser); Password=$($SqlPassword)"

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = Get-Content "$PSScriptRoot\Get_Create_Statement_All_Stored_Procedures.sql" | Out-String
    $sqlCmd.Connection = $sqlConnection

    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $dataTable = New-Object System.Data.DataTable
    try
    {
        $sqlConnection.Open()
        $sqlOutput = $sqlAdapter.Fill($dataTable)
        Write-Output -Verbose $sqlOutput
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
    catch
    {
        Write-Output -Verbose "Error executing SQL on database [$SqlDatabase] on server [$SqlServer]."
        return $null
    }

    if ($dataTable) { return ,$dataTable } else { return $null }
}

cls

cd $destination

Invoke-Sql | Out-File -FilePath $destination\Create_All_Stored_Procedures.sql

PS. This concerns an Azure SQL database hosted in a Microsoft managed subscription. It seems that using SMO doesn't work on the database as the $smoServer.Databases attribute is empty.


texts: Why does MySQLTuner only see 4Gb of RAM?

texts:
I ran MySQLTuner to verify the configuration of a MySQL database, and it reported this problem:

[!!] Maximum possible memory usage: 40.9G (1022% of installed RAM)

These figures come from computing max_connections multiplied by read_buffer_size + sort_buffer_size + join_buffer_size in the my.cnf file, which effectively gives 40Gb.

However, the server has 128Gb of RAM.  It appears that MySQL only sees 4Gb.

At first I thought that this was because a MySQL 32-bit binary on a 64-bit OS can effectively access only 4Gb of RAM.  However, both MySQL binary and OS are 64-bit:

root@box# isainfo -v
64-bit amd64 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse
        fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse
        fxsr mmx cmov sep cx8 tsc fpu

root@box# uname -a
SunOS box 5.11 11.2 i86pc i386 i86pc

root@box# file /usr/local/bin/mysql
/usr/local/bin/mysql:  ELF 64-bit LSB executable AMD64 Version 1 [SSE2 SSE FXSR CMOV FPU], dynamically linked, not stripped

and there is no limit on how much memory a process can access:

root@box# ulimit -a
address space limit (kbytes)   (-M)  unlimited
core file size (blocks)        (-c)  unlimited
cpu time (seconds)             (-t)  unlimited
data size (kbytes)             (-d)  unlimited
file size (blocks)             (-f)  unlimited
locks                          (-x)  not supported
locked address space (kbytes)  (-l)  not supported
message queue size (kbytes)    (-q)  not supported
nice                           (-e)  not supported
nofile                         (-n)  256
nproc                          (-u)  29995
pipe buffer size (bytes)       (-p)  5120
max memory size (kbytes)       (-m)  not supported
rtprio                         (-r)  not supported
socket buffer size (bytes)     (-b)  5120
sigpend                        (-i)  128
stack size (kbytes)            (-s)  8192
swap size (kbytes)             (-w)  not supported
threads                        (-T)  not supported
process size (kbytes)          (-v)  unlimited

Is there another way I can see how much memory MySQL is effectively using?

(In case you ask, shell is tcsh; I cannot tag the post "tcsh" due to insufficient privileges.)


texts: MariaDB 10 replication strange behaviour

texts:
I have following MariaDB 10 replication setup.

Server A MariaDB 10 (master), Server B MariaDB 10 (master), Server C MariaDB 10 (slave/master), Server D MariaDB 10 (slave).

A and B are getting new data from different sources (Percona Server, AWS RDS) using Tungsten replicator and replicate this data to C. C replicates to D.

My steps:

mysqldump from A and B
import to C and configure multi-source replication
mysqldump from C
import to D configure replication

After a while A and B are getting new data, but this data is not replicating neither to C or D (from C).

If I go to A or B and create fake tables and insert fake data everything replicates up to D.

No errors in mysql error log, no errors in replication status.

What can be possible wrong?


texts: What is the generally accepted method and tools for profiling MySQL queries?

texts:
Profiling database queries is difficult, and dependent on database technology.  In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query?

For example:

Run the query.
Use X tool on the query.
Analyze the results of X using a certain tool.
Check indexes....


texts:
I am trying to output a parameter from openquery, but my syntax produces this error

  Msg 137, Level 15, State 2, Line 6
  Must declare the scalar variable "@NameID".
  Msg 137, Level 15, State 2, Line 8
  Must declare the scalar variable "@NameID".

What in my syntax should be altered in order to have @NameID returned from the query?

Declare @sql nvarchar(4000), @Name nvarchar(100), @Nameid nvarchar(25)
SET @Name = 'Foxtrot'
SET @SQL = 'DECLARE @NameID nvarchar(25) Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
PRINT @SQL
EXEC sp_executesql @sql, @NameID = @NameID OUTPUT;

Select @NameID


texts: Reasons against having "concrete" values in own table with only an ID column and the value itself, and composing bigger objects of IDs

texts:
This is a follow up question to How to insert values into a highly normalized database without excessive duplicate checking?

The current situation:
We have an "massive" object that has around 30 columns worth of details (while it could be slightly improved, a lot of it sadly does really belong together, and can't easily be semantically separated). Every column simply contains an Integer, an ID key.
Now we have 30 more tables, each only have 2 columns: "id" and "value", where ID is an auto generated key, and value is the actual concrete value.

Whenever we want to get back data from our massive objects we need to do 30 joins from these "value" tables onto the main table.

Reasons given were "that a lot of data is going to be duplicated over the next 2-3 years" (it'd be a lucky stroke if one value gets used more than thrice, and at most 20 times imho, but that's besides the point), and any future changes to those values would be much easier then. Also saving storage space (which might be true, but for perhaps at most 200k records over 2 years nothing that should be noteworthy).

Now I know intuitively a few reasons why this design is bad. But I'm still a novice myself, and I would like proper detailed reasons that I could actually present to change a mind, and perhaps learn something new myself.

Friendly reminder that the database commands we're talking about here are mostly very basic Insert, Select and Update (and very rarely joins) methods. No one in our department is experienced enough in SQL (or willing enough to learn except me) to go above those commands, and almost all logic is done server-side after getting data from the database.


texts: Do Constraints move from one database to another?

texts: We have a development database, a test database, and production.  The only way for data to enter one of these three systems is via an SSIS package, which means that for the last month of development, production has been stable, with no additions made.  We keep, as far as data is concerned, the three systems in sync.  However; our data modeler made suggestions that I rolled into development on the existing structure of our tables.  I rolled this into test, with the new data we will be using going forward.  We'd like to roll these tables from Test directly into production.  What I'm wondering is if the constraints that I've set up in Test will go with the table or will I have to set them up in Production?


texts:
I am attempting to do a ROLLUP or CUBE function, and I am wanting to get back unique combinations based on the values of a field, rather than particular columns. Here's what I have so far.

With the following schema:

CREATE TABLE responses (
    id SERIAL PRIMARY KEY
);

CREATE UNIQUE INDEX responses_pkey ON responses(id int4_ops);

CREATE TABLE demographic_values (
    id SERIAL PRIMARY KEY,
    name text NOT NULL
);

CREATE UNIQUE INDEX demographic_values_pkey ON demographic_values(id int4_ops);

CREATE TABLE response_demographics (
    response_id integer NOT NULL REFERENCES responses(id),
    demographic_value_id integer NOT NULL REFERENCES demographic_values(id)
);

CREATE TABLE answers (
    id SERIAL PRIMARY KEY,
    response_id integer REFERENCES responses(id),
    question_id integer,
    rating integer
);

CREATE UNIQUE INDEX answers_pkey ON answers(id int4_ops);

And the following data inserted into this schema:

INSERT INTO public.responses VALUES (1);
INSERT INTO public.responses VALUES (2);
INSERT INTO public.responses VALUES (3);

INSERT INTO public.demographic_values VALUES (1, 'Melbourne');
INSERT INTO public.demographic_values VALUES (2, 'SF');
INSERT INTO public.demographic_values VALUES (3, 'Male');
INSERT INTO public.demographic_values VALUES (4, 'Female');

INSERT INTO public.response_demographics VALUES (1, 1);
INSERT INTO public.response_demographics VALUES (2, 2);
INSERT INTO public.response_demographics VALUES (1, 3);
INSERT INTO public.response_demographics VALUES (2, 4);
INSERT INTO public.response_demographics VALUES (3, 1);
INSERT INTO public.response_demographics VALUES (3, 3);

INSERT INTO public.answers VALUES (1, 1, 1, 5);
INSERT INTO public.answers VALUES (2, 1, 2, 4);
INSERT INTO public.answers VALUES (3, 1, 3, 3);
INSERT INTO public.answers VALUES (4, 2, 1, 5);
INSERT INTO public.answers VALUES (5, 3, 1, 1);

When I then run this query:

SELECT demographic_values.name, COUNT(answers.id) FROM answers
INNER JOIN responses on responses.id = answers.response_id
INNER JOIN response_demographics on response_demographics.response_id = responses.id
INNER JOIN demographic_values on response_demographics.demographic_value_id = demographic_values.id
WHERE question_id = 1
GROUP BY ROLLUP(demographic_values.name)

I get back this data:

   name    | count
-----------+-------
           |     6
 Male      |     2
 SF        |     1
 Female    |     1
 Melbourne |     2
(5 rows)

What I would like to be returned instead is:

     column1     | column2
-----------------+---------
 Melbourne, Male |       1
 SF, Female      |       1
                 |       2
(3 rows)

Each count here is counting up each unique demographic combination of all the responses. If there was another response which had only the demographic of "Melbourne" but not "Male", then I would expect:

     column1     | column2
-----------------+---------
 Melbourne, Male |       1
 SF, Female      |       1
 Male            |       1
                 |       3
(4 rows)

How can I go about doing this ROLLUP or CUBE or, I guess GROUPING SETS on field values, rather than field names?

Even if it returned the demographic values as unique columns (as opposed to comma-separated strings) I would be fine with that.


texts: SQL Server execution plan is using PK instead of index

texts:
The following query is taking 5 seconds to return 9692 rows:

SELECT
    Project.CalculateSequencialProject,
    Project.CreateDate,
    Project.OpeningDate,
    Project.ForecastClosingDate,
    Project.RealClosingDate,
    Project.ProjectStatus,
    Unit.Name AS UnitName,
    UTE.Name AS UTEName,
    Process.Name AS Process
FROM
    Project
        LEFT JOIN Unit ON Project.Unit_Id = Unit.Id AND Project.Company_Id = Unit.Company_Id
        LEFT JOIN UTE ON Project.UTE_Id = UTE.Id
        LEFT JOIN Process ON Project.Process_Id = Process.Id
WHERE
    Project.Company_Id = '????????????????'

The execution plan is like this:

And the most "expensive" part of it is detailed here:

As you may se in the tooltip image, the Clustered Index Scan with 75% of cost is using the table primary key instead of one of the following indexes.

CREATE NONCLUSTERED INDEX [IX_Company_Id] ON [dbo].[Project]
(
    [Company_Id] ASC
)

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [ProjectType_Id] ASC,
    [ProjectStatus] ASC,
    [Unit_Id] ASC,
    [Id] ASC,
    [ProjectDirector_Id] ASC,
    [UTE_Id] ASC,
    [Company_Id] ASC,
    [LostType_Id] ASC,
    [Pillar_Id] ASC,
    [ProjectPhase_Id] ASC
)
INCLUDE (   [CalculateSequencialProject],
    [DescriptionTittle],
    [OpeningDate],
    [ForecastClosingDate],
    [RealClosingDate])

Why?


texts: Get minimal logging when loading data into temporary tables

texts:
After reading The Data Loading Performance Guide, I am still unsure if it's necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index, in order to get minimal logging.
Obviously, the temp table is created in TempDB, which operates in the SIMPLE recovery mode, so I would have thought that it was a perfect candidate for minimal logging; however, I can not find a passage to confirm it.
Is a temporary table a candidate for minimal logging, and if so, is it worth adding the TABLOCK hint as recommended for permanent tables?


texts: Rebuild index online without risk to other requests

texts:
We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly.

We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild happens online and uses tempdb (Enterprise Edition SQL Server feature).

However during the 2 minutes of the rebuild, other connections with the database suffer. We get several timeouts or deadlocks. Sometimes the rebuild itself fails with a deadlock as well.

The rebuild failing is no problem, we can just try again. However other connections timing out or even taking very long is very troublesome.

Is there a way to run the rebuild with a lower priority, or automatically fail if the current processing resources will not suffice for an online rebuild?

There is no window of downtime for this database, so we cannot rebuild indices offline. Is there any way we can rebuild the indices online without significant disturbance of other requests?


texts: Importing Data in Parallel in SQL Server

texts:
I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the following ones:

How to load 1 TB data in 30 minutes

https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx

Importing Data in Parallel with Table Level Locking

https://technet.microsoft.com/en-us/library/ms186341(v=sql.105).aspx

and the answers in stackoverflow

Fastest way to insert in parallel to a single table

However, none of them have given a simple example with code. I know how to use bulk insert/bcp, but I don't know where to start with parallel import? Can anyone help me with it?

My system is Windows and I'm using SQL server 2016. The source data file is in txt format.

Thanks in advance for your help!

Jason


texts:
I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does the index only update when key columns are updating? Does include column updates add performance issues? According the example below, if the Balance and DiscountedAmount keep updating does it update the index IX_Temp_RegistrationNumber for every updates?

CREATE TABLE #Temp (
    TheID BIGINT
    ,RegistrationNumber BIGINT
    ,ClientName VARCHAR(50)
    ,Balance NUMERIC(10, 2)
    ,DiscountedAmount NUMERIC(10, 2)
    )

CREATE NONCLUSTERED INDEX [IX_Temp_RegistrationNumber] ON [#Temp] ([RegistrationNumber] ASC) INCLUDE (
    [Balance]
    ,[DiscountedAmount]
    )
GO


texts: Automatic Translation when Converting Unicode to non-Unicode / NVARCHAR to VARCHAR

texts:
Unicode code point 9619 is a character called "Dark shade": ▓ (http://unicode-table.com/en/search/?q=9619).

Using the SQL_Latin1_General_CP1_CI_AS collation and 1252 code page, I would expect that casting / converting that Unicode character to non-Unicode data type would result in a question mark (?) as code page 1252 does not appear to contain this character and this appears to be SQL Server's behavior when conversion can not take place.

So my question is: why does SQL Server convert this character to an ASCII code 166 which is "Pipe, Broken vertical bar": ¦ ?

SELECT NCHAR(9619), CAST(NCHAR(9619) AS CHAR(1)), ASCII(CAST(NCHAR(9619) AS CHAR(1)))


texts:
I am trying to build a database in which I am going to need to have a tree to keep track of cars.

Unfortunately, I don't always know what level of detail I am going to have, and the level that I do have will vary.  So although I will presumably know the manufacturer, I may not always know the model, submodel or year.  In fact, the further I go down the tree, the less likely I am to know.

However, my best data would be the most complete, so I don't want to just throw that away.  I would like to have a place in my DB where data on the cars is stored that contains all this, but while I might see a 2017 Nissan Maxima SL, most of the time I might only be able to make entries into my DB about a Nissan Maxima (no year or submodel).

It's also really important that I minimize the number of fields in the main table.  How would I structure this?  Is there a way to set it up so I don't have to have separate fields for year, make, model and submodel?

If I do, and all those fields exist in my models table, would I have to bring them all into my main table if that's where most of my records are being stored?

Keep in mind I will be making multiple entries of 2017 Nissan Maxima SLs (for example) in my main table.


texts:
I've a query in my php script

$result = mysql_query("SELECT State_Id FROM state_master WHERE State_Name='$statename'") or die(mysql_error());
$row=mysql_fetch_assoc($result);
return $row['State_Id'];

Which returns 23Unknown column 'State_Name' in 'where clause' where 23 is the correct state ID. What I'm doing wrong?

Is there any way to avoid the error and just to return the value?

I'm new in this so I hope someone will point out my stupid mistake.


texts:
I'm in the process of migrating a number of databases from ISO8859-1 to UTF-8. To fit the old string in new tables I figured there are 3 options:

Multiply the number of bytes with a number the corresponds to the distribution of multibyte characters in the domain, and add check constraints for length validation. I.e CHAR(3) might hypothetically become CHAR(5).
Declare column as CHAR(3 CODEUNITS32). As far as I can tell this will occupy 12 bytes
Declare column as GRAPHIC(3). As far as I can tell this will occupy 6 bytes.

I don't really consider 1 an option, that leaves us with 2 and 3. When would 2 be preferable over 3? I think I've seen some info where some characters may not map correctly from CHAR to GRAPHIC, but this seems to work fine for characters from ISO8859-1. Other than that, am I missing something or would GRAPHIC always be preferably over CHAR CODEUNIT32?


texts: PHP and mySQL - Binding a parameter in an if statement


texts:
I have a table where I record a number of values:

channel, cnam (both varchar)
calldate (date/time)
billsec (integer)
disposition (varchar - either 'ANSWERED' or 'NO ANSWER' or 'BUSY')

I need to report on the total number of records and billsec (which is a number of seconds) for certain cnams, along with a breakdown of billsec by disposition, and the average duration.

I have a query that lets me select the correct set of rows to be reported on:

SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS
chan1,cnam,billsec,disposition
FROM asteriskcdrdb.cdr
WHERE calldate >= '2017-12-08 00:00:00' AND calldate <= '2017-12-08
23:59:59' AND (duration-billsec) >=0
HAVING chan1  LIKE 'SIP/3%' or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
ORDER BY chan1 asc

This returns results like the following:

chanl     | cnam     | billsec  | disposition
----------+----------+----------+----------------
SIP/2000  | foo      |    180   |  ANSWERED
SIP/2000  | foo      |     30   |  NO ANSWER
SIP/2000  | foo      |     60   |  ANSWERED
SIP/3010  | foo foo  |     2    |  BUSY
SIP/3010  | foo foo  |     3    |  BUSY
SIP/3010  | foo foo  |    240   |  ANSWERED
SIP/3020  | foo2     |     30   |  ANSWERED
SIP/3020  | foo2     |    180   |  ANSWERED
SIP/3020  | foo2     |     10   |  NO ANSWER
...

I have the start for how the query for my final results would go:

SELECT cnam,
       sum(disposition) as TOTAL,
       sum(disposition where disposition = 'ANSWERED') as COMPLETED,
       sum(disposition where disposition = 'NO ANSWER' OR case when
       disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as 'Total DURATION',
       SEC_TO_TIME(avg(billsec)) as 'AVG DURATION'

My final results should look like this:

CNAM    | TOTAL | COMPLETED | MISSED | Total DURATION | AVG DURATION
--------+-------+-----------+--------+----------------+---------------
foo     |    3  |         3 |      1 |           1:30 |         0:32
foo foo |    3  |         3 |      2 |          03:05 |         1:02
foo2    |    3  |         2 |      1 |          03:34 |         1:12

I even know I can use a subquery to combine all this together. What I can't quite figure out is how to do that, given all the stuff that's in my initial query.


texts: MySQL chain replication timing of writes on first slave

texts:
With a MySQL chain replication setup, e.g. M1->S1->S2, what exactly is the order of operations for writing to the first slave?  Is the binary log from the master simply streamed directly through to the second slave?  Or are database updates to the first slave committed before replicating those updates to the second slave?

The documentation for --log-slave-updates states This option causes the slave to write the updates performed by its SQL thread to its own binary log.  It's not clear to me if this is done in parallel or only after data is written.  I'm only interested in row-based replication, if that matters.

I ask because I have the unusual situation of a custom application acting as a second slave and operating on events that come through the binary log.  It runs queries on the first slave.  If the log events were to get to the application before writes on the first slave are committed, it will be operating on stale data.


texts:
This one's got me a bit perplexed. How might one determine what process/program is making file-level backups via VSS/VDI and the SQL Writer service? I've tried using Profiler to trace backup/restore events, and that just shows me SQL Writer itself creating the backup events. I don't see anything in the Windows event log indicating what's talking to SQL Writer, nor does SQL Server put anything meaningful in its own error log.

I've got a situation where something is making snapshot backups and breaking the differential chain, but we're not sure what the culprit is. It's undoubtedly some kind of backup agent, or a virtualization assistant that talks to SQL Writer to allow for VM snapshots, but we need some way to pin it down.


texts: Adding Massive amount of Articles to existing publication cause screen to hang forever

texts:
I am using Win Server 2008 R2 / SQL Server 2008 R2 and i am trying to add about 2K + articles to the existing publication and the screen hangs @ the articles screen.

The screen has been there and hanged since 12 hours ago .

Based on the server spec : 256GB Ram with loads of core and threads, running on SSD/SAS.

it's highly unlikely due to hardware, it has to be due to some bug in the software.

When such scenario happens, what should I do to rectify or analyze the problem ?

Update :

Did some script via sp_addarticle but it still takes around 40 seconds per article.

I in fact have over 6k articles.

Sample script I used :

   EXEC Sp_addarticle
              @publication = 'DB_live_Pub',
              @article = N'vw_AuditLog',
              @source_owner = N'dbo',
              @source_object = N'vw_AuditLog',
              @type = N'view schema only',
              @description = NULL,
              @creation_script = NULL,
              @pre_creation_cmd = N'drop',
              @schema_option = 0x0000000008000001,
              @destination_table = N'vw_AuditLog',
              @destination_owner = N'dbo'

Update 2 :

I killed the job, stop all related agents, drop all publications, drop all subscriptions,
Recreated new subscription from scratch. Surprisingly, everything speeds up fast. I managed to create 5k+ Articles in 2 hours. After that i proceed with the snapshot, after some error debugging, managed to create a snapshot.

Now i am creating a subscription to the publisher.


texts: Set up a postgres database with a specific role without password prompt

texts:
Context

I have a PostgreSQL server (localhost:5432) which holds a database called my_database.

I have created long time ago, a simple role which owns this database; my_user, different from the postgres role;

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 my_user   | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This manually created role does not have an OS equivalent user, like postgres does.

I have a bash script to reinitialize my_database with a set of schemas and tables (from the create_db.sql file);

#!/bin/bash
set -e
dropdb -U my_user my_database || true
createdb -U my_user my_database --owner=my_user
psql -U my_user -d my_database -h localhost -v ON_ERROR_STOP=1 -a -f create_db.sql

Issue

Since a few days, this script is asking me 3 times the password for my_user. I guess one time per line.
Before that, I was able to run it without any prompt for any password. That was really cool.

The only thing I changed in between was this;
I usually connect to this server using pgadmin3 with the postgres user to checkout what's inside my tables.
In pgadmin3, I once set up this server credentials to my_user and its password to checkout some differences  with the postgres login.
Then, always in pgadmin3, I switched back the credentials to postgres as they were before.

Question

I cannot understand why this would have broken something which had since then made my script asking for password...
It may be related to something other, but I really don't know what?

I also tried to set up a .pgpass file with this line;

localhost:5432:my_database:my_user:my_user_password

and changing its access to 0600 as stated in the documentation.

But it doesn't seem to be recognized; the bash script is still asking 3 times for my_user password.
And, as I (the ubuntu normal OS user, which is not the same as the role my_user which owns my_database) owns this file , I wonder if I should set up permissions to this file to the postgres OS user instead?

Anyway, this .pgpass file was empty before, when I was able to reset my_database without any password prompt. So if I can recover the previous behavior it would be nice, except if this is not a good practice.

Any clue would be appreciated as I'm not much used to database management.

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

EDIT

I just noticed that if I set the .pgpass file with this content;

*:*:*:my_user:my_user_password

it works (it stops asking for a password).

But with the database name;

*:*:my_database:my_user:my_user_password

it doesn't (it keeps asking for a password).


texts: Insert into table which contains nested-table inside (Object in Oracle)

texts:
I have script of creating here:

create type Ingredients_type as object (
Ingredient_ID varchar2(20),
Ingredient_Name varchar2(50),
ICalori int,
IPretein int);

create type Food_type as object (
FoodType_ID varchar2(20),
FoodType_Name varchar2(50));

create type FoodIngredients_type as object (
Ingredients Ingredients_type,
Amount int);

create type FoodIngredients_table as table of FoodIngredients_type;

create table Foods(
Food_ID varchar2(20),
Food_Name varchar2(50),
FType Food_type,
FIngredients FoodIngredients_table,
Cost int,
FCalori int,
FProtein int)
nested table FIngredients store as FIngredients_col;

But I can't insert values into Foods. I have counted enough arguments and don't know where is missing or redundancy...

insert into Foods values ('F1', 'Food #1', Food_type('FT1', 'Noodle'),
FoodIngredients_table(FoodIngredients_type(Ingredients_type('I1','Ingredient #1', 0, 0), 10,
                                           Ingredients_type('I2','Ingredient #2', 0, 0), 20)),
                                           1000, 0, 0);


texts: Constraint exclusion to fetch latest N rows

texts:
I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted rows, so at the beginning I just queried for:

select id, logdate, content from measurements order by logdate DESC limit 500;

A few days later, I found out it was faster to set id to be (for this example) 10000000000 - extract(epoch from logdate), and use it as PRIMARY KEY, so

select id, date, content from measurements limit 500;

would naturally order by id, therefore yielding the latest records.

As the table grew, it became unmanageable, so I resorted to partitioning. I did it just as the documentation says:

CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

and I distributed existing rows onto their respective partition.

The problem is, I'm partitioning on logdate, but when querying I don't know the timespan. When I query for the last 500 rows, I can't know if they are from last week, last month of last quarter. Therefore, the query planner always scans all the partitions.

I can't believe no one has dealt with this same problem before, it sounds trivial and yet it has me puzzled.


texts:
Just to preface this, I am just a developer with some DBA experience but not an expert.

We are migrating from one data-center to another.  Basically we are doing this:

Create a full copy of existing database
Use the copy to get the target database up to date of the backup.
Turn on Mirroring from the existing database to the new one.
The web app will continue to use the existing database ( they are being migrated as well. )  So the old web app server and the new one will both point to the old data center.

ok, after step 4 , we are ready to switch the connection string for the database over to the new one.  My concern is... if mirroring is still on and we start to have all read/write operations happen on the target of the mirror... is that going to cause any problems??

ex. say the target of the mirror starts adding rows, will the mirroring catch that the two dbs are not in sync and start removing changes?  or... say a row gets deleted in the target db, is the mirror going to catch that it is not in sync and add that row back?

Basically my question is this:  If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.


texts: Hot and Cold data Storage using partitioning

texts:
I am working with a data-ware house and i want to partition the last three years of data into Hot data storage and any further than three years should be placed to cold. I want to be able to run a script monthly that would move data from Hot to Cold if data as gotten older I have currently Started by looking on line and have been testing the following script

Alter Database [Dev_PartionTest] Add Filegroup [HOT_DATA]       --------
Go                                                                  --------
Alter Database [Dev_PartionTest] Add Filegroup [COLD_DATA]       --------
Go                                                                  --------

Alter Database [Dev_PartionTest] Add FILE ( NAME = N'HOT_DATA', FILENAME = N'....\HOT_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB ) TO Filegroup [HOT_DATA]
Alter Database [Dev_PartionTest] Add FILE ( NAME = N'COLD_DATA', FILENAME = N'..\COLD_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB) TO Filegroup [COLD_DATA]

 Declare @Previousyear Date
 set @Previousyear = (SELECT DATEADD(year, -2, GETDATE()));

 Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

Create Partition Scheme RangePartScheme as Partition
 [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA], [PRIMARY])

 Create Clustered Index IDX_Part On Tester(created)
 On RangePartScheme (created);

Could you guide me as to how i can develop such a a script.

    Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

The issue i face is if i use the getdate to insert a record

 INSERT INTO Tester( Name, gender, created, description )
 VALUES ('Nicks', 'M',GETDATE(), 'Lloyds Office'),

The getdate function would return date higher than the date time used in the function above to set the upper bounds on the right in the create partition function. how can i set the current year without having to type it in manually everytime.
Can this script be running on a monthly process to update partition is that possible.


texts: Update different oracle database from different oracle database

texts:
I want to update a CLOB column in B Oracle 11g database using a CLOB column in A Oracle 11g database located in a different server.

What are the different ways to do that? and which is the best and easiest way?


texts:
I am trying to develop a small business web application for my friends business. I do have some php,html experience so I have done the interface and all. Now I am trying to work on the database. The whole process is simple , the user creates a quote and once the quote is approved , he can use the quote to create an invoice.
Here are the items in the quotation page ( I will call below items as variables/columns)

Quotation Id ( will be automatically generated)
Date
Requester ( alpha numeric with space )
Reference code ( alpha numeric with space )
Quote valid for ( alpha numeric with space )
Bill to ( Company Name, City , Country , Zip code , Telephone )
Ship to ( Company Name, City , Country , Zip code , Telephone )
Items ( there will be more than one items )
[ Item Name, Item Description, Unit of Measurement , Quantity , Unit Price ]
Total amount
Terms and Conditions ( this will be a 3 line sentence, it can be anything , it will be a text .. example below )
[ stock available/ not available / 2 -3 weeks delivery period / cash on delivery / 35 days credit ]
Created by

and the invoice page will have ( I will call below items as variables/columns)

Invoice number
Date
Requester
Reference code 1
Reference code 2
Purchase order code
purchase order date
Credit terms(days)
Bill to ( Company Name, City , Country , Zip code , Telephone )
Ship to ( Company Name, City , Country , Zip code , Telephone )
Items ( there will be more than one items )
[ Item Name, Item Description, Unit of Measurement , Quantity , Unit Price ]
Total amount
Terms and Conditions ( this will be a 3 line sentence, it can be anything , it will be a text .. example below )
[ stock available/ not available / 2 -3 weeks delivery period / cash on delivery / 35 days credit ]
Created by

As you can see both the quote and invoice has lot of similar columns , how can I make normalized table in sql for the above. I am guessing there will be quote table, item table , customer table and invoice table. But in that case there will be duplicate datas in both quote table and invoice table [ example , total amount is same, terms and condition is same , requester is same etc.. ].

What the application will do is , when the user press 'add quote' , a tab opens with fields and the user can input all the above(quote) variable/columns. And once he is done he can save it , in that case the variables it will be saved in the database in respective tables, later once the client approves the quote, the user can go to the list of quotes section and can press the action button to convert this quote to an invoice, when the user press the action button a new tab opens with the invoice format style, most of the fields will be automatically filled from the database, the remaining fields like [ purchase order number, purchase order date etc.. ] he will have to enter.

I guess I have explained what I am trying to do. What I am hoping from you all as help is the SQL statement for creating the table or at-least an idea of how many tables do I need and what all will be the foreign keys, may be ERD diagram with entities.

Making the tables 3NF , so there will be no duplications, trying to save the database space.


texts: What's the difference between expensive and cheap SQL Server backup products?

texts: I'm looking for SQL Server database backup solution and I have found many third party tools. But their prices vary greatly. For example, SQL Backup from RedGate or Idera costs about $900 - $1000 while SQLBackupAndFTP or SQLBackupMaster costs about $70. What's the reason? Does it mean that RedGate's or Idera's products are more than ten times better than the cheaper ones? Or they more reliable? They all do what I need (e.g. make schedule backups, store them somewhere and notify me by email), but I'm just  completely disorientated with such ten-times price difference. Do I miss anything?


texts: Power BI DESKTOP licensing - is it really free to share pbix files?

texts:
The Power BI Desktop app for Windows is free, which is great. However, the Microsoft site is loaded with talk of licensing to share/view Power BI content. Is that only for their hosted Power BI SERVICE?

I want to create reports in Power BI Desktop, and distribute these as .pbix files. Is there any reason I can't distribute these without paying a dime? I realize that Microsoft's goal is to get people into their hosted Power BI service, but using Power BI Desktop (free app) to distribute content for free seems to good too be true.


texts: Select all columns from table but apply function to 1 column

texts:
What I want to do: Select all columns but apply a function to one of the selected columns in a shorthand manner such that I don't have to type out all of the column names

Current approach: I am currently using select table.* to select all columns in a table.

Example table

Name data1 data2 data3 ....
a    1     1     1
b    1     1     1
c    1     1     1

Code:

SELECT * from table

Desired outcome: I want to be able to select all columns then apply a function to a specific column, is it possible to do this with select * or does it require selecting all column names and applying a function to my specified column?


texts: Error restoring SQL differential database files

texts:
Occasionally I'm having problems restoring DB differentials. I'm getting the error "the differential backup cannot be restored because the database has not been restored to correct earlier state." From researching the issue, it appears that the particular differential I'm having an issue with is out of sequence.

Is there a way to check the database_backup_lsn from the backup file (.bak) I'm getting? I'm getting the full/differentials backup files from a vendor and it's not easily possible for me to query the source db and msdb...

When I try to restore the problematic differential file, I can see some lsn values in the SQL server management studio window. However, I can't seem to understand if I can figure out if the differential is out of sequence. I'm going to paste the lsn information below to see if someone else can figure it out from that info. I just may not be understanding how the lsn's work.

first lsn             last lsn            checkpoint lsn       full lsn            start date finish date
12642000000130300283 12642000000188400001 12642000000130300283 12642000000130300283 9/17/2011 23:53 9/17/2011 23:56
12742000000735900168 12742000000757500001 12742000000735900168 12742000000735900168 9/18/2011 23:24 9/18/2011 23:25
12951000012892000172 12951000012922000001 12951000012892000172 12951000012892000172 9/19/2011 23:27 9/19/2011 23:28
13123000002683100125 13123000003655500001 13123000002683100125 13123000002683100125 9/20/2011 23:26 9/20/2011 23:26

The first record is the full db and subsequent 3 are differentials. The full and the first 2 differentials restore fine. It's the final differential that is not restoring and showing the above message.

thanks.


texts: How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?

texts:
This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in order to avoid the oversize of the same, I didn't design the DB, I'm just taking the maintenance of an application that use this DB, so I don't know the name of these stored procedures or triggers, what I want to do is locate this stored procedure or trigger, check the code and modify it to leave this "user history" longer on the table.

Someone told me to check the "sysobjects" table, where I can actually see something with the same name of the table, but this is the only information I have been able to retrieve, any advise?

Thank you.


texts: Handling changing data in the Fact table

texts:
We have an employee & benefits data warehouse which is producing incorrect information in the fact table.

In File A (Feb), the employee has a leaving date of 27th Feb 2015.
In File B (Mar), the employee has a leaving date of 24th April 2015.
In File C (Apr), the employee has a leaving date of 24th April 2015.

The Employee dimension is correct.  We have an SCD which picks up the fact that the leaving date has changed and the record is updated.

The fact table is a daily grain, so when File A (Feb) is loaded, we have set the EmployeeLeftOnDay to true for the 27th.  The data for the 28th is now 0 because the employee is listed as leaving the company.  This is fine because our measures are not affected.

When we load File B (Mar), the Employee is active because the leaving date is now 24th April 2015, so we'll populate all of the data showing that employee is active between 1st March 2015 and 31st March 2015.

Now to File C (Apr), the employee is active up to the 24th April 2015 and on that date, the EmployeeLeftOnDay is set to true.  For the 25th April - 30th April, the employee will still exist, but will have 0.

The reason data appears after an employee has left is because they may appear in later files until their payroll is closed.  At this point they become inactive in the source application and won't exist in source files.

So when we test the "Leavers" measure, we get 1 more than expected, because this person, according to the flag, has left twice.

I should add that the fact table captures employee and benefit information, so assume the employee has 5 benefits (Healthcare, Dental, Pension, Childcare & Life Cover), so for each day, you'd have a 5 records per employee with the cost breakdown for the benefits.  In the scenario I described above, this means the 28th Feb has 0 for all costs to that employee's benefits.

Is there a clever way to do this besides having to write an update statement to patch the data?

Thanks!


texts: MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time

texts:
I want to create a pair of table with a structure along the lines of:

CREATE TABLE tableA (
    ext_id bigint NOT NULL,
    local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB doesn't support this) */
    PRIMARY KEY (ext_id, local_id),
    );

CREATE TABLE tableB (
    ext_id bigint NOT NULL,
    local_id int NOT NULL,
    CONSTRAINT FOREIGN KEY(ext_id, local_id) REFERENCES tableA(ext_id, local_id) ON DELETE CASCADE ON UPDATE CASCADE /* <-- Requires InnoDB (MyISAM doesn't support this) */
    );

However as stated in the comments these features seam to be mutually exclusive based on the database engines that support them i.e. InnoDB doesn't support auto_increment on local_id on tableA, and MyISAM doesn't support tyhe FOREIGN KEY in tableB referencing tableA.

I'm looking for a way to simulate the missing feature on one of the database engines.

I could use a trigger to simulate the ON DELETE/UPDATE CASCADE part of the foreign key, but I don't believe this will work for enforcing the existence of (ext_id, local_id) in tableA when inserted into tableB. Beyond this I have no ideas on how I can solve this problem.


texts:
The Situation:
I have a write-heavy, read-light ETL workload with the basic process of:

get chunk of raw data (generally 1000-5000 records) from an import landing table.
pass the chunk of data from step 1 to the requesting windows service for some heavy duty calculations & processing. Each record ingested in a chunk may result in 100+ downstream records in other tables.
Windows service calls a save SP which has 14 MemOpt table variable parameters containing the processed "chunk" of data (three of these table variables have upwards of 50k rows or more).

The work is insert heavy and there is very little read ops going on in step 3, however, the "chunk" may contain data to be deleted and/or updated.  There is minimal read contention between step 1 & 3.
I've virtually eliminated deadlocks during step 3, although there is
significant blocking during 3 with insert/update ops.

Question:
Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?
What are the risks of introducing update conflicts?


texts: Indexed view doesn't exist on the subscriber

texts:
I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber.

The object I want to replicate is an indexed view. The base tables exist only on the publisher.

The replication fails due to the following error:

  Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber

It is true that the view doesn't exist in the subscription database. How can i create it without the base tables?


texts: What triggers the SSRS to generate a log file or modify a current one?

texts:
When running a trace on my new SSRS Reporting Services 2016, after troubleshooting some problems related to migrating from SSRS 2008, I find the following pattern running continuously:

Here is the continuously run script:

--========================================================
--the script that is run over and over
-- by the SSRS service
--========================================================

    declare @BatchID uniqueidentifier

    set @BatchID = NEWID()

    UPDATE [Event] WITH (TABLOCKX)
        SET [BatchID] = @BatchID,
        [ProcessStart] = GETUTCDATE(),
        [ProcessHeartbeat] = GETUTCDATE()
    FROM (

        SELECT TOP 4 [EventID]
          FROM [Event] WITH (TABLOCKX)
         WHERE [ProcessStart] is NULL
      ORDER BY [TimeEntered]

        ) AS t1
    WHERE [Event].[EventID] = t1.[EventID]

    select top 4
        E.[EventID],
        E.[EventType],
        E.[EventData]
    from
        [Event] E WITH (TABLOCKX)
    where
        [BatchID] = @BatchID
    ORDER BY [TimeEntered]

set @BatchID = newid()

UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (

    SELECT TOP 4  [NotificationID]
        FROM [Notifications] WITH (TABLOCKX)
        WHERE ProcessStart is NULL and
    (   ProcessAfter is NULL
        or ProcessAfter < GETUTCDATE())

    ORDER BY [NotificationEntered]

) AS t1
WHERE [Notifications].[NotificationID]
        = t1.[NotificationID]

    select top 4
    -- Notification data
    N.[NotificationID],
    N.[SubscriptionID],
    N.[ActivationID],
    N.[ReportID],
    N.[SnapShotDate],
    N.[DeliveryExtension],
    N.[ExtensionSettings],
    N.[Locale],
    N.[Parameters],
    N.[SubscriptionLastRunTime],
    N.[ProcessStart],
    N.[NotificationEntered],
    N.[Attempt],
    N.[IsDataDriven],
    SUSER_SNAME(Owner.[Sid]),
    Owner.[UserName],
    -- Report Data
    O.[Path],
    N.[ReportZone],
    O.[Type],
    SD.NtSecDescPrimary,
    N.[Version],
    Owner.[AuthType],
    SR.[SubscriptionResult]
from
    [Notifications] N with (TABLOCKX)

    inner join [Catalog] O
            on O.[ItemID] = N.[ReportID]

    inner join [Users] Owner
            on N.SubscriptionOwnerID = Owner.UserID

    left outer join [SecData] SD
                    on O.[PolicyID] = SD.[PolicyID]
                AND SD.AuthType = Owner.AuthType

    left outer join [SubscriptionResults] SR
                    on N.[SubscriptionID] = SR.[SubscriptionID]
                AND CHECKSUM(convert(nvarchar(max),N.[ExtensionSettings]))
                    = SR.[ExtensionSettingsHash]

where
    N.[BatchID] = @BatchID
ORDER BY [NotificationEntered]

In the meantime SSRS generated another log. There is nothing significative in the log, it is all information only, as you can see on the partial view picture below:

All seems normal. So the question is:

What triggers the SSRS to generate a report - other than an exception or error somewhere?

Where in SSRS can I go to have a look\modify this behaviour if possible?

I have checked the config files:
You should backup these alongside the report server databases:

Rsreportserver.config
Rssvrpolicy.config
Rsmgrpolicy.config
Reportingservicesservice.exe.config
Web.config for the Report Server ASP.NET application
Machine.config for ASP.NET

This is the log in question:

The modified date of the file has just changed and I noticed SSRS added information to this same file.


texts: In what data type should I store an email address in database?

texts:
I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent.  For example: this SQL Server recommendation uses varchar(80) or this Oracle example

Is there a reason to not use the full 254 character maximum?  Doesn't a varchar by definition use only as much storage as needed to hold the data?

Are there significant performance implications/trade-offs which cause so many implementations to use less than the full 254 possible characters?


texts:
I have a table with data as:

ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-22
3     Action1   2014-01-28
4     Action2   2014-01-20
5     Action2   2014-01-22
6     Action2   2014-01-28

I need to group by Date, grouping with 3 days of difference:

Action       Count
Action1      2
Action2      2

If a record is grouped with a previous record, it won't be in another group:

ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-23
3     Action1   2014-01-24

Action       Count
Action1      2

My solution:

SELECT T1.Action, DATEDIFF(DAY,T1.Date,T2.Date) as Count
FROM Table T1
JOIN Table T2 ON T1.Action=T2.Action
WHERE DATEDIFF(DAY,T1.Date,T2.Date) BETWEEN 1 AND 3
GROUP BY T1.Action,T1.Date,T2.Date

Is this a correct solution?


texts: How to selecting data where the date is with the next month or later

texts:
I want to display all data where the the expiry date is within next month or later, here is my code:

<cfquery name="ExpiryDate">
        SELECT *
        FROM Date
        WHERE Expiry >=

</cfquery>


texts: Calculating count on same table based on condition

texts:
So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number.

I.e. something like this:

declare @AmountOfSimilarSurnames int;
set @AmountOfSimilarSurnames = (...)

declare @Ref nvarchar(100);
set @Ref = 'CLT00' + @AmountOfSimilarSurnames

I need help setting that AmountOfSimilarSurnames

So far I got this but it is working:

select COUNT(*) FROM Client c1
join Client c2 on RIGHT(c1.Client_LName,1) = RIGHT(c2.Client_LName,1)


texts: How to know DB size using backup file without restoring it

texts: Is there anyway so that we can estimated/know size of database using backup file without restoring it?


texts: sql server database sharding - what to do with common data / non sharded data

texts:
We have a very large scale enterprise level database.  As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows.  sql proc optimization has been performed and hardware has already been scaled up to a very high level.

We are looking to shard the database now to ensure that we can handle company growth and future loads.

We have decided what particular data should be sharded. It is a subset of our database which is highly utilized.

However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an  Inventory table for instance or possibly an Employee table, user table etc .

I see two options to handle this common/universal data:

1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs.

2) design 2 - Give each shard its own copy of all common/universal data.  Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards.

concerns about design #1

1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update  a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily.  The data now exists on seperate sql instances and databases.   You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data.

2)a loss of referential integrity.  Not possible to do cross database referential integrity.

3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards.

4). increased database trips.  Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases.  Some network latency here but I am not worried about this issue as much as the above 3.

concerns about design #2

In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards.  the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively.

I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see.

thank you in advance.


texts:
I am having the hardest time following the tutorials on this site regarding ranking with MySQL.  I have a larger problem than others seem to show.
My table is constantly updating with single answers (ans) corresponding to a matching word (word).  I also have to check whether the row has an answer at all (ans IS NOT NULL).

I've devised the following:

SELECT userid, floor(sum(ans = word) / count(id) * 100) as score
FROM fb_asl.quiz
WHERE DATE(`time`) BETWEEN CURDATE() + INTERVAL (-3) DAY AND CURDATE() + INTERVAL (-3) DAY
AND ans IS NOT NULL
GROUP BY userid
ORDER BY score DESC;

This returns the following

1504    93
3567    79
8225    66

How can I go about getting a rank corresponding to a specific userid?  Say returning something like:

2   3567    79


texts: Calculate Inventory Costing Based on First in, First Out (FIFO)

texts:
I am trying to get the cost of inventory based on FIFO.

I saw a similar question but it does not look for cost of inventory and tables seem to be structured differently.

Using MySQL I have a table purchase_order_items, purchase_order_receipt_items and item_inventories the purchase_order_receipt_items table gets updated with every item that is received and for purchasing control, what was ordered and what was received.

The item_inventories table gets updated with a new line item for every inventory movement in or out and we calculate the current inventory using a simple SUM of all in and out quantity.

What I am missing is the cost of the current inventory. The cost of the same item varies and we don't have a cost added in the item_inventories table.

Table purchase_order_items:

| PO    | Date         | Quantity | Item | Cost  |
|-------|--------------|----------|------|-------|
| PO001 | 01-Jan-2019  | 1        | AO21 | 12.50 |
| PO002 | 02-Jan-2019  | 3        | AO21 | 10.99 |
| PO003 | 09-Jan-2019  | 2        | AO21 | 12.00 |

Table item_inventories

| ID    | Date         | adjustment_qty | Item |
|-------|--------------|----------------|------|
| 1O001 | 01-Jan-2019  | 1              | AO21 |
| 1O002 | 02-Jan-2019  | 3              | AO21 |
| 1O003 | 04-Jan-2019  | -1             | AO21 |
| 1O004 | 05-Jan-2019  | -1             | AO21 |
| 1O005 | 09-Jan-2019  | 2              | AO21 |

I currently calculate the item_inventories and get a total of 4 SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21' but that does not give me the cost of the inventory.

How do I join the purchase_order_items table to get the cost and qty in a SUM query of the item_inventories table?


texts: Take one replica offline from an availability group

texts:
I have 2 replicas running SQL Server 2016 Enterprise Edition in an AlwaysOn Availability Group.

One replica is primary and other replica is secondary and readable.

I have a business requirement that should allow one database replica to be taken offline and back online after a period of a few minutes by a custom developed APM monitor.

The database replica to be taken offline can be primary or secondary.

The database replica has to be take offline and online via transact SQL

When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

I can do a suspend or a planned manual failover but neither seems to support what I need.

The suspend & manual failover only works on secondary database replicas.

The suspend allows existing connections to execute requests.

The manual failover just switches the primary to secondary role and vice versa.

How do I do this ?


texts: Is there a way to find the transaction count on a sql table?

texts: I need to find out the count of inserts, updates and deletes on a SQL tables in a given day or today? All counts together is fine too, if individual counts are not possible. Is there a way to find this stats information in SQL Server 2012?


texts: What is the syntax for querying a spatial column?

texts:
What is the right syntax to get all the points within the bounding box? The table consists of a lat and lng column and a spatial column "pt" generated from the lat/lng values.

calculated the topleft and bottomright coords for the MBR

tested with the following query ( without the haversine )

SELECT *
FROM tb_gps
WHERE (lng BETWEEN 4.88263070241 AND 4.90030729759)
  AND (lat BETWEEN 52.3677890788 and 52.3785809212);

testing the spatial column generates an error

SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

This will give null results

SELECT * FROM tb_gps as t WHERE MBRCONTAINS( GeomFromText( 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)' ) , t.pt )=1;

The mysql version is

protocol_version    10
version     5.1.68-cll

It could be that I am using the wrong syntax, am not sure. Could someone help me out with this

( the coords in the second query are different, but that's not really the point)

edit

I had success with this query(at least I got some results back, so I am going to work on that a little more)

SELECT  *
    FROM    tb_gps
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point
                                    (
                                    4.8914691 + 10 / ( 111.1 / COS(RADIANS( 52.373185))),
                                     52.373185 + 10 / 111.1
                                    ),
                            Point
                                    (
                                    4.8914691 - 10 / ( 111.1 / COS(RADIANS(52.373185))),
                                     52.373185 - 10 / 111.1
                                    )
                            ),
                    pt
                    )


texts:
I have Oracle job that uses dbms_job package. I see that it becomes BROKEN:
Select job,what,BROKEN from dba_jobs;
-------- ------------ -----------
1   statspack.snap; Y

I would like to know the reason it's BROKEN
ASAIK for scheduled job we have dba_scheduler_job_log view where we can see the fail reason, but what about dbms_job jobs?


texts: How do I get a list of all the partitioned tables and partitioning details in my Teradata?

texts:
How do I get a list of all the partitioned tables in teradata?

and

How do I get a partitioning columns and details for a table  in teradata ?


texts: Can i restore mysql database to point of time when it was just installed/factory default?

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


texts: MongoDB Sharded Cluster on AWS reliability

texts:
I have setup one mongodb cluster on aws with 3 shards as replicaset, 3 mongos and 3 configs.

All the 3 EC2 Instance are t2.medium (4GB, 2CPU, 50GB EBS 150 IOPS (io1 not gp2))

Our database is only 1.65GB but on disk + replication it is around 30GB per Instance (including journarls ... etc) and each shard holds around 550MB of the data

It only functions perfectly when the all instances are online!

Do you think it is because of some misconfiguration or is it because of that these instances are small, not EBS optimised.

It was much better to use standalone, as it is easier to maintain when something happen!

as shard key we used _id : hashed

is it because the mongos and configs are hosted with the mongod ?

is it because when stepping down a primary then mongodb must sync the data to the new primary which because of the network of aws, memory and IO make this slowness?


texts: Job Activity Monitor - Log File Viewer is showing Duration for running jobs

texts:
As you can see in Log file Viewer it has a column Duration which change for a running job.
I want to include this column in my query (from  where I get jobname etc)
Any idea to make a SIMPLE script? My only reseacrh so far was sys.dm_exec_sessions and Total_elapsed_time BUT how to connect this to my query where I have my jobname etc?

My query so far;

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC) AND start_execution_date is not null
AND stop_execution_date is null;


texts: How to convert SQL view creation script into a query, instead?

texts:
I have the following SQL view creation script. I would simply like to turn it into a query. I tried to replace the initial line with a SELECT statement, but this did not work. Can someone please show me what I'm doing incorrectly?

CREATE OR REPLACE FORCE VIEW "RMS"."VW_CR_ADDITIONAL_INFO" ("COMMIT_REG_ID", "FUND_CERTIFIED", "FUND_CERTIFIED_DATE", "CANCELED", "APPROVED", "AMOUNT", "LAST_STATE_ID") AS
  WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;


texts:
I have a table where I need to insert a '*' sign before a lot of names. My table looks like this now:

id,name

1,cake
2,car
3,mountain

And it should be:

id,name

1,*cake
2,car
3,*mountain

The name column is nvarchar. I have the ids in a csv file. I thought I could make the update statement with concat and a where clause with the IDs, but I can't get it to work.

I tried

UPDATE table
SET    NAME = concat('*', NAME)
WHERE  id = '1'
        OR id = '2'

But got

  Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized
  built-in function name


texts:
I have records like

 A5
 A4
 Z1
 B2
 C7
 C1A
 C11A
 B1
 B4

I want them to be sorted in this manner

A4
A5
B1
B2
B4
C1
C11A
C7
Z1

using the ORDER BY clause.

I want them to be sorted by alphabets and then by numeric values.


texts:
I've successfully installed postgres on my Ubuntu machine.  I am running a script for a website I am working on, here is the relevant code:

apt-get -y install postgresql libpq-dev
su postgres <<EOF
    createuser app
    createdb ultimaterehab
EOF
PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

When executed this script gives the error:

createuser: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off

and when I try running psql I get a similar error:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "robin", database "robin", SSL off

I have tried editing the pg_hba.conf file to add the hosts but I keep getting the same error.


texts: unable to create schema on amazon rds for postgres

texts:
I'm trying to create a schema on a postgres instance located on Amazon rds.

That schema would be used to import data from an existing schema built on a local db.

Issue is I do not have permissions to create schemas on RDS.

The user dw_etl I'm using to connect to the postgres instance has the role rds_superuser, which is similar to the superuser role. dw_etl should be able to create new schemas.

Questions:

Is there a way to create schemas with RDS?
If I cannot create schemas on RDS, what would be the best way to "silos" the data? Will the public schema became some kind of dump?

Thanks


texts: SQL Server Transactional Replication - what is sp_MSreleasesnapshotdeliverysessionlock?

texts:
Occasionally when creating and finishing a snapshot to re-sync data, some of the subscribers seem to hang about for several hours afterwards before they start to take the snapshot. There's no blocking that I can see on the distributor. When I check on the subscriber for sessions against the subscription database, they are running: sp_MSreleasesnapshotdeliverysessionlock

This sometimes happens on a number of SQL Server versions, but all patched to recent CUs. What is this process? And what is likely to be going wrong?


texts: I need to create a user ,who can log on to a contained DB.In this context what is the benefit of using a user mapped to a certificate?


texts: Select rows with different id but same values


texts: MySQL: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it

texts:
I've been reading possible solutions to this problem but until this time, those solutions don't work with my case.

Activity: In phpMyAdmin, the following error pops up while opening a View.

Error: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it

Thanks a lot in advance.


texts:
I have a rather strange situation. There is a table ProductStock which has 2 triggers: one for afterInsert and one for afterDelete
both do an insert with the changed value in STOCKDEBUGTRIGGERED

afterDelete:

INSERT INTO STOCKDEBUGTRIGGERED
       (ProductID,Amount,StockOld,StockNew)
 select deleted.ProductID, count(deleted.ProductStockID),
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid) + count(deleted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid)
 from deleted
 group by deleted.ProductID

afterInsert

INSERT INTO STOCKDEBUGTRIGGERED
       (ProductID,Amount,StockOld,StockNew)
 select inserted.ProductID, count(inserted.ProductStockID),
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid) - count(inserted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid)
 from inserted
 group by inserted.ProductID

But this is the query which troubles me:

SELECT TOP 250
    STOCKDEBUGTRIGGERED.ProductID,
    productcode,
    'http://urltomyimages.com/' + ProductPictureName as img,
    Amount,
    STOCKDEBUGTRIGGERED.StockOld,
    STOCKDEBUGTRIGGERED.StockNew,
    ChangeDate,
    (select top 1 STOCKDEBUG.StockDebugWho from STOCKDEBUG
            where STOCKDEBUG.ProductID = STOCKDEBUGTRIGGERED.ProductID
            and STOCKDEBUG.StockOld = STOCKDEBUGTRIGGERED.StockOld
            and STOCKDEBUG.StockNew = STOCKDEBUGTRIGGERED.StockNew
            ) as who
    FROM STOCKDEBUGTRIGGERED
    inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
    order by ChangeDate desc

Please do not advice temporal tables, because this does work and is running on sql 2008. It only has a high load because this gets executed by a monitoring tool every 5 minutes.

Here are the client statistics:

and here is the query plan:

What troubles me is the 44% sort with the yellow exclamation mark:

So is there a thing I missed? I made a view for the query and threw in three WITH(NOLOCK) statements. But that did not make it run faster. No index suggestion.

edit have read https://stackoverflow.com/questions/29001721/is-it-possible-to-allocate-memory-to-a-query-in-ms-sql-server because I wanted to see if there was a way to allocate 5mb memory for this query to skip the use of the tempdb. And have read https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/ but that is not the case for me I think because I limit it to 250 rows.

edit2 if I remove the subquery:

SELECT TOP 250
    STOCKDEBUGTRIGGERED.ProductID,
    productcode,
    'http://ourimages.com/' + ProductPictureName as img,
    Amount,
    STOCKDEBUGTRIGGERED.StockOld,
    STOCKDEBUGTRIGGERED.StockNew,
    ChangeDate
    FROM STOCKDEBUGTRIGGERED
    inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
    order by ChangeDate desc

the sort increases to 89% and there is still a 15mb memory load because of the tempdb

edit3
as reply on @T.H. his answer:

execution time is now less than 1% or 2% of original!
Why did neither the perf advisor from azure or the ssms suggest these indexes?

edit4 To prove that @T.H. is right. You can see exactly when I created his two suggested indexes. It had a spike every x time because of that query up to 20% dtu. All spikes are gone and it is nice 1% avg dtu.


texts: Select Data from two tables ONLY IF ID on both tables are SAME

texts:
I need to select returns result

SELECT wl.id FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id

Only IF ALL of wp.product_id EXISTS in wishlist_products_bought.product_id

Thanks


texts: Getting conversion error, for value that doesnt exist in database

texts:
I am running this query:

SELECT
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
        THEN 1
        ELSE 0
        END) AS 'Official Holidays'
FROM
    EmployeeAttendance_Backup ea
    , Customer em
    , Employee emp
WHERE
    ea.markedFor >= '2012-08-26'
    AND ea.markedFor <= '2012-09-25'
    AND em.RSCode = ea.DistID
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY
    em.CustomerName;

I get an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value
    'EmployeeName A B' to data type int.

I've tried running the query with an explicit JOIN such as:

SELECT
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
        THEN 1
        ELSE 0
        END) AS 'Official Holidays'
FROM
    EmployeeAttendance_Backup ea
    LEFT OUTER JOIN Customer em ON em.RSCode = CAST(ea.DistID AS INT)
    LEFT OUTER JOIN Employee emp ON emp.EmployeeMasterID=ea.Empid
WHERE
    ea.markedFor >= '2012-08-26'
    AND ea.markedFor <= '2012-09-25'
    AND em.RSCode = ea.DistID
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY
    em.CustomerName;

However, I get the same error as above.

If I remove all the SUM statements from above, I get zero records. However, if I enable even one, I get the same error.

The strange part about this error is, there is no such Employee with the name EmployeeName in any of the tables used in this query.

I am stumped as to what is happening. Can anyone shed some light?


texts: Finding earliest connected value over two columns

texts:
I have a, perhaps slightly esoteric, use case.

Given a table like the following:

 id1 | id2 |      timestamp
-----+-----+---------------------
   3 |   4 | 2016-03-22 09:52:15
   1 |   2 | 2016-03-22 12:05:28
   2 |   3 | 2016-03-22 11:55:56
   5 |   6 | 2016-03-22 11:58:23
   6 |   7 | 2016-03-22 09:51:54

I would like to "trace" each id in id1 to its oldest id2 where connection can span over id1 and id2. For instance in the above dataset I would like the resultset to contain:

1 |   4 | 2016-03-22 09:52:15
5 |   7 | 2016-03-22 09:51:54

The results should be ordered by the timestamp column.
And ideally no other occurrences of "1" and "5".

I am using Amazon Redshift, which imposes some limitations.

So far I have dabbled with using window functions combined with a self join:

SELECT t1.id1,
FIRST_VALUE(t2.id2) OVER (
   PARTITION BY t1.id1, (t1.id1=t2.id1 or t1.id2=t2.id2)
   ORDER BY t2.timestamp ASC
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
FROM    test_decision t1
LEFT JOIN test_decision t2 ON t1.id1 = t2.id2

The general idea is to group based on logical expression (e.g. contains either id1 or id2), rather than partitioning only on rows with both. I've tried SELECT ... GROUP BY in a similar manner, but without getting the results I want.

I hope this use case makes sense to some of you.

In an effort to eliminate ambiguity: The rows are connected by having a value of id1 or id2  in either id1 or id2 columns of other rows. For instance, 1 leads to 4 through rows having (id1,id2) = (1,2),(2,3) and (3,4) in my example.

Regarding recursiveness: I think I can solve it by joining multiple times and coalescing the "outermost" value, my problem here is that the problem can be arbitrarily deep, e.g. I can solve this case by joining twice, but is there a way in SQL to get arbitrary recursion depth? And is that a good idea?


texts: Postgres Pass Subquery results to function that returns TABLE

texts:
I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I get syntax errors

SELECT
  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    )
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views
        group by organization_id
) as sub;

+------------------------------------------+
| filters                                  |
|------------------------------------------|
| (32a430c6-eb33-4ac1-8ce7-9fb64e5b465c,1) |
| (a9177e44-95bf-4b1b-8724-b48efc97e3cf,4) |
+------------------------------------------+

The function signature is as follows

FUNCTION zdb.filters(
    index regclass,
    labels text[],
    filters JSON[])
RETURNS TABLE (
    label text,
    doc_count bigint)

This will give me errors

SELECT
  label
, doc_count
FROM  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    )
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views
        group by organization_id
) as sub;

update 8/26/19

The hangup is that I want to join the result of the function with another table, but I can't seem to get it.

this is the closest I can get, but the result of the function call, is still a record that I can't expand

WITH cviews AS (
    select
        id
    , organization_id
    , created_by
    , created_at
    , modified_by
    , modified_at
    , name
    , description
    , cache
    from conversation_views
    where organization_id = '3b440f43-c5f1-4338-a904-601c4dc8fd15'
)
, aggs AS (
    select organization_id, array_agg(id::text) as labels, array_agg(cache) as filters
      from cviews
      group by organization_id
  )
, counts AS (
  select
    '3b440f43-c5f1-4338-a904-601c4dc8fd15' as organization_id
    , zdb.filters(
        'conversations_zdx'
    , aggs.labels
    , aggs.filters
    )
  from aggs
)
select
cviews.*, counts.*
from cviews
inner join counts on counts.organization_id::UUID = cviews.organization_id::UUID


texts: Design Pattern - One of Many Parent Tables

texts:
I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables.  I've seen two solutions for the problem, but neither is personally satisfying.  I'm curious what other patterns you've seen out there?  Is there a better way to do it?

A Contrived Example
Let's say my system has Alerts.  Alerts can be received for a variety of objects -- Customers,  News, and Products.  A given alert can be for one-and-only one item.  For whatever reason  Customers, Articles and Products are fast moving (or localized) so the necessary text/data cannot be pulled into Alerts upon creation of an Alert.  Given this setup I've seen two solutions.

Note: Below DDL is for SQL Server but my question should be applicable to any DBMS.

Solution 1 -- Multiple Nullable FKeys

In this solution the table that links to one-of-many tables has multiple FK Columns (for brevity's sake the below DDL doesn't show FK creation).  THE GOOD - In this solution it's nice that I have foreign keys.  The null-optinality of the FK's makes this convenient and relatively easy to add accurate data. THE BAD Querying isn't great because it requires N LEFT JOINS or N UNION statements to get the associated data.  In SQL Server, specifically the LEFT JOINS preclude creating an indexed view.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    ProductID   int null,
    NewsID      int null,
    CustomerID  int null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

ALTER TABLE Alert WITH CHECK ADD CONSTRAINT CK_OnlyOneFKAllowed
CHECK (
    (ProductID is not null AND NewsID is     null and CustomerID is     null) OR
    (ProductID is     null AND NewsID is not null and CustomerID is     null) OR
    (ProductID is     null AND NewsID is     null and CustomerID is not null)
)

Solution 2 -- One FK in each Parent Table
In this solution each 'parent' table has an FK to the Alert table.  It makes it easy to retrieve alerts associated with a parent.  On the down side, there is no real chain from the Alert to who references.  Further, the data model allows for orphaned Alert's -- where an alert is not associated with a Product, News, or Customer.  Again, multiple LEFT JOINs to figure out the association.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

Is this just life in a relation database?  Are there alternate solutions you've found more satisfying?


texts: MongoDB: Index intersection does not eliminate the need for creating compound indexes

texts:
This is a quote from the MongoDB docs:

https://docs.mongodb.com/v3.0/core/index-intersection/#index-intersection-and-compound-indexes

But it doesn't explain why it doesn't eliminate the need for compound indexes. Why doesn't it?


texts:
I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ID number) in table 1 and 60 columns () in table 2 that must be used as joining keys. So, my code would be something like below:

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 OR
t1.col1 = t2.col2 OR
t1.col1 = t2.col3 OR
...
t1.col5 = t2.col1

and so forth. The combination of 5 and 60 is huge and it kills the server. It also does not sound logical. I was thinking of putting 60 columns as row and increase the number of records for reducing the number of column. However, not yet sure if this is the best solution. Any solution that does not kill server is highly appreciated.

Note: Kindly note that each column from tb1 is to be matched against 10-15 columns in tbl2. For instance, column col1 that contains "Account Number" is joined with col1-10 which hold all potential "Account Number". col1 in no way is joined with email address or some other columns.


texts: Is it possible to determine the default Collation and server version of a database without a full restore?

texts:
I work for a company who regularly receives large SQL database backups from clients for support purposes. We support multiple SQL versions and collations, but currently have to manually confirm our client's SQL versions before restoring to the correct SQL instance. I am wondering if there is a way to automate this process.

I am looking to:

Find the SQL Server version a database was backed up from (we get 2008 R2 as well as 2014 and 2017)
Find the database's default Collation

Without requiring a full restore to a potentially incorrect SQL instance, which takes a significant amount of time due to their size. (maybe a piecemeal restore?)


texts: Where is my old partition in mysql show create table?

texts:
I am keep adding daily base partition for a table as e.g.

ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) (
PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')),
PARTITION p21 VALUES LESS THAN (to_days('2012-12-26')),
PARTITION p22 VALUES LESS THAN (to_days('2012-12-27')),
PARTITION p23 VALUES LESS THAN (to_days('2012-12-28')),
PARTITION p24 VALUES LESS THAN (to_days('2012-12-29'))
)

When I run this show create table tblData is only shows me the partition p20 to p24. I am wondering where is all my previous p0-p19? Is it get dissolve when each time I run the alter table to add partitions?


texts: ms sql database in suspect mode when we upgrade from developer to standard edition

texts:
sql database in suspect mode when we upgrade from developer to standard edition 2012 version.
in log file there is two error occurred 933 and error 905.
following error occurred when we try to get back online the db

  Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
  Msg 905, Level 21, State 1, Line 1 Database 'VTSDB' cannot be started
  in this edition of SQL Server because it contains a partition function
  'FNprocessedrawdata'. Only Enterprise edition of SQL Server supports
  partitioning. Msg 933, Level 21, State 1, Line 1 Database 'VTSDB'
  cannot be started because some of the database functionality is not
  available in the current edition of SQL Server.


texts:
I have a table with roughly 11 million rows, defined as:

CREATE TABLE [sko].[stage_närvaro](
    [datum_fakta] [datetime] NULL,
    [person_id] nvarchar NULL,
    [läsår_fakta] nvarchar NULL,
    [termin_fakta] nvarchar NULL,
    [period_fakta] nvarchar NULL,
    [vecka_fakta] nvarchar NULL,
    [veckodag_fakta] nvarchar NULL,
    [ämne_id] nvarchar NULL,
    [ämne] nvarchar NULL,
    [frånvaro_min] [float] NULL,
    [närvaro_min] [float] NULL,
    [frånvaroorsak_id] nvarchar NULL,
    [frånvaroorsak] nvarchar NULL,
    [beskrivning] nvarchar NULL,
    [personal_id] nvarchar NULL,
    [försystem] nvarchar NULL
)

With the following non-clustered index:

CREATE NONCLUSTERED INDEX [stage_skola_närvaro_ix1] ON [sko].[stage_närvaro]
(
    [person_id] ASC,
    [termin_fakta] ASC,
    [läsår_fakta] ASC
)

When I run the following delete query, it takes atleast 2+ hours to complete.

DELETE  sko.stage_närvaro
FROM    sko.stage_närvaro e
WHERE   försystem = 'Extens'
AND EXISTS (
    SELECT  *
    FROM    ext.v_imp_närvaro v
    WHERE   e.person_id = v.person_id
    AND     e.termin_fakta = v.termin_fakta
    AND     e.läsår_fakta = v.läsår_fakta
)

Is my delete-query using my index? Would it help to disable the index before deleting, and enable it afterwards?

Edit:
The view ext.v_imp_närvaro has the same amount of rows as the table sko.stage_närvaro.

Edit2:
I suspected that it was an I/O issue, so I ran the following query as suggested by DaniSQL here:

SELECT TOP 10
        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0 -- remove zero wait_time
        AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
  'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK',
  'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT',
  'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
  'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
  'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
  'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
  'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR',
  'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

With the following result. I'm not sure how to interpret them though.


texts: Import Windows MySQL dump on a Debian Linux server

texts:
I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records.
Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, also MySQL 5.7).

During the import of the dump I received several error messages, which were not helpful.
Therefore I looked at the dump file with the nano editor and found out that the content cannot be displayed correctly:

��-^@-^@ ^@M^@y^@S^@Q^@L^@ ^@d^@u^@m^@p^@ ^@1^@0^@.^@1^@3^@ ^@ ^@D^@i^@s^@t^@r^$
^@
^@-^@-^@
^@
^@-^@-^@ ^@H^@o^@s^@t^@:^@ ^@l^@o^@c^@a^@l^@h^@o^@s^@t^@ ^@ ^@ ^@ ^@D^@a^@t^@a^$
^@
...

But if I open the dump under Windows in e.g. Notepad++, everything can be displayed perfectly.

So I thought about an error during the FTP upload. However, if I transfer a file from Windows to Linux and then transfer it back, the new file will still be readable under Windows, but not under Linux.

Can anyone give me a hint?
Thanks!

Little update:
This is how I made the dump:

.\mysqldump.exe -u root --databases m_data > D:\m_data_dump.sql

Update 2:
I also tried exporting with explicit charset:

.\mysqldump.exe -u root --default-character-set=utf8mb4 --databases m_data > D:\m_data_dump.sql

And imprted with:

mysql -u root -p --default-character-set=utf8mb4 < m_data_dump.sql

But sam result.


texts: Restaurant reservations - Tables combinations

texts:
I have 3 tables in a reservation system for a restaurant. So far the software was used only by restaurant's staff, however we want to accept reservations online as well. We have some small tables for 2 that can be easily moved to each other and make room for bigger parties and I want to accept reservations automatically if all of the tables that can be combined are available.
tables: holds all tables for each area in the restaurant.
| id | min_capacity | max_capacity | name | area   |
|----|--------------|--------------|------|--------|
| 1  | 2            | 4            | #1   | Inside |
| 2  | 6            | 8            | #2   | Inside |

reservations: holds reservation details
| id | datetime            | name     | status   |
|----|---------------------|----------|----------|
| 1  | 2020-09-01 20:00:00 | John Doe | Upcoming |
| 2  | 2020-09-05 13:00:00 | Jane Doe | Upcoming |

And one pivot table that holds reservation <=> table relation:
| id | table_id | reservation_id |
|----|----------|----------------|
| 1  | 1        | 1              |
| 2  | 2        | 2              |

How can I store different combinations of tables (manually entered) and "attach" reservations to tables/table combinations (so I can check if tables are available for specific time) efficiently?
Edit: More details, as I am nowhere close to finding a solution:
I am trying to figure out a way of "describing" which tables can be combined with other tables and "discard" a combination if one of the tables in that combination is already reserved.
For example I have 3 small restaurant tables (not database tables :D):

#1 for 2-4 people
#2 for 2-4 people
#3 for 2-4 people

Tables #1 and #2 can be combined to make a table for 4-6 people and tables #2 and #3 can be combined to make a table for 4-6 people.
But! tables #1 and #3 cannot be combined, as they are too far away from each other.
Here's a little drawing of the situation: Floorplan & Combos
I want to manually "describe" which table can be combined with different table. I don't know how to store this data so I can reserve table #1 and that automatically "disqualifies" Combination #1 and Combination #3 (from the drawing) for the specific date & time.


texts: Best approach to compare specific columns of 2 tables in different databases?

texts:
I'm running an application who does have a shorten version of a table in the backend database.

Both application- and backend-tables are having the same primary keys and some specific key columns.

What would be the best approach to compare both tables to find unmatched records? (The tables are containing currently 1.5 Mio rows -> going up to 5 Mio rows)

Application-Table-Example

id (pk), column1 (pK), column2, column3, column4, column5, column6, column7, column8, column9, column10 [datetime]

Backend-Table-Example

id (pk), column1 (pK), column2, column10 [datetime]

Columns I want to compare

id (pk), column1 (pK), column2, DATE(column10) [datetime -> converted to DATE]

My current approach is to create a CRC32-Checksum by the columns "id (pk), column1 (pK), column2, column10" for each row in the application database and to save them in a temporary table with the structure "id (pk), column1 (pK) and hash (crc32)". Afterwards I run an union select in the backend database and create also a crc32-checksum by the backend table and combine it with the temporary table. By grouping them after all common tables I should get for each dataset 2 rows. If I get only 1 row for a dataset, we have found an unmatch.

SELECT MIN(TableName) as TableName, {$columns} FROM (
                SELECT 'APPLICATIONTABLE' as TableName, {$columns}
                FROM APPLICATIONTABLE
                UNION ALL
                SELECT 'BACKENDTABLE' as TableName, {$pks}, CRC32(CONCAT_WS({$data['fields']})) as hash
                FROM BACKENDTABLE
            ) tmp GROUP BY {$columns} HAVING COUNT(*) = 1

Do you have any suggestions for a better approach? (My current approach works, but it's maybe not the most performant one)

Frontend- and Backend-Database are on different MySQL-Servers
Federated tables are no option to skip the temporary table part
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html looks like, that it doesn't support a partial comparison of explicit columns?
To some datetime fields I need to add fuzziness to obtain the same checksum (DATE(column10) ignores mismatches in second range)

Thanks a lot!