How to "self audit" all of the SQL Server licenses at an organization? So I have started a new job with a nice non-profit organization, which has been around for a few decades. They have a wide variety of SQL Server editions and versions (ex: 2005 Express to 2016 SP2 Enterprise) and across many Windows VMs (versions anywhere from 2003 to 2016).

The previous DBAs did not complete any documentation around the number of licenses the company owns, nor editions, versions. Effectively no useful records.

I have run the MAP toolkit to gather details on the SQL Servers. So at least I've got a starting point for what servers exist, and I've dug into which are still serving up production data.

I have also used the new ApexSQL Discover tool with mixed results (sorry guys I love your stuff but I can't wait 3 days for this tool to run when I need to add 1 server). And also dbatools find-dbainstance (not a huge fan of using PS to export large amounts of data). So by piecing this all together, I'm fairly certain at this point that I've discovered most or all of the instances that ever existed on this domain.

Of course, I've queried the licensing in a number of ways and thoroughly examined the completely useless extremely helpful MS SQL licensing guide of which nothing seems to provide any information on the licenses that the organization owns or how to acquire it.

The crux of the issue: Some of these licenses were purchased a decade (or two) ago. Some were surely paper invoices. Remember I mentioned this is a non-profit? On the plus side, licensing is cheap. On the other hand, licensing is cheap, so most or all of the licenses do not have SA because it's cheaper to buy new ones. edit: This was the reasoning at the time for not purchasing SA, I was not involved.

Should I just assume that since most likely none have SA--that the server is also likely to be licensed properly--and just buy new ones as needed? That seems like a horrible approach.

My question:

How the heck do I know what SQL Server licenses my new company owns without trying to dig up paper invoices from 2004? I've never been in this situation before. Is my only option to get audited or pay for a partner to audit? (and if so, Microsoft what is you doin' bb??).

Any insights that dba.se can provide here will probably add years to my life. Thanks all!

[…] nothing seems to provide any information on the licenses that the organization owns or how to acquire it.
How the heck do I know what SQL Server licenses my new company owns […]

If you have a Premier Agreement
This is fairly straight  forward, you will have an Account Manager assigned (TAM) which can pull up any licensing information given to Microsoft (assuming it was bought through a licensed 3rd party or part of your Premier contract).
From the above you can then divvy out the licenses as needed/used based on your environment.
If you don't have a Premier Agreement
In this case you'll need to log-in/contact/find all records related to licensing on your own. This might mean looking for paper records from 2004 as you've suggested, contacting whatever 3rd party reseller of licensing you went through in those periods of time, or tracking down POs to find out if anything was bought and where it was bought.
The last ditch possibility under this item is to setup a time to speak with a licensing specialist or a Microsoft account representative to see if they can pull anything from previous agreements of years. Note that if you don't have SA, then the licenses may not be applicable to your environment. Also note there are older style licenses that also may no longer be applicable to the environment.

[…] most or all of the licenses do not have SA because it's cheaper to buy new ones.

SA gives other benefits than just being allowed to be at any version. It mainly focuses benefits around license mobility, HA/DR, virtualization, and hybrid scenarios. It's also generally cheaper than buying net new licenses.


Sub query with not equal Sub query with not equal

table_1

col1
SV0362
RU0177CSPG
NO0124CSPG
VN3582
AT9923CSPG
BZ0324
PE0309
AS4293EXPT
ML0331OMKT

table 2

colA
OO56128665
OO56128665
OO56128665
OO56128665
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
OO56128665
OO56128665

I want to compare col1 of table1 with colA of table 2 and return the values which are not qual

something like this

select * from table1  where col1 <> (select colA from table_2 a,table1 b where  b.col1=a.colA)

but this would give single row returns more than one subquery

You just need to use NOT IN

SELECT * FROM table1 WHERE col1 NOT IN (SELECT colA FROM table_2)


Hold exclusive lock on a whole DB As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance.

These tables are only written by an ETL that runs daily, then they are only read. Therefore, I'll move these tables' indexes to a separate filegroup and keep this filegroup as readonly. When my ETL runs, I must set the filegroup to read-write, run the ETL, then set it back to readonly.

The issue is that, for doing so, I must run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, and for this operation to run there can't be any other transaction running on the whole DB.

ALTER DATABASE MyDB SET SINGLE_USER isn't an adequate solution, because I also can't let my ETL kick/rollback other jobs. It must wait them to finish, keep them blocked while it runs, then let them finish transparently. Also, with that operation any transaction could grab a lock on the database while ETL is running, and block my ETL.

I really don't know the best solution for this, I'm open to any suggestion. My current idea is to request an exclusive lock on the DB, so that I can run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY without breaking any other transaction. In the worst case scenario, I'd keep a loop asking for the lock until I find the DB free and grab it.

I don't need the lock/single_user during the whole ETL. Only for setting the filegroup to readonly and read-write.

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:

  For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).

Setting the filegroup to read only doesn't actually improve performance. It might appear that way if there are users/jobs that update the data while most users are just reading data (blocking) but that's a misinterpretation of your observation.

A common misconception is that it saves on locking however that would require the database to be read only not just the specific file group (Source1, Source2).

From your description, it looks like nobody else apart from the ETL job should have write access to the table. That means, all users are read only by definition. So what makes you think further making the table read only will help performance? Have you observed other users writing to the database hence causing blocking? If so why not just secure the table and allow only the account used for ETL to write data while all other accounts can only read? Much easier to do, far less intrusive plus it enforces your rule of only ETL can write, all others can only read.


Possible to use libmysqlclient-dev.lib functions (native c client) with mySQL Router? I've setup the mysql innodb cluster with some difficulty, and have mysqlrouter running and listening on ports with the correct IP address. I then have a C program trying to connect to mysqlrouter to submit queries to, but the function, mysql_connect() gives me an error: "No such file or directory".

It's likely it was looking for a mysqld-specific file. Has anyone managed to setup communications between a C program and mysqlrouter?

Edit: Fixed, have to setup permissions for the IP address mysqlrouter is on
https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-general-using-developing.html

The immediate answer, without more information, is that you are trying accessing MySQL (the protocol, not the server), using unix socket/named pipes, which is the only reason I can think from the top of my mind why a mysql connector may want to open a file. Check your connection options and make sure you are connecting using TCP, with a host, port combination.

It could be something else, but it is what I would try debugging first, for other issues we would need more context and code.


Database design - summary table vs always counting over tables Imagine a system where a user can make a whole lot of different actions, like:

Making a purchase
Liking something
Opening a specific view (pretty much every click)
Inviting a friend
...

The actions are stored in tables, example: purchase_log, invite_log etc. With "log", I mean the action history of our users. Not system logs.

The actions need to be acted upon, examples:

Display user statistics (when opening profile view for example)
Counting actions toward giving achievements (by a separate service, performed once a minute or so for all users)

My questions:

Is it bad practice to have a separate table like user_statistics where a simple count is incremented, instead of counting/searching over the xxx_log-tables? This obviously breaks the normalisation rules.
Does the answer change depending on system load / size of application?
Does the answer change when considering transaction locks / concurrency issues?
Does the answer change with SQL vs NoSQL?
Does the answer change with sync vs async systems?

I think the answer will vary depending upon what you want to achieve.

As you have mentioned that these things need to be acted on analysed, reported & displayed then I would consider that having the data in tables would be advantageous.

The decision to calculate summary data vs calc on the fly depends upoon size volume and scalability and accuracy required. A mixture of real time and batch processing might be viable and practical approach.

I would expect performance would be an important consideration. Do end users need to see their stats updated immediately? Or are you collecting this data to drive marketing analysis which can happen periodically at a later date.

Go back to your requirements. What data do you need. Who needs it. When do they need it. How fast & how often. Do they need raw data or aggregated. Detail or Summary? How much data are you expecting to collect. How long do you need to retain it? What are the storage requirements and how will that perform?

If you can answer the questions above you may narrow down your choices and your remaining options will be clearer.


Oracle impdp error when importing data I get this error ORA-02289: sequence does not exist when I try to import a schema to my database, could you give me some recommendation?

Import Command:

impdp '"sys/mmm as sysdba"' directory=BACK_IMPORT dumpfile=0502190800%U.dmp
logfile=impdp_MBD.log SCHEMAS=MSCHEM PARALLEL=7 encryption_password=0qazw

Export Command:

expdp '"sys/mmm as sysdba"' encryption=all encryption_password=0qazw
encryption_algorithm=aes256 COMPRESSION=ALL DIRECTORY=BACK_EXPORT
COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL flashback_time=systimestamp
DUMPFILE=$name% U.dmp LOGFILE=$name.log PARALLEL=7 SCHEMAS=MSCHEM,IOOUT,ZHIST;

You are importing MSCHEM schema. Please check in source database that one of TABLE/Procedure/Package using a sequence that exist in another schema. If so, don't worry just recreate it. you can find this sequence name in import log also.


Completion Counting only Once Have an SQL query for DB2 that returns the timestamp of when an event completed. It completes at 2 different date/times, which is the result expected.

That result completes at those 2 different times for the same entity. I desire that the entity be counted one time for the purposes of my query.

How would I get the query to return the result for only the first instance of completion for that entity?

You don't specify what "first" means (or provide any table structure or example data), but let's assume it's the earliest completion timestamp:

  SELECT entity, min(completion_ts)
    FROM your_table
GROUP BY entity

If you are just getting results for a single entity, you can simplify even more:

  SELECT min(completion_ts)
    FROM your_table
   WHERE entity = 'XXX'


Turning on TF610 in SSIS I have a simple SSIS package that loads a dummy file that contains 100000 rows.  Each row is around 4k long, one int column and one long text column.  I'm trying to test TF610 when loading this data into a table with a clustered index.

In my SSIS package, my Control Flow has a Execute SQL task to enable TF610, then on success go to my Data Flow Task which loads the flat file into the table.  Both the Execute SQL and OLE DB Destination use the same Connection.

If I start a profile while running the SSIS package, and watch the commands, I can see DBCC TRACEON(610) executed then the INSERT BULK operations begin to fire.  They both are using the same PID, so I'm assuming it's the same session.

When I check the log record length though, the insert is NOT being minimally logged.

If I enable TF610 globally and run the same SSIS package though the transaction is minimally logged.

I must be doing something wrong when turning on the TF610 in my SSIS package but I can't figure out what...

And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.


Grouping data with an interval but with overlapping time range groups I currently have constructed a group by query in which returns a list of time with a 15 minutes interval and its list of count aggregate

