texts:
I'm struggling to wrap my head around this one. I'm sure someone will simplify it.
We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts.
We have a table that stores the parent and their children. Looks like this:
Parent    Component
1000      1300
1000      1301

When an order is placed, our users order a part number of 1000, then add in line items 1300, 1301 as options. Parent parts do not have a sales value assigned to them, but components do have a value. So what I'm trying to do is roll up everything to the parent level (sales and quantities) on a sales order.
Here's how a sales order would look:
SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      0
0001          1300         C              1      500
0001          1301         C              1      350
0001          1301                        1      400

As you can see above, we can have components (StockCode 1301) in here without parents (think of these as replacement parts). If the sales order line does not have a ParentChild value, they need to be excluded from the roll-up.
Here's how I'm wanting this data to look. A parent/child should only have a Qty of the parent.
SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      850
0001          1301                        1      400

I'm guessing that STUFF/FOR XML PATH would be a good starting point?
Here is the test data:
CREATE TABLE #Structure (
  Parent INT
 ,Component INT )

 INSERT INTO #Structure (Parent, Component)
 SELECT 1000, 1300
 UNION
 SELECT 1000, 1301

 CREATE TABLE #SalesOrder (
   SalesOrder VARCHAR(4)
  ,StockCode INT
  ,ParentChild CHAR(1)
  ,Qty INT
  ,Price INT )

 INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price)
 SELECT '0001', 1000, 'P', 1, 0
 UNION
 SELECT '0001', 1300, 'C', 1, 500
 UNION
 SELECT '0001', 1301, 'C', 1, 350
 UNION
 SELECT '0001', 1301, NULL, 1, 400

 SELECT * FROM #Structure

 SELECT * FROM #SalesOrder

 DROP TABLE #SalesOrder
 DROP TABLE #Structure


texts:
I am accidental DBA. One of my clients has been trying to upload a data file and he's been encountering  error message about database size limitation. He hasn't sent us the exact error information but has asked if the database is set to autosize. I have heard tables being set to auto resize, database autoshrink feature..but what exactly is database autosize? I checked the database properties and the data file is set an initial size and autogrowth is enabled and is set to unrestricted growth and the transaction log has a restricted growth feature enabled.
Could you help me here?


texts:
Our 11g RAC cluster has two nodes, each with 8 CPU cores and a memory target of 16GB. System RAM is 64GB (don't ask me why the memory target is just 16GB, I wasn't there when that choice was made). The disk is SSD. The CPU load as shown in the Enterprise Manager never exceeds 25% and is usually around 20%, and the RAM occupancy stays around 20%. The IO operations per second (that worry my the most) vary between 2000 and 8000, with an average around 5500. The load is mostly OLTP style. System load is around 1, maybe 1.5.

My question is, can we consider this cluster to be "heavily" loaded? Again my concern is with IO, obviously CPU and RAM are not stressed.

I'm asking because the application occasionally shows signs of sluggishness. The developers refuse to put logging statements that would show how much time a query takes to run ("too many queries"), so I'm left looking for less direct ways to figure this out.


texts: Updating unindexed values with postgresql question

texts:
The inexact wording of an interview question has been as follows.

  A PostgreSQL database contains a table called “apps”, having
  millions of rows, numerous indexes, and is heavily used. There are at
  least two columns in this table:

"id": an integer serving as the primary key
"type": a string containing unindexed values such as "SlackApp", "GithubApp", etc.

  You need to update this table so all occurrences of a particular
  string in the “type” column are replaced with another string. This
  process should take place in a migration that will be executed
  from one of the fleet hosts while the other hosts in the fleet are
  still running, therefore this change should happen without requiring
  downtime.

  How would you go about solving this problem? What is the estimated
  time spent in the migration process? What are the tradeoffs of your
  solution compared to other solutions?

My answer to the above question would be to issue a transactional SQL statement to keep atomicity of operations and that would be linearly dependent on number of rows. Is there much more detail that I could add to the answer?


texts: What is the permission necessary to drop a service in service-broker?

texts:
When I want to drop a service which is already created, this error happens:

Msg 15151, Level 16, State 1, Line 316
Cannot drop the service '//Audit/Contract', because it does not exist or you do not have permission.

My sql query to perform drop operation is:

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
    DROP SERVICE [//Audit/Contract]

So, the main question is: What is the permissions necessary to drop a service in service-broker?


texts: MySQL CHAR_LENGTH() alternative in IBM DB2

texts:
I have a value in a cell that has a Unicode character:

"Nit€sh"

I want to get the length in SB2.
The problem is, If I use the length function it's considering '€' as 2 and total is 7. I am trying to achieve a total of 6 in this case.

EXAMPLE
In MySQL we had char_length() to address this.

Is there anything similar in DB2?


texts:
I have a mysql table

table name 'results'

Reg.No  SubjectCode  Attempt  Grade  Marks
112108  CMIS 1113    1        D      17
112110  CMIS 1114    1        A      85
112119  CMIS 1114    1        D      18
112108  CMIS 1113    2        D+     25
112110  CMIS 1113    1        D+     25
112107  CMIS 1113    1        B      70
112108  CMIS 1113    3        C      40
112110  CMIS 1113    2        C      40
112119  CMIS 1114    2        C      42
112120  CMIS 1114    1        D      17

This is about students' results. Students need at least a "C"
    (marks>40) to pass the subject. Until pass, students can have
    several attempts. But from the second attempt maximum grade they
    can get is "C" So students who was unable to pass the subject in
    first attempt,used to have another try until they pass the subjects
    So for final results sheet the highest result will be take into account in each subjects.(highest results is given by the highest marks)
So for example highest marks of 112108 for CMIS 1113 is 40.
So likewise I want to get the students results for ecch subjects(But always maximum marks will be take into account)
Actually my expected results table should be like this

Reg.No  SubjectCode  Attempt  Grade  Marks
112110  CMIS 1114    1        A      85
112107  CMIS 1113    1        B      70
112108  CMIS 1113    3        C      40
112110  CMIS 1113    2        C      40
112119  CMIS 1114    2        C      42
112120  CMIS 1114    1        D      17

So how can I retrieve these data using sql query?


texts: Explicit usernames in references to objects

texts:
Much of our code -- both in external programs and in stored procedures -- uses explicit usernames, when referring to various DB-objects:
SELECT * FROM prod.object

or
prod.StoredProcedure(.....)

This causes obvious problem, when a copy of the database is loaded into a schema with a different name -- the same object becomes qa.object now...
I proposed, we drop such explicit references completely, referring simply to the object and StoredProcedure instead.
However, this would cause problems when another account (such as prod_ro) logs in and tries to execute prod's stored procedures or browse prod's tables.
How do people handle such situations?


texts: How to trouble shoot intermittently slow SQL Server

texts:
I have a Sql Server that runs stored procedures slowly sometimes. I’d assume it was a problem with the contents of the stored procedures, but sometimes it’s slow to update stored procedures.

Where does one start?


texts:
I am configuring a test mongo setup with 2 shards, and I'm trying to add zone ranges to each shard:

sh.updateZoneKeyRange('mydb.test', {id:MinKey,ts:MinKey}, {id:MaxKey,ts:1548787704000}, 'cold')

sh.updateZoneKeyRange('mydb.test', {id:MinKey,ts:1548787704000}, {id:MaxKey,ts:MaxKey}, 'hot')

First command runs fine, but second tells me:

Zone range: { id: MinKey, ts: 1548787704000.0 } -->> { id: MaxKey, ts: MaxKey }  on  hot is overlapping with existing: { id: MinKey, ts: MinKey } -->> { id: MaxKey, ts: 1548787704000.0 }  on  cold

I thought the maximum bounds were exclusive and minimum bounds inclusive?


texts: Estimated vs Actual rows differences (actual much smaller than estimated) - sort

texts: I'm running a query that is processing some nodes out of an XML document.  My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some data that I extract from xml columns via XPath.  The Sort operation has an estimated number of rows to be around 19 million, whereas the actual row count is about 800.  The query itself runs reasonably well (1 - 2 seconds), but the discrepancy has me wondering about the query performance and why this difference is so large?


texts:
I was asked "when do we need to bring covering indexes to play, and why?" by a DBA.

My explanation was:

  It allows engine to pull all of the relevant data directly from the values in the indices themselves.

My explanation was not satisfactory to the DBA that asked me this question. What other details are relevant?

I know difference between clustered vs. non-clustered indexes


texts: SQL Server placement of master database files vs resource database files

texts: In a post on SqlServerCentral.com, it is mentioned by several people that the files for the Resource database (mssqlsystemresource.mdf and ldf) should be placed in the same folder as the master database file. This was for SQL Server 2005. Is this still a recommendation when it comes to SQL Server 2017? I tried looking in BOL, but could not find any mention of this there.


texts: How to convert an Update a query from MS Access to Oracle SQL Software

texts:
I have an update query below written in Microsoft Access and I would like to
convert that to Oracle SQL Developer 17.2.0.188.
There is also an image in the attachment showing the query expression.

The query said: "update the Products.UnitsInStock to UnitsInStock-Quantity
if the ShippedDate is not null and This_Order_Was_Shipped = "YES"

UPDATE Products INNER JOIN (
           Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
       ) ON Products.ProductID = [Order Details].ProductID
SET    Products.UnitsInStock = [UnitsInStock] -[Quantity]
WHERE  (
           ((Orders.ShippedDate) IS NOT NULL)
           AND ((Orders.This_Order_Was_Shipped) = "YES")
       );


texts: Mysql user accesable with out password from the host machine but not from the remote machine

texts:
I have setup mysql and created a user 'test' and have given a password for it.

Strange thing I observed is, for test user I am able to access with out password from the machine I have installed mysql but when I try to access remotely then I need to give a password.

Why is this behaviour? any idea?


texts: Postgres: Unsafe use of new value of enum type

texts:
I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the constraint I put on the field completed_at.
  `DO $$ BEGIN
    CREATE TYPE activity_state AS ENUM ('TODO', 'SCHEDULED', 'DONE');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END $$;

  CREATE TABLE IF NOT EXISTS ${activitiesTable} (
    ...
    state activity_state NOT NULL,
    completed_at timestamptz CHECK (
         (completed_at IS NULL AND state != 'DONE')
      OR (completed_at IS NOT NULL AND state = 'DONE')
    ),
  );

  -- Alter state enum
  ALTER TYPE activity_state ADD VALUE IF NOT EXISTS 'DONE_BY_PASSED';

  -- Alter constrain on
  ALTER TABLE ${activitiesTable} DROP CONSTRAINT maintenance_activities_check;
  ALTER TABLE ${activitiesTable} ADD CONSTRAINT maintenance_activities_check CHECK (
         (completed_at IS NULL AND (state != 'DONE' OR state != 'DONE_BY_PASSED'))
      OR (completed_at IS NOT NULL AND (state = 'DONE' OR state = 'DONE_BY_PASSED'))
    );

The enum seems to be added correctly, but once I use it my new check constraint I get the error:
error: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
Not sure how I can alter my constrain once the enum is changed? I am using postgres 12.


texts: Release unused space in SQL Server database

texts:
I have a SQL Server database with roughly 1.5 TB test data (size of core tables are calculated using sp_spaceused 'tablename'). At the time of data creation process, I have allocated 350 GB to each data file, and my database contains 9 data files and 3 log files.
I want to shrink the database and release unused space before taking a back up. In the database shrink options it shows Available free space as 51%.
Using Management Studio tasks-> shrink options:

I shrunk individual data files and log files.
then, shrunk the database.

But still shows as 51% free space is available. Can anyone tell me what I'm doing wrong there or am I missing any steps on shrinking process?
What I'm looking for is, since there is a lot of unused space in the database, I want to release it, so the database size gets reduced.


texts:
I am currently in situation that I dont know what to do, and have no idea how to solve problem.
The problem is that I have one SELECT statment like
SELECT PaymentType FROM ata WHERE ATAID = $ataId

After that I have IF/ELSE statment like
if ($paymentType != 2 && $paymentType != 3) {
            $sql = "SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE
                        aa.ProjectId = $projectId
                    AND
                        aa.AtaId = $ataId
                    AND
                        wr.status = 5";
        } else {
            $sql = "SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        ata a
                    ON
                        a.ATAID = aa.ataId
                    INNER JOIN
                        invoices i
                    ON
                        i.Id = a.financeId
                    WHERE
                        aa.ProjectId = $projectId
                    AND
                        aa.AtaId = $ataId
                    AND
                        i.Status = 6";
        }

As you can see the code is write in PHP but I need to  write this in Oracle SQL function.
So far what I try
           CASE WHEN PaymentType !=  2 and paymentType != 3 THEN
             SELECT PaymentType FROM ata WHERE ATAID = 1889
               OR
                    SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE
                        aa.ProjectId = 141
                    AND
                        aa.AtaId = 1
                    AND
                        wr.status = 5
            ELSE
                SELECT
                    SUM(aa.Total) AS total
                FROM
                    ata_articles aa
                INNER JOIN
                    ata a
                ON
                    a.ATAID = aa.ataId
                INNER JOIN
                    invoices i
                ON
                    i.Id = a.financeId
                WHERE
                    aa.ProjectId = 1
                AND
                    aa.AtaId = 1
                AND
                    i.Status = 6
            END CASE;

I have try a multiple solution but none of these dosn't work. Can someone guide me and tell me what is the best solution to solve this issue ?


texts: Proper remote access control management in PostgreSQL

texts:
I want to create environment that allows the limited number of machines connect to my database server.
For debugging needs all IP address have the remote access to PostgreSQL server

listen_addresses = '*'
host all all 0.0.0.0/0 md5

I want to limit access. What is the canonical way to do that?
I though of using proxy server to connect from local machines to PostgreSQL server, but I'll appreciate other ideas.


texts: Data Deplucation and relationship integrity

texts:
I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion.

This is the scenario: We have Salesforce and a multiple local provisioning systems (short history - multiple companies were merged). As part of an effort to standardize, we setup an ETL that pulled the data from all of the local systems, transformed it and uploaded it to Salesforce. Currently we use Salesforce mainly as a Ticketing system and there is no write-back to the Local Provisioning system. The data is refreshed in Salesforce every 2 hours.

The function that the data in Salesforce provides is that as customer calls or emails come in, they are matched against Contact information (pulled from the local provisioning systems).

Our Marketing team is currently wanting to implement Salesforce Marketing cloud, which pulls data from the Contacts table.

Now you have the background - here is the issue:

Since we have a lot of 'duplicate' data - the 2 most common scenarios are:

Person A has an account with Brand X and Brand Y. The ETL sees 2 unique contact objects, assigned to 2 different brands, 2 different accounts with 2 different products and imports them as 2 separate contacts, even though the relevant details (Name, Email, Phone number) is the same.

Person A has multiple accounts within a Brand. Sometimes this can be due to having multiple businesses or they are a reseller but aren't using a Reseller login or whatever - in which case the ETL sees multiple unique contacts (as within the Provisioning system, the contacts have a unique and different primary Key) - and as above, the ETL sees this is multiple unique contacts, belonging to different accounts, having different product sets.

This causes an issue with the Match rates for case generation and it also potentially causes an issue with the Marketing tool that we want to use - in particular an Individual with Multiple accounts might receive multiple copies of Marketing material.

The Marketing team want to just De-Dupe the Contact data, however whilst I have a few theories as to how to do that, that would either:

1: Lose relationships between the local systems and Salesforce - There are some ancillary issues that may be generated here since Products are bound to contact information and when a case is created, the associated products are presented to the Agent.

2: Create some form of relationship mapping to try and preserve things - this may get extremely messy and may end up crossing Brand/Account boundries, which IMO could create Legal/Privacy issues if anything went wrong. Not to mention it creates something that is not pulled from the Source provisioning system.

Currently, I've designed this process with the mantra that what is in the source system must be replicated to Salesforce, but with what I'm being asked to do, will break that.

So - to sum up and ask a question: Are there any 'rules' about De-duplication and Data Integrity I should be aware of or reference? How would you architect such a requirement? Any Pitfalls or 'gotchas' or similar I should be aware of? Should I even pursue this course of action?

Edit:

I'm looking more for some guiding principles as opposed to a specific answer - I appreciate that it's a bit broad, however Breadth is what I need right now, I should be able to work on the detail, but right now I need some help on the big picture.

First answer of find out what the business needs is a good one - so I'm going to sit down with the Marketing team and other stakeholders and see what we get.

More information and general principles/advice like that is appreciated.


texts: I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper case automatically. How do I replicate this behavior in SSMS?


texts: How to track specific item as part of a kit

texts:
Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked independently of the box. What I am trying to accomplish is to be able to track the serial number from the part and link it up to which box it goes in such that the following rules are followed:

Each box cannot contain more than one primary part
Not every box has a primary part (some were broken and never replaced)
If a primary part needs to be moved from one box to another the original box it was taken from shows up as having no primary part.

I thought this would be fairly easy to accomplish with MS Access since it is already deployed across the network, but I am having a hard time preventing multiple boxes from being assigned the same primary part. If I just put the part SN as a field in the same table as the box then I have to remember to change 2 things any time one of the parts moves.


texts: How to query based on related table without returning records from related table

texts:
Table_A

^ID^Name^
|1 |Joe |
|2 |Jane|

Table_B

^ID^Foreign_ID^Company  ^Specialty^
|1 |1         |Microsoft|SQL      |
|2 |1         |Microsoft|PHP      |
|3 |2         |Oracle   |SQL      |
|4 |2         |Oracle   |HTML5    |

The query:

SELECT Name, Company
FROM   Table_A
         LEFT JOIN Table_B
         ON Table_A.ID = Table_B.Foreign_ID
WHERE  Table_B = 'Microsoft'

Say I'm formatting a report, and I'm only interested in employee's that work at Microsoft and I only want 1 record per employee, how do I prevent the Speciality column in Table_B from creating multiple rows?


texts: I have a scenario where there is a transaction being executed to update a few tables.  On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on one of the tables used in the transaction.  The question is:  Will all the data from all of the tables in the transaction be available when the trigger executes?


texts: Trying to limit stored procedure results based on date

texts:
I have the following stored procedure:

ALTER PROCEDURE [dbo].[TW_spRPTAllReceiverInventory]
@SiteId varchar(50),
@ReceiverId int,
@UserName varchar(50)

AS

BEGIN

DECLARE @SQL varchar(8000),
        @Index int,
        @Value varchar(500),
        @Source varchar(100),
        @tmp DATETIME

SELECT @Index = CHARINDEX('Initial Catalog=', ConnectionString) + 16,
       @Value = ConnectionString
FROM   CustomerSetting
WHERE  SiteId = @SiteId

SET @Source = '['
               + SUBSTRING(@Value , @Index, CHARINDEX(';', @Value, @Index) - @Index)
               + ']'

SET @SQL = 'SELECT R.SiteId, L.* FROM '
           + @Source
           + '.[dbo].[vwReceiver] R JOIN '
           + @Source
           + '.[dbo].[vwInventory] L ON R.ReceiverId = L.ReceiverId
        WHERE R.ReceiverId = '
           + CAST(@ReceiverId as varchar)
           + ' AND L.ModifiedUserName = '
           + Cast(@UserName as varchar)

EXEC(@SQL)

END

What I need to do is filter the results based on one of the columns (the column is ModifiedDate, which is a DateTime column) to only show records created today. How do I accomplish this?


texts:
In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql.
I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, overall.
So how to do such a dump ?


texts: Mysql replication, allow inserts while database is disconnected

texts:
I've tried getting information on this elsewhere but can't really find what I'm looking for. I'm making a system to handle sales, etc for a company that has different branches. They want to have the system both on the cloud as well as locally so in case internet goes down they can continue making sales with the local database instead of the database in the cloud. So all databases will have to be masters.

I'm making sure that all race conditions are taken care of in the database (updating stock table, etc) and in the case that one database is disconnected and must continue making sales I am using two columns as index in my sales table and journal_entries table (one column is a character representing the branch and the other column is just an incrementing number), so as far as I know this should allow the databases to sync when the disconnected database comes back online.

Is there anything I'm forgetting though? Any particular scenario where my database design would run into problems by allowing them to run without being able to replicate for a while?


texts:
I wonder how MS Access manage to show long list of records when we want to browse theme in Datasheet view.

1 - Does it load limited amount of records on start-up and then load the remainder upon user interaction (scrolling for example)?

2 - Does it care about such things automatically or natively?

Thanks.


texts: Cross join flatten JSON - flatten only one the most recent row

texts:
As reported in this question, i want to flatten a JSON starting from a nested JSON object that contains arrays like this one:
port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

It works, but it flattens all rows that were returned from the query: i need only to flatten the objects that are contained into the first, most recent row (ordered by insert time desc), but when in my clause i'll insert LIMIT 1 it will return only the first flattened json row, why? maybe this is a beginner question, but i'm not able to figure out!
This is the my actual query:
SELECT  mt.measurement_id,
        p.*
from mytable mt
  cross join jsonb_to_recordset(mt.data->'ports') as p(port_name text, port_description text, port_status_int int, port_status text, port_speed_int int, ports_speed text)
where device_id = 20;


texts: problem using count in trigger phpmyadmin

texts: how to record the number of column lastbuy_idrbch and lastsell_idrbch from table idrbch in my new table (test) using trigger and count function ?


texts: Understanding SQL Server encryption (by password)?

texts:
After asking this I still have a question.

A company need to store sensitive data. Please let's not talk about certificate right now.

A DBA does the following :

  CREATE SYMMETRIC KEY SecureSymmetricKey1
  WITH ALGORITHM = DESX
  ENCRYPTION BY PASSWORD = N'blabla';

A programmer wants to encrypt data and does the following :

  -- open key
  OPEN SYMMETRIC KEY SecureSymmetricKey1
  DECRYPTION BY PASSWORD = N'blabla'

  -- actual encrpyt
  DECLARE @encrypted_str VARBINARY(MAX)
  SET @encrypted_str =  EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');

Another programmer wants to READ the data so he does :

  DECLARE @decrypted_str VARBINARY(MAX)
  SET @decrypted_str = DecryptByKey(...encrypted_str...)

All fine.

Questions :

When a programmer opens a symmetric key he must the know the password. I don't think that a programmer should know the password. How can this be solved ?
If a GOD hacker got the  entire .BAK file and he restore the backup onto his own machine - he can view the stored procedure source which one of the programmers wrote, and see the password. and then the HACKER can do :

OPEN SYMMETRIC KEY SecureSymmetricKey1
DECRYPTION BY PASSWORD = N'blabla'

What am I missing ?

Thanks for helping.


texts: How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer

texts:
I am using an Azure SQL Database. I deleted some users from a Azure SQL database after I saw them mentioned in a Vulnerability Assessment report. Here's how I deleted them:

Log in to SSMS
Expand the Object Explorer tree
Expand the 'Security' folder
Expand the 'Logins' folder
Highlight the user
Right-click and select 'Delete'

I went back to the Vulnerability Assessment blade and ran a new 'Scan' but the users I deleted still show up in the list. The list included SQL code to show that my users still exist. I ran that code back in SSMS in the master database and confirmed my users still exist. Here's the relevant code:
SELECT * FROM sys.database_principals
When I run the following code, I get an error saying the user does not exist ('or you do not have permission' - but I am a server admin so I ruled that out):
DROP LOGIN <username>
Note: I already deleted the users from all databases on the server.
How do I get rid of these logins?


texts: Use twice DISTINCT ON ina multiple join situation

texts:
I'm using PostgreSQL 10. I have an entity Product.

The product has images, so I have a Foreign key from Product to Image.

A Product have 1 ON 1 relationship, with a Video entity

A product can have multiple categories and a category can have multiple products, so I have M2M relationship from Product to Category

--- Product ---
id | name |

--- Image ---
id | image | product_id

--- Video ---
id | channel | url |product_id

--- CategoryProduct ---
id | category_id |product_id

--- Category ---
id | name

I need to get a list of Product(s) with their first/one Image and Video and also the first Category

I use DISTINCT ON to get just one image, in the query bellow:

SELECT DISTINCT ON(p.id)  p.id,  p.name, i.image, coalesce(v.channel, v.url) as video
                  FROM product AS p
                  INNER JOIN  image as i on p.id = i.product_id
                  LEFT JOIN video as v on p.id = v.product_id"

My issue is how do I get also the first Category for the Product ?


texts: Database structure for multiple users sharing one dataset, but also many users with their own dataset

texts:
I'm building a web app with php/mysql, and I'm trying to figure out how to set up multiple users sharing data.  Here's the best analogy I can come up with on what I'm trying to do:

You have a calendar app, and a user wants to allow his/her spouse to access the events on the calendar and update them as they see fit.  There would be no difference in roles and both users would log in with their own credentials.  You also have other users who may or may not have a significant other to share a calendar with.

Right now I have a users table with a user_id_PK field that contains the username & hashed password of the user.  Its functioning fine in and of itself, but I want to add the functionality detailed above to allow multiple users to access the same data.  So one user might send out some kind of automated email link to have the other person register for an account, and something about that link would tie the 2 accounts together.  Any ideas???


texts:
I've been reading about SQL Server Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index and display the fragmentation in percent:

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent desc

Can I add ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD to this script or do I have to write a new script.

I found http://sqlfool.com/2011/06/index-defrag-script-v4-1/ but am not sure on how I use it properly.

I'm new to database administration and am trying to setup a maintenance plan that will rebuild indexes when they hit a certain threshold.


texts: MSSQL: Auto-numbering rows in commit order

texts:
General setup

I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate transaction, as it should be atomic with respect to other data.

I also have some consumers reading from this table periodically. Each consumer has a pointer to the last ID processed within this event table.

Issue faced

After some experimenting with identity column it seems IDs are assigned to new records at insert time, not commit time. This means that I situation like this might arise:

Producer A inserts event EA into table (ID assigned: 1)
Producer B inserts event EB into table (ID assigned: 2)
Producer B commits
Consumer reads latest event EB, and sets "last processed" pointer to 2
Producer A commits
Consumer will never process event EA, because it has a lower ID, assuming already processed

Question

How do I ensure, that events (DB rows) are numbered in increasing commit order? I don't mind gaps in
the series if transactions are rolled back etc., but there should never be committed an item with a lower ID than what has already been committed (and therefore maybe already processed), and of course they need to be unique.
I would like not to lock the entire table, since many transactions run in parallel and some might run for a while.

Can I use some kind of insert trigger, stored procedure or similar to ensure this, or is it better to do some kind of post-processing in application code?


texts:
I have a MySql database, just containing Innodb tables, that seems to have corrupted itself.

It replicates to another server and it has corrupted that one too!

The symptom is that I can restart the server but as soon as I try to connect to it, it crashes.

The log file talks about a possible index page corruption to a specific table and that fits in terms of the table being accessed when the problem occurred.

Following the InnoDB recovery guidelines in the MySQL manual, I have set the innodb_force_recovery option to 6 which lets me restart the database and dump the tables.

Anything less than 6 and it will not stay up.

My questions are:

How do I know the extent of the innodb corruption?
Is the only safe option to recreate the innodb files from scratch (I assume via a re-install) or is there a smarter way to do this?
Can I trust the table dumps or should I go back to the most recent backup (also a table dump) and accept the data loss?
If it is an index page corruption, can I rebuild the primary index of the affected table and then restart the DB without the innodb_force_recovery option?


texts:
We have a PROD full backups copied over to support envs daily  and we restore to support environments to triage some issues, as the DB is growing big we would want to run the full backup copy only on the weekends. Daily backups are done as schedule but copying over to support env happens only on weekend. Copy-only or differential can we apply with one full backup ? or what is the best practice and effective way .

Sunday :Full Backup taken
Mon-Friday : Differential/Copy-only backups taken
Saturday : Fullbackup + (Mon-Friday)Differential/Copy-only backups applied .

Will this work out? and one thing to remember we have daily full backups.

We do hourly log backups, and want to know the best practice for backup strategy and restore in support env


texts:
I have a DB with multiple large tables. For example one single table has 738,829,922 rows.
The primary key of the table is a datetime. For better performance and maintainability the table is partitioned by monthly range.
Here is an example of the partition stats:

Around 95% of all query are hitting only the partition of the current month (also only the last 3 days).
Over the month I notice a performance drop the seems to be related to the row count of the havely use partition. It instancly goes back to normal after switching to a new partition.

I worked hard with the microsoft support to optimize all to the maximum. Thinks like statistics, fragmentation, parameter sniffing, query plans are well handled and leave not much room for optimization.
Question
I hope smaller partitions (weekly or daily) might solve the problem. To try this I have a gerneral questions abount partitions.
Are there some negative effects of having around 3,000 partitions on a single table? Are there other tips/tricks about partitioning table of this size?


texts: Change expiry date of TDE certificate of User Database without changing Thumbprint

texts:
I have been searching through internet on how to change expiry date of TDE certification of a user database at SQL Server 2014 SP2 and got so many articles and most of them are suggesting to drop and create another certificate with a different expiry date.

My concern is regarding backup of Database which was taken before changing Expiry Date. When I drop certificate and create a new one with different expiry date, thumbprint of certificate changes and hence unable to restore the backup which was taken prior to change.

Below is the error screen-shot:

Appreciate if I can get some expert advise on this issue with details.


texts: Can I Set The Value of App_Name() AFTER Login?

texts:
I've got an older application that is connecting via ODBC to my database server.  I want to discover what the Application Name is inside a stored procedure.  I'm not able to set the Application Name (or the ProgramName value in xp_who2) at login due to the ODBC ware not supporting it.  Is it possible to set that value after login via some "set" command?

I understand there are other ways around this (like calling my stored procedure with the application name) but I'm hoping to avoid that as it would require more than one coding change, and rather do something for this session just after login.


texts: Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

texts:
Hi everyone hope you're doing well
I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors .
this is my function

create or replace function Show_Description
(i_Course_Number course.course_no%Type) Return Varchar2
  ACCESSIBLE BY(function show_binary)

   AS

     V_Description Varchar2(50);
  Begin
      Select description
      into V_Description
      from course
      Where course_no = i_Course_Number;
   Return V_Description;

  Exception

   When No_Data_Found Then
     Return('Course Is not in the database');
   When Others Then
     Return('Error in running Show_Description Function');

END;

I want to know if it's even possible to use 'ACCESSIBLE BY' clause with a function.this the error I receive : ORA-00922 :missing or invalid option


texts: How can I create a stored procedure using xp_fixeddrives?

texts:
I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008

ALTER PROCEDURE freespace
AS
BEGIN
    CREATE Table #freespace(drive VARCHAR(50),freedrivespace VARCHAR(50));
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE @space  CURSOR;
    SET @space= CURSOR FOR
    SELECT Name FROM SERVERNAME;
    OPEN @space;
    FETCH NEXT
    FROM @space INTO @servername;
    WHILE @@FETCH_STATUS=0
    BEGIN

    SET @sql = '['+@servername+']...xp_fixeddrives';
    INSERT INTO #freespace
    EXEC(@sql);
    FETCH NEXT FROM @space INTO @servername;
    END
    CLOSE @space;
    DEALLOCATE @space;
END

The servername table consist of servername of linked servers like server1, server2.

When I run this query an error like this is displayed:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'server1' in sys.servers. Verify that the correct server name
was specified. If necessary, execute the stored procedure sp_addlinkedserver to add
the server to sys.servers.

 (0 row(s) affected)

OLE DB provider "SQLNCLI10" for linked server "server1" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked
server "server1" was unable to begin a distributed transaction.

I think the problem is with the dynamic SQL.  I've tried many changes to this query, but still I get this error.

Update

now I need to include the servername also into the table for that if i created an extra column 'servername' and used this query for inserting values

SET @sql = insert into #freespace values(@servername,['+@servername+']...xp_fixeddrives');

EXEC(@sql);

But it shows an error ,please help


texts: How to get last inserted id and insert it into another table using stored procedure?

texts:
Here is my SP.

ALTER PROCEDURE [dbo].[My_SP]
    -- Add the parameters for the stored procedure here
    @ClientID bigint,
    @CreatedBy int =null,
    @CreatedDateTime datetime = null,
    @ModifiedBy int = null,
    @ModifiedDateTime datetime = null,
    @Name nvarchar(255)

    SET NOCOUNT ON;

    INSERT INTO dbo.Clients
    (
    CreatedBy,
    CreatedDateTime,
    ModifiedBy,
    ModifiedDateTime,
    Name,
    Active
    )
    VALUES
    (
    @CreatedBy,
    @CreatedDateTime,
    @ModifiedBy,
    @ModifiedDateTime,
    @Name,
    1
    )

    SELECT @Name AS ClientName

    INSERT INTO BusinessRules (
                        ClientId,
                        ProjectId,
                        TemplateName,
                        Revised,
                        Serial,
                        Topic,
                        QualityItem,
                        CriticalToQC,
                        [Description],
                        OwnerNotes,
                        RelevantDocs,
                        WhereToCheck) SELECT
            @ClientID,
            null,
            b.TemplateName,
            b.Revised,
            b.Serial,
            b.Topic,
            b.QualityItem,
            b.CriticalToQC,
            b.[Description],
            b.OwnerNotes,
            b.RelevantDocs,
            b.WhereToCheck
    FROM BusinessRules AS b WHERE  ProjectID = 394

I need to use the inserted @ClientID in the next INSERT INTO statementas ClientId.

How can I achieve this?


texts: Can't restore Azure bacpac into localdb with SqlPackage

texts:
I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail.

Here is the command I use:

PS C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin> .\SqlPackage.exe /Action:Import
/SourceFile:"C:\Users\S\Desktop\MyDb.bacpac"
/TargetConnectionString:"Data Source=(localdb)\v11.0;Initial Catalog=MyDb; Integrated Security=true;"

And here is the error message:

*** Error importing database:Data cannot be imported into target because it
contains one or more user objects. Import should be performed against a new, empty
database.
Error SQL0: Data cannot be imported into target because it contains one or more
user objects. Import should be performed against a new, empty database.

My machine is newly installed, and I have never used localdb at all.


texts: Two-phase commit for distributed transactions: what if a commit failed?

texts:
I have 2 servers participating in 2PC scenario:

* Srv1: votes OK
* Srv2: votes OK
* Coordinator runs "commit"
* Srv1: commits
* Srv2: does not accept "commit" command because of network failure

From this moment my distributed DB is not consistence anymore.

How it should be solved?


texts: Query list of logins that have logged in the last X days on SQL Server

texts: Is there any good query that included internal system objects like sys.dm_exec_sessions or sys.dm_exec_connections that would list logins that have connected to the SQL server over the last X days? 15, 30 days? I am also looking for any other query that may help or related to this.


texts: Property class Length Annotation PersistedResolvableAnnotation

texts:
I am trying to deploy an ssdt project using sqlpackage.exe utility.
I get the following error (in German):

  Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht fortgesetzt werden. Die Property-Klasse
  Length ist nicht in der Element- oder Annotation-Klasse
  PersistedResolvableAnnotation enthalten.

It means sth. like:

  Error while creating deployment plan. Deployment cannot proceed. The
  property-class Length is not included in the element or annotation
  class PersistedResolvableAnnotation.

I could not find much information about "PersistedResolvableAnnotation". But I realized that it is included in the model.xml in the dacpac.

It contains handful of SqlLogins defined similar to this one:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <References Name="[Angela]" Disambiguator="8" />
                    <Annotation Type="**PersistedResolvableAnnotation**" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="8" />
                        <Property Name="Offset" Value="62" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

Angela (and the other candidates) do not exist on destination server as Login, although they are registered database users in the target db. I would expect another error - if any - than this useless one.

The database the project is based on a pretty old SQL Server verison. Is it possible that this is some deprecated feature / syntax or property coming from Sql Server version and is simply not supported ?
Does anyone know more details?


texts: Deleting an Oracle database without connecting to it?

texts:
I have two databases which were created using dbca.
I made some changes in the second database, and I can't login to that database anymore, and, of course, I can't delete it by using dbca.

I am using Centos 5 and Oracle 10g Release 2.

How can I delete a database manually without impacting another dababase?


texts:
We have a Windows Update (WSUS) database that is used by Windows Update (obviously). A couple of times a day we will receive deadlocks on this database caused by the WSUS application running 2 update queries on the same record. The 2 queries come from the same process on one server.

We have tried changing the database to read committed snapshot, however that hasn't improved the situation. The WSUS process isn't reporting any errors, and the sys admin team is happy with how it's running.

Is there anything we should do to try and fix this, or should we just ignore the alerts for deadlocks on that database?

UPDATE: Deadlock has happened again. See below for the graph details:

For some reason stack exchange won't let me post the xml here.


texts: Does Restore "From database" restore from the most recent backup of the database or the actual live database?

texts:
In SSMS 2005, I want to use the GUI to copy a database by restoring in to a new database.  I was using the "From database" radio button for the source, but then the databases in the list disappeared recently.  I read the sql server documentation and it says that only databases that are on record as having a backup  will show up in this list.  Nothing in the documentation says whether this backup will the actual data source or not.

When I select "From Database" in the source options, does it pull from the most recent backup of the database, or the actual live database?


texts:
I experienced an incident that my connections are full. Even I as an administrator I cannot log to MySQL due to "too many connections.". Does anybody knows how to setup this max_connections+1? I use version 5.6.14. Thank you very much.

https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html


texts: Example where schema are better than multiple databases

texts:
In several database systems (e.g., PostgreSQL and MS SQL Server) there is a concept of schema. There are many questions out there asking what schema are and how they are used; here is a good one. Usually the answer boils down to "namespace" and "permissions". However, what remains unclear to me is why a schema has any advantage over just adding a new database. A different database allows one to have both separate namespaces and permission assignments.

Can someone provide an example in PostgreSQL where multiple schema in a single database provide an advantage over multiple databases without schema? From a purely organizational perspective they appear equivalent.


texts: When are COLUMN aliases in FROM clauses needed?

texts:
FROM provides column_alias, the SQL spec calls these <derived column list> clauses. This is what the postgres docs say about them,

  A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

When are these needed? When can I not otherwise just use a COLUMN alias?

SELECT t.*
FROM table_name AS t (a,b,c);

vs

SELECT t.col1 AS a, t.col2 AS b, t.col3 AS c
FROM table_name AS t;

This example taken from the chosen answer by @ypercubeᵀᴹ which doesn't seem too useful.

FROM aliases in the above context provide no real benefit unless you're

relying on partial aliasing (NOT aliasing the whole table)
that is dependent on ascending column ordering
your table has more than three columns (or you could just write it explicitly in the from clause).

It seems like doing that relies on the hazards of regular t.* stacked with added obscurity. So when is FROM aliasing useful?


texts: Is it Ok to use GUID as the Foreign Key?

texts:
Here's my current situation:

We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far.

Now, that table is required to be able to store the records uploaded from the mobile devices. But the records uploaded from the mobiles are to be verified first before being stored into the "MyTable". Hence, a staging table is created, say "Temp_MyTable".

In other words, this is the flow:

Mobile devices ------> Staging Table ------>  Actual Table

                    [GUID as PK]         [bigint as PK], [GUID as FK]

The thing is, the Staging Table is using GUID as the primary key. Reason, we have to reference back the records uploaded from multiple devices. Users may re-upload the record after modification. Note: The GUID here is generated by the mobile (simple javascript).

So, now, i want to reference back the records of the Actual Table to the Staging Table. In this case, a Foreign Key 'probably' required. Unfortunately, it is the GUID data type.

And after reading a number of articles, it seems that using GUID as the Foreign Key may be disadvantageous - more storage or slower.

I would like to know, is it Ok to use GUID as the Foreign Key? Can someone justify that it is actually 'perfectly fine'? Or is there a better design?


texts:
I'm running Windows Server 2008 R2 and executed a large query by accident. Next, the sever ran out of hard drive space.

I now have a file in my administrtor documents (log.log) that is over 18GB. I can't seem to delete the file as it just appears again. Any help.

When I delete the log it says I don't have administrator privilages. (I do)


texts:
I have a 5TB+ MySQL database on RDS which I need to optimize. One particular table 1.5TB. We ran OPTIMIZE TABLE on this table in a separate RDS instance restored from a snapshot and the results were encouraging, but the command took 32 hours to run!

Is there a way to improve performance of the OPTIMIZE TABLE command? This table has 6 indexes other than the PK. I believe removing them will offer significant speedups. We plan to bump up the to the largest instance size RDS offers (r3.8xlarge) to help speed up the process. Are there any parameter groups I can tweak to improve the performance?


texts: Cannot create an additional database in Oracle 18c XE

texts:
Creating a new database in Oracle 18c XE throws this error, does this mean that I can only use the database created by the installation?

ORA-00442: Oracle Database Express Edition (XE) single instance
violation error
ORA-00600: internal error code, arguments:
[OSDEP_INTERNAL], [], [], [], [], [], [], [], [], [], [], []
ORA-27302: failure occurred at: sxesingleins ORA-27303: additional
information: environment variable ORACLE_SID should be XE only


texts: Database Mirroring - Mixed SQL Server 2012 to SQL Server 2016

texts:
I am pretty sure what the answer is but I want to confirm this.
Is there a way to create mixed DB mirrors SQL Server 2012 to SQL Server 2016.


texts: Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

texts:
I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.

I currently have the following result in the execution plan for a very simple SELECT query:

As you can see it is off by 5 rows.

I then run:

UPDATE STATISTICS  Person.Address WITH FULLSCAN
UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN
GO
EXEC sp_recompile 'Person.Address';
GO
SELECT * FROM Person.Address OPTION(RECOMPILE)

However, it is still off by 5 rows.
Why?

I know I shouldn't worry unless there is a performance problem. However, I am trying to understand the actual effect of a complete statistics update


texts: Getting 2 columns from sets of 2 rows in Postgresql

texts:
So I have my data organized as:

id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date

1 | netIncome | 100 | stockid1 | 2019-05-01
2 | totalRevenue |1000 | stockid1 | 2019-05-01
3 | salesExpense |20 | stockid1 | 2019-05-01
4 | totalAssets | 10000 | stockid1 | 2019-05-01
5 | netIncome | 30 | stockid2 | 2019-04-23
6 | totalRevenue |600 | stockid2 | 2019-04-23
7 | salesExpense |15 | stockid2 | 2019-04-23
8 | totalAssets | 900 | stockid3 | 2019-04-23

So for each stockid, date pair, there is only one of each different report_item (there's not 2 different totalAssets for X date, Y stock id). There is a totalAssets for each stockID for every quarter (which aren't always on the same date).

What I want to do is return stock_id, date, totalAssets, totalRevenue as columns, effectively merging the 2 rows (one for totalAssets, totalRevenue) for each date/stock_id pair into 1 while discarding any rows where report_item is neither totalAssets or totalRevenue.

So this is what I did to turn turn the totalAssets and totalRevenue rows into columns, which gets rid of rows with other report_items, although for each stock_id, date pair there are still 2 rows. What I want is to have them in one row.

select date,
       stock_id,
       (case when report_item = 'netIncome' then id end) as netIncome,
       (case when report_item = 'totalRevenue' then id end) as totalRevenue
from report_items where report_item IN('netIncome', 'totalRevenue') order by date desc limit 100

Returns:

date | stock_id | netIncome | totalRevenue
2019-05-01 | stockid1 | 100 | null
2019-05-01 | stockid1 | null | 1000
2019-04-23 | stockid2 | 30 | null
2019-04-23 | stockid2 | null | 600


texts: My questions is quite similar to Mysqldump from the Amazon RDS. I have the same behavior. Everytime when I do a backup using mysqldump my entire RDS freezes and hence my application is not responding. I am using --single-transaction with the mysqldump command but that doesn't help at all. With this, RDS is quite unusable since I can not dump a backup or data sample during live operation. Is there a way to get the data out of RDS without freezing everything?


texts: SSIS: Working with 3 constrains where 2 should be OR and one should be AND

texts:
So I have an SSIS flow in which I am working with three constraint within a task, but 2 of them must be an OR and one must be an AND. In pseudo-code would be something like:

if ( (constraint-a OR constraint-b) AND constraint-c)

Problem is, we can only set OR/AND to all precedents:

Are there any solutions in which we could group some of these constraints?


texts: Vehicle database design - Type, Make, Model, Vehicle

texts:
I kindly ask you for advice, if I am correct with my DB design. I am writing an app in Django (it has its own ORM), which will use PostgreSQL.
My expectation is, that I will specify:

Type (example: Automobile, Motorcycle, Boat, ...)
Make (example: Honda, BMW, Audi, ...)
Model (example: CBR1000, A6, A8, 535i, ...)
Vehicle - all other info, like VIN, price, ...

So I think that the best way is to have Makes unique. I don't know, what to do with a Type. I think that the best way is to have a foreign key of a Type in a Model - because for example Honda can produce Automobiles and also Motorcycles. So by Model we can found, what vehicle type is it.
I want to make a web form filter (some kind of three linked dropdowns), where the user will select a Type, then it will filter out all Makes for that selected type. Then in another dropdown, he will select a Make and in the third dropdown, there will be shown all Models, which is available in that selection.
Something like a Copart has - Vehicle Finder on the right side of web page https://www.copart.com/vehicleFinder/
I hope that it makes sense, I have created a schema like this, and I am not just sure if it is usable and it is right. Here it is:

link to editor: https://dbdesigner.page.link/gS62T3GYPth2TAAbA
Thank you very much for any advice!


texts: Finding rows with same id but different date

texts:
Consider below table tbl:

id    date
1     2016
1     2017
2     2017
2     2017
3     2016
3     2017
4     2018
5     2018

How can I get only rows with same id but different date.

Right now I can only get ids with more than 1 count like so:

select id, date
from tbl
group by id
having count(id)>1

How can I only get id 1 and 3 along with date?


texts:
I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data.

Each row has a field for KM (mileage in KM metric unit), prevMileage (mileage of previous row) and nextMileage (mileage of the next row). The prev and next fields are calculated in a list before this one. The problem with this is that if the KM for a row is NULL the prevmileage of the next row and the nextmileage of the previous row will be NULL as well.

To make my calculation correct I need to add the previous mileage to the calculated value I make for each row. But if there are multiple NULL rows after each other, this is impossible.

What I need now is a way to update the 'previousmileage' field of the next row.

This is the code I have so far and a preview of the table:

Date      |     KM     |   prevmileage    | nextmpileage
--------------------------------------------------------
01/01/2008|     5000   |       4500       |     55000

Code

    , CalculatedList as (
SELECT
    PNL.MonthDate,
    PNL.FK_CarID,
    PNL.AllocationID,
    PNL.CO2,
    PNL.KM,
    PNL.beginMonth ,
    (CASE   WHEN PNL.KM IS NULL
            THEN (CASE  WHEN PNL.PrevMileage IS NULL
                        THEN (CASE  WHEN PNLnext.PrevMileage IS NULL --Here I want to update the prevmilage of the next row
                                    THEN (CASE  WHEN PNL.NextMileage IS NULL
                                            THEN 0
                                            ELSE ((PNL.NextMileage / DATEDIFF(DAY, PNL.PrevMonthDate, PNL.NextMonthDate)) * DATEDIFF(DAY, PNL.PrevMonthDate, PNL.MonthDate))
                                            END)
                                    ELSE 0
                                    END)
                        ELSE (CASE  WHEN PNL.NextMileage IS NULL
                                    THEN PNL.PrevMileage
                                    ELSE (CASE  WHEN PNL.PrevMileage = 0 OR PNL.NextMileage = 0
                                                THEN (CASE  WHEN PNL.NextMileage = 0
                                                            THEN 0
                                                            ELSE (PNL.NextMileage / 2)
                                                            END)
                                                ELSE ((PNL.NextMileage - PNL.PrevMileage) / DATEDIFF(DAY, PNL.NextMonthDate, PNL.PrevMonthDate)) * DATEDIFF(DAY, PNL.MonthDate, PNL.PrevMonthDate)
                                                END)
                                    END)
                        END)
            ELSE PNL.KM
            END ) as Calculated,
    PNL.NextMileage,
    PNL.PrevMileage,
    PNL.PrevMonthDate,
    PNL.NextMonthDate,
    (CASE   WHEN PNL.KM IS NULL
            THEN 'N'
            ELSE 'Y'
            END) AS RealData,
    PNLnext.PrevMileage as test
FROM
    PrevNextFullList as PNL outer apply
     (select top 1 ml2.*
      from PrevNextFullList ml2
      where ml2.FK_CarID = PNL.FK_CarID and
            ml2.MonthDate > PNL.MonthDate
      order by ml2.MonthDate desc
     ) PNLnext
)


texts:
I have two SQL tables of data extracted from a ERP (invoices)
the first contains the "head" of the invoice (invoice_number, date, customer, ...)
the second table contains the "rows" of all invoices (good_description, qty, price, ...)

To simplify:

  T1.ID  |  T1.COL_A | T1.COL_B           T2.T1_ID |  T2.COL_A | T2.COL_B
  -----------------------------           -------------------------------
   1     |    AAA    |    BBB                 1    |    eee    |    fff
   2     |    CCC    |    DDD                 1    |    ggg    |    hhh
                                              2    |    iii    |    jjj
                                              2    |    kkk    |    lll

If I make a SELECT joining two tables by ID field it would give:

           T1.COL_A  |  T1.COL_B  |  T2.COL_A | T2.COL_B
          ----------------------------------------------
   1          AAA    |    BBB     |    eee    |    fff
   1          AAA    |    BBB     |    ggg    |    hhh
   2          CCC    |    DDD     |    iii    |    jjj
   2          CCC    |    DDD     |    kkk    |    lll

Ok quite simple.

The problem is that I need a specific format to import the data in another ERP, a kind of CSV format made like this:

    ROW_TIPE    ;  T1.COL_A  ;  T1.COL_B  ;  T2.COL_A ; T2.COL_B
----------------------------------------------------------------
  INVOICE_HEAD  ;     AAA    ;    BBB     ;           ;
  INVOICE_ROW   ;            ;            ;    eee    ;    fff
  INVOICE_ROW   ;            ;            ;    ggg    ;    hhh
  INVOICE_HEAD  ;     CCC    ;    DDD     ;           ;
  INVOICE_ROW   ;            ;            ;    iii    ;    jjj
  INVOICE_ROW   ;            ;            ;    kkk    ;    lll

So the rows are "grouped" and not repeated and at the first column is inserted the type of row to be processed by the ERP importing the data (if it's INVOICE_HEAD or INVOICE_ROW)

What's the best way to achieve this result in SQL?

The data is from a Pervasive Btrieve. I managed to extract the invoice data from that in two CSV files (one for each table). Then I imported in a SQLite to make the manipulation needed to get a format that is possible to import in the new ERP software.


texts: How to convert columns into rows in MS Access

texts:
Please help me to understand, how to convert columns to rows in MS Access. I have the below table, I would like to convert as the below result table.

NoOf_A NoOf_B NoOf_C NoOf_D
200    300    400    500

Result Table:

Type_OfCValue  Total
NoOf_A          200
NoOf_B          300
NoOf_C          400
NoOf_D          500


texts: How to insert the gathered dates into another table using INSERT SELECT in MySQL?

texts:
insert into rfdate (date)
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday')

Above code doesn't work.

Code below gathers the needed date irregardless of where database you are in since it's just date. You can try it too even without using a table with the columns presented.

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');


texts: Prevent inserts to a non existing Synonym

texts:
I have a Synonym called VirtualTable which points to RealTable1.

A lot of inserts are happening to VirtualTable - about 50 every second.

Very often I drop the synonym and re-create it to point to RealTable2.

Unfortunately it happens some times, that between the time I drop the synonym and recreate it, an insert is attempted against VirtualTable and I get an annoying but understandable error:

Invalid object name 'dbo.VirtualTable'

I tried dropping and creating the synonym in a transaction, but that doesn't make any difference. How can it encapsulate something that doesn't exist.

I know that I cannot rename or repoint a Synonym.

So my question is how I can make sure that inserts to VirtualTable are paused for the millisecond that it takes me to recreate the Synonym?

Or perhaps someone has a different idea of how to solve this.


texts: Best way to design table with common one to many reference

texts:
I have existing POC contacts table in MySQL storing individual contacts (around 200k dummy and real records imported from staffs contacts), one day my boss asked me to store companies too, which have almost zero intersection columns with contacts except for the 1-to-many references (address, email, website, phone, notes, task).
-- originally this was the schema that evolved over time:

contacts
  - first_name, last_name, prefix, suffix
  - email, website, phone, address --> only primary ones
  - company_name, gender, birthday, photo, favorite

tables with 1-to-many relationship with contacts
(companies will have the same):
contact_websites # also stores social media
contact_emails
contact_phones
contact_notes
contact_tasks
contact_addresses
contact_attachments

-- need to add 2 new tables:

companies
  - name
  - email, website, phone, address --> only primary ones
  - industry
  - category_id
  - employee_count
  - annual revenue

company_staffs (many-to-many with contacts)

with also have one to many relationship with all those contact_*

Both will be shown as rows of the same table in the UI.
what's the best way to store these kinds of things, should I:
A. create a common table, split contact to 3 tables:
`contacts` that have 1-to-1 relationship with
   `contact_individuals` and `contact_company`
most of `contacts` columns moved to `contact_individuals`
    except the common columns with `contact_companies` table
`contacts` keep the 1-to-many relationship with `contact_*` tables

contacts -1-1- contact_individuals
contacts -1-1- contact_companies
contacts -1-M- contact_*
contact_company_staffs (many-to-many of contact_individuals and contact_companies)

pros: use least storage
cons: lots of migration, lots of joins

B1. split both tables and split the 1-to-many tables:
`contacts` for individuals
`contact_companies` for companies
using union when needed to show them both in the same view.
create its own 1-to-many tables for company
  (probably best way is to merge them all,
   since they have common fields: label,
   value[website/email/phone/address/note], created_at, updated_at)

contacts -1-M- contact_*
contact_companies -1-M- contact_company_*
contact_company_staffs (many-to-many of contacts and contact_companies)

pros: use less storage especially when contact details not filled
cons: many tables, each with their own joins

B2. split both tables but reuse the 1-to-many tables:
same as B1 but the 1-to-many tables (contact_*)
  have additional column other than `contact_id`: `company_id`

contacts -1-M- contact_*
contact_company -1-M- contact_*
contact_company_staffs (many-to-many of contacts and contact_companies)

pros: doesnt use too many tables as B1
cons: still need union when need to show both at same time

B3. split both tables but reuse the id:
same as B2 but the id will reused, so there would be unique for both table
  probably enforced in application level

have same pros and cons with B2, just one less column `company_id`
  on each of 1-to-many `contact_*` tables

C. merge both tables:
`contacts` stores both for individual and companies,
  so some columns only used by either individual or company,
  add a new bool column `is_company`

contacts -1-M- contact_*
company_staffs (many-to-many of contacts[individual] and contacts[company])

pros: simplest, easiest
cons: use much storage for columns that are not used by both contact type

D. merge and split all common 1-to-many on it's own table like in graph databases, so it would be:
`contacts` (name, common fields of individual and company)
`contact_details` (label, value, type)

contacts -1-M- contact_details
company_staffs (many-to-many of contacts[individual] and contacts[company])

cons: lot more joins
pros: should use less storage for the details, especially when people dont fill it


texts: Is it any good for performance to have master and slave on the same server

texts:
There are similar questions on this site, but they mostly cover server failure and data backup aspects.
I'm concerned only as far as query performance. If master and slave share the same resources, Master table is used only for writes and Slave only for reads, will this help db to handle growing number of queries more efficiently, given that queries are properly optimized?


texts: Copy table structure excluding nullability into local temporary table

texts:
How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, keys, indexes, etc. I'll also note that this must be done programmatically within a procedure, so I can't just generate a create table script and modify it. The best I've come up with has been:

select top 0 *
into #MyTempTable
from dbo.MyTable

which copies the nullability of each field, thus defeating the purpose for me.

I've also played around with dynamic SQL and extracting the column data from table INFORMATION_SCHEMA.COLUMNS to build the create table statement, but the issue there is that the temp table goes out of scope after the dynamic SQL statement executes and control is returned to the "main" procedure. (And I'd rather not jam the rest of the procedure into the dynamic SQL statement.) If there were a way to return the temp table from the exec (@Sql) statement or keep it in scope somehow, it might work, but I don't know that there's a way to do that.

Related questions:

Copy complete structure of a table
Create table structure from existing table


texts: Do SQL Sharding or Cluster help speed up slow queries, or just concurency?

texts:
I am having a performance problem with my current single instance database as the volumes of data grow. The application I am working on receives inserts once a month, and the oldest month is deleted at the same time. The number of rows grows in the hundred millions for the most accessed table (one row per item per month per store) and is joined on a table storing the data specific to items regardless of time (like its label).

A normal query acts as follow: Do a aggregation either per month, store or article over a different amount of store/months and categories.

I was looking at some sharding possibilities that would allow me to keep the queries as they are (I can't afford to rewrite everything for now) and run the queries exactly as if I had only one server but in the end that server would run the query on multiple shards/replications to speed things up.

Can I expect any good speed increase on currenly slow queries? So far my understanding of cluster/sharding is mostly to help reduce loads from many read/writes but this is not a problem at all for me at this time since there is not a lot of queries going on (rarely multiple queries at the same time on the server instance).

Thank you

EDIT: I'd like to point out that today, I am coping with ok speeds by having the full table in memory so I've removed the disk read times, so simply storing the data on different disks wouldn't solve all...


texts: Is the size limit of a json field before or after compression in Postgres?

texts:
The size limit of a json field is 1GB (source: this StackOverflow answer):

json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB.

I ran some experiments with inserting json of varying sizes into a table in Postgres.
Taking pg_sizeof of rows here seems to indicate that Postgres does compression of the json data.
My experiment:
create table json_size (n integer, j json);

For n with values of 1, 10, 100, 1000, 10 000, 100 000, 1 000 000.
with q as (select generate_series(1, n)::bigint as x)
insert into json_size (n, j)
select 1, coalesce(json_agg(to_json(q.*)), '[]'::json) from q;

select n, pg_column_size(j) as size from json_size;

Results
n       size
1       10
10      92
100     996
1000    3224
10000   33488
100000  335115
1000000 3351365

Is the size limit of 1GB before or after this compression is done?


texts: SSMS Template Browser Add-In development

texts:
Okay, I’m trying to do something that I just can’t seem to get accomplished and I haven’t found anything online to address this request.
In the “Template Browser” I want to add another category of templates to keep the various templates separate. Looking at the template browser, there are two icons in the “header”. One icon is for the “SQL Server Templates” and the other is for the “Analysis Services Templates”. (See icons in red circle in the image below.)
I want to add a third icon (or more) to the header along with the “SQL Server Templates” icon and “Analysis Services Templates” icon.
Does anybody know if this is possible, and if so, how do you do it?  I'm a full-stack developer / DBA / BI and would like to build / modify this on my own as opposed to just installing a third party solution.
I have SQL Server 2012 with an instance of SSMS 2012 and an instance of SSMS 2018.


texts:
I inherited database systems like this. Currently I have publisher database in SQL Server 2005 compatibility mode on Windows Server 2008 R2 with SQL Server 2008R2 SP2 machine. Distributor is on the same machine. Subscriber is 2008R2 SP2 and database is in SQL Server 2008 compatibility mode. We are using Transactional Replication. Isolation level is Read Committed. Distributor resides on Publisher. Even though when I right click on publication and even though subscription shows as pull subscription, I think it won't matter since distributor resides on publisher itself. Please correct me if I am wrong. Storage system is IBM flex which is shared by five servers including publisher and subscriber.

Since couple of days, I see latency of few hours, it catches up in the morning and starts going up again in the afternoon. I followed https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/ to see exactly what was happening. I ran following query.

USE distribution
go
EXEC Sp_browsereplcmds
@xact_seqno_start = '<seq#>' -- seq# is same for start and end
,@xact_seqno_end = '<seq#>'
,@publisher_database_id = <publisher database id --this is different than database_id

I see that there are supposedly massive updates being done on few tables involved in replication and Log Reader is just scanning transaction log, not able to replicate anything till transaction completes. Interestingly, I can't see any blocking on either publisher and/or subscriber. Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? Will it help to change polling interval to 1 and readbatchsize to 1000 or 5000. What's the command to change that setting?

I changed Log Reader Agent default profile as follows. Polling Interval from 5 to 1 and ReadBatchSize to 5000. This brought latency from 13 hours to zero almost instantly. But I see that it went back to 13 hours.

Replication is in sync and I don't have a single clue for actual root cause which caused the latency and now it went away.


texts: How to skip OR if AND statement row count is not 0

texts:
Heey Guys,

I have a question and i can't find the solution.
So this is my QUERY

SELECT *
FROM `hwr_story`
WHERE question_count = '8'
AND (lower(subcategory) = lower('True') OR subcategory IS NULL)
ORDER BY RAND() LIMIT 1

Result:
http://cl.ly/image/1r0c2P0m3K07

So what i want, is if this return a row, i don't want the or statement.

SELECT *
FROM `hwr_story`
WHERE question_count = '8' AND lower(subcategory) = lower('True')
ORDER BY RAND() LIMIT 1

But if the row count is 0 then i want it to get

SELECT *
FROM `hwr_story`
WHERE question_count = '8' AND subcategory IS NULL
ORDER BY RAND() LIMIT 1

Is there anyway to do this in one Query?


texts: How to revoke DBADM and clean up any related objects from DB2 LUW

texts:
This question has started because of us taking copies of production backups and restoring them into lower environments (with scrambled data of course) for developers to practice and/or debug against.

We have IDs that are assigned DBADM,SECADM,DATAACCESS, and ACCESSCTRL (mainly the instance owner). When we restore into a lower environment, we end up needing to log on as the original instance owner and grant the same above authorities (DBADM, SECADM, DATAACCESS, and ACCESSCTRL) to the new target instance owner.

I figured it was not a good idea to leave the original ID in the database, so I attempted to revoke its privileges. Not long after my package rebinds started failing, as there are apparently packages tied to that original ID. Not knowing what they contained and if I could/should delete them or not (even though I'm thinking they are harmless to remove???) I ended up restoring the privileges to the original instance owner and just leaving it there.

That has always bothered me. An ID with the powers of DBADM,SECADM,DATAACCESS, and ACCESSCTRL shouldn't just be lying around. To me that is a security hole. To me the ID should be revoked and any other cleanup that needs to be performed should be executed to keep the database safe. The same would be true if say I would quit the company or my fellow DBAs would. We would want to revoke IDs and clean up and/or transfer ownership of objects to remove any security holes.

The problem is, I don't know what all needs to be cleaned up. I have done a bit of poking around on Google and IBM's documentation, and I can find nothing to suggest the steps that should normally be taken when removing such a high user from the system. Or even a user with BINDADD authority?

What do you all remove/revoke and in what order? Is there a way this can be scripted/automated? How do you know which packages you can remove? Are there other things that need to be moved/transferred?

Anyone else encounter this? Thoughts? Experiences?


texts: How to separate name string by the spaces in the name in T-SQL

texts:
I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr

I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle name, suffix), while allowing for names that may or may not have a middle name or suffix.

I've got it figured out for everything but the third instance, where there is a suffix.

For first name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
    else
        SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName

It's pretty crazy, but it works...

For middle name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then
            REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@name))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))))))
    else ''
end as middleName

Not quite as complex as the first name.

Since the last name is at the beginning that's the easiest:

SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName

But I can't figure out how to allow for names with a suffix (Jr, Sr, III, etc).


texts: Linked servers login failure: Impersonate works, Username/password fails

texts:
I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2.

The login works perfectly when I select the impersonate option. However when I try to use the remote user/password option it fails, even though the remote user is the same as the local user being impersonated.

The user account I'm trying to connect with is a windows admin on both machines.

Unfortunately, I need to be able to connect to machines that the local user doesn't have admin rights to (though I will have the username/password of an admin when this occurs), so impersonation won't work.

Where am I going wrong?


texts: Oracle XE: Transfer Database from Linux to Windows

texts:
our professor gave us a linux VM with a Oracle XE 11g Database.

I want to transfer this Database to my Windows installation because I dont't want to boot up the VM for every exercise.
Everything should be transferred. Users, Permissions, Procedures, Tables, Data, and so on.

How can I do this.

I already tried to use the "Backup Database" and "Restore Database" buttons but I don't know where the backup is placed and where I have to put the backup files on my windows machine.

Thanks...


texts: In Postgres, how to select by a column of type UUID

texts:
I have a DB table that someone else created, its PK is a single column of type 'uuid'. I'm struggling to perform a simple select by that column. Examples I have tried with no success:

select from site where id = "9d4da323-4c20-360f-bd9b-ec54feec54f0"

select from site where id = UUID('9d4da323-4c20-360f-bd9b-ec54feec54f0')

select from site where id::text = '9d4da323-4c20-360f-bd9b-ec54feec54f0'

what's the right syntax?


texts: Temp Table Clustered Key Not Being Honored: Bug or Expected Functionality?

texts:
As I was putting some test sets of data together, I noticed some funny behavior with temp tables.  When working with large sets of data in clustered temp tables that are populated via a parallel execution plan, the clustered key does not look to be honored when selecting data.  This issue also seems to affect all versions of SQL Server that I've tested (include vNext).

Here's a dbfiddle.uk example of the test.  You may have to execute it a couple of times to get the result I am finding, but it shouldn't take more than one or two executions to yield the same results.  Additionally, this is the local execution plan I'm getting on my environment which shows that the only difference between the large and small data sets is the way data is fed into the tables (e.g. parallel vs serial plan).

If you want to play-at-home, here's the test I'm running:

-- Large Data Set
CREATE TABLE #tmp
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO #tmp
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Smaller Data Set
CREATE TABLE #tmp2
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO #tmp2
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Large Record Set
-- Clustered Key Not Honored*
SELECT TOP 10 *
FROM #tmp

-- Small Record Set
-- Clustered Key Honored
SELECT TOP 10 *
FROM #tmp2

DROP TABLE #tmp
DROP TABLE #tmp2

I've not found any references indicating this is expected behavior, but before I submit a connect item, I first wanted to reach out and confirm this isn't a localized problem.  Can someone either point me to documentation identifying this is expected behavior or alternatively confirm this is, in-fact a bug?

EDIT: In response to the comments about not including an ORDER BY clause, I was always under the assumption the TOP keyword returned the data in the order in which it was inserted, which should, in this case, be the order dictated by the clustered key.  When running the same statement against a formal table, the expected behavior is returned:

-- Large Data Set with a Formal Data Table
CREATE TABLE tmp
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO tmp
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Large Record Set
-- Clustered Key Not Honored*
SELECT TOP 10 *
FROM tmp

DROP TABLE tmp

(6325225 row(s) affected)

(1 row(s) affected)
ID
-----------
1
2
3
4
5
6
7
8
9
10

(10 row(s) affected)

(1 row(s) affected)

Even the execution plans are the same, so why the different result sets between a temp table and a formally defined table?

Finally, a shout out to Joe Obbish as I gratuitously ripped off his CROSS JOIN approach to build large sets of test data as it's quite efficient!


texts: Why am I getting different results from a query vs. a view

texts:
The base problem I'm facing is that I need the latest record for each item.

A little setup... MySQL 5.6.14.

I need to create two views (because MySQL won't let me have a subquery in a view).  My first query sets up the data like this.

select
    `inventoryrecords`.`inventoryrecordid` AS `inventoryrecordid`,
    `inventoryrecords`.`logicaldeviceid` AS `logicaldeviceid`,
    `inventoryrecords`.`passrfid` AS `passrfid`,
    `inventoryrecords`.`tagepc` AS `tagepc`,
    `inventoryrecords`.`currentstate` AS `currentstate`,
    `inventoryrecords`.`statedateutc` AS `statedateutc`,
    `inventoryrecords`.`ownerobjectid` AS `ownerobjectid`,
    `inventoryrecords`.`ownerobjecttype` AS `ownerobjecttype`
from
    `inventoryrecords`
where
    1
order by `inventoryrecords`.`statedateutc` desc

Then I can restrict everything to just the last record for each TagEPC with my "real" query like this.

select
    `lastinventoryrecords_step1`.`inventoryrecordid` AS `inventoryrecordid`,
    `lastinventoryrecords_step1`.`logicaldeviceid` AS `logicaldeviceid`,
    `lastinventoryrecords_step1`.`passrfid` AS `passrfid`,
    `lastinventoryrecords_step1`.`tagepc` AS `tagepc`,
    `lastinventoryrecords_step1`.`currentstate` AS `currentstate`,
    `lastinventoryrecords_step1`.`statedateutc` AS `statedateutc`,
    `lastinventoryrecords_step1`.`ownerobjectid` AS `ownerobjectid`,
    `lastinventoryrecords_step1`.`ownerobjecttype` AS `ownerobjecttype`
from
    `lastinventoryrecords_step1`
group by `lastinventoryrecords_step1`.`tagepc`
order by `lastinventoryrecords_step1`.`statedateutc` desc

When I try to select * from the "real" view, I do not get the data I expect.  However when I run the query with the sub-query in a window I do.

select * from (
lastinventoryrecords_step1 )
group by tagepc
order by statedateutc desc

yields incorrect data whereas

select * from (
    select
        inventoryrecordid,
        logicaldeviceid,
        passrfid,
        tagepc,
        currentstate,
        statedateutc,
        ownerobjectid,
        ownerobjecttype
    from
        inventoryrecords
    where
        1
    order by statedateutc desc ) as t1
group by tagepc
order by statedateutc desc

yields the right data.

What in the heck is going on?  What am I missing?


texts: How to best store Google ngrams in a database?

texts:
I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database.

I am wondering how one should store Google ngrams in a database best. I mean, if you are not using onegrams, but e.g. twograms or threegrams the amount will be much larger. Can I store 500 million records in one database and work with it or should I split it to different tables?

After how many records should one split it and how should one split it best (considering that twograms has 100 files and thus probably about 5 billion records)? Is it recommended to use MySQL horizontal partitioning or rather build one’s own partitioning (e.g. via first character of word => twograms_a).


texts: SQL Service Account creation in AD using PowerShell

texts:
I'm wondering if it's possible to create a service account remotely from the SQL box using PowerShell, and put the service accounts created in the OU (folder) they belong in AD as part of a unattended SQL install.

Could this be done without having to pre-create the service accounts before SQL installation? I'm able to query the AD from the SQL box by importing the AD module.


texts: pt-online-schema-change with statement based replication in MYSQL

texts:
Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR).

What is the better way of doing it as currently I have 10 slaves and a Master.


texts: Recover raw Databases from corrupted MySQL 8 + Windows Server Implementation

texts:
I have a (Virtualized/Hyper-V) MySQL 8 (8.0.16.0) DB server whose installation has become corrupt and will no longer start the service, or report any errors as to why it won't start (this is a virtual Windows 2008R2 server).

So I went and created a brand-new, duplicate, VM from scratch, installed (the same version of) MySQL on it, and am now trying to figure out how to get the tables from the old server into the new one.

If I simply copy the entire Data folder over, as is the process described in virtually every article I've found on the subject, the new instance also won't start (exactly like the old one won't) but if I then copy a pristine MySQL.ibd file (from the clean installation) into the migrated Data folder, the service will, once-again, start normally, but none of the copied Databases/Tables will exist in it.

All I want/need to do is get the shiny, new, clean, MySQL to see and incorporate all of the old databases from their original files, but after more than a day of reading many guides on how to do this (from here and elsewhere), I have not been able to find a solution that works.

So here I am, asking again, in 2020, for MySQL 8(.0.16.0), on Windows 2008R2, what exactly do I need to do in order to fully recover these tables into a new server when some elements of the base structure are corrupt/invalid?

Thanks!


texts: Mixed data type case expression PostgresSQL

texts:
I am trying to do a case expression with mixed data types with no avail. Please could I get some help with what I'm doing wrong, please see my statement below

Case
   when c.student_age > 19
     and c.count_forms > 3
     and ss.value :: date <@ fiscal_year.f_year
       then "Y"
   else "N"
end as student_r_year

I'm using both int and date data in the first part of the case statement and that's where the issue is.


texts:
I am trying to run the below sql command:

SELECT ARRAY(
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name ='gis_field_configuration_stage'
);

and I get the below error:

ERROR:  could not find array type for datatype information_schema.sql_identifier


texts: Copy database from one mysql sever to another mysql server

texts:
Following are the details of the database and server:

Server1:
Host Name: abc
Port Num: 01
database: test

Server2:
Host Name: xyz
Port Num 02
database: spareparts
userid: spts
password: abcdefgh@123

I have to copy all tables from database test to database spareparts.

Please guide me how I can copy them. I am new in using mysql. Also please let me know if any other details are required.


texts:
I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying:

String or binary data would be truncated.

When I go to check the source table I find out that the table does not exist. It was deleted by the drop statement. I thought the second statement would not be executed because the first one (the insert) failed.

How can I write the below code so that it will fail the entire query and not drop the table unless the first part (the insert) succeeds?

Do I need to use Begin, End and Go?

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable

DROP table SomeOtherTable


texts: How to do a restore of a large postgresql database?

texts:
I'm working on restoring a backup from on-premise postgresql to another postgresql database hosted in AWS (rds-aurora postgresql, serverless). The data size is 170gb of compressed data.

I earlier thought to do using pg_restore because I can pass the -j option, but it's taking a lot of time and space to extract the files.

My file is in the format =  tar.gz  (20200204_data_tar.gz)

I tried to extract and pass it to pg_restore using pipe to save time , but got the below error. Note : backup was generated using pg_start_backup.

tar -xzOf 20200204_data_tar.gz | pg_restore
    --host=my-test.us-east-1.rds.amazonaws.com --port=5432 --username=postgresql--dbname=mytest -j 16 --password --verbose --exit-on-error --data-only

pg_restore: [archiver] input file does not appear to be a valid archive

On-premise version 9.3.4
New Cluster version is using 10.7

Can someone please suggest me options on how to do a restore for this large database?


texts:
I'm trying to run the following:

db2 alter tablespace TABLESPACE1 reduce

I keep getting errors. As you can see, I have around 50% free pages. I'd like to shrink it in order to reclaim OS disk space.

 Tablespace ID                        = 281
 Name                                 = TABLESPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8943720
 Useable pages                        = 8943696
 Used pages                           = 4559696
 Free pages                           = 4384000
 High water mark (pages)              = 8914736
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 8
 Prefetch size (pages)                = 24
 Number of containers                 = 3
 Minimum recovery time                = 2014-12-09-16.28.18.000000

  Container ID                         = 0
 Name                                 = /data/db2dat_10/sida1/TABLESPACE1_01.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /data/db2dat_11/sida1/TABLESPACE1_02.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /data/db2dat_12/sida1/TABLESPACE1_03.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes