texts: How to use a recursive CTE to get ancestors in a hierarchy

texts:
create table division (
  id serial primary key,
  name varchar not null
);

-- nested sets table

create table location (
  id serial primary key,
  name varchar not null,
  division_id integer not null references division(id),
  tree_id integer not null,
  parent_id integer references location(id),
  lft integer not null,
  rgt integer not null,
  level integer not null
);

create table report (
  id serial primary key,
  somevar_1 integer not null,
  somevar_2 integer not null,
  tyme timestamptz not null,
  -- other stuff
  location_id integer not null references location(id)
);

-- division hierarchy is Division0 > Division1 > Division2 > Division3
-- reports are generated at Division3, and aggregated at
-- either Division2 or Division1 (usually the latter)

-- this means that to get my data for Division1, i have to
-- do two self-joins on the location table
-- plus one for the report table

given the above schema, i'm experimenting with different ways to speed up querying for the sum of each somevar aggregated at either Division2 or Division1. right now, what i do is

select
  sum(somevar_1) as somevar_1, sum(somevar_2) as somevar_2,
  div1.name, div1.id
from
  report join location as div3 on report.location_id = div3.id
join
  location as div2 on div3.parent_id = div2.id
join
  location as div1 on div2.parent_id = div1.id
where
  -- for example, all reports this year
  date_trunc('year', tyme) = '2019-01-01'
group by div1.id, div1.name

the above query runs in about 4.8s locally with a row count a bit shy of 55k reports for 2019. this is just the base data, without any further processing. the processing step also does something similar, and it's in a web application, so the user is waiting for a not-insignificant amount of time.

for my question: can i use recursive CTEs to speed this up? i haven't yet grokked recursive CTEs, so writing one is still a bit beyond me at this point.


texts: My log file was too big, and I made it unrestricted in growth. Should I shrink it with SQL Server Management Studio?  What are the downsides, if any, of doing this?


texts: How to perform a pivot on multiple columns


texts:
I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model.

I ran a full index rebuild at the weekend during which the log file swelled to about 32GB on a 46GB data file with 10MB log autogrowth which I imagine has caused the high VLF count.

From what I understand I should have upped the autogrowth, however this is not typical activity. Typically the .trn files backed up every few hours are 100-200MB so 10MB doesn't seem ridiculous.

So, I think I need to shrink the log file and allow it to grow again in as few growths as possible as scripted here out of hours. Does this sound sensible in this particular situation?

Also, the initial size of the log file is now 33896MB should I reduce this to something more sensible (200MB) after the shrink?


texts:
My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.

Query (1):

IF ( SELECT COUNT(UnitTrustCounterId)
     FROM   UnitTrustCounter
     WHERE  PeriodId = 0
   ) > 0
    SELECT  1
ELSE
    SELECT  0

Query (2):

IF EXISTS ( SELECT  *
            FROM    UnitTrustCounter
            WHERE   PeriodId = 0 )
    SELECT  1
ELSE
    SELECT  0


texts: Aggregate function / query in Oracle 11g

texts:
I am working in Oracle 11g.

I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert)

I have written an inline view (with tons of LEFT OUTER JOINS and CASE statements) that sets up the following scenario:

ID (non-unique)  |  Received
----------------------------
        1               Y
        1               N
        2               N
        2               N
        2               Y
        3               N
        3               Y

I need to query the inline view:

If each ID has at least 1 row with a 'Y', return 'Y'.

Otherwise return 'N'.

So for the scenario above, I would return a 'Y'.**

I thought an aggregate function would work, and I've been reading through GROUP BY, ANY/ALL, and Count.  However, I did not see

Not asking anyone to write my SQL for me ... if anyone could even suggest a function or technique or article I could read / run down.  I guess I don't know enough about complex SQL to know what to search for.

If this is not possible to write, I guess I could look into doing it as a PL/SQL Function.  I know that I can use a cursor and track things with variables.  However, I thought it would be more efficient to write it as pure SQL (seem to recall the Oracle Optimizer can't see into functions/procedures).

Thank you very much in advance,

Philip


texts: Log shipping restore delay of a minute between iterations

texts:
On a test database running Log Shipping jobs the initial Restores to catch up performed the first time I run the Restore job seem to have a 1 minute or so Wait time built in.  I would have expected Log Shipping to Restore those backups in succession at maximum speed.  Each Restore is taking under a second to complete (TRNs are approx 50K each).  But the delay between each file where it Xlocks the DB again is 64 - 68 seconds but not a fixed amount.
Switching to No recovery has not changed a thing.
Thanks in advance for your help

...Ray


texts: DROP command denied to user 'root'@'localhost'

texts:
Okay. Apparently someone removed the DROP privilege from root (they have every other privilege granted), presumably to prevent accidental deletion of tables. Problem is now no users have the privilege. Upon trying to reassign the privilege, mysql responds with

mysql> GRANT ALL ON *.* TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried creating a new user with the same privilege; same error.

Error creating account root@[hostname]: Access denied for user 'root'@localhost (using password: YES)

I double checked my who I'm authenticated as: root@localhost.

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>  select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MYSQL Grants

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

returns

+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               43 | 5.6.34       |
+------------------+--------------+
1 row in set (0.00 sec)

How then do I grant the privilege?


texts: SQL Server select records from header and body table

texts:
I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records.

Header

Id Group Name Value
1  1     n1   v1
2  2     n2   v2

Body

Id Group Name Value
1  1     nb1   vb1
2  1     nb2   vb2
3  2     nb3   vb3

Expected Result

Id Group Name Value
1  1     n1    v1
2  1     nb1   vb1
3  1     nb2   vb2
4  2     n2    v2
5  2     nb3   vb3

If it was just one group I could have just done a union but the number of groups isn't fixed.

The Id column in the result is just a row number. The other three columns Group, Name, and Value are more important to me.

The header row is always the first row for that group.


texts: Aggregate and GROUP BY conflict with HAVING?

texts:
I don't know if my logic in my query is incorrect, or if I simply have a syntax error, or both.

For each customer that had more than two orders, I want to list the customerID and the total number of orders placed.  However, this query is producing the following error, (in MS Access 2016)

  The level clause includes a reserved word that is misspelled or missing. or the punctuation is incorrect.

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Order_T;
GROUP BY CustomerID
HAVING(COUNT(OrderID) > 2)

It produces no errors if I remove the HAVING clause, but then doesn't show only customers with more than 2 orders.


texts:
I have created a report for SSRS 2008 which produces the results below.

What I am looking to achieve is for those results in pink, AD, to be under both AM and PM with the text centered i.e. merged cells.

The weekdays and AM/PM are group headings in a matrix based on fields in my table. To display the data I am using a table within the data region of a matrix.

I've read other posts about creating an additional row in the table which has 2 cells merged together, using an expression to hide this row when it is not used though I've not managed to get this to work correctly.

UPDATE 28/03/2012:
I've managed to get the AD to appear under both AM and PM. What I did was remove the AM/PM column group and create 2 cells under the Weekdays group and input the text AM and PM in these cells. I then created 2 rows in the data section of the matrix with the bottom row having 2 columns. In each of these cells there is a table with the expression

=IIF(Fields!SessionIdentifier.Value="AD","AD",Nothing)

and the row visibility in each table is set using an expression:

=IIF(Fields!SessionIdentifier.Value="AD", False, True)

What I would like to do now is hide the top row in the data region of the matrix when it is empty but I haven't figured out the expression to do this.


texts: How to query for certain text in log messages from Google App Engine that are exported to BigQuery?

texts:
I have used the 'Create Export' in the Logging section of Google Cloud Platform to export log entries into Big Query for analysis

Here is part of the structure of the table in BigQuery that logs the log entry

I want to search for entries that contains this user id 14994838999176.

In BigQuery, I issue the following query (in standard SQL dialect),

SELECT * FROM gae_log.appengine_googleapis_com_request_log_20180323

where protoPayload.line.logMessage  like '%14994838999176%'

LIMIT 1000

It failed with the error message:

 Query Failed

 Error: Cannot access field logMessage on a value with type
 ARRAY<STRUCT<time TIMESTAMP, severity STRING, logMessage STRING, ...>> at [3:25]

How can I correct my sql statement?


texts: Storing Analytic Data for Multi-Tenet SaaS with AWS Aurora

texts:
I have an app that a user can upload an excel sheet of analytics data to S3. I am wanting to trigger a Lambda function on upload to do some data processing and then write the analytics to a client's organization's database (I am using Aurora). Eventually we will be capturing live clickstream data but for now we are just using generated reports.

My question is is this best practice to just have all the analytics in a database with thousands and thousands of events in as many rows? I can see that your table supports a maximum of ... a little over 4.29 billion rows. but does that mean I can just pile them into a giant table until then? If I am potentially getting 50k rows per month should I just not think worry about it until I see a performance hit (if I even ever see one)? Or am I, as a newbie, just worrying over nothing?

Ideally I don't just want to make this thing work, I want to learn how to make something that lasts and scales. And reading the docs on Aurora it sounds like this shouldn't be an issue but I don't know if I am just not seeing something that will become an issue.

Thanks for any advice and feedback!


texts: Performance tuning for MySQL database (read only)

texts:
We are developing a reporting tool that is connected to MySQL database of size 500 GB size.  Reporting tool will be fetching the millions of rows from the database.  The tool is used only for reports.

It won't be updating, inserting or deleting any data.
There won't be any join we need to use, at a time tool will be interacting with a single table exclusively.
Tool will use only "order by", "where", "limit (for paging)" clause on the the database.
All the table will be having a column "rowno" as a primary key.  There is no FK relationship between the tables.

Edit:

MySQL version: '5.7.16-log'

Database Engine: MyISAM

Tool Used: MySQL Workbech

What's best way of performance tuning for this database (like using specific row_format etc.)?


texts:
We have these 3 tables:

USERS

userid
name
email

TEAMS

teamid
name
options (0,1,2 or 3)

TEAMUSER

id
teamid
userid
isadmin (0 or 1, if they are administrator of team)

Let's say we have user 1, 2 and 3 inside users table. Lets also say we have 3 teams, team 1, 2 and 3.

User 1 and 2 are part of the same team, the team id 3. So there are rows in teamuser table also.
User 1 is also part of team 2, and user 3 is part of team 3, but it's not relevant here.

THE QUESTION IS THIS:

How do I get an SQL that says:
SHOW ME THE TEAMS WHERE USER 1 AND 2 (OR X AND Y) ARE PART OF THE SAME TEAM? (and isadmin to see if they are admin and the team options also)

So user 1 and 2 can be part of more than 1 team together, they can be together in several teams, and that's what I'm interested in. Not the teams that they are individually in, but only the teams that they are both in.

So it would be awesome to get a list of rows like this if it's possible in one query:

teamid | userid | isadmin | options
-----------------------------------
  3    |   1    |    1    |    1
  3    |   2    |    0    |    1


texts:
I built an SSIS project on "test" server, and deployed the project across to "live" server. The project has property ProtectionLevel set to EncryptSensitiveWithPassword. Everything deployed successfully.

After fixing an issue within the project, without realising it, I attempted to deploy the project from "live" to "live". When I entered in the password for the project the following error came up:

Specified initialization vector (IV) does not match the block size for this algorithm (Microsoft.SqlServer.ManagedDTS)

At the time this completely perplexed me, as I have numerous projects and had never seen this before. Google provided very little help, other than C# references to a certain encryption algorithm. Once I realised that I was trying to deploy on "live" server, I went back to "test" and deployed successfully.

I'll be completely honest, all my other projects had been deployed "live" to "live". This is the first project I had created on "test" and deployed across (perhaps a poor attempt to ease my conscience).

My question is: Why does this error occur? but also, does this mean that I can now only deploy this project to the "live" server from "test". Is it not possible for me to now deploy "live" to "live"?

Thanks in advance for any help/insight in to this.


texts: Combining Always Encrypted AND Column level encryption in SQL Server 2016

texts:
We have requirements to encrypt sensitive column data using SQL Server 2016 and selected the Always Encrypted(AE) feature to encrypt those columns using deterministic approach..

Since, the AE deterministic encryption doesn't allow inequality, range, or LIKE queries on these encrypted columns, we have tried of doing encryption for these type of columns using the symmetric key (column level) encryption technique.

Is it a good practice to implement the AE feature on certain columns(which doesn't need any inequality, range, or LIKE queries ) and symmetric key type encryption on columns where inequality, range, or LIKE queries needs to be generated?

Is it a good practice to combine the AE encryption and column level encryption on a single table considering the performance, security and maintenance?

Experts advice please.


texts: Transaction Log Shipping doesn't use the latest transaction log

texts: I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2.  The problem is that when the logs are being restored on the secondary server, it always uses the second-most-recent log file rather than the most recent one available.  I suspect this is because the restore happens at the same time the backup occurs on the primary server.  I thought I could use the "delay" option to get around this, but it doesn't seem to do the trick.  What am I missing?


texts:
I am working on a replication problem that completely baffles me! This client has two MySQL replication clusters on HUGE bare metal HW. See Environment below.
The IO_Tread of the slave is WAY behind, several hours or more. Yes, the IO_tread, not the SQL_tread. Why is it so hard to download not all that large binlog records and write them to disk. I tried finding a resource bottleneck, but given the massive HW none can be found.
The only strange observation is that the slave has 8x the IO OPS than the master. But even this does not really overload the SSD disk. Packet traces show the SLAVE often setting the TCP window to zero. Why, there are plenty resources?
Anyone with ideas what could be causing this strange behaviour? Why do I have more IO on the slave? What can cause the IO_tread to slow down?
Environment:
Both machines:
Bare metal DELL,   MySQL 5.6.30,   12CPUs,   128GB mem,
datadir on SSD, Net I/F:  Emulex 10Gb,  ROW based binlog FMT
Symptoms:
MASTER:
CPU:  67%  1 processor lightly used,   MEM:  70% used, 30% free,
IO OPS: ~2500 tps,  30% util on SSD,   slave client tread: Send binlog to slave.
SLAVE:
CPU:  40%  1 processor lightly used,   MEM:  70% used, 30% free,   IO OPS:   ~16000 tps,  70% util on SSD,   Error counters on net I/F are 0 (zero),
TCP window is often set to 0 on IO_tread,
Slave IO_tread is VERY slow. Lags more than an hour!
Another slave on SAME master has no trouble at all! This slave has much lower HW spec!
Trouble downloading master binlog.
Why this insanely high IO rate?
Stopping the slave also stops the IO OPS. (As expected, the OPS are from MySQL)
Copying large amounts of data from master to slave over the network (using ncat) show performance as expected.
Other observations:
When reversing the roles the problem stays the same.
Another replication cluster with same HW has no trouble. IO OPS in this cluster on slave are slightly less than on master. This cluster uses STATEMENT based binlog


texts: SQL Trying to sum and group by distinct values

texts:
I have a table

+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x     | xl       | xl1        | y1       |       1 |
| x     | xl       | xl2        | y1       |       1 |
| x     | xl       | xl3        | y1       |       1 |
| x     | xl       | xl1        | y2       |       1 |
| x     | xl       | xl2        | y2       |       1 |
| x     | xl       | xl3        | y2       |       1 |
+-------+----------+------------+----------+---------+

I am trying to count the number of different materials, and sum the quantities along the LineName, so that I get something like this:

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       2 |
+----------+------+---------+

Instead, when I use a combination of count distinct, sum and group by, I get an incorrect result:

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       6 |
+----------+------+---------+

How should I do this correctly? I've tried with

SELECT LineName, COUNT(DISTINCT(Material) as Cmat, SUM(ProdQty) as ProdQty
FROM table
GROUP BY LineName

But it does not create the desired result. I've been looking on stack exchange for some similar topics, but they don't seem to match my question.


texts: Different MySQL index sizes after restore

texts:
I've been trying to create a backup for a very large MyISAM DB with mysqldump and I've noticed that some of the tables have variations in index sizes after restoring.

For example, one of my larger tables has an index length of 50316242944 and the restored dump of that table has an index length of 50227628032

I've run optimize table and have done a checksum and everything seems to be fine. I've even run ALTER TABLE generic_dummy_table_name ORDER BY datetime; which I found on a similar question on this site.

Could someone explain to me why there is such a large difference in index length between my tables and if there is an easy way to fix it?


texts: What is the Relationship between Instagram users and user followers

texts: I'm building a blog project, which each user can follow other users. I'm still figuring out the design and creating the diagrams first. I know that the relationship between users and followers is many to many. Is this the correct approach for the ER design of the relationship?


texts: Is there a way to make postgres "COUNT WHERE" query log(N)?

texts:
Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this:

find an item with the value of rating 50(or greater) in our b-tree index, pretty fast.
Iterate though all items which are on the right.

Am I right? If yes, why don't postgres store number of items in each node so we can skip the second step making the request incredibly fast? If no, why are they so slow?

And is there any way to have an index for counts or something to make request as fast as looking for a single item in binary tree?


texts:
Oracle Hints

  Hints can extend the capability of cost-based optimization. This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL.

Indexes

  Indexes can provide faster data access. Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table.

Those are the definitions of Hints and Indexes provided by Oracle, having said that, we can see that both have the objective of improving the performance of the query.
I'd like to know if there is any other difference between them.

Hints are more for performance issues, and Indexes are for response speed?


texts:
I have a table:

CREATE TABLE methods
(
    method_id serial PRIMARY KEY,
    method_name varchar(100)
);

I now want to create a table with the following columns:

CREATE TABLE experiments
(
    method integer[] REFERENCES methods(method_id),
    trials integer
);

I get an error:

  Key columns "method" and "method_id" are of incompatible types: integer[] and integer.

I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already:

PostgreSQL 9.3 development: Array ELEMENT Foreign Keys

My interest is that 'method' should be an array of integers referencing 'method_id' from table 'methods' but I can't figure it out. I thought that the link above might be a solution, but seems that was not implemented (?)

Some posts propose using junction/join tables:

Foreign key constraint on array member?

I am an absolute beginner and I could not figure it out yet. For me, it is a matter of understanding first how to tackle this issue of a foreign key on an array. Multiple methods can be considered as forming a class of methods. Each class can have a certain number of trials.


texts: Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110

texts:
I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year and to compatibility level 110 next mid year.

If I deploy SQL Server 2014 with compatibility level 100 will I able to see the new features of SQL Server 2014 or I need to upgrade the compatibility level also in order to use new features of SQL 2012 and SQL 2014?


texts: Sort order specified in primary key, yet sorting is executed on SELECT

texts:
I'm storing sensor data in a table SensorValues. The table and primary key is as follows:

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

Yet, when I select the sensor value valid for a specific time the execution plan tells me it is doing a sort. Why is that?

I would have thought that since I store the values sorted by the Date column, the sorting would not occure. Or is it because the index isn't solely sorted by the Date column, i.e. it can't assume that the result set is sorted?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Edit: Can I do this instead?

Since the table is sorted DeviceId, SensorId, Date and I do a SELECT specifying only one DeviceId and one SensorId, the output set should already be sorted by Date DESC. So I wonder if the following question would yield the same result in all cases?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

According to @Catcall below, the sort order is not the same as the storage order. I.e. we can't assume that the returned values are already in a sorted order.

Edit: I've tried this CROSS APPLY solution, no luck

@Martin Smith suggested I'd try to OUTER APPLY my result against the partitions. I found a blog post (Aligned non-clustered indexes on partitioned table) describing this similar problem and tried the somewhat similar solution to what Smith suggested. However, no luck here, the execution time is on par with my original solution.

WITH Boundaries(boundary_id)
AS
(
  SELECT boundary_id
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
  UNION ALL
  SELECT max(boundary_id) + 1
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
),
Top1(SensorValue)
AS
(
  SELECT TOP 1 d.SensorValue
  FROM Boundaries b
  CROSS APPLY
  (
    SELECT TOP 1 SensorValue
      FROM SensorValues
      WHERE  SensorId = 53
        AND DeviceId = 3819
        AND "Date" < 1339225010
        AND $Partition.PF(Date) = b.boundary_id
        ORDER BY Date DESC
  ) d
  ORDER BY d.Date DESC
)
SELECT SensorValue
FROM Top1


texts:
When I was studying data warehouse modeling, the first thing I came into contact with was the order fact table, and the product, user, date dimension tables.

The order table will have quantity, total_amount field for aggregate. (Sum the units sold, sum the total_sales_amount)

So I'm considering does a fact table always need to have a field for aggregate?

I'm asking this because in my data warehouse designing, I'm trying to create a schema like this.

Is this a real standard multidimensional data model that meets Kimball's methodology?


texts: How to delete all rows in a Greenplum row oriented table

texts: In Greenplum, what is the best practice to delete all rows from a table? DELETE FROM TABLE or TRUNCATE TABLE? I dont have any child tables or foreign key constraints.


texts: Why does nested loops join only support left joins?

texts:
In Craig Freedman's blog, Nested Loops Join, he explains why the nested loops join cannot support a right outer join:

  The problem is that we scan the inner table multiple times – once for
  each row of the outer join.  We may encounter the same inner rows
  multiple times during these multiple scans.
  At what point can we conclude that a particular inner row has not or will not join?

Can someone please explain this in a really simple and educational way?

Does it mean that the loop starts with the outer table (R1) and the scans the inner (R2)?

I understand that for a R1 value that doesn't join with R2, it should be replaced with a NULL so the result set becomes (NULL, R2). For me it seems impossible to return an R2 value when R1 does not join, for the reason that it cannot know which R2 value to return. But that's not the way it's explained. Or is it?

SQL Server does in fact optimize (and often replaces) RIGHT JOIN with LEFT JOIN, but the question is to explain why it's technically impossible for a NESTED LOOPS JOIN to use/support RIGHT JOIN logic.


texts:
I have the following error while trying to delete some old Miantenance plans on the MS-SQL server:

Please help.


texts: How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

texts:
I have data like this (STATUS, and NAME are the column headers):

STATUS      NAME
ACTIVE      A1
INACTIVE    A1
INACTIVE    B1
INACTIVE    B1

I want to include a CUSTOM field that returns a constant for a particular NAME  value, if that NAME value is adjacent to a specific STATUS value on any row in the data:

CUSTOM      STATUS      NAME
ACTIVE      INACTIVE    A1
ACTIVE      ACTIVE      A1
INACTIVE    INACTIVE    B1
INACTIVE    INACTIVE    B1

Because NAME = A1 had a STATUS of ACTIVE in row 2 above, I want CUSTOM to show ACTIVE for all rows tied to the NAME = A1. I tried this for the CUSTOM field:

case when NAME in (select NAME from SOURCE where STATUS='ACTIVE') then 'ACTIVE' else 'INACTIVE' end CUSTOM

But the above is still reflecting the same as STATUS on any given row.


texts: Migration script fail and revert on first error

texts:
I have a db migration script about 4000 thousand lines.
I would like to compose it when it reverts all changes on first error.
What is the proper way to do that?  Is wraping with try/catch is the only way?


texts: How to upload multiple csv files to sql server without constrains

texts:
I backed up an old SQL Server DB with .CSV files for each table. The old DB is now gone; the .CSV files are all I have.

Now I want to create new DB and upload all those csv file (my old db removed) .

I looking for a way to upload multiple csv files via the sqlServer gui and without any constrain .

I want to disable the constrain because if not the sql will tell that I a problem with the FK

I thought to running this command EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" but I think it not help me because I need to running it before I upload all the csv files , and before I upload them I not have any table in my new DB
thank you all.


texts: Permissions To Update Legacy DTS Packages

texts:
I am trying to grant access to allow users to view and update existing legacy DTS packages. The problem is that they cannot see the needed nodes under Management to begin working on them. I've tried granting every possible permission, including making them db_owners of the MSDB but nothing so far has worked. If I grant CONTROL at the server level the nodes become visible but this is certainly not a sustainable set of permissions.

Attached are 2 images. One of the nodes as I see them as one as they seem them. I've prowled around the web trying to identify the minimum rights needed but have so far not come up with the right formula.

Does anyone know what the minimum rights for this process are or am I stuck making them temporary super-users?

This is what I see

This is what they see

Thanks!


texts: SQL Server TempDB behaviour in large memory environment

texts:
Reading over this question reminded me of a question I had a while ago.

We have a SQL Server that has 512GB of RAM, the main database is 450 GB.  We see quite a lot of action in TempDB (ok, I think it's "quite a lot of action" - it may not be!).  I installed a demo version of RamDisk Plus Server, created a 50GB ramdrive, pointed TempDB at it, and saw no improvement in performance at all.

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache?

Is a ramdisk pointless in this scenario?

I know SQL Server 6.5 had support for TempDB-In-Ram, but I see that was discontinued a long time ago!


texts: PostgreSQL Streaming Replication - LOG: archive command failed with exit code 1

texts:
When I bring up the replica Node I am get the following error in the log

2020-04-27 00:12:26.278 EDT [3084] LOG:  archive command failed with exit code 1
2020-04-27 00:12:26.278 EDT [3084] DETAIL:  The failed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C

The achive command is set to below:

archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'

The configuration:

Primary Server postgresql.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'
max_wal_senders = 10
wal_keep_segments = 64

Standby server postgres.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/replica/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f'
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = 'host=192.168.56.103 port=5432 user=postgres password=****'
restore_command ='cp var/lib/postgresql/pg_log_archive/replica/%f %p'
recovery_target_timeline ='latest'


texts: How to manage a growing MySQL slow_log table?

texts: I see MySQL supports storing slow_log as a table (internally using mysql/slow_log.CSV file), and this appears convenient, but is it a feature I should use in production? I found online articles on the importance of temporarily disabling slow query logging while rotating the file log, but what's the best practice for managing slow_log.CSV? eg. copy and truncate table or rotate the slow_log.CSV just like db-slow.log?


texts: Why would a table use its primary key as a foreign key to itself

texts:
Looking through a database, I came across a table that used its primary key as a foreign key to itself.

I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it would use another column to reference the primary key.

Since the primary key is unique, in this situation wouldn't the row only be able to point back to itself? That seems to be a tautological link, since if I already have the row, then I already have the row.

Is there any reason this would be done?

I am certain the constraint is written that way (not just looking at the diagram) because the same table and column are used for both halves of the definition.


texts: Left Joining Tables with tables that have subquery aggregate

texts:
I'm tring to list every personID in the person table while left joining it to the POSEligibility table that may not have a personID entry or if it does may have multiple entries as a personID is created for each startdate entry. New startdates are added each year for those eligible. Which is why i'm using the MAX Aggergate on startdate. I only want the most recent date. If I remove the subquery I get all my personIDs from the person table with NULL entries for those personIDs that don't have a startdate as expected. But I also get all the startdates that each personID has, since of course the Aggergate is missing. Any ideas?

SELECT
  per.personID
 ,id.lastName
 ,id.firstName
 ,id.middleName
 ,CONVERT(VARCHAR(10), pos1.startDate, 101) AS StartDate
 ,cal.name
 ,en.grade
 ,pos1.eligibility
FROM dbo.Person per
LEFT OUTER JOIN dbo.POSEligibility pos1
  ON per.personID = pos1.personID
INNER JOIN dbo.[Identity] id
  ON per.personID = id.personID
INNER JOIN dbo.Enrollment en
  ON per.personID = en.personID
INNER JOIN dbo.Calendar cal
  ON en.calendarID = cal.calendarID
INNER JOIN (SELECT
    pos2.personID
   ,MAX(pos2.startDate) AS startdate
  FROM dbo.POSEligibility pos2
  GROUP BY pos2.personID) pos2
  ON pos2.personID = pos1.personID
    AND pos2.startdate = pos1.startDate
WHERE en.grade = '01'
AND cal.name LIKE '%BES'
AND en.active = '1'
AND en.endYear = '2017'
GROUP BY per.personID
        ,pos1.startDate
        ,id.lastName
        ,id.firstName
        ,id.middleName
        ,cal.name
        ,en.grade
        ,pos1.eligibility


texts:
one of my database is under recover pending issue since yesterday ... am trying to solve it but am unable to do so .. Please please help me

tried this

Exec Sp_resetstatus database;
Alter Database database Set Emergency
DBCC CheckDB(database)
Alter Database database Set Single_user with rollback immediate
dbcc checkDB (database,repair_allow_data_loss)
alter database database set multi_user

but am getting this error

  The suspect flag on the database "DATABASE" is already reset. Msg
  5061, Level 16, State 1, Line 2

  ALTER DATABASE failed because a lock could not be placed on database
  'DATABASE'. Try again later. Msg 5069, Level 16, State 1, Line 2 ALTER
  DATABASE statement failed. Msg 945, Level 14, State 2, Line 3 Database
  'DATABASE' cannot be opened due to inaccessible files or insufficient
  memory or disk space.  See the SQL Server errorlog for details.

Please help me guys


texts:
Leaving aside why one would want to do such a thing:

In MS SQL Server 2008, Is it possible to have a report SP grant permissions to a column used in that report to a table in a database other than the one it's running in?

I'm looking at something like the following:

USE mydb
CREATE PROCEDURE dbo.report
AS
BEGIN
GRANT SELECT ON otherdb.dbo.table (column) TO [myuser]

SELECT * from otherdb.dbo.table
END

Execute cannot be used on objects in a db other than the current one and USE statements cannot be included within a stored procedure. Is there another way?


texts:
I use PostgreSQL 9.4 and I have a table called certificates containing the following columns:

CREATE TABLE certificates (
    id serial PRIMARY KEY,
    common_name varchar(255) NOT NULL,
    state varchar(255),
    expires_on date,
);

CREATE INDEX index_certificates_on_common_name ON certificates (common_name);
CREATE INDEX index_certificates_on_expires_on ON certificates (expires_on);

I'd like to fetch all the certificates that have not been renewed yet, which means all the certificates that

don't have another certificate matching the common_name OR
have another certificate matching common_name, but the state is not issued or expires_on is past.

I created the following query. It works, but I wonder if it can be optimized to be more efficient.

SELECT
    C.*
FROM
    certificates as C
WHERE
    C.expires_on - 30 <= 'date-of-today' AND
    (
        SELECT COUNT(id)
        FROM
            certificates AS N
        WHERE
            certificates.common_name = N.common_name AND
            N.expires_on > certificates.expires_on
            AND N.state = 'issued'
    ) = 0;

Specifically, my concern is the nested query that I believe is run once for each record in C.

I tried by self-joining the table but the resulting query may return duplicates (e.g. if a certificate has 2 or more renewals) and I feel like using DISTINCT or GROUP BY may not be the best option.

SELECT C.id, N.id
FROM
    certificates AS C LEFT JOIN certificates AS N ON C.common_name = N.common_name
WHERE
    (C.expires_on - 30 <= 'date-of-today') AND
    (N.id IS NULL OR (N.state = 'issued' AND N.expires_on > C.expires_on));

Any suggestion?


texts: Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?

texts:
Which one is the best choice for primary key in SQL Server?There are some example code:

Uniqueidentifiers

e.g.

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('Karin')
GO

Identity columns

e.g.

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[Material code](or Business Code,which identity of a material. e.g. customer identifier)

e.g.

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL
 )

 INSERT new_employees
    (fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');

Please give me some advice for choosing the primary key from the three type identity column,or other choices.

Thanks!


texts:
Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18.
We are facing issues with our web application, Website is loading slowly if there are more users accessing it I mean even if 10-20 users open application simultaneously. We didn't face this issue when using the previous version of MySQL 5.7.18.
The main difference in MySQL settings is query cache, It's been deprecated in MySQL 8.0.17, We were using query cache in the previous version could this be a cause? The total size of the database is about 1TB. We are not experiencing any high CPU or Memory issues at the moment.
When reverted back to MySQL 5.7.18 we don't observe slowness in website loading.
Machine Details:
It's a AWS EC2 instance,
RAM : 8GB,
OS : Windows Server 2012,
SSD : 2TB EBS Volume
MySQL Settings from my.ini [Most of them were the same in the older version as well]
default-character-set=utf8mb4
skip_ssl
event_scheduler=OFF
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
port=3306
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
skip-log-bin
lower_case_table_names=1
max_connections=1500
table_open_cache=2000
tmp_table_size=16M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=32M
key_buffer_size=3584M
read_buffer_size=512K
wait_timeout = 480
read_rnd_buffer_size=1M
skip-innodb
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_buffer_pool_size=72M
innodb_log_file_size=48M
innodb_thread_concurrency=8
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=1060M
max_connect_errors=100
open_files_limit=4110
sort_buffer_size=1M
table_definition_cache=1400
binlog_row_event_max_size=8K
wait_timeout = 480
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose-local-infile = 1

Note: We don't use InnoDB tables in our application all tables are in MyISAM, I aware that all internal tables are in InnoDB since Mysql version 8. Also, we are not using replication.
In production, we expect about 200-500 users accessing portals simultaneously, Can anyone please suggest changes in MySQL settings for our application loading time to decrease.
SHOW GLOBAL VARIABLES : pastebin
SHOW GLOBAL STATUS : pastebin
SHOW ENGINE INNODB STATUS : pastbin
InnoDB Metrics : pastbin


texts: SQL Server Database Migration - Backup/Restore while Original DB still in Production

texts:
I have a very large DB in full recovery mode. My SQL Server is 2008 R2. The thing is that I need to migrate to a new server while having the original DB still in production.

Both backup and restore will take several hours, so...

What happens to the records added into the database while doing the backup?
If I'm right, this is not a problem and the .bak file will include all data until the time of the end of the backup. Do I mark the Copy-only?
Once the backup is done, and I start to restore... what's the best option to get the rest of the records which have been added to the original DB while restoring in the new server?

I've tried full+differential, but I'm getting a message

  the differential backup cannot be restored because the database has not been restored to correct earlier state

in my test environment.

It's sure that it's the last full backup because I'm doing it one minute later after just modifying one record.

Also, I restore the full with norecovery and the differential with recovery.

What do I do wrong?

The backup will be to disk because I need to do it as fast as possible.

Edited:

I'm still getting the message 'the differential backup cannot be restored because...'. What's the problem?

Do i have to do something special while doing the backup to disk? These are the LSNs:

+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| BackupType |  |     FirstLSN      |  |          LastLSN  |  | CheckpointLSN     |  | DatabaseBackupLSN |
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| FullBackup |  | 99000000025100209 |  | 99000000034000001 |  | 99000000033800001 |  | 98000002545900051 |
| DiffBackup |  | 99000000033800001 |  | 99000000036800001 |  | 99000000035000041 |  | 98000002545900051 |
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-


texts:
I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query:

select count(*) from table_a

For two tables I would like to see the difference of the count-star-queries, for example. If this difference is 0, then the tables have the same number of rows.

For more tables you can calculate an average of the count-star queries (or something more meaningful than the average, up to you) and compare the single count-star's with this value. The main point is that, if all the N tables have the same size, I have as result N columns (or rows) filled with 0's. If the tables have not the same number of rows, then in each of the N columns (or rows) I expect to see the difference of the number of rows of a given table and the average (or something more meaningful, cf. above).

I have this problem on an Oracle DB but I could need this query on PostgreSQL in the future, probably we will migrate.

The most efficient the query is, the coolest, of course. Please comment on this also if you want.


texts:
With this query in postgresql

WITH ExtractedPosition AS (
    SELECT P.rider_id,
           P.start_date,
           P.day_index,
           P.start_hour,
           P.end_hour,
           extract(DOW FROM (P.start_date)::DATE)::INT AS position
    FROM PartTimeWorkingHourIntervals P
             JOIN Riders R ON (R.id = P.rider_id) /*Filter early, no more exist chain or where select, cuz joined*/
    WHERE (R.isAvailableForWork = TRUE and
           R.isOngoing = FALSE)
),OrderedSchedule AS (
    SELECT P1.rider_id,
           P1.start_date,
           P1.day_index,
           P1.start_hour,
           P1.end_hour,
           rank() OVER (ORDER BY (7 + P1.day_index - position) % 7) AS pos
    FROM ExtractedPosition P1
    ), SD AS (
    SELECT P2.rider_id,
           P2.start_date,
           P2.day_index,
           P2.start_hour,
           P2.end_hour,
           P2.pos,
           rank() OVER (ORDER BY P2.pos) AS pos_t
    FROM OrderedSchedule P2
)
SELECT *
FROM SD;

This is the output from my IDE.

I’m trying to get the same pos to produce the same rank, yet it only increases by 1 for each common group. So the expected pos_t should look like

(1,1,2,2,3,3,4,4,5,5,6,6,7,7)
instead of

(1,1,3,3,5,5,7,7,9,9,11,11,13,13)

I can think of a tedious solution to add in more Common Table Expressions using group by and joins, but it might overcomplicate the solution
Does anyone know of any simple modification to my current query?


texts:
If I put a replicated database in single user mode will the Log Agent count as the single user?

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I'm trying to block users from connecting to my database while it is being updated in the morning. So I was thinking of putting it in Single User mode, run update then put it back in multi user mode. But the database is also a publication database.


texts: DBCC sqlperf(waitstats) contains signal wait time longer than last time waitstats was cleared

texts:
I'm trying to troubleshoot some performance problems on our SQL2000 production server.
I ran DBCC SQLPERF(waitstats) and noticed the stats hasn't been cleared in a long time.  So I cleared them and did some refreshing and noticed after only a few minutes, OLEDB reported a signal wait time in the order of hundreds of millions of milliseconds.  How is this possible?


texts: SQL Server 2008 mirrored Database with manual failover, need reporting database

texts:
Our current setup is a principal database used in production with a manual failover mirrored database for HA.

I need to setup a reporting database as well. What would be the best approach? Shall I setup a SQL job to drop and create the snapshot of the mirrored Database and refresh this every night?

Does anyone have any other suggestions?

Thanks


texts: Accessing the OLD/NEW table value in a trigger function in plain SQL

texts:
I am trying to build a trigger function to automatically update parent order values any time any of it's items changes.

This is what I have created:

CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
    UPDATE orders SET
       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
    WHERE id = OLD.order_id;
$$
LANGUAGE SQL;

CREATE TRIGGER update_totals AFTER INSERT
OR UPDATE OF fees
ON order_items INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

When I try to run this, I get this error:

missing FROM-clause for table "old"

I tried creating a reference to the original table in the trigger:

CREATE TRIGGER update_totals AFTER INSERT
OR UPDATE OF fees
ON order_items
REFERENCING OLD ROW AS old_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

But that just gives the error syntax error at or near "REFERENCING".

How do I get the order_id from the original order_items table?


texts: How to use pg_restore in database with different postgis installation (postgis schema location)?

texts:
I have database1 where postgis is installed in the public schema.
And database2 where postgis is located in a schema called postgis.

When I dump database1.schema1, schema1.table1 references its geom column as public.geometry.

Therefore pg_restore throws "schema public does not exists" error
because schema1.table1 has public.geometry column and the schema does not exists nor is postgis installed in that schema.

How can I make a clean pg_dump without having schema qualified geometry columns?

The database2 search_path points to the schema postgis.
I am using PostgresSQL 10.


texts: How does ON DELETE CASCADE relate to JSON?

texts:
For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON?

Can you give an example of a JSON-DB which supports ON DELETE CASCADE or allows some work around for that?


texts: Unique constraint on multiple columns and one of them already has index

texts:
There is much stuff around about 'unique' vs 'index' and I read lot of them, but still can't find explanation for my case.

In short, my database currently has a non-clustered index on two columns.
We have some inconsistent state as a result of data duplication and I want to add a unique constraint on all four columns of the table.
Since 'unique' will create an index under the hood does it still make sense to keep the old index around?

More specifically, it goes something like:

create table Stuff (
  id PK,
  col1 varchar not null,
  col2 varchar not null,
  col3 bigint not null,
  col4 bigint not null);
create index for_search on Stuff (col3, col4);
alter table Stuff
  add constraint data_integrity_above_all
  UNIQUE (col1, col2, col3, col4);

and if the answer is 'yes', then would it be a good move to specify UNIQUE (col3, col4, col1, col2); column order or is it too much implicit and not end well on maintenance?

More information about the nature of the data: col3 and col4 are another table ids (but not FK); col1, col2 -- user data.

DB: SQL Server 2012


texts:
I recently ran into several problems with a BULK INSERT task, so I thought I'd share my experiences. I'm not looking for help, just offering help for others that may experience something similar in the future.

I have a python script on a network drive, where the path on my "workstation" is something like z:\pythonscript\script.py and a subdirectory z:\pythonscript\data.  The network drive is a physical drive on the server (MS Server 2008 R2) with the database, but has a path on that computer of S:\blah\shared\pythonscript\script.py.

Lessons Learned

BULK INSERT takes a special permission set in SQL Server.
If using an autoincrement field (or not explicitly set field) not present in your data file, use a view and exclude that column from the View
The data file needs to be on-server for the DB server, and you have to use the local path from the server's perspective.
Set SQL Server service user(s) to be able to read from locations where data files are present.
If writing error files, they a) cannot already exist AND b) the target directory needs write permission for the SQL Server service users
SQL Server 2008R2 doesn't provide an interface for accessing system environment variables, including %TEMP%


texts: SQL Server - Check for opened Activity Monitor

texts:
I'm looking for a way to send an alert when a DBA tool is connected for "too long" on a SQL Server Production database.

For most of the tools I listed out, I can find the program name with the following query.

select
    login_time,
    host_name,
    program_name,
    login_name,
    nt_domain,
    nt_user_name,
    last_request_start_time
 From sys.dm_exec_sessions
 where host_name is not null

However, I can't find the Activity Monitor tool even though it's running. I use a SQL Server 2012 Management Studio and I run it against a SQL Server 2012 instance.

Do you have an idea about the reason I can't see it ?
Does the Activity Monitor do "connect/disconnect" to show its content ?

If it's the case, it's absolutely normal I can't find it there and I would have to change my way to check it with an extended event or a logon trigger...


texts: Is it possible to replace count distinct inside Correlated sub-queries?

texts:
I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...

Is it possible to minimize the number of sorting by doing it after the aggregations or at least replacing the count distinct by another function?

Query:

 SELECT

"R"."Lat" AS "Lat",
"R"."Long R" AS "Long R",
"R"."Dept" AS "Dept",
"R"."Reg" AS "Reg",

"B3"."M" AS "M",
"B3"."St" AS "St",
"B3"."Sp"AS "Sp",
"B3"."Reg" AS "Reg",
"B3"."year" AS "year",
"B3"."id_program" AS "id_program",
"B3"."program" AS "program",
"B3"."Lib" AS "Lib",
"B3"."Ef"/"R2"."NB Dept" AS "Agg_Ef",
"B3"."Eex"/"R2"."NB Dept" AS "Agg_Eex",
"B3"."Ein"/"R2"."NB Dept" AS "Agg_Ein",
"B3"."Sehr"/"R2"."NB Dept" AS "Agg_Sehr",
"B3"."Sin"/"R2"."NB Dept" AS "Agg_Sin",
"B3"."Sr"/"R2"."NB Dept" AS "Agg_Sr",
"B3"."Sc"/"R2"."NB Dept" AS "Agg_Sr",

"C2"."RE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RE",
"C2"."RD"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RD",
"C2"."RDP"/("R2"."NB Dept"*"B2"."NB St")AS "Agg_RDP",
"C2"."RC"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RC",
"C2"."RA"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RA",
"C2"."EE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_EE",
"C2"."BE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BE",
"C2"."BD"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BD",
"C2"."BDP"/("R2"."NB Dept"*"B2"."NB St")AS "Agg_BDP",
"C2"."BC"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BC",
"C2"."BA"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BA"

FROM "Database"."R_Table" "R"

INNER JOIN (
 SELECT
 "R"."Reg" AS "Reg",
 COUNT (DISTINCT "R"."Dpt") AS "NB Dept"
FROM "Database"."R_Table" "R"
 GROUP BY "R"."Reg") "R2" on "R2"."Reg" = "R"."Reg"

INNER JOIN (
 SELECT
 "B"."Reg" AS "Reg",
 COUNT (DISTINCT "B"."St") AS "NB St"
FROM "Database"."B_Table" "B"
 GROUP BY "B"."Reg") "B2" on "B2"."Reg" = "R"."Reg"

INNER JOIN (
 SELECT
"B"."Job" AS "Job",
"B"."St" AS "St",
"B"."Sp" AS "Sp",
"B"."Reg" AS "Reg",
"B"."year" AS "year",
"B"."id_program" AS "id_program",
"B"."program" AS "program",
"B"."Lib" AS "Lib",
SUM("B"."Ef") AS "Ef",
SUM("B"."Eex") AS "Eex",
SUM("B"."Ein") AS "Ein",
SUM("B"."Sehr") AS "Sehr",
SUM("B"."Sin") AS "Sin",
SUM("B"."Sr") AS "Sr",
SUM("B"."Sc") AS "Sc"
FROM "Database"."B_Table" "B"
GROUP BY "B"."id_program","B"."program","B"."year","B"."Reg","B"."Job",
         "B"."Sp","B"."Lib","B"."St") "B3" on "B3"."Reg" = "R"."Reg"

LEFT JOIN (
SELECT
"C"."Job" AS "Job",
"C"."Sp" AS "Sp",
"C"."Reg" AS "Reg",
"C"."year" AS "year",
"C"."id_program" AS "id_program",
"C"."program" AS "program",
"C"."Lib" AS "Lib",
SUM("C"."RE") AS "RE",
SUM("C"."RD") AS "RE",
SUM("C"."RDP") AS "RDP",
SUM("C"."RC") AS "RC",
SUM("C"."RA") AS "RA",
SUM("C"."EE") AS "EE",
SUM("C"."BE") AS "BE",
SUM("C"."BD") AS "BD",
SUM("C"."BDP") AS "BPE",
SUM("C"."BC") AS "BC",
SUM("C"."BA") AS "BA"
FROM "Database"."Content" "C"
GROUP BY "C"."id_program","C"."program","C"."year",
         "C"."Reg","C"."Job", "C"."Sp","C"."Lib") "C2" on
         concat("C2"."id_program","C2"."program","C2"."year","C2"."Reg","C2"."Job","C2"."Sp","C2"."Lib") =
         concat("B3"."id_program","B3"."program","B3"."year","B3"."Reg","B3"."Job","B3"."Sp","B3"."Lib")


texts:
I've tried researching cross-cluster migrations but other than the one white paper Microsoft put out I can't find any information.  Can this be used to upgrade from SQL 2012 to SQL 2016 and if so would there be any issues with replication?  If somebody has experience doing this can you provide any tips?

Going from SQL Server 2012 (Windows 2008 R2) to SQL Server 2016 (Windows Server 2012 R2)

Cross-Cluster Migration


texts: Looking for SQL Server 2000 DTS Components

texts:
I am trying to upgrade a very old SQL Server 200 to SQL Server 2008 R2. We have many DTS Packages that I need to migrate over and view using SSMS 2008. I know I need the sql server 2000 dts designer components but every Microsoft Link I try fails.

Does anyone happen to have this that they can link to for a download of it?

Thanks in advance!


texts: Primary Server is "Resolving state" & Quorum node is in "failed" state

texts:
I would like to say that in my environment I have a 2 node in WSFC with the nodes

SQLServer-01 (Primary Server)
SQLServer-02 (Secondary Server)

And for third vote in cluster also One (File share witness)in quorum & also listener has configured .

I was not a guy who had made the configuration of WSFC , quorum and installation of both SQLServer.

Last Thursday the system administrator guy was updated some widows path and had setup a schedule time to restart the windows server operating systems.

When we came to office as on Sunday. we saw the SQL Server Database engine was stopped in both server as well as quorum was went to in Failed state.

We are able to start the SQL Server engine services in both server. In primary server after connecting with SSMS then find out the Primary Server is in Resolving State & Secondary is UP state. And Also AlwaysOn Availability Group node is in Resolving state. And those databases are participating in AlwaysOn availability group in primary server , which is showing like (Not synchronizing /Recovery pending). As you can see the below attachment sheet of SSMS screen shot. which has taken in Primary Server.

I am also attaching the quorum state

and fail over cluster manager screen shot for (cluster node participation)

also attaching the screen shot of file share witness from fail over cluster manager window.

My Question is that in this situation ,

1)how i shall bring back online to my Quorum node.
2)After that how i shall bring back my Primary server from resolving to normal state.

I know that this is a one of the critical situation of troubleshooting in the life of DBA world.
Even i have already went through the MSDN BOL WSFC Disaster Recovery through Forced Quorum and Perform a Forced Manual Failover of an Availability Group
Even as per my DBA knowledge it is only solution through manual fail over to bring back online to quorum node.

Regarding in this regards, any suggestion will be appreciated.

-----After request of @John Eisbrener to upload fail over cluster manager log.

and


texts: MySQL is inserting "" as 0 in decimal fields. How to stop that?

texts:
The fields are nullable, so they do allow nulls.

Why would MySQL convert "" to 0 instead of NULL?

Is there a way to force it to be NULL instead?


texts: Oracle DB : Export only table data from from all tables of given schemas

texts:
Scenario :

I want to export all data(or rows) from all tables from given schemas let say s1, s2, s3 from oracle DB installed on unix server(U1) .
This data has to imported on another oracle DB installed on another unix server(U2) .
On U2 all the tables are created already, just need to import data from U1 db .
I tried exporting for few schema using sql developer . But its a kind of manual thing and have around 20 schemas .
And I don't want to export other objects like views, synonymn, procedure etc only data from each table .

Question:

So, how this can be achieved using expdp, impdp command line utility ? or is there any better method ?


texts:
In PostgreSQL one can create a User Defined Type with an implicit CHECK by doing the following:

CREATE DOMAIN OrdinalT AS INTEGER CHECK (value >= 0);

In Sybase ASE 15.7 I've figured that the following is almost equivalent (except for the CHECK):

sp_addtype OrdinalT, "INTEGER";

How do I make a CHECK part of the user-defined type definition in Sybase?


texts: Clustered index when optimizing for "fetch all children of a single parent"

texts:
Say I have a table BlogPost and a table BlogComment. Obviously a BlogPost can have many BlogComments. The most important query of the BlogComment table would look something like this

select * from BlogComment where BlogPostId = @postId

In this case, doesn't it make sense to define the clustered index like this?

create table BlogComment (
    Id bigint identity primary key nonclustered not null
    , BlogPostId bigint not null foreign key references BlogPost(Id)
    , CommentText varchar(max) not null
);
create clustered index x on BlogComment(BlogPostId, Id);

My reasoning is that when you want to fetch all the comments for a given post, you are likely to only have to pull a few pages from disk into memory. If you were to cluster on the identity column, then every relevant comment might be on its own page.

I think that this clustering strategy means that adding a new comment could cause a page split, but since comments are read far more often than written, this seems like the correct tradeoff.

Is this a good strategy in general whenever you have a parent-child relationship and you know you want to optimize for "fetch all children of a single parent" queries?


texts:
This is going to be very basic as I'm a complete newbie when it comes to database design.

I've been tasked with setting up a way to track employee data over a number of months and potentially years. I need to keep track of their Performance, Quality and Attendance on a daily basis but I'm unsure on how to setup my tables so that I don't run into troubles down the line.

At the moment I made a very basic database in Access 2010. I have 2 tables, one holding Employee details (FirstName, LastName, Email, Manager) and one holding Manager details (FirstName, LastName, Email). I've successfully created a relationship between the manager field in the Employee table and the Manager table but I'm at a loss on how to implement the tracking of the actual stats.

How should I go about creating table(s) from now? Should I make a table each for Performance, Quality and Attendance with the primary key as the date? Or should there be a table for each employee? How should I relate the data to each employee and each day so that it doesn't become complicated?

Final goal is to use vb.net to create an application for generating reports if it matters. I don't have a problem with that step but I want to make sure I have a solid foundation first.

Any help appreciated, thanks.


texts: Do I need to keep SQL Server Profiler interface open?

texts:
I'm running SQL Server Profiler on SQL Server 2008R2. I opened Profiler, gave it the required settings (including a stop time 24 hours from now), and then left. Profiler was running from my desktop and the database it's monitoring is located on some server. I was connected using VPN. My VPN lost connection at some point and I came back and saw that Profiler has stopped working completely - and that the table is a few hours behind. Is it not that the Profiler runs on server?? Is Profiler a desktop app?

On that note, do I need to keep it open? I thought that Profiler creates a job and it'll run until the stop time mentioned in the settings.


texts: Using an IF statement for all rows in a query

texts:
I'm working on a soccer database right now, and I am trying to solve one little problem.

So I have the table games, where I stored home team, away team, miscstats and boxscore:

game_id | hometeam_id | awayteam_id | miscstats_id | boxscore_id
1         2             4             1              1
2         3             7             2              2
3         4             3             3              3
4         8             5             4              4

In boxscores I store one row per player and match:

boxscore_id | player_id | team_id | goals | fouls
1             1           2         1       4
1             2           2         0       3
1             3           4         1       7
1             4           4         1       10
2             5           3         5       0

And in miscstats I store random stats, like number of yellow cards showed to the bench players:

miscstats_id | homebenchyellowcards | awaybenchyellowcards
1              3                      4
2              1                      5
3              2                      6
4              7                      10

Now, for some advanced statistics calculation, I would like to aggregate the number of homebenchyellowcards and awaybenchyellowcards for each team. So, for example, if my select function is correct, team number 3 has 7 bench yellow cards, 1 from game 2 (when team played at home) and 6 from game 3 (when team played away).

I am trying to do this:

SELECT (IF (e.hometeam_id=b.team_id, x.homebenchyellowcards, x.awaybenchyellowcards)) as
bench_yellow_cards FROM miscstats as x INNER JOIN games as e ON (e.miscstats_id=x.miscstats_id)
INNER JOIN boxscores as b ON (e.boxscore_id=b.boxscore_id) WHERE b.team_id = 2

But it gives me '1' as a result. I have been reading that this is because the 'if' function just analyses the first row when this condition is met (so, game_id 2) and then stops with the rest of the rows. I tried SUM statements too, but then it sums every row in homebenchyellowcards.

Anybody has an idea on how passing the if condition to all the rows in order to sum them properly?

Thanks in advance!


texts: Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

texts:
I have TBV function . Like this

ALTER FUNCTION [dbo].[fn_Functiont]
(
 @accessibleIds   ListAccesableIds READONLY
)
RETURNS TABLE
AS
RETURN
(
SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS ClientFirstName, c.LastName AS LastName, c.Number AS DocumentNumber, c.Id
FROM Document AS d
JOIN System AS ps ON d.SystemId = ps.Id
JOIN Client AS c ON c.Id = d.ClientId
LEFT JOIN Shop AS b ON b.Id = d.ShopId
WHERE d.OperationTypeId IN (2, 4, 5) AND c.Type = 1
)

I want to pass to this function List containing integers.I was bale to create table valued type but here is the question how could i pass List input parameter to the function?I know that i could pass lists via ADO.Net to the SP very easly but my problem regards to the Entity Framework and TBF functions

I am calling this function through Entity Framework 6 the approach which we used was database first.


texts: Is it good practice to create new tables for new elements in a database?

texts:
I am trying to create a good model for a database that I need for an application. The way that I have tought about realizing it involves creating a new table for each new element that is added, but I am unsure if that really is such a good idea. I found a related question that used a garden as an analogy, and I quite liked that, so I will try to exemplify using that below. Note that each gardener only works with one garden. The gardeners doesn't need to be able to be at several gardens.

I have a set of Gardereners as such:

PrimaryKey | Name | Garden
1          | Bob  | Garden1
2          | James| Garden2
3          | Ian  | Garden3

Each Garden will point to a new table containing the flowers. The Garden contains all the flowers in that garden.

FlowerNumber | Type
1            | Holly
2            | Lilach
3            | Larch

The question is then, would it be "right" to create a new table Garden for each new Gardener that is created? I tought it kind of wasteful to create new tables all the time, but I am not sure of any other ways to do it. I think object-oriented thinking might have perverted my way of thinking about databases.


texts: Fabric - Operation CREATE USER failed for 'fabric_server'@'%''


texts: Storing files in Mongo's GridFS vs File System

texts:
I read the question here:

https://softwareengineering.stackexchange.com/questions/150669/is-it-a-bad-practice-to-store-large-files-10-mb-in-a-database

and this paper:

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

where they talk about the pluses and minuses of storing blobs in a DB vs just storing pointers to files on a file system. I have been using mongoDB to store files using GridFS (pymongo driver). Doesn't MongoDB's chunking address some of the issues that RMDBs have in this area? Is it still better in most/some cases to store the file to a file system?


texts:
I am trying to restore a binary pg_dump backup under a different database name that was in the original dump. Despite giving --dbname command line argument, pg_restore tries to restore everything under the original database name - the behavior I especially want to avoid.

createdb mydb

pg_restore --dbname=mydb --exit-on-error -Fc database.bak

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 16396 SCHEMA topology u9o2f4j0
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "topology" already exists
    Command was: CREATE SCHEMA topology;

How can I make pg_restore to restore the database under a different name?

pg_dump --version
pg_dump (PostgreSQL) 9.5.5

pg_restore --version
pg_restore (PostgreSQL) 9.6.1


texts: Why did adding a self-referential foreign key freeze the table?

texts:
I'm running PostgreSQL 11.5.  I have a simple table of registers.
create table register(
  id serial primary key,
  name text not null
);

This table is small (~6000 rows), relatively heavy read (100s queries/s), almost no write.  I wanted to add a self-referential foreign key:
alter table register
  add column leader_id integer references register(id);

Given the size of the table I assumed this would be a rather mundane change.  It ran fine locally and on the staging server.  However, when I ran it against production, the table locked up.  Logs show ALTER and various SELECT statements taking ~10m to complete.
2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

What's going on here that is causing the table to lock up?  How can I safely work with a self-referential foreign key?


texts: Multiple instances of same foreign key in a single table

texts: I have a table that references the same foreign key multiple times (4 times) in the same table. Can someone explain why/how this makes sense?


texts: Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)?

texts:
I have created a dump of a DB using:

pg_dump db_name > db_dump.sql

and proceed to restore it with:

psql --single-transaction db_name < db_dump.sql

Prior to attempting to restore the DB, I create the DB as:

DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name;
GRANT ALL ON DATABASE db_name TO db_user;

Also, prior to creating the DB db_name I add the extension dblink to the DB template1 as the user postgres as per:

CREATE EXTENSION dblink;

The command \df+ executed in the DB db_name shows:

 List of functions
 Schema |  Name  | Result data type | Argument data types |  Type  | Security | Volatility |  Owner   |...
--------+--------+------------------+---------------------+--------+----------+------------+----------+---
 public | dblink | SETOF record     | text                | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, boolean       | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text          | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text, boolean | normal | invoker  | volatile   | postgres |...

When I go to execute psql --single-transaction db_name < db_dump.sql I see the following error message:

ERROR:  function dblink(text, text) does not exist

The output from \df+ tells me that the extension has been added though.

Why is this happening? What have I missed?


texts: What's the most efficient way to load a csv file into SQL Server 2016?

texts:
Alright, so this question seems simple up-front. Well, there's a bit more to it than just loading the file.

The entire story goes like this:
For some reason, our client sends us what can only be described as a relational database, flattened, condensed into a single csv file (but the delimiter is a tilde instead of a comma). It's kinda terrible, actually; the same data gets repeated ad infinitum throughout the entire file. So, in order to bring some order back into this data, I load it into an actual relational database. Because of the volume of data, loading it into a database makes it easier to inspect the data for issues. And it also makes it much easier to export.

There are 53 rows per record and somewhere in the ballpark of 250,000 records per transmission. I want to split that into 6 normalized tables. I'm not sure whether to validate the data in the C# program or the SQL Server 2016 LocalDb instance I'm using.

I'm not an experienced DBA; I'm a C# programmer who has dabbled in SQL a little. I feel comfortable enough with the syntax, but I want to make sure I'm doing this right.

Also, everything has to be completely automated. The file comes in, the C# program starts when it receives a file, and loads it into the database.

Let me explain the layout more. The file is 53 fields, and each line contains a detail line for a statement (what they are being charged for, how frequently they were charged for the item, total cost or credit for that item, etc). The problem is that EVERY line has the information for an entire mailing, the payer, resident, property, and remittance. With that being known, let me explain how I'm doing this now:

Open the file
For each line of the file, retrieve the keys for the tables that describe the mailing, payer, resident, property and remittance destinations.
Compare that data with the cached ones. If the cached data is invalid, query the DB to see if that entity has already been added. If not, create it. Cache that.
Add the new detail line and relate it to the mailing, which has a one to many relationship with details. (The mailing itself has a many to one relation to the payer, property, and remittance.)
Close the file when finished.

It's not the slowest thing in the world, but it's all being done completely in RAM. Since the program comes dangerously close to running out of RAM in its current state, we've decided to load the data into a database instead of keeping it all in local RAM. Hopefully that sheds some more light on this for potential answerers. Thank you!


texts:
Given:
$psql -U postgres
Password for user postgres:
psql (12.1, server 9.6.2)

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |

I then ran:
postgres=# select count(*) from foo;
 count
-------
     0
(1 row)

postgres=# insert into foo (a) values (1) returning (select count(*) from foo);
 count
-------
     0
(1 row)

INSERT 0 1

Why did it return 0 rather than 1, i.e. since I inserted one row?


texts: How to manually set a return value to a subquery with multiple results

texts:
I have a subquery which can return multiple results if a customer has duplicated sales codes.

I currently LIMIT 1 to just return the most recent but ... Is there away to return NULL or "DUPLICATE" if RESULT > 1?

I am using this field to overwrite a field which already has a default - therefore, I would just prefer to use the default or raise an alert for rectification.


texts:
I've recently inherited management of a large number of SSRS environments.  I'm attempting to generate a list of all the database server/instance/names for each of the SSRS databases (not the data sources).

These are stored in the rsreportserver.config files, but they are encrypted and I'm not sure how to extract them.

Is there an easy way to get this information from a large number of instances?

If there is a way to do it via command line, I can easily push the command to all servers and collect the output.


texts: Oracle 11: Change global index to local on huge production table

texts:
I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.

So I have idea to use INVISIBLE local INDEX (calculating for 12-16h) and then drop global  index and make new index visible and rename it to just dropped global index.

But..

SQL> create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online
                                              *
ERROR at line 1:
ORA-01408: such column list already indexed

Is there any other option to change global index to local using atomic/fast operations to change id on the fly?

Dropping global index and then creating new one is NOT acceptable.


texts: mssql on ubuntu will not start, cannot update 404

texts:
I did a routine Ubuntu system update for security patches today (2018-02-21)... mssql will no longer start or update, seeing failures in error log, systemctl and update output with 404 like:

Err:13 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 Packages
  404  Not Found

Problem: routine update breaks my vanilla Ubuntu 16 LTS with mssql-server instance. No other substantive info obvious to me looking in logs, etc...


texts:
I am currently working as junior Oracle DBA in a company.Now my team leads always tell me that I have to understand how SCN works and what I can do with the knowledge about SCN.So,are they really correct?
I know SCN is system change number and a little more something about SCN but where can I find the exhaustive documentation and practice material related to SCN?


texts: MySQL `where...in` statement - does it remove duplicates?

texts:
I'm curious about if MySQL's where ... in (...) statement removes duplicates as an optimization.
For example, if I use a subquery, is it important that I use DISTINCT to remove duplicates?
An example query:
SELECT * FROM foo WHERE bar_id IN (
    SELECT id FROM bar where user_id = 4
);

The subquery has the potential to return duplicate bar.id's.
Does MySQL query optimization make this query better?
SELECT * FROM foo WHERE bar_id IN (
    SELECT DISTINCT id FROM bar where user_id = 4
);

From my tests on a database with about 2.5mm rows in the bar table (with indexes where appropriate for such a query), the result time is roughly the same (on a large RDS instance with plenty of overhead for now).

I should note that I'm hoping for an explanation more than "the subquery returns less results when using DISTINCT, so of course it's better", as that ignores MySQL's query optimizer.
For example, perhaps DISTINCT uses more resources and is therefore slower overall, especially if a where...in(...) statement easily optimizes duplicates. These are the details I'm unsure about.


texts:
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think can allow more loose settings for insertion speed. At the moment, I'm using the following techniques.

disable log_bin
enable skip-innodb-doublewrite
set transaction_isolation to READ-COMMITTED or READ-UNCOMMITTED (actually READ-COMMITED)
set innodb_flush_log_at_trx_commit to 0 or 2 (actually 0)
set innodb_buffer_pool_size to 5GB (system has 6GB RAM)

Are there any more techniques for faster insertion on InnoDB?
And do I have to modify innodb_io_read_thread and innodb_io_write_thread ?
If you need more information, please tell me.


texts: Do queries called from within a transaction but found within another method act the same?

texts:
Imagine the following code:

class foo {

    public function bar() {

        // Start the transaction
        $transaction = $this->db->beginTransaction();

        try {

            // Get our member data
            $member_data = $this->getMemberData();

            // Update users details
            $sql = $this->db->createCommand("UPDATE users SET......");
            $sql->execute();

            // Now let's commit our data
            $transaction->commit();

        } catch (\Exception $e) {

            // Let's rollback the transaction
            $transaction->rollBack();

        }

    }

    public function getMemberData() {

        $sql = $this->db->createCommand("SELECT * FROM users WHERE id=123 LIMIT 1 FOR UPDATE");
        $member_data = $sql->queryOne();

        return $member_data;

    }

}

Now, does the query within the getMemberData method act as part of the transaction since it is being called within the transaction and it doesn't matter to the database how it is being called since we have a transaction open or am I required to move the query inside the transaction code block?


texts: DB2 Trigger Error : An unexpected token "OR INSERT" was found following SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

texts:
I am getting an error while executing the following queries. As per the DB2 documentation, we can have both update and insert event in the same trigger, but when I am trying to do so I am getting an error.

CREATE TABLE NULL_LOOKUP (
    ID INT NOT NULL,
    TABLE_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE PARENT (
    PARENT_ID INT NOT NULL,
    PARENT_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE CHILD_ONE (
    CHILD_ONE_ID INT NOT NULL,
    CHILD_NAME VARCHAR(100) NOT NULL,
    PARENT_ID INT
);

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER RESET_NULL_TRIGGER
BEFORE UPDATE OF PARENT_ID OR INSERT ON CHILD_ONE
REFERENCING NEW AS N
FOR EACH ROW WHEN (N.PARENT_ID IS NULL)
    BEGIN ATOMIC
    SET N.PARENT_ID = (SELECT ID FROM NULL_LOOKUP WHERE TABLE_NAME = 'PARENT');
    END
@
--#SET TERMINATOR ;


texts:
I am trying to delete an expired certificate from my database server.  The certificate was used to encrypt connections to sql server 2014 r2.  We no longer have a need to encrypt connections and so we want to delete the certificate instead of renewing, however when we delete it, sql server fails to start because it is unable to establish an ssl connection because it can't find the certificate.

I have done the following:

SSCM -> Protocols (right click) -> Selected Properties and set force encryption to NO.
On the certificate tab the drop down is blank

But SQL will fail to start with the error above.  No databases are encrypted, either.

I checked by running this query

SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

encrypted state returns a null for all.

I did "export" and password protect the service master key but I don't know if that has anything to do with it or not and not sure how to "undo" that if it does.


texts: How to password protect a field in access?

texts:
I'm looking to password protect a field in my database containing confidential customer information. Is there a reasonable way of doing this in a timely manner?

Thanks


texts: How can I change the default storage engine in phpmyadmin?

texts: I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM.  Is there a way to change the default storage engine?


texts:
Is data striping possible like mentioned here data striping in older SQL Server 2005
in the newest version of SQL Server like SQL Server 2012?

I know this is possible in 2008 and 2008R2.


texts: What is the difference between PHYSICAL_READ_BYTES_DELTA column and PHYSICAL_READ_BYTES_TOTAL  column in DBA_HIST_SQLSTAT table? Are they correlate?  Please provide also an example.


texts: I want to delete connections in tnsnames.ora. I need to figure out which ones are currently in use, so that I don't delete them mistakenly

texts:
I'm using Oracle 12c in Debian 8 (on a vm)
This question is for educational purposes only. I'm not using any production servers, so anything that you can tell me won't have consecuences

To start with, I found this link to delete registers in tnsnames.ora. I didn't test it yet because first, I needed to know if there were active connections in the database using the information of the tnsnames.ora.
I'm asking here because I found no way of doing this, but it may be possible to do it.
For you to know, I'm using this command for connecting to the database, so that I clearly specify a tnsname
rlwrap sqlplus sys as sysdba@tnstest

The contents of my tnsnames.ora are the following
tnstest =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

But, if I want to be 100% sure that this is working, I use this command
tnsping tnstest

Which results are...
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 23:51:38

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

Used parameter files:
/opt/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

After all of this I know that my configuration is working, I'm using a register in tnsnames.ora for the connection, but I don't know a way of checking...
"Hey, now that I'm connected, I want to know which register I used from the tnsnames file for connecting, if I used that file of course"
Is there any possibility that I could get this information?
Well, my following attempts of trying to figure this out, were checking the v$session view, if there was any field that I could use to get this information, but I couldn't find one.
After all of this, ultimately, I came here for asking. I don't know what else to try nor I have more ideas of what should I do next.

P.S: there's a chance that this problem is impossible to solve, because this is task I'm doing due to I'm a student of sysadmin, and our teacher intentionally added unsolvable questions.
Even so, I still think there might be a solution for this particular case


texts:
I have a Accounts table in MainDatabase with the following columns:

AccountID | Username | Password | AccountDatabase

And AccountDetails table for SpecificDatabase (SpecificDatabase will be based on the name the employer entered, so it will be dynamic):

AccountID | FirstName | LastName | AccountAccess

So every time a new employer registers, his inputs will be  saved in MainDatabase, and simultaneously creating a SpecificDatabase of his own. The problem I am facing is whether the employees for a specific employer will also be saved in the MainDatabase or in the SpecificDatabase.

If I saved the employees in the MainDatabase, I don't have to create a "token" to know what SpecificDatabase the employee belongs to, but MainDatabase.Accounts would end up having many rows. And if I saved the employees in its own SpecificDatabase, I would need to create a "token" in their login details, but maintenance for the SpecificDatabase will be easy (ex. Employer expires, deletion will be easy).

TL;DR

What would be the most efficient way to store employee accounts, in the main database or in its employer's database?


texts:
Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It would have to go through and delete data that was older than March 31, 2006.

I wanted to get an idea how much of the database was affected but that is really hard without a data diagram. I ended up writing a script that would search the entire database for dates older than March 31st, 2006.

I know this seems like brushing your teeth with a sledge hammer but is there any way to improve this query?

--MOAQ: Mother of all Queries
--Sam Nesbitt
--2016-03-01

--user definable
declare @colType        int
declare @searchData     varchar(10) --change this datatype as needed
declare @searchOpp      varchar(10) --enter seach opperator
declare @searchTable    varchar(100)

set @colType = 61
set @searchData = '''2006-03-31'''      --Enter search data here
set @searchOpp = '<='               --Enter your search opporator here                          --'<='
set @searchTable = '%'              --Give the query a hint to the column name its looking for  --'%start_dt%','%'

IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults
CREATE TABLE ##searchResults(
    tableName   varchar(100)
    ,columnName varchar(100)
    ,result     datetime        --make sure this data type matches @searchData data type
)

--system use

declare @tableName  varchar(100)
declare @colName    varchar(100)

declare @searchTables table(
    tableName   varchar(100)
    ,columnName varchar(100)
)

insert into @searchTables
select QUOTENAME(d.TableName), QUOTENAME(d.ColName)
from
    (SELECT c.name AS ColName, t.name AS TableName
    FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @searchTable
        and c.system_type_id = @colType --is datetime
    ) as d

--create a cursor for memory table
declare c1 cursor read_only
for select tableName, columnName
from @searchTables

print 'begin queries'

open c1
fetch next from c1
into @tableName, @colName

while @@FETCH_STATUS = 0
begin

    --magic happens here
    EXEC
    (
        'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + @searchData + ' ) as d'
    )

    --dump queries to log
    print 'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + CAST(@searchData AS varchar(256)) + ' ) as d'

    fetch next from c1
    into @tableName, @colName
end

close c1
deallocate c1

--select * from @searchTables
--select * from ##searchResults

--pull summary of results here
select tableName, columnName, count(result)
from ##searchResults
group by tableName, columnName
order by tableName, columnName

--comment this out if you want to use the results before they are dropped
IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults

https://github.com/Swazimodo/MOAQ

Update
I used QUOTENAME to wrap the column and table names. I also added dbo. to the table reference. One gotcha that I found was that QUOTENAME could not be inside the EXEC function. It had to be a sting literal.


texts: Any reason for declaring a view with cross-database SELECTs for the same database?

texts:
I currently have to take over an old project and found a single view that's kind of a special snowflake because it's declared this way:

USE [DbName]
GO

create view [abc].[vXyz] as
select id, name, etc
from [DbName].[abc].[xyz]

Is there any good reason to do this? Only thing I could maybe think of is that this view is/was maybe also accessed from another database and whoever wrote it thought they would need to declare it this way to work properly, but as far as I'm aware it's not.


texts:
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means that tutor can make CRUD operations with subject, questions and answers. But in this case appeares one big problem as saving version. For example, I am a tutor and I decide edit some question or answer ( or may be several questions and several answers). When I enter as student, I want to get a list of questions in which I made mistakes but tutor already edited this question and I get wrong information.

Main question: How I can save old versions questions and answers?

Each test has questions and every question has naswers. Tutors can edit/add/delete any of those components. For example, he or she can modify one of the questsion's text, add several questions to a test and edit one of the answers to the question that has just been modified. Any hints how I can handle all this so that I could retreive a version of the test at the exact time in the past?

Here is what I have come up with so far. The solution is extremely complex and I feel there is a better way of doing that.


texts: Which of these table designs is better for performance?

texts:
I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.

Here's what I know

Company has over 2.5 million accounts
Of these, they currently work an average of 200,000 per month (that changes with staffing levels, which are currently low)
They have 13 different cost types they'd like to track, and they have warned that they might add more in the future
They want the costs to be tracked daily
Costs are not split across the entire inventory. They are either split across the # of accounts that are worked per month (200,000), or users can enter account identifiers to apply a cost to a group of accounts, or they could simply specify which accounts to apply the cost to.

My first thought was a normalized database:

AccountId
Date
CostTypeId
Amount

My issue with this is, do the math. This table is going to get huge quickly. Assuming all 13 cost types get applied to all worked accounts for the current month, that's 200k * 13 * N days in month, which is somewhere around a 75-80 million records per month, or close to a billion records per year.

My second thought was to denormalize it a bit

AccountId
Date
TotalCost
CostType1
CostType2
CostType3
CostType4
CostType5
CostType6
CostType7
CostType8
CostType9
CostType10
CostType11
CostType12
CostType13

This method is more denormalized and can create up to 6 million records per month (200k * N days in month), or about 72million per year. It's a lot less than the first method, however if the company decides on a new Cost Type in the future, another database column will need to be added.

Of the two methods, which do you prefer? Why? Is there another alternative that you can think of which would handle this better?

I am most interested in reporting performance, both summerized and detailed reports. The job that will spread the costs out over accounts will be run nightly when no one is around. A secondary concern is database size. The existing database is already almost 300GB, and I believe the space on disk is around 500GB.

The database is SQL Server 2005