SELECT
    CONVERT(DATE, DATETIME_COLUMN) AS DATE,
    CONVERT(TIME(0), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')) AS TIME_START_INTERVAL,
    DATEADD(SECOND, -1, CONVERT(TIME(0), DATEADD(MINUTE, 60, DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')))) AS TIME_END_INTERVAL
FROM
    TABLE
GROUP BY
    CONVERT(DATE, DATETIME_COLUMN),
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')

However, this will only group them by 15 minutes. IS there any way that I can have them grouped as shown in the image? Overlapping of data is expected as the time range overlaps.

Below is one technique you could use to get counts for the overlapping ranges. This method this uses an inclusive start and exclusive time range to ensure datetime values with fractional seconds don't get dropped on the floor (e.g. '2018-01-28 00:59:59.123') but still reports the end time without fractional seconds like the data in your question.

WITH
    intervals_15_minutes AS (
        SELECT DISTINCT
             DATEADD(MINUTE, (DATEDIFF(MINUTE, '', DATETIME_COLUMN) / 15)*15, '') AS TIME_START_INTERVAL
        FROM dbo.YourTable
    )
SELECT
      CAST(TIME_START_INTERVAL AS date) AS DATE
    , CAST(TIME_START_INTERVAL AS time(0)) AS TIME_START_INTERVAL
    , CAST(DATEADD(second, -1, DATEADD(MINUTE, 60, TIME_START_INTERVAL)) AS time(0)) AS TIME_END_INTERVAL
    , COUNT(*) AS OVERLAPPING_COUNT
FROM  intervals_15_minutes
JOIN  dbo.YourTable ON
    YourTable.DATETIME_COLUMN >= intervals_15_minutes.TIME_START_INTERVAL
    AND YourTable.DATETIME_COLUMN < DATEADD(MINUTE, 60, TIME_START_INTERVAL)
GROUP BY
      intervals_15_minutes.TIME_START_INTERVAL;


referntial integrity issue with two tables cant delete a record So I am trying to delete a record from customers table. I would assume it's a referential integrity issue, however I don't really see what is wrong with it. Could anyone shed some light? Please and Thanks. Error Message below

 DELETE FROM customers
        WHERE customer_id = 1

table structures:

    CREATE TABLE  payment_details  (
    paytype_id varchar(10),
   card_no  int(8) NOT NULL,
   customer_id  int(50)  NOT NULL,
   CVV  int(3) NOT NULL,
   card_type  ENUM ('Visa', 'Amex', 'American Express', 'Mastercard') NOT NULL,
   expiry_date  DATE NOT NULL,
   PRIMARY KEY(paytype_id),
   KEY customer_idfk1 (customer_id),
   CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE  customers  (
   customer_id  int(50)  NOT NULL AUTO_INCREMENT,
   fname  varchar(20) NOT NULL,
   lname  varchar(20) NOT NULL,
   dateOfBirth date NOT NULL,
   address  varchar(30) NOT NULL,
   city  varchar(20) NOT NULL,
   county  ENUM('Armagh','Carlow','Cavan','Clare','Cork','Derry','Donegal','Down','Dublin','Fermanagh','Galway','Kerry','Kildare','Kilkenny','Laois','Leitrim','Limerick','Longford','Louth','Mayo','Meath','Monaghan','Offaly','Roscommon','Sligo','Tipperary','Tyrone','Waterford','Westmeath','Wexford','Wicklow') NOT NULL,
   phone  int(10) NOT NULL,
   email  varchar(20) NOT NULL,
   payment_method  ENUM ('Visa', 'Amex', 'Cash', 'American Express', 'Mastercard') NOT NULL,
   valid_licence varchar(5) NOT NULL,
   status  varchar(10),
   PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  error  - #1451 - Cannot delete or update a parent row: a foreign key
  constraint fails (g00337857abu.payment_details, CONSTRAINT
  customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers
  (customer_id))

You have a foreign key from payment details to customers.

This is a declarative referential integrity constraint which enforces that you can’t have a row in payment details (‘child’ row) for which the customer is is not in customers (‘parent’).

The idea is to maintain data consistency - if you had an ‘orphan’ child row without a ‘parent’ Customer, you would never know which customer made the payment.
If you want to delete the customer and his payments, you can either delete the payments first:

DELETE FROM payment_details where customer_id = 1

Or you can define the foreign key as a cascading one in which case the engine will delete it for you:

CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE


SQL Server Login can't see databases As a test of theory (because I want to brush up on my SQL Server Security understanding) I want to set up a SQL Server Login that can only see a few fields on one table in one database.

I have not assigned any roles to this login except the default public. I don't want the hypothetical user to have any server role (ie not sysadmin, dbcreator)... and I don't want to use any standard role for the database (db_datareader etc)...

The login has a default database, and the login is mapped to the database (default schema dbo).  Now in the user securables I have granted access to one table and used the column permissions to specify the columns but I'm really not getting this far when testing the login.

When I log into the SSMS with SQL Server authentication and the new login, I don't see any databases except the master/tempdb...

Should I be seeing my default/mapped database?  I don't see any other questions asked quite like this so I am guessing I am missing something completely obvious.

In order to log into SSMS with a sql login, do you HAVE TO HAVE an assigned server role to see non system databases? I can see the databases when I add sysadmin but then I am right back to over granting.

CREATE LOGIN [Chris_Test2] WITH PASSWORD=N'¯\Ï<ÞZcÅÎ5¿ûY!ËDåÜ''¨Äs»¾Þja7',
DEFAULT_DATABASE=[Finance_Receivables_TEST],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

USE [Finance_Receivables_TEST]
GO
/****** Object:  User [Chris_Test2]    Script Date: 01/18/2017 14:40:21 ******/
GO
CREATE USER [Chris_Test2] FOR LOGIN [Chris_Test2] WITH DEFAULT_SCHEMA=[dbo]

It sounds like you have removed the View Any Database permission from the Public role.  If the Public role doesn't have the View Any Database permission an account has to be the owner of the DB to see it's row in sys.databases.


Lock a table in Sybase ASE 15.5 I would like to lock a table to only allow users to perform read operation in Sybase ASE 15.5, how can I do it? I have tried the below command, but seems there is some confusion. Any idea?

Session A: Perform below commands and after that try to perform insert operation. New records can still insert into locked table (but I think it is not commit the transaction yet). Can still select record from the locked table.

begin transaction
lock table testB in share mode

Session B: Cannot select data from the locked table.

I think you should look at locking mechanism of table, there are three types of them - AllPages, Datapages and Datrows, you can read more about them at:

Locking Mechanism in Sybase

If you alter locking of table, update/delete and select can take place together without any blocking on mentioned table.

Inside query, you can put noholdlock on a table or other options for other users to access/no access based on these options as mentioned in below link:

Controlling Isolation

In order to answer your question in detailed level, we need to have more clarity on question. Your question doesn't explain details of issue. I hope above helps.


How to use grouping sets in this query that shows database sizes - adding the last time it was updated I have the following query that shows me the database sizes, including log dna data files.

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

     --  LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update))
        --             from sys.dm_db_index_usage_stats dius
                    --where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id))

                     --(DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

and that gives me the following picture in one of my test servers:

I would like to add the last time the database was updated to the results of my query, as you can see on the commented lines in the query above, but that is giving me the following result set:

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

       LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update))
                     from sys.dm_db_index_usage_stats dius
                    where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id)),

                     (DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

How can I change my query to display the last time the database was updated?

I just removed the DB_Name from Grouping

SELECT [Database Name] = DB_NAME(s.database_id),
       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'
                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'
                     ELSE s.Type_Desc END,

       LastUpdate= case when s.type_desc ='ROWS' then NULL
                       when s.type_desc = 'LOG' then NULL
                       else
                           (select COALESCE(max(last_user_update),MAX(last_system_update))
                                         from sys.dm_db_index_usage_stats dius
                                        where dius.database_id = s.database_id)
                    end,
       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
GROUP BY      GROUPING SETS
              (
                     (s.database_id, s.Type_Desc),
                     (s.database_id)
              )
ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

output:

Database Name   Type         lastUpdate  Size in MB Size in GB
db_xxx          Log File(s)   Null            8.00     0.01
db_xxx          Data File(s)  Null            8.00     0.01
db_xxx          NULL          2019-07-14 17:27:04.473  16.00       0.02


Select rows where one row meets a condition I have a table with 3 columns:

UserId  ProductId    Status
1       100          1
1       1            0
2       100          0
4       100          0
5       1            1
5       100          1
6       2            1

I want to select all rows that have ProductId = 100 at least once per user and the user has at least one more record other than with ProductId = 100.

Expected results:

UserId  ProductId    Status
1       100          1
1       1            0
5       1            1
5       100          1

I solved it like this by referring to another solution:

select "ProductId", "UserId", "Status"
from "MyTable"
WHERE EXISTS (
    SELECT 1 FROM "MyTable" WHERE "ProductId" = 100
)

But it returns all users where even one record is 100.

If you can handle both products on the same row:

select t1.UserId, t1.ProductId, t1.Status,
       t2.ProductId as otherproduct, t2.Status as otherproductstatus
from MyTable t1
join MyTable t2
  on t1.UserID=t2.UserID
     AND t1.ProductID=100
     AND t2.productID!=100

However with this you'll get all products for a user:


Cannot log in to the MySQL server Cannot log in to the MySQL server they say mysqli::real_connect(): (HY000/1045): Access denied for user 'Stephen Ankoh'@'localhost' (using password: YES)

Login as root and grant access privileges to user
GRANT ALL PRIVILEGES ON dbname.* TO 'yourusername'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'yourusername'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
restart mysql /mariadb service.
it will grant user with local as well as remote host(if you want).


Are these database tables normalized? I am developing a webapp for an institute and I designed these tables:

Are these tables normalized?

Are they normalised?

No.

Student has Mob1 and Mob2 fields.


Populating an empty table using loops, conditionals, and accessing multiple tables This question pertains to a MySQL database, although, in the end, this may be database neutral.

I am trying to populate an empty table (let's call it NewTable) that I created. The data population is based upon retrieving a bunch of data from other tables, along with occasional checking of that data to create a given value. The crux is that I want to get this data for every person found in a given table, let's call it table A. And there are conditionals involved too as I retrieve some of this data. Here is some pseudocode for it….

For each personID in TableA
    select statistic from a column in TableA for that personID as column 1 of NewTable
    select statistic from a column in TableB for that personID as column 2 of NewTable
    select statistic from a column in TableC based upon a value for that personID found in another TableD
          If selected statistic has value X, then assign column 3 of NewTable a certain value; otherwise assign column 3 of NewTable another value
    :
    :
End Loop after data retrieved for last personID in TableA

NewTable should now be populated after this loop ends.

Here is a bit more specific information based on the comment below:

**TableA:**

PersonID : char(6)
Points : Decimal(3,1)

**TableB:**

PersonID : char(6)
Points : Decimal(3,1)

**TableC:**

UID : tinyint(6)
Seas : int(10000)
Wk : tinyint(20)
V : char(3)
H : char(3)

**TableD:**

PersonID : char(6)
Grp : char(3)

**NewTable:**

PersonID: char(6)
Points : Decimal(3,1)
Points_Alt : Decimal(3,1)
Pattern : tinyint(6)

For each PersonID AS current in TableA:
   SET current AS PersonID IN NewTable
   SELECT Points FROM TableA WHERE PersonID = current AS Points IN NewTable
   SELECT Points FROM TableB WHERE PersonID = current AS Points_Alt IN NewTable
   For each UID in TableC WHERE Seas = 2011 AND Wk = 10:
        if V == SELECT Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 3 AS Pattern IN NewTable
        else if H = select Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 6 AS Pattern IN NewTable
    End Loop
End Loop

Can anyone provide me an example of how to do this using a SQL query? Some actual example SQL code would be very helpful.

Thank you.

I might be misunderstanding your question; however this should work.  (The code is written for SQL Server since I don't have MySQL experience.)

CREATE TABLE TableA (PersonID INT, Points decimal(10,2));
CREATE TABLE TableB (PersonID INT, Points decimal(10,2));
CREATE TABLE TableC (UID INT, Seas INT, Wk INT, V char(3), H char(3));
CREATE TABLE TableD (PersonID INT, Grp char(3));
CREATE TABLE NewTable (PersonID INT, Points decimal(10,2),
        Points_Alt decimal(10,2), Pattern int);

INSERT INTO TableA VALUES (1, 10);
INSERT INTO TableB VALUES (1, 23);
INSERT INTO TableA VALUES (2, 11);
INSERT INTO TableB VALUES (2, 24);
INSERT INTO TableC VALUES (3, 2011, 10, 3, 4)
INSERT INTO TableC VALUES (4, 2011, 10, 5, 6)
INSERT INTO TableD VALUES (1, 3);
INSERT INTO TableD VALUES (2, 6);

INSERT INTO NewTable (PersonID, Points, Points_Alt)
SELECT TableA.PersonID, TableA.Points, TableB.Points AS Points_Alt
FROM TableA
    INNER JOIN TableB ON TableA.PersonID = TableB.PersonID;

UPDATE NewTable
SET Pattern = 3
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011
    AND TableC.Wk = 10
    AND TableC.V = TableD.Grp;

UPDATE NewTable
SET Pattern = 6
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011
    AND TableC.Wk = 10
    AND TableC.H = TableD.Grp;

SELECT * FROM NewTable

The first statement creates the data in NewTable and the 2 remaining statements set Pattern to the particular value based on either H or V as in your pseudo-code.


PL/pgSQL function + client-side lo_import I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this:

create function add_file(flag integer, sth varchar) returns void as
begin
   if flag = 1 then
      insert into tab_one values (my_file_oid, sth);
   else
      insert into tab_two values (my_file_oid, sth);
   end if;
end;

I'm using the psql command:

\lo_import('path/to/file');

Both the function and the lo_import command are in the same .sql file.

I cant change the lo_import() to an INSERT statement, becouse I need client-site lo_import. There is variable LASTOID, but it is not avaible in the add_file function, and it wouldnt get updated updating on every call add_file().

So, how can I insert a large object into the database with, in our example, 'flag' and 'sth' by insert statement and everything in function with arguments?

The file to be loaded is on the client computer.

psql is the wrong tool for this kind of job because it basically can't get results into variables. It's good for user interface and static scripts, but for
interactive scripting where results from one operation are to be piped into another, it falls short. The best solution is to switch to a real scripting language such as Perl or Python that have good client-side APIs, including large-objects streaming.

When limited to bash, it's still possible to operate psql from the outside with coproc to get results out and back in, but it's much more complicated.


how to convert from raiserror to throw in the current context? I have a function that checks is the file is present on disk in an specific location.

Using that function I am checking for the latest backup, in the code below.
If we don't have a backup anymore, for a specific date, I want to raise an error.

Currently I am using raiserror but
because of Deprecated raiserror detection I would like to use throw instead.

How can I do it?

this is part of my `stored procedure`:

IF @ServerNameToRestoreOn IS NULL
               THROW 50001, '@ServerNameToRestoreOn cannot be NULL - it must be the name of server\instance you want to restore the database to', 1

               IF @Database IS NULL
               THROW 50001, '@Database cannot be NULL - it must be the name of the database you want to restore', 1

               IF NOT EXISTS
                (
                    SELECT MAX(backup_set_id)
                    FROM msdb.dbo.backupset a
              INNER JOIN msdb.dbo.backupmediafamily b
                     ON a.media_set_id = b.media_set_id

                    WHERE a.database_name = @Database
                      AND a.type='D'
                      AND a.is_copy_only = 0
                      AND a.backup_finish_date < @StopAt
                      AND master.dbo.fn_FileExists(b.physical_device_name) = 1
                    GROUP BY database_name
                )
                BEGIN

                         SELECT @sql = 'The database called "%s" does not have a backup before' + CAST (@StopAt AS VARCHAR(20)) + @vCrlf + @vCrlf
                         RAISERROR(@sql ,16,1,@Database)

                END

How can I replace the raiserror by throw?
I want to show the @Database and the @stopat in the error message

or, in better wording:

how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR?

I think this is what you mean? This is the closest you can get AFAIK.

--Catch any errors, include stored proc name for stack trace & cheeky link to SE
BEGIN CATCH;
       IF (XACT_STATE()) = -1 OR ((XACT_STATE()) = 1 AND (@@TRANCOUNT = 0 OR @@TRANCOUNT > 0))
          BEGIN
             ROLLBACK TRAN;
             DECLARE @errMsg NVARCHAR(MAX)= FORMATMESSAGE('%s: %s Try troubleshooting: http://dba.stackexchange.com/search?q=msg+%i', OBJECT_NAME(@@PROCID),ERROR_MESSAGE(),ERROR_NUMBER());
             THROW 51000, @errMsg, 1; --Throw error
          END
END CATCH


Row Level Trigger for insert/update to a table Given a table ITEM as below :
CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) not null,
CONSTRAINT pk_item primary key (ItemNo)
);

I need to write a row-level trigger called update_item that will fire whenever Quantity and/or Price is inserted/updated. When this happens, the Total column will be set by the trigger as the product of Quantity and Price.
Any help will be appreciated.

In MySQL you need to make deferent trigger for updating and Inserting;
DELIMITER $$

CREATE TRIGGER before_ITEM _insert
BEFORE INSERT
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;
END $$

DELIMITER ;

CREATE TRIGGER before_ITEM _UPDATE
BEFORE UPDATE
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;
END $$

DELIMITER ;

But for that purpose you can use generated Columns like
CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) GENERATED ALWAYS AS (Quantity * Price),
CONSTRAINT pk_item primary key (ItemNo)
);


Duplicate row with Primary Key in PostgreSQL Assume I have a table as follows named people, where id is a Primary Key:

+-----------+---------+---------+
|  id       |  fname  |  lname  |
| (integer) | (text)  | (text)  |
+===========+=========+=========+
|  1        | Daniel  | Edwards |
|  2        | Fred    | Holt    |
|  3        | Henry   | Smith   |
+-----------+---------+---------+

I'm trying to write a row duplication query which is robust enough to account for schema changes to the table. Any time I add a column to the table, I don't want to have to go back and modify the duplication query.

I know I can do this, which will duplicate record id 2 and give the duplicated record a new id:

INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;

However if I add an age column, I'll need to modify the query to also account for the age column.

Obviously I can't do the following, because it will also duplicate the primary key, resulting in a duplicate key value violates unique constraint -- And, I don't want them to share the same id anyway:

INSERT INTO people SELECT * FROM people WHERE id = 2

With that said, what would be a reasonable approach to solving this challenge? I would prefer to stay away from stored procedures, but I'm not 100% against them, I suppose ...

Simple with hstore

If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns:

Basic example: duplicate the row with id = 2 but replace 2 with 3:

INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

Details:

How to set value of composite variable field using dynamic SQL
Assign to NEW by key in a Postgres trigger

Assuming (since it's not defined in the question) that people.id is a serial column with an attached sequence, you'll want the next value from the sequence. We can determine the sequence name with pg_get_serial_sequence(). Details:

PostgreSQL SELECT primary key as "serial" or "bigserial"

Or you can just hard-code the sequence name if it's not going to change.
We would have this query:

INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

Which works, but suffers from a weakness in the Postgres query planner: The expression is evaluated separately for every single column in the row, wasting sequence numbers and performance. To avoid this, move the expression into a subqery and decompose the row once only:

INSERT INTO people
SELECT (p1).*
FROM  (
   SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
   FROM   people p WHERE id = 2
   ) sub;

Probably fastest for a single (or few) row(s) at once.

json / jsonb

If you don't have hstore installed and can't install additional modules, you can do a similar trick with json_populate_record() or jsonb_populate_record(), but that capability is undocumented and may be unreliable.

How to set value of composite variable field using dynamic SQL

Transient temporary table

Another simple solution would be to use a transient temporary like this:

BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

I added ON COMMIT DROP to drop the table automatically at the end of the transaction. Consequently, I also wrapped the operation into a transaction of its own. Neither is strictly necessary.

This offers a wide range of additional options - you can do anything with the row before inserting, but it's going to be a bit slower due to the overhead of creating and dropping a temp table.

This solution works for a single row or for any number of rows at once. Each row gets a new default value from the sequence automatically.

Using the short (SQL standard) notation TABLE people.

Dynamic SQL

For many rows at once, dynamic SQL is going to be fastest. Concatenate the columns from the system table pg_attribute or from the information schema and execute it dynamically in a DO statement or write a function for repeated use:

CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int) AS
$func$
BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column
      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$  LANGUAGE plpgsql;

Call:

SELECT f_row_copy('people', 9);

Works for any table with an integer column named id. You could easily make the column name dynamic, too ...

Maybe not your first choice since you wanted to stay away from stored procedures, but then again, it's not a "stored procedure" anyway ...

Related:

psql: SELECT * ... except one column
How do I list all columns for a specified table

Advanced solution

A serial column is a special case. If you want to fill more or all columns with their respective default values, it gets more sophisticated. Consider this related answer:

Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3


When next will my fullback up run I created a maintenance plan to run full backups on a SQL Server 2008 R2.

The schedule runs every last Thursday of the month. Sometimes due to some unknown reason, such as electric outage. When the backup is successful, I verify it by checking SQL Server report for backup and restore events.

Now my question is: does SQL Server provide a way to check the next time the maintenance plan will run based on the the maintenance plan schedule? Also is there is a script to run to show me this would be appreciated?

This script will provide you with detailed information on SQL Server Agent Jobs and their schedules. This doesn't include any scheduled task that may exist in Scheduled Tasks for Windows.

USE msdb
Go

SELECT dbo.sysjobs.Name AS 'Job Name',
    'Job Enabled' = CASE dbo.sysjobs.Enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,
    'Frequency' = CASE dbo.sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
    END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
    END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END,
--  active_start_time as 'Start Time',
    CASE len(run_duration)
        WHEN 1 THEN cast('00:00:0'
                + cast(run_duration as char) as char (8))
        WHEN 2 THEN cast('00:00:'
                + cast(run_duration as char) as char (8))
        WHEN 3 THEN cast('00:0'
                + Left(right(run_duration,3),1)
                +':' + right(run_duration,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(run_duration,5),1)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 6 THEN cast(Left(right(run_duration,6),2)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
    END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
        WHEN 0 THEN 'Once'
        ELSE cast('Every '
                + right(dbo.sysschedules.freq_subday_interval,2)
                + ' '
                +     CASE(dbo.sysschedules.freq_subday_type)
                            WHEN 1 THEN 'Once'
                            WHEN 4 THEN 'Minutes'
                            WHEN 8 THEN 'Hours'
                        END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
        FROM dbo.sysjobhistory
        GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
    'Job Enabled' = CASE dbo.sysjobs.Enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,
    'Frequency' = CASE dbo.sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
    END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
    END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END,
--  next_run_time as 'Start Time',
    CASE len(run_duration)
        WHEN 1 THEN cast('00:00:0'
                + cast(run_duration as char) as char (8))
        WHEN 2 THEN cast('00:00:'
                + cast(run_duration as char) as char (8))
        WHEN 3 THEN cast('00:0'
                + Left(right(run_duration,3),1)
                +':' + right(run_duration,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(run_duration,5),1)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 6 THEN cast(Left(right(run_duration,6),2)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
    END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
        WHEN 0 THEN 'Once'
        ELSE cast('Every '
                + right(dbo.sysschedules.freq_subday_interval,2)
                + ' '
                +     CASE(dbo.sysschedules.freq_subday_type)
                            WHEN 1 THEN 'Once'
                            WHEN 4 THEN 'Minutes'
                            WHEN 8 THEN 'Hours'
                        END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
        FROM dbo.sysjobhistory
        GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

Update 11/4/2013

Here is another script that associates plans with their associated jobs

SELECT
s.name as [PlanName],
spl.start_time AS [StartTime],
spl.end_time AS [EndTime],
spl.succeeded AS [Succeeded],
sysjobs.name as [JobName],
'Frequency' = CASE sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
LEFT OUTER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id
INNER JOIN msdb.dbo.sysjobs sysjobs on sysjobs.job_id = sp.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id


Is this database fragmentation or something else? What can be done? I have the table below in PostgreSQL (automagically generated with insertions using Marten):

create table mt_doc_order
(
    id               uuid                                                                                not null
        constraint pk_mt_doc_order
            primary key,
    data             jsonb                                                                                not null,
    mt_last_modified timestamp with time zone default transaction_timestamp(),
    mt_version       uuid                     default (md5(((random())::text || (clock_timestamp())::text)))::uuid not null,
    mt_dotnet_type   varchar
);

I am inserting a lot of data to see if there is at some point a bottleneck in terms insertion performances.

I recorded the insertion time for every 5000 records inserted and here is below a graph showing the number of records inserted in the table (x-axis), and the insertion time (y-axis):

It seems that around 25,000,000 records the database needs more time for insertion, I supposed that there is something related to the update of structure maintaining the primary key (ie. B-Tree?).

I thought at some point that it was an effect of fragmentation but as far as I know fragmentation is more something that have to do with update / delete in table filled with index(es) so that with Postgre you need to do some vacuum command to ease the b-tree maintaining those indexes.

Plus it looks that even though the auto-vacuum dameon was running, there was no vacuum command after more than 25 millions records inserted:

root@4e862966a7ad:/# ps -axww | grep autovacuum
   63 ?        Ss     0:00 postgres: autovacuum launcher
  139 pts/0    S+     0:00 grep autovacuum

SELECT
    schemaname, relname,
    last_vacuum, last_autovacuum,
    vacuum_count, autovacuum_count  -- not available on 9.0 and earlier
FROM pg_stat_user_tables;

gives:

| schemaname | relname     |last_vacuum| last_autovacuum |vacuum_count| autovacuum_count |
|------------|-------------|-----------|-----------------|------------| -----------------|
| public     | mt_doc_order|   <null>  |    <null>       |      0     |         0        |

So I am wondering:

How do you call that effect of there is relatively a "lot" of data that seems to trigger some sort of bottleneck upon insertion performance?
Is there anything that can be done  to improve the insertion performance once we reach that amount of data in one table?

A performance knee is pretty common when inserting into an indexed table when the indexed values are not inserted in order.  That is probably the case here, if your uuid values are generated in a way that they do not order chronologically.

Every insertion dirties a random leaf page in the index.  As long as all the leaf pages fit in shared_buffers (and your checkpoints are far enough apart), this is not much of a problem as you can "re-dirty" the same leaf pages over and over, but separate by a substantial amount of time, without much consequence. But once you have enough leaf pages that they exceed shared_buffers, that means every insertion ejects a random dirty page to the kernel, and the kernel then tries to write out random isolated pages to disk, which has horrible performance. In theory you should be able to tune the kernel to maximize the amount of write consolidation, but I've never had good luck with this.

You can put off the performance collapse by making shared_buffers be a large fraction of RAM.  But eventually (if you have enough data) that will still be exceeded.  The solutions that work beyond this point are to leave the primary key off until the table is populated and then add it afterwards, or to make sure you are inserting the rows in approximately the same order as the index order.


Database schema initially not designed for scaling, how to optimize it? I developped 2 years ago a back office for helping a logistic company to manage the deliveries.
At this time, the company only had one "partner" (let's call it Partner A).

The main table is delivery_note.

I tried to design the database to be the more generic as possible.
So I decided to put all the partner related fields in a dedicated table.

But two years later, we have 5 partners (and more are incoming) and I don't like to add a new FK in delivery_note each time we add a new partner.

The relation between a delivery_note and a partner is 1 1 (optional). In practice a delivery_note can be linked to only one partner.

The schema looks like:

Do you know any pattern or way to design a more flexible schema to handle this case ?

Thank you

I find your design a bit backword, can a delivery_note_partner_A exist without a corresponding delivery_note?
Assuming that is not the case, I would suggest:
CREATE TABLE delivery_note
( delivery_note_id ... NOT NULL PRIMARY KEY
, ...
);

Depending on how much processing of the notes for each partner you are going to do, you may consider something like:
CREATE TABLE delivery_note_partner
( delivery_note_partner_id ... NOT NULL PRIMARY KEY
, delivery_note_id ... NOT NULL REFERENCES delivery_note (delivery_note_id)
, delivery_note JSON NOT NULL
);

Whether the partner should reside in the JSON doc or as an attribute is a decision to make
EDIT:
After some discussion in a separate thread, this is the suggestion I proposed:
-- removed attributes delivery_not_parter_X
CREATE TABLE delivery_note (
 delivery_note_id int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
...
PRIMARY KEY (delivery_note_id),
UNIQUE KEY `UNIQ_1E21328E551F0F81` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- sample data with above attributes removed
INSERT INTO `delivery_note` (`delivery_note_id`, `status`, `seller_name`, `first_name`, `last_name`, `email`, `street`, `street_extra`, `post_code`, `city`, `lat`, `lng`, `phone_number`, `phone_number2`, `residential_type`, `floor`, `elevator`, `service_type`, `order_number`, `order_picker_name`, `order_flow_origin`, `delivery_day`, `delivery_hour_start`, `delivery_hour_end`, `observation`, `internal_observation`, `created_at`, `updated_at`, `delivered_at`, `route_plan_order_id`, `route_customer_id`, `route_route_id`, `delivery_dday`) VALUES
(1, 'delivered',    'Sauvage',  'Adrien',   'Thibault', 'spotier@wanadoo.fr',   '16 rue des violettes', NULL,   '77500',    'Chelles',  48.89320500,    2.59495500, '0476134831',   NULL,   'house',    'fourth',   0,  'comfort',  '32027253', NULL,   'store_order',  '2020-01-02 00:46:52',  '2019-12-30 15:23:47',  '2019-12-30 16:45:03',  NULL,   NULL,   '2019-12-30 12:11:31',  '2019-12-30 12:11:31',  NULL,   93004,  8782,   NULL,   0),
...);
-- new table
CREATE TABLE delivery_note_partner
( delivery_note_partner_id int(11) NOT NULL AUTO_INCREMENT
, delivery_note_id int(11) NOT NULL
, delivery_note JSON NOT NULL
  -- example of generated column, for indexing purposes
, payment_method varchar(20) generated always as (delivery_note ->> '$.payment_method')
,     CONSTRAINT PK_DELIVERY_NOTE_PARTNER
          PRIMARY KEY (delivery_note_partner_id)
,     CONSTRAINT AK1_DELIVERY_NOTE_PARTNER
          UNIQUE (delivery_note_id)
,     CONSTRAINT FK_DELIVERY_NOTE
          FOREIGN KEY (delivery_note_id)
          REFERENCES delivery_note (delivery_note_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Since MySQL does not support indexes on JSON, this is an example of an index for attributes that are used in predicates

CREATE INDEX X01_DELIVERY_NOTE_PARTNER ON delivery_note_partner (payment_method);

-- some migrated sample data for new table
INSERT INTO delivery_note_partner (delivery_note_id, delivery_note)
VALUES (1, '{ "payment_method":"credit_card", "total_price_amount": 6500, "total_price_currency":"EUR" }')
    ,  (12, '{ "client_account_number":"ACC605739261" }')
    ,  (17, '{ "sum_owed_amount":7500, "sum_owed_currency":"EUR", "delivery_price_amount":2500, "delivery_price_currency":"EUR" }');


How to insert all the dates in a date range into Oracle database? For instance, The date range is from 26th Jul to 1st Aug, that is

  26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug

How can I construct a function (of course, I need to implement this function using SQL or PL/SQL) like this:

// insert all the dates from "from" to "to" ("from" and "to" are included)
// into Oracle datebase
bool InsertDateRange(date from, date to) {
    bool state = true;

    for (date d in [from, to]) {
        // insert
    }

    return state;
}

If given a different date range, say [27th Feb, 1st Mar], I also need to know this year is a leap year or a common year.

You can use a hierarchical query to generate a sequence of dates, something like this:

insert into my_table (my_date_column)
select dt + (level - 1)
from (select date '<your start date>' dt from dual)
connect by dt + (level - 1) <= date '<your end date plus 1 day>';

Date arithmetics: dt + (level - 1) -- are smart enough to know when one month ends and another begins (including Februaries in leap years).

If you do need a PL/SQL function for this, I'm sure you will be able to wrap it around this statement easily.


Oracle: How to create a not null column in a view Given a table like:

CREATE TABLE "MyTable"
(
  "MyColumn" NUMBER NOT NULL
);

I want to create a view like:

CREATE VIEW "MyView" AS
SELECT
    CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";

Only where the column "MyColumn" is "NOT NULL".

In SQL Server this is pretty straight forward:

CREATE VIEW [MyView] AS
SELECT
    ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn]
FROM [MyTable];

However the Oracle equivalent results in a "NULL" column:

CREATE VIEW "MyView" AS
SELECT
    NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn"
FROM "MyTable";

Is there anyway to force Oracle to mark the view's column as "NOT NULL"?

As I understand it now, you need the entity framework to see the table as having a NOT NULL BINARY_DOUBLE, but the data needs to be/is stored in a NOT NULL NUMBER.  This is a problem when using a view because the view does not pass the NOT NULL attribute through when it contains a CAST.

Your options include the two Alex Poole +1 mentioned on SO (1. Virtual Column, 2. New column of different type with a trigger) or 3. New table with a different type column and a trigger.  You should prefer these options in the order they are presented.

Previous Answer

Columns in views from columns in tables that are marked NOT NULL will inherently be NOT NULL as well.  There is nothing special you need to do to make this happen.

If instead you are trying to make the view accept nulls when the table is marked as NOT NULL, then you should look into INSTEAD OF triggers.  They will allow you to insert a null into a view and have it inserted as a zero into the table.  Here is an example:

SQL> drop table t1;

Table dropped.

SQL>
SQL> drop view v1;

View dropped.

SQL>
SQL> create table t1 (c1 number not null);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL>
SQL> insert into t1 values (null);
insert into t1 values (null)
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LRIFFEL"."T1"."C1")

SQL>
SQL> create view v1 as select cast(c1 as binary_double) c1 from t1;

View created.

SQL>
SQL> insert into v1 values (2);
insert into v1 values (2)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

SQL>
SQL> insert into v1 values (null);
insert into v1 values (null)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

SQL>
SQL> CREATE TRIGGER TRIGGER1
  2  INSTEAD OF INSERT ON V1
  3  REFERENCING OLD AS old NEW AS new
  4  BEGIN
  5    INSERT INTO t1 VALUES (NVL(:new.c1,0));
  6  END;
  7  /

Trigger created.

SQL>
SQL> insert into v1 values (2);

1 row created.

SQL>
SQL> insert into v1 values (null);

1 row created.

SQL>
SQL> select * from t1;

        C1
----------
         1
         2
         0


How can I make multiple joins on the same table? I have a table (signal) which contains references to two other tables (ee and eeav).
I want to perform an SQL Query which combines the data from ee and eeav tables.

The situation is as follows:
The table signal can either have a reference to ee or eeav. In other words: when ee has a non-null value, eeav has the value null, and vice versa.
The table eeav has a reference to an ee.

These are the tables:

Table: Signal

+-----------+------+-------+
| s         |      |       |
+-----------+------+-------+
| s.id      | s.ee | s.eeav|
| 1         | 20   | null  |
| 2         | null | 500   |
| 3         | 40   | null  |
| 4         | null | 700   |
+-----------+------+-------+

Table: EquipmentEntity

+-------+---------+
|  ee   |         |
+-------+---------+
| ee.id | ee.name |
| 20    | ee20    |
| 30    | ee30    |
| 40    | ee40    |
| 50    | ee50    |
| 100   | ee100   |
| 101   | ee101   |
| 102   | ee102   |
| 103   | ee103   |
+-------+---------+

Table: EquipmentEntityActualValue

+---------+-----------+---------+
|  eeav   |           |         |
+---------+-----------+---------+
| eeav.id | eeav.name | eeav.ee |
| 400     | eeav400   | 100     |
| 500     | eeav500   | 101     |
| 600     | eeav600   | 102     |
| 700     | eeav700   | 103     |
+---------+-----------+---------+

Desired result

+----------+-----------+---------+
| s.id     | eeav.name | ee.name |
+----------+-----------+---------+
| 1        | null      | ee20    |
| 2        | eeav500   | ee101   |
| 3        | null      | ee40    |
| 4        | eeav700   | ee103   |
+----------+-----------+---------+

I am able to combine the signal table with the eeav table (including the ee) as follows:

SELECT s.Id, eeav.Name, ee.Name FROM signal s
INNER JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
INNER JOIN equipmententity ee ON (eeav.Ee = ee.Id)

I also managed to combine the signal table with the ee table:

SELECT s.Id, ee.Name FROM signal s
INNER JOIN equipmententity ee ON (s.Ee = ee.Id)

How can I "combine" these two results, so that I get the result as visualized in the "desired result" table?

I think this does what you need

 SELECT s.Id, eeav.Name, IFNULL(ee.Name, ee2.Name) AS 'Name' FROM signal s
 LEFT JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
 LEFT JOIN equipmententity ee ON (eeav.Ee = ee.Id)
 LEFT JOIN equipmententity ee2 ON (s.Ee = ee2.Id)


Ola Hallengren Scripts and Maintenance Plan Editor - How to execute sequentially on completion? I'm using Ola Hallengren's scripts to perform maintenance on my databases. In the Maintenance Plan Designer I am trying to setup a plan that works step by step, but no matter what I try everything gets executed at one time.

My first and second attempts, left column in the photo, was to use "execute job" and call each job in the order I want.  The first constraint was "success" but all jobs execute at once.  So I tried "completion" next.  Same result.  I then constructed the same tree using execute t-sql instead of execute job using this code:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO

One again I got the same result, everything executes at the same time.  I even tried adding a @return_value for executing sp_start_job but that did nothing either.  Finally I tried one script like this:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - SYSTEM_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='IndexOptimize - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - SYSTEM_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - LOG';
GO

And it also executes everything at once.  Besides scheduling them one by one in the maintenance plan editor at different times, is there any way I can make them execute one after another.  I'm not concerned if they succeed or not, I just want one to run then the next instead of everything at once.

Why not just run each job as a seperate step?

And so on and so forth


Is NEWSEQUENTIALID unique within a database for Many tables? Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in different tables.

Regular NEWID() will guarantee uniqueness in a database, and chances of having duplicate is chance of being struck by asteroid/lightning, however they do not alleviate the fragmentation issue.

This article talks about Mac address, however I want to specify question for database specific above.
https://stackoverflow.com/questions/28404964/is-uniqueidentifier-unique-across-databases

Acording to the documentation from Microsoft.

NEWSEQUENTIALID

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

I recommend using NEWSEQUENTIALID over NEWID() for any case that that you aren't relying on the built in randomness, mainly due to the fragmentation issues associated with random GUIDs. There is a fantastic article by Kimberly Tripp on some of the pitfalls of using NEWID() on a Primary key.

There are a few situations where NEWSEQUENTIALID may not guarantee  uniqueness as are listed in the documentation.

  The UuidCreateSequential function has hardware dependencies. On SQL
  Server, clusters of sequential values can develop when databases (such
  as contained databases) are moved to other computers. When using
  Always On and on SQL Database, clusters of sequential values can
  develop if the database fails over to a different computer.

However judging by the phrasing of your question it seems like this will be contained on a single database server.

It is also worth noting(not that it applies here) that NEWSEQUENTIALID can be predicted so if privacy of that GUID is a concern this could also be a problem.


Sudden MySQL connection issue We have almost 60 network guys working via a proxy server, developing on one Linux-based in-house server.

Suddenly, it is saying to everyone:

Warning: mysql_connect() [function.mysql-connect]:
Too many connections in XXXXXXXX FILE NAMES

We have cleared tmp folders as well, but after restarting we face the same issue.

What is the solution to this error?

SET @@GLOBAL.wait_timeout = 30;

Have your users disconnect when they are through.

Do not have more than 20 for MaxClients in Apache.


truncate table in mysql 8.0 is slower than older version I have few hundreds of tests which use mysql. I was using Mysql 5.7 (percona) and I changed it to Mysql 8.0 . My problem is that before every test I truncate ALL my tables. This was fine with 5.7 but mysql 8.0 takes 5 seconds to truncate all 60 tables.
This is mysql config which I use for both version:
[mysqld]
skip-external-locking
key_buffer_size = 32M
max_allowed_packet  = 1G
thread_stack        = 512K
thread_cache_size       = 8
expire_logs_days    = 2
max_binlog_size         = 100M
max_connections = 1200

tmp_table_size                                  = 512M
max_heap_table_size                             = 512M

skip-name-resolve

innodb_buffer_pool_size=256M
innodb_log_buffer_size=32M
innodb_log_file_size=64M

innodb_flush_method=O_DIRECT
innodb_thread_concurrency=64
innodb_write_io_threads=32
innodb_read_io_threads=32
innodb_flush_log_at_trx_commit                  = 2
wait_timeout = 1800
interactive_timeout = 1800
innodb_file_per_table = 1

P.S: This is not just truncate, create table and alter table also take a lot more time compared to previous version.
P.S 2: I tested this both on my system and GitHub's Action. Same result for both.

Alas, DDL statements are slower in 8.0.  This is because they can now be inside transactions and rolled back.
If the goal is to start with a fresh set of empty tables for a new test, I am having trouble coming up with a faster way.  Perhaps something involving a "Logical Volume" (cf "LVM").  (Note MySQL needs to be stopped throughout the steps below.)
Setup:

Establish a separate disk partition for the main directory tree for MySQL.
Clone it (LVM).  (This will be the master for a later step)

When starting a new test:

Drop the current logical volume
Clone the above clone to get a fresh set of empty tables

(Sorry, I don't have all the details.)


Why does VALUES provide a different DEFAULT column name than SELECT? SQL Columns can get different default names depending on if the columns come from subqueries (where they all get ?column?)

# SELECT 1,2,3,(SELECT 1),(SELECT 2),(SELECT 3),* FROM (SELECT 1,2,3) AS t;
 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------+----------+----------+----------
        1 |        2 |        3 |        1 |        2 |        3 |        1 |        2 |        3
(1 row)

Or, from VALUES LIST (where they all get column#)

SELECT * FROM (VALUES (1,2,3)) AS f;
 column1 | column2 | column3
---------+---------+---------
       1 |       2 |       3
(1 row)

This is mentioned in the docs on VALUES LIST,

  By default, PostgreSQL assigns the names column1, column2, etc. to the columns of a VALUES table. The column names are not specified by the SQL standard and different database systems do it differently, so it's usually better to override the default names with a table alias list, like this:

And, then this is mentioned in the docs on SELECT

  In more complex cases a function or type name may be used, or the system may fall back on a generated name such as ?column?.

Is there any reason we generate different sets of names like this? Why not just have the SELECT list generate sequentially all unnamed columns in the same fashion as the VALUES list?

This shows a behavior that I think is somewhat awkward, I would expect, column1-column3 twice.

WITH t1 AS ( SELECT 1,2,3 ),
     t2 AS ( VALUES (1,2,3) )
SELECT * FROM t1, t2;

 ?column? | ?column? | ?column? | column1 | column2 | column3
----------+----------+----------+---------+---------+---------
        1 |        2 |        3 |       1 |       2 |       3
(1 row)

The general approach of the SQL standard is that many things not imposed by chosen semantics are left to the vendor to optimize or specify. Under the chosen policy users choose names when they care what they are.

SELECT without FROM isn't standard SQL either. MySQL chooses to use the expression that evaluated to a column's value as its name. Those PostgreSQL "?column?"s aren't the column names, they are strings output instead of the column names. If the system defaults to internal unique names then clashes are avoided compared to defaulting to patterned names. In your example you could have selected "column1" but with your expected behaviour you'd need "t2.column1".

Ultimately, they just chose a certain design for the language. And even where people leave a rationale, engineering and ergonomics are about pragmatic timely tradeoffs where one decision cannot necessarily be justified as "best".


Does a database backup include all files? If I have two files in my database (mdf & ndf), when I do a backup of the database with the statement:

backup database mydb;

are both the two files are backed up?

Yes. Details here: Backup Overview (SQL Server)

This is the section relevant for you:

Full backup

A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

You should always test the backup (ideally by performing a restore to another location) to ensure you can recover your data in the event of a disaster.


Indexing a LONGTEXT column I would like to set up an index for a LONGTEXT column using:

CREATE INDEX post_meta ON wp_postmeta (meta_value(8));

There are currently ~1 million rows in the relevant table.

Questions:

Will creating this index affect the data in any way whatsover, for example, droping leading 0's or anyhting like that?
Is there any reason NOT to do this? There are many rows with content greater than 8 characters, but I frequently query on a type of entry that is 8 or less.

I was fighting a similar problem today with some slow SQL queries generated by WooCommerce in WordPress and I managed to resolve the issue by creating the following two indexes:

alter table wpn0_posts add index woo_index1 (post_type, post_status, id);

alter table wpn0_postmeta add index woo_index2 (post_id, meta_key, meta_value(8));

In my case, however, the query was always looking for IDs in the longtext field:

  WHERE meta_value     = '15358'....

That is why the index works in my case for my specific queries. I hope this helps you :)

Edit: You may also check the following question I posted today and I managed to optimize my query by adding indexes:

How to optimize this specific MySQL query which examines 2.84M rows and 29.49k InnoDB distinct pages


Sql DateDiff Question Select  distinct  DATEDIFF(ww, GETDATE(), siparis_test) AS Hafta

The first day of week is sunday in the above function. How can I change it so that monday is the first day?

Thanks in advance.

Just use SET DATEFIRST, e.g.

set datefirst 1
go


Row level security in PostgreSQL for different groups Need a little help with row-level security in Postgres.

I have several tables and three group roles (group1, group2, and group3). I have several login roles that are members of each group, while "admins" are members of all groups. I would like to enable row-level security such that members of group1 can only see rows that were created by them or another user in group1 -- and same for group2 and group3. I can easily do this for individual users by creating a row that contains current_user and using something like "CREATE POLICY row_policy ON table1 USING (true) WITH CHECK (created_by = current_user);", but how does one enable this for different group roles so that every member of that group has the same row-level privileges?

So, if user1 has group in common with user2, user2 should be able to see that row, yes?

One way would be to enumerate all the groups that user1 is in, and all the groups user2 is in, and see if there's a union

See How to get all roles that a user is a member of (including inherited roles)? to get all groups a user belongs to

See also pg_has_role https://www.postgresql.org/docs/9.6/static/functions-info.html


How can I design a database to track each individual's stat changes weekly, in a group? There is a group of people, and I need to track each person's individual numbers weekly such as their weight lost, steps walked, and bad foods eaten.  I plan on implementing this database into highcharts models where they can view their progress in a visual manner, further motivating them to keep doing well.

The thing is,  how can I do this?  Initially, I was thinking of creating a table named Person, and include their name, age, steps walked, weight loss etc.  But if I were to update the steps walked, it would overwrite the prior steps walked data instead of adding a separate instance. If I do one table per stat, that seems kind of inefficient as I would end up with 52 tables per even 10 people, thats 520 tables!

I hope this makes sense, as it is a little difficult for me to wrap my head around the total scope of it to see what is needed...any help is MUCH appreciated!

Enclosed is what I kind of mean

You are thinking along the right track in wanting to store information about each person.  Since you want to record a history of their statistics, the best way to do that is to use another table which will keep one row for each time your record statistics with the date when they were recorded.  Now the question becomes how do you want to record each statistic?  You have two basic options.
Column Wise
If you have a fairly fixed set and want to make make it very simple, you can place each statistic by name as a column on Measurement along with the Date.  So for example, you'd have a column for Weight, Steps Walked, and so on.  The advantage of this approach is it is simple and clear, and you can use a data type specific to each type of statistic.  The disadvantage to this approach is that to add new kinds of statistics you have to add new columns, and you have to make all of the statistic types optional should you not record one or more of them for a given Measurement.

Row Wise
A second option would be to make the statistic more generalized by having a Statistic Type table.  There would be a row in Statistic Type for each kind of statistic you want to record.  Then, you would associate Measurement to the Statistic Type and record the Value.  The advantage of this approach is that you can easily add more statistics, and you only have to record just the values for the statistic types you measured.  The disadvantage is that this is more abstract and complex, and you have to use a generalized data type that can support all of the various units of measure, or you have to create a mutually exclusive set of columns each of a data type matching a statistic type.  If you go with the first approach, you can really turn it into a science project in order to support all the various data types and still ensure data integrity.

Other Points
The diagrams I show are created using Oracle's Data Modeler tool which is a free download and very powerful.  You can create DDL directly from it.  One thing you want to consider that I didn't discuss is that, if you use surrogate keys as I did here, you want to also define an alternate unique key for each table using more natural columns.  In the case of Person that could be Name.  This way, you won't accidentally add the same person twice if your list gets long.  A second thing is you have to think about if you have one value per statistic or more than one.  For example, Weight and Steps are good examples of a single statistic for the Measurement.  Today you record that I weight 200 pounds and walked 5000 steps. But for something like Bad Foods Eaten there could be more than one.  If that is the case you have what we call a repeating group.  To resolve this, you either have to create a fixed number of columns - one for each bad food - or create a new table that will be a child of the Measurement that will have one row for each bad food.
Examples
Here is an example of the way your data would look using the column approach (skipping the surrogate keys for simplicity of display):

And here is an example of the way your data would look using the row approach:

I hope this helps! A great book on data modeling like this is Steve Hoberman's Data Modeling Made Simple.


Database design of Ledger Account and Balance sheet generation I am building an application which has deposit and loan accounts management,  for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a hard time in designing the database model best suited. Is there any standard model for the database design of accounts(especially ledger) which can used?

As to what dezso said, ledgers are quite special depending on the business that is using them. You can try looking at other opensource applications with the same features and functions as your program if you're looking for a basis for your database.

You can look for some opensource projects at Ohloh.net and Github.com

But as a fellow application developer, I would suggest you take the time to read up on some basic database design best practices and principles. And a bit of normalization, at least up to the Third Normal Form. Having a faulty database can give you a lot of headaches in the long run when maintaining your application. Refactoring database changes aren't that easy with a mature program.


Conditional Database View of Postgresql I have a situation here. Currently I have

two polling tables (poll_one and poll_two) which will poll data from outside data-source. This two tables will keep interchange between each other.
one checking table (check_poll) which tells me which tables (poll_one or poll_two) is active
one user edit table (edit) which stores exact same columns and names as the two poll tables.

Basically I wan to create a dbview that joins the edit table and the active poll table. However, I am not sure how to do a conditional dbview to first check which polling table is active before performing my query.

I cannot use stored procedures as this dbview will be used for my hibernate mapping.

Below is a sample definition of my dbview:

SELECT
CASE
WHEN u.name IS NULL THEN p.name ELSE u.name
END AS name,

CASE
WHEN u.size IS NULL THEN p.size ELSE u.size
END AS size,

//same for the rest of columns

FROM user_edit_table u
LEFT JOIN poll_one p //I wan to make this conditional
ON p.id = u.id

Is it possible? Is there any other ways?

Thanks for all the replies,
An example is shown in the Image Below:

So based on the check table, I will choose which table I wan to left join. There is no foreign relation between the tables. All are stand alone.

It's not exactly clear how the one or the two table is to be chosen but something like this should work (I assume that the check_poll table has a single row always):

SELECT
    COALESCE(p.name, u.name) AS name,
    COALESCE(p.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u
  LEFT JOIN poll_one AS p //I wan to make this conditional
    ON p.id = u.id
WHERE c.poll = 1        -- whatever shows which poll table to choose

UNION ALL

SELECT
    -- same list
FROM check_poll AS c
  CROSS JOIN user_edit_table u
  LEFT JOIN poll_two p
    ON p.id = u.id
WHERE c.poll = 2 ;

or even combine the two subqueries in one:

SELECT
    COALESCE(p1.name, p2.name, u.name) AS name,
    COALESCE(p1.size, p2.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u
  LEFT JOIN poll_one AS p1
    ON  p1.id = u.id
    AND c.poll = 1
  LEFT JOIN poll_two AS p2
    ON  p2.id = u.id
    AND c.poll = 2 ;


script to spool all tables in a schema to xml format in Oracle 12c I've written the following script, spooltables.sql, which I'm calling from SQLcl as $> @spooltables.sql

The aim is to loop through all tables in a given schema and output them to separate XML files.

set term off;
set feed off;
set sqlformat xml;
begin
for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop

   dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');

end loop;
end;
spool off;

This doesn't execute, it just prompts for a new line in the SQLcl terminal.

I'm not really experienced writing these Oracle loops, can anyone tell me what I need to do to get it working as required?

So I've tried the following based on the below answer:

    spool spooltables.sql
    set term off;
    set feed off;
    set sqlformat xml;
    begin for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop
    dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
dbms_output.put_line('select /*+ parallel / from '||table_list.table_name||';');
    end loop;
    end;
    /
    spool off;

    $> @spooltables.sql

The sql file executes but seems to execute immediately without doing anything - the file also gets emptied.

I don't know SQLcl, but in sqlplus a pl/sql block must be terminated by a line

/

I hope the following will work

spool sqltables.sql
set echo off
set feed off;
set serveroutput on
prompt set termout off
prompt set sqlformat xml
begin
for table_list in (select table_name from dba_tables WHERE owner='MYUSER') loop

   dbms_output.put_line('spool ./'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');
   dbms_output.put_line('spool off');

end loop;
end;
/
prompt exit
spool off;
@sqltables.sql

so I added SET SERVEROUTPUT ON. This is necessary in sqlplus, otherwise you will not see the dbms_output output.
Also I added the PROMPT statements that write commands to the sqltables.sql file.


Getting exception on running sh.status() command on MongoDB sharded cluster I am running sh.status() command on my mongos instance of sharded Mongo cluster and I am getting below exception.

mongos> sh.status()
assert: command failed: {
    "ok" : 0,
    "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1542173873192)\nSort: {}\nProj: {}\n No query solutions",
    "code" : 2
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

2018-11-14T05:38:55.904+0000 E QUERY    [thread1] Error: command failed: {
    "ok" : 0,
    "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1542173873192)\nSort: {}\nProj: {}\n No query solutions",
    "code" : 2
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

I am running Mongo 3.2 on ubuntu 16.04 platform. It is working fine on MongoDB version 3.0 on ubuntu 14.04 platform.
I have reinstalled everything and tried this on new machines too, everything is working fine except sh.status() command.

Thanks.

It was becuase notablescan property enabled in my mongo config servers. After disable notablescan, command is working fine. you can check status of notablescan by running this command.

db.adminCommand( { getParameter: 1, notablescan: 1 } )

Output would be like below if notablescan is enabled.

{
  "notablescan": true,
  "ok": 1
}


Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower? I have a Stored Procedure that I have written that deletes rows from a table in the database.  When I will run the Stored Procedure in the Production environment it will run whilst another program is inserting rows into the same table.  In case there is a deadlock I want my Stored Procedure to be the one that is killed.

In the Stored Procedure I have set DEADLOCK_PRIORITY to LOW.

When doing testing in my test environment I am finding that having this option set it makes the deletion run a lot slower.  In the testing environment the process that inserts records is not running so, in theory, there is no locking issue.

I am finding that when the DEADLOCK_PRIORITY is set to LOW it is taking 1.5 hours to delete ~500,000 rows.  When the DEADLOCK_PRIORITY is not set (ie using the default setting) the deletion of the rows only takes around 15 minutes.

Is there a reason why there is such a difference?

The database is using Simple Recovery model in the test environment.  (In the Production environment it is using the FULL recovery model).

Thanks.

Going to guess here... transaction log file sizes

In the test system was small and grew when you tested the first time with LOW
When you re-run without LOW, then log file was already sized correctly.

Easy to verify: check the "lastwaittype" in sys.sysprocesses (deprecated but useful) to see if you gave LOG related waits affecting the SPID doing the DELETE.

Otherwise double check that the 2 executions are identical

same data
any triggers enabled
ditto foreign keys, indexes
foreign key cascades
...

Also see Why does an UPDATE take much longer than a SELECT?


Long delay before redirected restore "starts" On several occasions, I noticed that it takes a long time before anything "happens" during a redirected restore. There are no entries in db2diag.log and list utilities show detailshows:

Completed Work             = 67129344 bytes

I assume this is because the filesystems differ between source and target machine, and that something has to be checked/prepared, but it is just my guess. Does anyone know what is happening during the delay?

Example:

db2 "values current_timestamp"

1
--------------------------
2018-02-20-11.08.39.881924

  1 record(s) selected.

db2 list utilities show detail

ID                               = 19820
Type                             = RESTORE
Database Name                    = ****
Member Number                    = 0
Description                      = db
Start Time                       = 2018-02-20 10.25.07.240025
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
      Completed Work             = 67129344 bytes
      Start Time                 = 2018-02-20 10.25.07.240029

Between the RESTORE DATABASE ... REDIRECT and the RESTORE DATABASE ... CONTINUE command, you will execute all of the SET TABLESPACE CONTAINER commands.

If your database resides on a filesystem that does not support fast file allocation (for example: AIX JFS2), then these SET TABLESPACE CONTAINER operations can take quite a long time if you have large containers – DB2 is formatting the tablespace containers.

When this is happening you'll see lots of I/O on your system, but no apparent activity on the database restore.


pulling multiple entries from a match of two tables I have two tables, attributes and attributes_entries

attributes hold the title and attribute_entries hold the value of that title.

if I do:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM   attribute_entries
INNER JOIN attributes
       ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'ford'
      AND attributes.title = 'manufacturer'

I can get a result of:

ent_title | attr_title
ford      | manufacturer

if I do another query of:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM   attribute_entries
INNER JOIN attributes
      ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'whatev'
      AND attributes.title = 'nickname'

I get the result of:

ent_title | attr_title
whatev    | nickname
whatev    | nickname

both of these queries allign with the data I have in there.

so I figured this would work:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM attribute_entries
INNER JOIN attributes
      ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'ford'
      AND attributes.title = 'manufacturer'
      AND attribute_entries.title = 'whatev'
      AND attributes.title = 'nickname'

This clearly isn't working.  What is my best approach to get the results in one statement?

You should filter using pairs:

WHERE (attribute_entries.title = 'ford' AND attributes.title = 'manufacturer')
      OR
      (attribute_entries.title = 'whatev' AND attributes.title = 'nickname')

Or you can use IN

WHERE attribute_entries.title IN ('ford', 'whatev')
      AND attributes.title IN ('manufacturer', 'nickname')

In case you need both conditions true you can use EXISTS:

WHERE EXISTS(<your first query>)
      AND
      ExISTS(<your second query>)

You can check it :db<>fiddle here


Page life expectancy error in sql server I am new to sql server monitoring.Basically system administrator who worked in organisation set monitoring in window server 2012R2 to monitor memory issues in sql server and I am getting following notification and I don't exactly understand where to look for solution.

Notification I am getting in email:

Memory/memory pressure I checked:

Seem like there isn't any memory pressure but I don't understand why I am getting page life expectancy state transition (Normal to Failed) ?

Thanks in advance for reading/sharing knowledge/helping.

Update in question as requested:

Select @@version result:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
    Aug 19 2014 12:21:34
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Other useful information:

Result of :

select cntr_value from sys.dm_os_performance_counters where object_name LIKE '%Manager%' and counter_name = 'Page life expectancy'

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks Where memory_node_id<64

cntr_value = 788645; memory_node_id = 0;

I am running two instances on server and Window server 2012R2 is OS with 6GB RAM.

cntr_value = 788645

Your PLE is 788645, that is perfect and even more: it reflects that your server happily doing nothing.

So, don't worry and don't look your monitoring tool: it reflects something known only to its author

P.S. 788645 seconds = 9,... days, that is right, yor image also shows 2017-09-03 that, I think, is server last restart time. Since that day no more than 2Gb of data was passing through your buffer pool and it's still there, so, one more time: no memory pressure, no working activity on this server.


if-clause in select query I want to fetch some persons data from two tables via equi join. Some persons have address data and some have not. For those persons who haven't the addresses_id from table persons is NULL.

For all persons who have an address I do (query#1):

SELECT p.last_name, p.first_name,
       a.street, a.house_nr, a.post_code, a.city
  FROM persons AS p, addresses AS a
 WHERE p.id=42 AND a.id=p.addresses_id;

For all persons without address data I do (query#2):

SELECT p.last_name, p.first_name
  FROM persons AS p WHERE p.id=42;

Is there any smooth way to put these two queries into one. Maybe with an if-clause? I don't want to check the addresses_id field before by myself and then execute query#1 or query#2.

I'm using MySQL with InnoDB engine.
Thanks.

Maybe something like this:

SELECT
  p.last_name,
  p.first_name,
  a.street,
  a.house_nr,
  a.post_code,
  a.city
FROM persons AS p
LEFT JOIN addresses AS a
WHERE p.id=42


PostgreSQL database does not exist error I am getting a "database does not exist error", even when the database exists:

postgres=# GRANT ALL PRIVILEGES ON DATABASE Owino to OdooD;

  ERROR:  database "owino" does not exist

Name    |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges

Owino   | dominus  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres           +
        |          |          |             |             | postgres=CTc/postgres  +
        |          |          |             |             | orokon=CTc/postgres    +
        |          |          |             |             | powerbidev=CTc/postgres+
        |          |          |             |             | odoodev=CTc/postgres

The database was created with double quotes, so the name is now case-sensitive and you are now stuck with the dreaded quoted identifiers. "Owino" is a different name than Owino

GRANT ALL PRIVILEGES ON DATABASE "Owino" to OdooD;

Depending on how you created the user, you might need to use the dreaded double quotes for the user name as well ("OdooD").

I highly recommend to simply never use double quotes in SQL.

See the manual for more details:
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS