Oracle 10g SGA sizing Help Help in sizing oracle 10g database sga
We've an Oracle 10g r2 database, size = 4.5TBASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system caching).Server Configuration is Intel Xeon 4CPU (8core per cpu) i.e total 32 cores; Total Internal Memory is 128GB. Operating System is Solaris 10 update 9.

The SGA size is 80GB, if we increase the SGA to 84GB~85GB the cpu utilization shoots upto 99%~100%. With 80GB SGA the cpu utilization is 20%~50% depending upon the load.

What is the relation between cpu cores and sga size? I used 4GB memory per core, is this a correct estimate for the Intel Xeon platform
Why does the cpu util shoot to 100% with an increase in SGA?Is there a rule of thumb for sizing the oracle sga for oltp environment?

vmstat output:
r   b   w   swap          free         sr   s0 s1 s2 s3 us
0   26  27  128146676   6448872 115 0   3   0   94  19
1   74  31  123050360   4031544 0   0   2   0   56  16
0   83  31  122930884   3921080 0   0   0   0   31  10
1   75  31  122934416   3921480 0   0   0   0   61  13
0   78  31  122787016   3802200 0   0   0   0   104 25
0   84  31  122595368   3626344 0   0   0   0   110 22
0   91  31  122717320   3759480 0   0   0   0   108 25
0   88  31  122831348   3889744 0   0   6   0   111 27
0   79  31  122581408   3647472 0   0   0   0   101 44
1   84  31  122306816   3388964 0   0   0   0   71  27

The "sr" column reports zero for most of the entries (but the first where it shows a high value 115)Moreover the vmstat shows an unusually high blocked and swapped out processes.

This question is more complicated than how SGA relates to cpu utilization.  Many other factors can cause a high CPU usage even if there is adequate SGA.  Start looking at the other Oracle diagnostics for the usage of the shared pool, large pool and buffer.
Even more important you need to investigate the application front end, number of sessions, type of data operations your users are up to...
What kind of SQL is getting fed to the database, does it use bind variables, does it require full table scans?
Oracle tuning is more successful when you look at the whole picture. Tell us more...


MySQL COUNT based on a condition I have a MariaDB database, in which I have this table:

ID | Item Name | Item replaced |
-----------------------------------------
0  | Item 1     | Replaced
1  | Item 2     |
2  | Item 3     | Replaced
3  | Item 3     |
4  | Item 3     |
5  | Item 1     | Replaced
6  | Item 2     | Replaced
7  | Item 2     |
8  | Item 3     | Replaced

I want to count the number of same items and display so ITEM 3 in this case would be '4' as there are four of ITEM 3 in my database but I also want to show how many are replaced so in this case it would be '2' as only two of ITEM 3 is marked as replaced.

End result I want to get is something line

ITEM 1        2 - 2 (2 item 1's and both are are replaced)
ITEM 2            3 - 1 (3 Item 2's and only 1 is replaced)
ITEM 3            4 - 2 (4 Item 3's and 2 are replaced)

I have tried a UNION but that doesnt seem to work for me.

Also it would be helpful if I could count replaced as NULL or Empty.

I found this answer on SO but it's not quite what I want, this case the user is listed more than once so this example wouldnt work for me.

You'll need a case expression to count the occurrences of 'Replaced':

select
 item_name,
 count(*),
 count(case when item_replaced = 'Replaced' then 1 end)
from your_table
group by item_name


MySQL stored routine performance while using PREPARE Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. All databases have same schema structure.

Stored routine body:

PROCEDURE `sp_name`(IN db_name  VARCHAR(50)
                    ,IN stardate DATE
                    ,IN enddate DATE)
......
SET @sql_query = CONCAT(
'SELECT
    .....
    .....
    .....
FROM ',
db_name, '.view
WHERE
    ....  stardate, .... enddate .....
GROUP BY
    ..... ,.....');

PREPARE sql_query FROM @sql_query;
EXECUTE sql_query;
DEALLOCATE PREPARE sql_query;

Now I am facing performance problems. While executing the stored routine from each database, execution time is less then 1 sec. With the prepared statement, it is 5-6 sec. Could someone please tell me if there is space for any improvements?

You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure.  You also should see no difference at all from the fact that the procedures are stored in a different database from your data.

From the start, though you're presenting us with two different unknowns -- different schema and dynamic SQL.  The best way to troubleshoot this is to sectionalize the problem down to the individual variables... by putting a procedure with static (not dynamic) in a different schema from the data (shouldn't matter) or by putting a procedure with a dynamic query in the same schema as the data (also shouldn't matter).

But I suspect the problem is related to neither factor.  I think it's the dynamic queries you're building.  You may actually be getting wrong results, in addition to your queries taking longer to run.

You appear to have startdate and enddate concatenated right into the text of your dynamic sql.  That cannot possibly be valid, since prepared statements do not share procedure variable scope with stored procedures, and concatenation like this puts the names of the variables, not their contents, in to the string... in fact, that dynamic sql should throw an error when you run it -- however, there's an insidious exception, here: it would appear to work if the view you're querying also happens to have columns called startdate and enddate... but the query isn't giving you the correct results, because it thinks you're referring to columns in the queried table in your WHERE clause -- not the procedure variables, making your where clause essentially not eliminate any rows based on those dates.

You could replace startdate with @startdate and enddate with @enddate in the body of your prepared statement, and then, before the actual PREPARE, assign the values of those session ("@") variables to be the same as the procedure variables:

SET @startdate = startdate;
SET @enddate = enddate;

Note that this doesn't put the contents of the variables into your query, either -- concatenating the contents of variables from outside your procedure into dynamic sql almost always is a bad (insecure) idea -- but this doesn't do that.  It puts the variable names into the statement, which is safe.

I think this is the solution to your problem, if the question accurately represents the dynamic sql you're building.

However, I have a further recommendation, in the interest of security and good practice.  I mentioned never concatenating external variables into dynamic sql... but you can easily sanitize the variable db_name, making it safe to concatenate in.

Replace this:

FROM ',
db_name, '.view
WHERE

with this:

FROM `',
(SELECT schema_name FROM information_schema.schemata
  WHERE catalog_name = 'def'
    AND schema_name = db_name), '`.view
WHERE

This scalar subquery will return only two possible things: it will always return either the same value passed in as db_name if that database really does exist; otherwise it will return NULL.  This will make the generated SQL throw an error if an invalid database name is provided, rather than potentially executing arbitary sql code slipped into your dynamic query string via the db_name variable and executed with the permissions of the procedure definer.


How to check decimal separator in T-SQL How can I check with a T-SQL Command what decimal separator is in use in a given user context.

Microsoft Windows Server 2012 has a bug where the regional settings for Swiss German introduced an incorrect decimal separator and digit grouping
KB3060383

We are running our batch jobs via Sql agent with service accounts and we noticed that some of these service accounts are using the wrong decimal separator, some not (since some steps fail). So we want to have a step in each job that displays what settings it is using when running. Checking the regional setting alone doesn't help due to the above mentioned bug. The Windows servers have been patched but we still notice these problems.

I'm aware that we could code the jobs more defensive however that would cover the wrong settings and would make it even more difficult to discover where the bug is still active.

A T-SQL solution won't help here because SQL Server always uses . as the decimal separator and, as a server application, doesn't honor client regional settings.  One easy way to get the regional setting of the SQL Agent job step account is with a Powershell step with script like:

$numberDecimalSeparator = ((Get-Culture).NumberFormat.NumberDecimalSeparator)
Write-Output "NumberDecimalSeparator is '$numberDecimalSeparator'"

The fact you are having problems with decimal separators suggests the scheduled code is constructing dynamic SQL strings instead of using parameters.  Parameterized queries pass values in native form rather than strings, thus avoiding problems with regional settings that affect date formatting, decimal separators, etc.


How do I restrict PostGIS database with user specific access? I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 .

Working on urban application so we have single database and many users .I want to create department wise user group so that that group can not access other tables in database.

I want to update data to want to share data to them.

e.g. electric department should use only view/edit their data and no other department person should edit their data.

Tried following :-

1.Searched on google and PostgreSQL help as well created few users and their roles but when I log in using postgis by 'user1' credentials in QGIS I can see all my data.

2.Going through MANAGING USERS AND PERMISSIONS IN POSTGRESQL AND POSTGIS

thanks..

I would use schemas to manage the individual department objects.  For example:

    CREATE SCHEMA elect_dept;
    CREATE SCHEMA water_dept;

Store each department's objects in the appropriate schema and use roles to manage access, for example:

   CREATE ROLE elect_dept;
   ALTER ROLE elect_dept SET SEARCH_PATH TO elect_dept,water_dept,public;
   GRANT USAGE ON SCHEMA elect_dept,water_dept TO elect_dept;
   GRANT SELECT ON ALL TABLES IN SCHEMA water_dept TO elect_dept;
   GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA elect_dept to elect_dept;

In the above example the role elect_dept can view water_dept schema objects and edit elect_dept schema objects.

It may make sense to create the roles as group roles and grant them to individual named accounts.

    GRANT elect_dept to smithj;

A little rough and ready, but with some tidying, should give you what you need.


one to many relationship DB structure I have a table of millions of records (user contribution). sometimes, our content team find duplicate content (as it is almost the same context, this represents about 0.1%) and they asked to make redirect to the original content/article.
Now:

I can add a column to the same table to point to the original article to be redirected to

create table article (id int primary key auto_increment, body text,
title varchar(128), ....., redirect_to int,
FOREIGN KEY (`redirect_to`) REFERENCES article (id));

Or to have this 1-M relationship in separate table as below

create table article (id int primary key auto_increment, body text,
title varchar(128), .....,);

create table article_redirect (article_id int, redirect_to int,
FOREIGN KEY (`article_id`) REFERENCES article (id),
FOREIGN KEY (`redirect_to`) REFERENCES article (id));

From Database Administration concepts and standards, which option is better and why?

Even though only 0.1% of the articles have a duplicate, the cost (in terms of wasted space) of a NULL int value (for a redirect_to column) in the article table is small. (In fact, if using the InnoDB storage engine, you will waste no space at all if the value is NULL. (See e.g. Bill Karwin's SO answer here as well as the MySQL documentation about InnoDB field contents.) However, if the redirect_to column needs to be indexed, then there is a performance penalty.

A separate table (article_redirect) is the more normalized approach. This will add another join to your queries, but again the cost of that (in terms of extra memory and processing time) is small because the table is small, especially if best-practice indexing is used. You should therefore have a compound index (article_id, redirect_to) on the article_redirect table.

For the separate table approach (article_redirect), it's good practice to define an explicit primary key (in every table). Assuming an article can only be a duplicate of one other article (but many articles can duplicate the same article), then the primary key should be article_id.

As an aside, I will mention that it's usually not good practice to have TEXT columns (TEXT is a type of BLOB) together with other "meta data" columns such as title, timestamps and so forth that you might retrieve in queries. Instead, consider moving this to a separate table with a foreign key pointing back to your article table. See the MySQL documentation about optimizing for BLOB types for details.


SQL Server database backup and saving at the same time What will happen when is running differential backup on SQL Server 2012 and some application is trying save data to table at the same time?

DBMS will take care of these race conditions.It will be done by some  schedule. What called scheduling? you can read about it here .


Use Dynamic SQL To Create View From Cursor I want to iterate the table names that are listed in a table and create one view that has all of the info listed.  I have the cursor set, I have the base of the view set, but how would I modify the syntax to add a UNION ALL statement instead of repeating the Create View statement?

This is the syntax I have:

Create Table dbo.tabletoprocess
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,DatabaseName varchar(100) NOT NULL
    ,TablePrefix varchar(25) NOT NULL
    ,ActiveCustomer varchar(50) NOT NULL
)

Insert Into dbo.tabletoprocess (DatabaseName, TablePrefix, ActiveCustomer) VALUES
('arc', 'night91_', 'yes')
,('bell', 'abc123_', 'yes')
,('fed', 'fred13_', 'yes')
,('rea', 'bad12_', 'yes')
,('bva', 'red121_', 'yes')
,('sez', 'bc12_', 'yes')

Declare @dbname varchar(100), @tableprefix varchar(100), @sql nvarchar(max), @fullpre varchar(500)

Declare DB_CURSOR CURSOR FOR

    Select databasename, tableprefix
    FROM tabletoprocess
    WHERE ActiveCustomer = 'yes'

    OPEN DB_CURSOR
    FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

    Set @fullpre = @dbname + '.' + @tableprefix

    WHILE @@FETCH_STATUS = 0
    BEGIN

        Set @sql = 'Create View dbo.Test As
                    Select *
                        FROM OPENQUERY(linkedserver, ''Select op.field1, op.field2, o.field3, a.field4,
                                                a.field5, a.field6, a.field7, a.field8, a.field9,
                                                o.field10, o.field11
                                                FROM '+@fullpre+'orderinfo as op
                                                LEFT JOIN '+@fullpre+'order as o ON op.oi=o.oi
                                                LEFT JOIN '+@fullpre+'address as a
                                                ON o.osa_id=a.ai'')';
        Print @sql

         FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

    END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

Have you tried modifying the set command like such:

Set @sql = isnull(@sql,'') +
           case when @sql is NULL
                then ' create view dbo.Test as '
                else ' union all '
           end +
           'SELECT ...'

Obviously (?) the print @sql (and exec @sql) is issued after the cursor loop has been completed/closed.

Here's a sample fiddle showing the results:

declare @sql varchar(max), @cr char(1)
set @cr = char(10)

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT x from mytable1 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT y from mytable2 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT z from mytable3 ...'

print @sql

And the results of running the above:

create view dbo.Test as
SELECT x from mytable1 ...
union all
SELECT y from mytable2 ...
union all
SELECT z from mytable3 ...


What is best strategy for backup on streaming replication(hot standby) environment I have questions.
What is best strategy for backup on streaming replication(hot standby) environment.
I’d like to do backup on slave server and restore(PITR).

First, I set up streaming replication(hot standby).

I tried backup using barman tool on slave.
But barman tool couldn’t execute on slave because pg_start_backup() can’t be executed on slave sever(read only).
I tried backup using pg_basebackup on slave.
But pg_basebackup is not included wal and wal archive.
Because I need a PITR(point-in-time-recovery), I tried to copy wal and wal archive from master to slave.
It was successful but was complicate and also inefficient.

Anyone have good strategy for backup on streaming replication(hot standby) environment?

Also, If  backup is executed on master(about 100GB DB size), How much it has effect on master?

Thanks,

As of >= 1.3.1 Barman supports backup from a standby replica (concurrent_backup). Barman config, e.g. /etc/barman.d/standby.conf looks like this:

[standby]
description = "Replica of main PostgreSQL DB"
ssh_command = ssh postgres@db02
conninfo = host=db02 user=postgres
backup_options = concurrent_backup
streaming_conninfo = host=db02 user=postgres
streaming_archiver = on

If your master is running on PostgreSQL <= 9.5 you'd have to install pgespresso extension (there are binary packages e.g. for Debian from PGDG APT repos). PostgreSQL 9.6 introduced native streaming API, there's no need for special extension.

On standby server make sure to configure archive_command:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p barman@backup:/var/lib/barman/standby/incoming/%f'

the incoming directory should match

barman:~$ barman diagnose | grep incoming_wals_directory

Also on standby server update pg_hba.conf (where 10.0.0.3 is ipaddress of barman server):

host   all             postgres            10.0.0.3/32        trust

And enable WAL files streaming:

barman~$ barman receive-wal standby

You can check your configuration using:

barman:~$ barman check standby

Server standby:
        PostgreSQL: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        pgespresso extension: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Then you should be ready to run full backup:

   barman:~$ barman backup standby


I want to select date only This is my query:

select c.InFavourOf as name,
       c.ChequeDate,
       p.Amount as amount,
       m.No as CNo
from Cheque c
  inner join PaymentDetail p
    on c.MasterID= p.PaymentMasterID
  inner join PaymentMaster m
    on p.ID=m.ID

Result:

I want the date values in column ChequeDate formatted as dd mm yyyy without the time part.

How can I do that?

The convert function can do that. And if you want dd mm yyyy format 103 (with / separator) or 104 (with . separator) would be fine.

select c.InFavourOf as name,
       convert(varchar(10), c.ChequeDate, 103),
       p.Amount as amount,
       m.No as CNo
from Cheque c
  inner join PaymentDetail p
    on c.MasterID= p.PaymentMasterID
  inner join PaymentMaster m
    on p.ID=m.ID

If you want space separator, use replace function.

replace(convert(varchar(10), c.ChequeDate, 103), '/', ' ')


Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10? I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ubuntu?

Funny thing. Someone in another post suggested going to this URL:

https://launchpad.net/~nathan-renniewaldock/+archive/ppa

I got that link from this DBA StackExchange post by @galador:

https://dba.stackexchange.com/a/9476/877

Give it a Try !!!


MySQL creates tables very slow On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), older version of MySQL (5.7) and Ubuntu 16.04 everything is much faster. I copied the config in /etc/mysql/mysql.conf.d/mysqld.cnf from the faster computer to the slow one but nothing changed.
How can I trace from where is this problem coming?

For example

CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) ENGINE='InnoDB';

finished for roughly 0.12~0.90 seconds and from

SHOW PROFILE for query 7;

I get

'starting', '0.000132'
'checking permissions', '0.000025'
'Opening tables', '0.000877'
'creating table', '0.092163'
'After create', '0.007109'
'query end', '0.000024'
'closing tables', '0.000007'
'freeing items', '0.000019'
'cleaning up', '0.000013'

Which is very slow compared to my work machine where the same query executes for 0.01~0.009. Any ideas on how can I see where is the problem coming from?

I would put my money on the new features of MySQL 8.0, like Atomic DDL
https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.
Atomic DDL is made possible by the introduction of the MySQL data dictionary in MySQL 8.0. In earlier MySQL versions, metadata was stored in metadata files, nontransactional tables, and storage engine-specific dictionaries, which necessitated intermediate commits. Centralized, transactional metadata storage provided by the MySQL data dictionary removed this barrier, making it possible to restructure DDL statement operations into atomic transactions.

Note the change to a data dictionary from metadata files.


Best Practice : Schema design for Large Number of rows with few columns I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I have a couple hundred entries and the CSV files are about 25 megabytes each.

Each of these rows contain a row label,  a data type label, a start position, and an end position; so about 4 fields per row.

The row label id has over a million potential values (nor do I have a complete list of potential values), and will not appear in the same order in each record nor necessarily even appear.

The data type label is categorical.

The start and end position fields are large integers.

My thoughts are to create a table with Record names and supporting data, another with the categorical label of the
data,  but I am being challenged on how to deal with the 750,000 columns per entry and the start and end positions.

I was thinking of big data table with all 750,000 rows per entry. but it seems like it would be very inefficient to approach it this way; the table looking something like this.

RecordID
RowLabel
CatLabelID
StartPosition
EndPosition

I am running SQL Server 2012 standard edition.

What is the best practice schema to address this type of large data set?

I would just go with a bigint with Identity column. Just remember to start it at the negative seed value since sql server does not support unsigned ints. Something like this...

CREATE TABLE Records
(
 RecordID bigint IDENTITY(-9,223,372,036,854,775,808,1),
 RowLabel varchar(255) NOT NULL,
 CatLabelID INT,
 StartPosition varchar(255),
 EndPosition varchar(255)
)


Why does sp_whoisactive show locks on databases, but not tables? Recently I have noticed some threads running on our sql server which are invoked from our application, which are in a sleeping status for a few minutes and have open transactions (usually just one).

I added the parameter @get_locks = 1 to sp_whoisactive to see what they were holding and the xml result set looks like this

<Database name="NOG_APP">
   <Locks>
      <Lock request_mode="S" request_status="GRANT" request_count="1" />
   </Locks>
</Database>

I don't see any objects in here... how am I to interpret this? Does it mean that this thread is not holding any locks? If so why doesn't the output return NULL?

Running SQL Server 2012 SP3.

It means someone has a connection open in that database. You can reproduce it by:

USE NOG_APP;
GO
BEGIN TRAN;
GO

And then checking sessions from another window:

sp_WhoIsActive @get_locks = 1;

If you think that's your biggest problem, though, it's probably time to check in with users and ask them about their biggest database complaints. I think you're probably barking up the wrong tree. I can only wish my biggest problem would be open sleeping transactions with nothing locked....


Implementation of stack in MySQL I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion.

The Stack must be a collection of tables with the stack top pointing at a particular table. We may have various such stacks in our DB. The stack length may increase if for example the field from the table is full. Likewise the stack length may decrease in the opposite manner!

Could you please give me an implementation idea! is it really possible at first place? Hope I'm clear with my question.

If you really need to do a stack in MySQL I think you should clarify your answer as to why.  One key aspect of MySQL is that it makes a bunch of fundamental assumptions that it is serving to a single application's codebase.  If you are on MySQL, the best thing to do is implement this application-side.  If you really need to do this in the database, you need to rethink how you are using MySQL, or whether you want to use a different database.  Treating SQL as a public API on MySQL is problematic because applications can set sql_mode which can do things like determine how invalid data is treated or even what counts as valid data, and the dba has no control over what sql mode an application is using.

This being said, a stack on a db might look like (assuming you are not doing composite keys):

CREATE TABLE stack_demo (
   id bigint autoincrement primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);

Or better yet, the PostgreSQL version:

CREATE TABLE stack_demo (
   id bigserial primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);


Query data in a BOM hierarchy I have a data table with about the following schema:
ID (Int)             --ID of the entry
Parent_ID (Int)      --ID of the parenting entry, or 0 if there is none
Level (Int)          --Level in the hierarchy of this entry. Starts at 1.
User (int)           --ID of the user who owns this entry

I've seen this type of hierarchy referred to as BOM (bill of material).
I must admit, I'm not very experienced with how to approach BOM from a SQL perspective. It doesn't seem that difficult to approach it from procedural code in an application but SQL is another beast here.
Anyway, what I would like to achieve is to get all entries of a certain user with the ID X. But since it's possible that entries earlier in the hierarchy belong to another user, just filtering with WHERE user = X may and in all likelihood will break the hierarchy.
In other words, I need all entries WHERE user = X but also all entries higher up in the hierarchy.
I'm currently reading up on CTEs as I have an inkling this might be the right approach but I'd appreciate any hints in the right direction. If you have a working code example you're willing to share, that would be much appreciated, too.

As you mentioned you can use a CTE, actually a Recursive CTE, to do what's known in the industry as a BOM Explosion.
If I understand your logic correctly, this is essentially the query of the Recursive CTE you'd want to use.
WITH CTE_Recursive AS -- Recursive CTE to explode the BOM
(
    -- Base Case
    SELECT ID AS ChildId, Parent_ID AS ParentId, [Level], [User]
    FROM BOM
    WHERE [User] = 5678-- Filter on the User to start with here

    UNION ALL -- This allows us to call the CTE recursively

    -- Recursive Case
    SELECT R.Parent_ID AS ChildId, B.Parent_ID AS ParentId, R.[Level] - 1 AS [Level], B.[User] -- Notice how the Parent of the previous call becomes the Child here, as it recursively works its way up the tree (and the new Parent comes from the BOM table, and is the Parent of the previous Parent).
    FROM CTE_Recursive AS R
    INNER JOIN BOM AS B
        ON R.ParentId = B.ID
)

-- Final SELECT for the results
SELECT ChildId, ParentId, [Level]
FROM CTE_Recursive

Let me know how this works out as I don't have any data to test it with at the moment, and you basically want a reverse BOM explosion, since you need to filter on a specific child and get all ancestors. (A regular BOM explosion is what I'm more used to writing recursive CTEs for, where you start with a specific ancestor and get all children.) Also please see the link I provided above for more examples and information on using recursive CTEs.


How to remove specific characters from strings in sql I've got a table with a column. I want to change a part of some strings in the column.

Input

6|Drivers Manuals

BH-EB531

TRST-BACMN-

Desired output

Drivers Manuals

BH-EB531

TRST-BACMN

Below was the code I tired to execute

 select REPLACE(REPLACE(COL, '6|', ''),'-','') FROM Table

Output

Drivers Manuals

BHEB531

TRSTBACMN

the script need to remove '6|' and last character only if it was '-'

Can any one help me to achieve desired output

SELECT (
        REPLACE
                (CASE
                    WHEN RIGHT (COL, 1) = '-'
                        THEN LEFT(COL, LENGTH(COL) - 1)
                    ELSE COL
                END, '6|', ''
                )
        )
FROM Table


Insert set value into DB I'm pulling values from RESTORE FILELISTONLY FROM DISK for multiple backup files into a temp table.
I am trying to insert a set value (@dbSet) to a column (@dbSetC) but when running my insert it's not allowing me.

I'm not sure how to run the INSERT command and have both the result of the EXEC in the first columns then the @dbSet value into the @dbSetC column.

create table #tmpA
(
    idx int identity(1,1),
    lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
    Select
    'C:\temp\db.bak' union select
    'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)

declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
    SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
    SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

    INSERT INTO #tmp
        EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
        SET @countr = (@countr + 1)
END

A possible solution would be to create a #tmpHold table with the same columns as #tmp minus the dbSet column.  Insert the results of the exec into #tmpHold and then insert into #tmp the data from #tmpHold plus the extra column @dbSetc.

create table #tmpA
(
    idx int identity(1,1),
    lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
    Select
    'C:\temp\db.bak' union select
    'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
    ,dbSetC int
)

create table #tmpHold
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)
declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
    SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
    SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

    TRUNCATE TABLE #tmpHold;
    INSERT INTO #tmpHold    --insert results of EXEC into #tmpHold
        EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
    INSERT INTO #tmp    --insert #tempHold + @dbSet into #tmp
        SELECT *,@dbSet FROM #tmpHold
    SET @countr = (@countr + 1)
END


Trigger to update a date field on one table when another table is updated I want to be able to update a date filed in one table when another table is updated.
I have the following two tables: Transdetails, Customers.

In the Customers table I have a field called ZLastDateOfSale. I want this field updated with the current date when Transdetails.TradingDate is updated per Customer.

Can you please help me create this Trigger?

My Example is below:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        update dbo.Customers
            set dbo.Customers.ZLastSale = dbo.Transheaders.TradingDate
        from dbo.Customers
    END

Updated Example 11/05/2014:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        UPDATE c
            SET ZLastSales = i.TradingDate
        FROM dbo.Customers AS c
          JOIN inserted AS i
            ON i.UniqueID = c.UniqueID     -- use the appropriate column for joining
          JOIN deleted AS d
            ON  i.AccountID = d.AccountID
            AND ( i.TradingDate <> d.TradingDate
               OR d.TradingDate IS NULL
                ) ;
    END ;

Hello,

Thank you so much for your input and response.
I changed your provided example, ran the script and it was successful, however, what it did was to update the ZLastSale date on all Customers with todays date. This field should be blank and only update moving forward from now when the Trigger fires. This field should only be updated upon a sale from a Customer.

Also, when I tested entering a new transaction with a date of 30/05/2014 it didn’t update the required ZLastSale field. It is displayed as 11/05/2014 so something is not working right somewhere.

My ID’s for each table are:

Customers.UniqueID and Transheaders.AccountID

Thanks again for your help.

You should be using the inserted and deleted pseudo tables to find out the  rows for which the column was affected by the trigger - and then update only the related rows in the second table:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        UPDATE c
            SET ZLastSale = i.TradingDate
        FROM dbo.Customers AS c
          JOIN inserted AS i
            ON i.CustomerID = c.CustomerID     -- use the appropriate column for joining
          JOIN deleted AS d
            ON  i.TransheadersID = d.TransheadersID
            AND ( i.TradingDate <> d.TradingDate
               OR d.TradingDate IS NULL
                ) ;
    END ;


Index Key Column Order I have created a joining table for many-to-many relationship.

The table only has 2 cols in it, ticketid and groupid

typical data would be

groupid    ticketid
20         56
20         87
20         96
24         13
24         87
25         5

My question is when creating the composite key should I have ticketid followed by groupid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED
    (
        [ticketid] ASC,
        [groupid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

Or the other way, groupid followed by ticketid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED
        (
            [groupid] ASC,
                    [ticketid] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]

Would searching the index be quicker in option 1 as the ticketid has more chance of being unique then the groupid and they would be at the start of the composite key? Or is this negligible?

This all depends on how exactly you are retrieving data from this clustered index.  The clustered index will be sorted by the leading key column, followed by sorting on each subsequent key column as defined in the index definition.  The best way to see this is through an example.

Test Object Setup
use TestDB;
go

if exists (select 1 from sys.tables where name = 'TestTable2')
begin
    drop table TestTable2;
end
create table TestTable2
(
    groupid int not null,
    ticketid int not null
);
go

insert into TestTable2(groupid, ticketid)
values
    (20, 56),
    (20, 87),
    (20, 96),
    (24, 13),
    (24, 87),
    (25, 5 );
go

Test 1 (groupid, ticketid) Key Column Order
Now let's create the clustered index defined on the key columns (groupid, ticketid):
alter table TestTable2
add constraint PK_TestTable2
primary key clustered (groupid, ticketid);
go

Now we want to look at how this data is stored in the page:
dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:310

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 310, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

We see that it is in fact sorted by groupid first, then ticketid.  This will result in the follow query being an index seek:
-- this is an index seek
select *
from TestTable2
where groupid = 25;

And this query will be a scan because it can't seek on ticketid:
-- this is an index scan
select *
from TestTable2
where ticketid = 13;

Test 2 (ticketid, groupid) Key Column Order
But now let's change it up and swap the order of key columns:
alter table TestTable2
drop constraint PK_TestTable2;
go

alter table TestTable2
add constraint PK_TestTable2
primary key clustered (ticketid, groupid);
go

Now let's take a look at how the data is on the index page:
dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:308

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 308, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

Now we see that the data is sorted by ticketid first, then groupid.  This will change the behavior of our test queries as follows:
-- this is an index scan
select *
from TestTable2
where groupid = 25;

-- this is an index seek
select *
from TestTable2
where ticketid = 13;

Summary
So as you can see above, it boils down to your workload and the kind of queries that are hitting this clustered index.  It's the different between being able to seek based off of the WHERE clause (or a JOIN) or scan depending on the sequential ordering of the key columns and how they are defined.


Altering same table across multiple databases I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases.

I am not sure I know what the right procedure for this is but I think it should go a little something like this?

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT datname FROM pg_database WHERE datistemplate = false
  LOOP
    EXECUTE 'ALTER TABLE public.' || quote_ident(r) || ' ALTER VARCHAR(200);';
    EXECUTE ...
  END LOOP;
END;
$$;

Any thoughts?

I think the fundamental question you need to ask yourself is how much of a guarantee you need that this has propagated to all db's.  Do you need every change to commit or roll back together?  If so you should probably write a script in a programming language of your choice (Perl, Python, or the like), send the DO block there, and then use PREPARE TRANSACTION on every db connection (you'd open up one connection per db).  If all of these are successful you commit.  If any one fails, you roll back.

This is actually one very helpful application of PostgreSQL's transactional ddl in that you can guarantee that all your databases have schemas which are kept in sync.


A query on two joined tables I have 2 tables. The first table is feeds and (feeds.ID) is the primary key. The second table is items. feeds.ID is related to items.feed_ID, so with a simple inner join I can extract the data of each feed.

Now, I deleted some of the feeds but didn't delete them from the items table. I want to find the items which have no resource on the feeds table. On the other hand, I want to find the useless items. What is the correct query?

I have tried this:

select * from items where feed_id NOT IN
    (select id from feeds
        inner join items on feeds.id=items.feed_id group by feed_id) group by feed_id

but it shows an error.

Try this query:

select * from items where feed_id NOT IN (select id from feeds)

Also set foriegn key relationship with delete cascade. So it will get deleted from dependent tables as well.


Can't connect to MySQL Cluster Storage (data) nodes I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another machine using NDBAPI.

When I tried to connect from the same host it worked, but when I try to connect from a different machine I get a socket timeout.

Any clues? There is no strange network setup.

Thanks!

Couple of things to check...

In your config.ini file, check that you haven't specified the host that is allowed to connect to this ndbapi slot - check the 'hostname' parameter in your config.ini file or run "ndb_mgm -e show". If you leave out the hostname parameter then you can connect the data node from any host
Firewall. Temporarily turn off your firewall and see if that fixes it. If you need to lock down the ports used by the data nodes then set the ServerPort parameter in the [ndbd] sections or the [ndbd default] section of the config.ini file. The firewall also needs to allow port 1186 (by default) for the management node.
You've filled up all of the API slots - run "ndb_mgm -e show" to check. If needed, add extra [mysqld] or [ndbapi] sections to your config.ini file and restart your nodes.


Transactional Replication Reinitialize I'm using SQL Server transactional replication and looking for ideas on reducing the amount of downtime caused by re-initializing subscriptions.

I have multiple publishers pushing data to a single subscriber across the WAN.  I recently had to recover the subscriber from backup.  Upon restoring the backup, the subscriber was out of sync with all of the publishers.  The only way I knew to get the subscriber back in sync with all of the publishers was to re-initialize all the subscriptions.

Since the publishers and subscriber are separated by a WAN connection, this process was extremely time consuming.  In some cases it takes 24+ hours to generate a snapshot.

Any ideas on how to reduce the downtime caused by this type of scenario?  The only idea I have at this point is to use Always On with the subscriber.

Whether you use a backup or a snapshot, you need to routinely pre-stage the data needed for reinitialization near the subscriber.  The goal is to always  have a backup/snapshot that is within the Distribution Retention Period, and close to the subscriber.

Also whenever you are using a snapshot to initialize a remote subscriber, it's better to copy the snapshot local and use an Alternate Snapshot Location.

Also note that if you have a single subscriber database with multiple subscriptions, you can't initialize from a backup.


Is there any difference between Rem and -- comments for SQL*Plus? On SQL*Plus prompt, both Rem and -- qualify as comment indicators:

Rem this is a comment
-- this is also a comment
create table emp (
id number primary key,
name cvarchar2(40));

Is there any difference at all between the two commenting techniques?

The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot.  The following will work in SQL*Plus:

REM comment
-- comment
/* comment */
begin
   DBMS_OUTPUT.PUT_LINE('Test'); --comment
   DBMS_OUTPUT.PUT_LINE('Test'); /* comment */
end;
/

These will not:

begin
   DBMS_OUTPUT.PUT_LINE('Test'); REM comment
end;
/

begin
   REM comment
   DBMS_OUTPUT.PUT_LINE('Test');
end;
/

The 11.2 documentation on all comment types has more comment information.  The basics are...

  You can enter comments in a script in
  three ways:

  using the SQL*Plus REMARK command for single line comments.
  using the SQL comment delimiters /*... */ for single or multi-line
  comments.
  using ANSI/ISO (American National Standards Institute/International
  Standards Organization) comments - -
  for single line comments.

The documentation also includes notes on four places that comments should not be used, but these do not include any further differences.


Stored Procedure has too many arguments Here is the code for my stored procedure, lots of things going on and lots of parameters being passed. I'm knew to sql and I'm sure there's a far better way of performing these functions but need some help! Thanks in advance! :)

ALTER PROCEDURE [dbo].[spCompleteRepair]

@unit_id INT,
@BasicHB NVARCHAR,
@AdvHB NVARCHAR,
@CCRepair BIT,
@CCReplace BIT,
@Fan NVARCHAR,
@RE BIT,
@notes NVARCHAR,
@bin_num NVARCHAR,
@loc_num NVARCHAR,
@repairserial NVARCHAR

AS

BEGIN

INSERT INTO RepairDetails
    (unit_id, BasicHB, AdvHB, CCRepair, CCReplace, Fan, RE, notes, completion_date)
VALUES
    (@unit_id, @BasicHB, @AdvHB, @CCRepair, @CCReplace, @Fan, @RE, @notes, GETDATE())

UPDATE Units
SET bin_num=@bin_num, loc_num=@loc_num, repair_serial=@repairserial, unit_status=1
WHERE unit_id=@unit_id

UPDATE OutBins
SET filled=1
WHERE bin_num=@bin_num AND loc_num=@loc_num

UPDATE Orders
SET Orders.order_status = 1
FROM Orders
--check to see if there are units for the order
WHERE EXISTS (
        SELECT *
        FROM Units
        WHERE Units.order_id = Orders.order_id
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM Units
        WHERE Units.order_id = Orders.order_id
            AND Units.unit_status <> 1)

END

CODE TO CALL PROCEDURE:

 using (SqlCommand command = new SqlCommand("spCompleteRepair", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@order_id", txtorder_id.Text);
                    command.Parameters.AddWithValue("@unit_id", txtunit_id.Text);
                    command.Parameters.AddWithValue("@BasicHB", ddlBasicHB.Text);
                    command.Parameters.AddWithValue("@AdvHB", ddlAdvHB.Text);
                    command.Parameters.AddWithValue("@CCRepair", ddlCCRepair.Text);
                    command.Parameters.AddWithValue("@CCReplace", ddlCCReplace.Text);
                    command.Parameters.AddWithValue("@Fan", ddlFan.Text);
                    command.Parameters.AddWithValue("@RE", ddlRE.Text);
                    command.Parameters.AddWithValue("@notes", txtNotes.Text);
                    command.Parameters.AddWithValue("@repairserial", txtRepairSerial.Text);
                    command.Parameters.AddWithValue("@bin_num", ddl_binnum.Text);
                    command.Parameters.AddWithValue("@loc_num", ddl_locnum.Text);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }

The C# code you are using to invoke the stored procedure is passing in @order_id as the first parameter.  That parameter is not referenced in the actual stored procedure.  Try removing that parameter from the C# code.


Why don't SQL Server queries use more than 7MB/sec of disk I/O I have an SSD that, using IOmeter test, shows performance over 200MB/s.  However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec.  This holds true even for queries that take over 2 minutes to run.  What could the bottleneck be that it is only using 7MB/sec from SSD?

I'm running:

Windows Server 2012 Standard
SQL server 2008 r2
Intel i7 3820
32GB of ram
sandisk SSD

From the comments chain, it looks like you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network. It (typically) isn't.

As @MartinSmith hinted at (twice) the most likely explanation for that is SSMS or the application you're using not consuming the results as fast as SQL Server is serving them.  Follow either of the suggested methods to remove the consumption of the rows from your measurement and you'll get a true(r) picture of the maximum IO throughput:

Discard results after execution
Assign the result to scalar variables

Just in case you haven't already, you'll obviously need to DBCC DROPCLEANBUFFERS to ensure the data is actually read from disk rather than buffer cache. Usual caveats of "on test only, don't do this in an active live environment" etc apply.

Honing in on a couple of your other comments:

  While executing a query that returns 9 million rows CPU usage will stay around 13% with 9% attributable to sqlserver... wouldn't it return the results quicker than 3 min if all data was in RAM?

What exactly are we testing here, how and why? If your 9 million row query is anything other than a SELECT * FROM dbo.SomeTable then there are a 1001 factors that come in to play, other than just raw IO throughput.

Your Intel I7-3820 is a 4-core processor. If your test query doesn't generate a parallel plan, I'd be surprised if you could thrash more than 20% CPU utilisation from the system.

The 3 minutes to return 9 million rows is very suspicious and suggests we're not getting a full picture of what your testing. My guess would be this is a case of sub-optimal (non-parallel) query plan, stuffed full of nested-loop operators pulling millions of rows i.e. not just a single table SELECT to verify the IO consumption.

I suggest:

SELECT * to test just IO through SQL Server.
New question with the execution plan of your query if you want to dig into why it doesn't saturate the IO.


logins without any users There are several logins in my SQL Server that don't have user mappings to any database; they are simply dummy logins.

Someone created the login but it does not have any associated users.

I need to a script to list out those logins.

Thanks in advance.

The query below is a good starting point.  It returns a list of logins that have no matching user in any attached database.  Be aware there are many reasons a login might exist at the server level, but not have an explicitly created user in any user database.  Before removing the logins shown by this code, you should validate that they are not needed.  As an example, this code may list the service accounts used to run SQL Server services; removing them might be a bad idea.

It filters out server roles, since they cannot be added to databases as users.  Similarly for certificate-mapped logins, since they can only be used for code signing.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT dp.sid
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
WHERE dp.sid IS NOT NULL
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

PRINT @cmd;

DROP TABLE IF EXISTS #database_sids;
CREATE TABLE #database_sids
(
    [sid] int NOT NULL
);
INSERT INTO #database_sids ([sid])
EXEC sys.sp_executesql @cmd;

SELECT sp.name
    , sp.type_desc
FROM sys.server_principals sp
WHERE NOT EXISTS (
    SELECT 1
    FROM #database_sids ds
    WHERE ds.sid = sp.sid
    )
    AND sp.[type_desc] NOT IN (
          N'SERVER_ROLE'
        , N'CERTIFICATE_MAPPED_LOGIN'
        )
ORDER BY sp.name;

The query gathers the list of security identifiers from each database in the SQL Server instance, then returns logins that don't appear in that list of security identifiers.

There are a couple of "gotchas" with this approach.  If any databases are offline when the code runs, users in the offline database(s) will not be excluded from the output, resulting in possible false-positives.

Since the script uses the security identifier, instead of the name of the user, it may return false positives for users and logins with the same name, but different SIDs.  This can easily occur If you restore a database from a different source SQL Server and run this code against it without "fixing" the orphaned users first1.

1 - I am associated with the site SQLServerScience.com and wrote this blog post


Solving high page load time using SQL Profiler I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what is going on during the login and see what I can change. However, I'm noticing a few oddities.

If I store to table the "duration" column in profiler is [n]*1000
compared to what is on the profiler GUI. (ie. GUI Windows shows
"exec proc1" and duration 50, but in the SQL trace table "exec
proc1" shows 50000.
If I add up all the duration values they are
    not even 1/10th of the time the page takes to load. (I know profiler
    will slow down the page load, but even without profiler a "fresh"
    page load is 10 seconds+ on average and profiler duration (ms) add
    up to roughly 1.1 second.
Also in between each run I am running CHECKPOINT, DBCC
DROPCLEANBUFFERS, DBCC FLUSHPROCINDB () to ensure each test is
"fresh".

Is there anything I'm doing wrong that would make these discrepancies occur? Is there anything you would recommend to change in my approach to solving this problem?

When you run DBCC DROPCLEANBUFFERS, DBCC FLUSHPROCINDB you require that the data is loaded into RAM and all plans are recompiled.

In a live system, you'd only this double whammy on startup or after heavy maintenance that evicted all data from cache and all plans were invalidated by a statistics update.

Your typical response time would be for the 2nd and subsequent calls.

On a really "cold" system your app in IIS would be unloaded too and would incur a CLR compile overhead


Deleting aged postgresql rows with foreignkey constraints I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated

For instance, if I run DELETE FROM links_reply WHERE id=3;, I get a violates foreign key constraint error.

But when I run DELETE FROM links_groupseen WHERE which_reply_id=3; and then run DELETE FROM links_reply WHERE id=3;, everything's fine. Delete the child first, then the parent. Works!

Things get complicated though, when the respective tables are huge, and I'm trying to delete all rows before a certain date. E.g. I want to DELETE FROM links_reply WHERE "submitted_on" < now() - interval '30 days';. In this particular case, how do I take care of 'the child table' first? What command do I run?

First start a transaction:

begin;

Start deleting all from links_groupseen first:

delete
from links_groupseen
where which_reply_id in (select myproperid from links_reply where "submitted_on" < now() - interval '30 days')

and then delete from links_reply:

DELETE
FROM links_reply
WHERE "submitted_on" < now()- interval '30 days';

and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.


Is inserting multiple queries takes same time as that of single query? I have a requirement where I have to insert a lots of rows.For inserting we use like this

insert into emp(name,age) values('abc',12);

This will insert only one row.For inserting multiple rows we can either write insert query multiple times or write a single query with multiple values.For example

condtion1

insert into emp(name,age) values('abc1',121);
insert into emp(name,age) values('abc2',122);
insert into emp(name,age) values('abc3',12);

condition2

insert into emp(name,age) values('abc',12),('abc2',122),('abc3',12);

My question is are both (condition1 and condition2) above takes time?I guess condtion2 takes less time than condition2. If my guess is true then what is the reason for it?

In SQL Server at least (can't speak for the other RDBMS you've mentioned, sorry), a single statement will scale better than multiple, to a point. You can test this yourself using your exact statements and data, of course; nobody here can test that for you with your specifics, and your specifics may tilt things one way or another. "Which is faster, x or y?" questions are generally discouraged here because you can test them in your own environment much faster than any of us could throw guesses and logic at you. Especially when you're trying to get an answer for all of the database platforms you've listed - nobody is an expert in all of those and any such answer would either be very biased (like this one) or far too blanket to be useful.
In general, though, the overhead of preparing individual statements (and possibly sending them separately depending on your code and the behavior of your provider) should add up, much like @mustaccio said:

If you want to send three letters, would you go to the post office once carrying three letters, or three times carrying one letter?

This would especially be true in your scenario if each small statement is broken up across individual packets and even different connections. Again, I have no idea how your provider works or how your code sends these statements - if it's a single batch of a variable number of statements, that can actually be harder for SQL Server to optimize than individual single-statement batches, since SQL Server optimizes at the batch level.
Note that the VALUES() clause has an arbitrary limit of 1,000 values, so you may need to create multiple statements depending on how many values you have. The reason is concern over compile time, as Paul White explains here. Also note: Oracle has the same restriction.
According to Martin Smith's testing, compile time at least is minimal and relatively invariable up to a little over 250 values. See these charts (for details please see his answer):

If you go with a VALUES() clause be aware that each variation (meaning number of actual value sets) will generate its own plan, regardless of whether you use properly parameterized statements or just inline constants, and this is true even when the database's parameterization setting is set to simple. So you may want to consider using the optimize for ad hoc workloads server setting (lots of info here and here to prevent single-use variations from filling the plan cache (generally a good idea for most systems anyway, unless you are CPU-bound and compilation costs are proven to be excessive).
A better answer to this problem is to use table-valued parameters (TVPs), which allow you to send sets of structured data through a single parameter, giving you an efficient way to pass data and a single plan that can be reused regardless of the number of values passed. The catch here is I'm not sure Java understands what those are (in C# you can send, say, a DataTable as a Structured parameter).


Selecting rows conflicting values in WHERE clause I am having trouble selecting rows in MySQL. I do not want some rows to be selected, but they are being selected.

My query:

SELECT  name,url
    FROM  additions
    WHERE  enabled = 1
      AND  picture = 1
      AND  name LIKE '%value%'
      OR  tags LIKE '%value%'
      OR  subtext LIKE '%value%';

I do not want the rows that do not have a picture (picture = 0) and also rows that are not enabled (enabled = 0) to be selected. I think the rows match because either the name, tags and/or subtext matches the value, because of the OR condition in WHERE clause.

I want only the rows to be selected that have a picture and are enabled, regardless of the match in name, tags, subtext. How can I achieve that?

The answer: as ypercube said, the solution is:

adding parentheses around the OR conditions

like so:

SELECT name,url FROM additions WHERE enabled = 1 AND picture = 1 AND (name LIKE '%value%' OR tags LIKE '%value%' OR subtext LIKE '%value%');

With the parentheses, AND will take precedence and the entire section in the LIKE value comparison will have to be true together with the other evaluations, like enabled = 1 and picture = 1. Without the parentheses the OR statements are evaluated individually (and if one of them happens to be true, a row will be selected).


Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS I found this wonderful code here

    CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

    GO
    CREATE PROCEDURE [dbo].[sp_UseStringList]
        @list StringList READONLY
    AS
    BEGIN
        -- Just return the items we passed in
        SELECT l.Item FROM @list l;
    END

    GO

but I dont know how to test it via SSMS please

declare @list   StringList  = '1,2,3,4,5'
exec sp_UseStringList @list

gives the error:

Operand type clash: varchar is incompatible with StringList

How can I test the list '1,2,3,4,5' please

As you have declared dbo.StringList to be a User-Defined Table Type, you have to utilise it as you would a table.

So instead of assigning '1,2,3,4,5' as a string, you must INSERT this value into the table.

There's a pretty decent example in BOL, but I'll create one based on your example above.

CREATE TYPE [dbo].[StringList] AS TABLE(
  [Item] [NVARCHAR](MAX) NULL
);

GO
CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

GO

DECLARE @StringList StringList

INSERT Into @StringList values('1,2,3,4,5')

EXEC sp_UseStringList @StringList


Two nullable columns one required to have value No-Explanation Question:

Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have a value

Problem Description:

Let's say I have table called Expense

and have 2 dates :

prevision_expense_expiration_date DATE NULLABLE
expense_payment_date DATE NULLABLE

the logic of those 2 columns is the following:

I made a purchase of something and I know I have to pay for it, some date, like a phone bill. I will enter this as an expense with a expense_payment_date. This date is the supposed date I should pay but not the actual date of the payment, like the expiration date of the invoice.

In other situation I sell a gift card of some provider for it's service. I may have the expense of buying to my provider the service transfered to my client only if the client redeem the card. Hence the gift card has an expiration date, I want to do a prevision for that 'expense' without inserting as an expense for the time the gift card is valid, if the gift card expires, that 'expense' should not enter into the account system.

I know I can have 2 equally tables called prevision_expense and confirmed_expense but doesn't sounds right so I have in the same table, 2 dates, nullable, but I want to constrain or something so that one is always required.

There's another posible strategy:

payment_date DATE NOT NULL
is_prevision_date BOOL NOT NULL

So, in this case, if the date is prevision bool value would be 1, otherwise will be 0. No null values, all is good. except that I want the option to store BOTH values when first I have a prevision date and THEN (lets says two days later) have a confirmed date for that expense, in which case with strategy 2 i won't have that option.

Am I doing everything wrong in the database design ? :D

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error


Invalid default value when trying to modify a different column Problem: I'm trying to modify my column's datatype from a smaller VARCHAR to a larger one so that I can take in IPv6 addresses as well (my current column only takes a size large enough for IPv4 addresses)

Example:
ALTER TABLE accounts MODIFY COLUMN ip VARCHAR(45)

shoots back an error as

Invalid default value for 'birthday'

I'm a bit confused because while I do have a birthday column in my accounts table, why is an error being thrown here about it when I'm not touching it?

SHOW CREATE TABLE accounts

What versions?  There have been several changes, some incompatible, regarding DATE and other temporal types.  This one jumps out:

  ----- 2013-09-21 5.7.2 Milestone 12 & 2013-07-31 5.6.13 -- Bugs Fixed -- Incompatible Change -----

  It is possible for a column DEFAULT value to be valid for the sql_mode
  (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) value at table-creation time but invalid for the sql_mode (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) value when rows are inserted or updated. Example:

  SET sql_mode = ''; CREATE TABLE t (d DATE DEFAULT 0); SET sql_mode =
  'NO_ZERO_DATE,STRICT_ALL_TABLES'; INSERT INTO t (d) VALUES(DEFAULT);

  In this case, 0 should be accepted for the CREATE TABLE (
  http://dev.mysql.com/doc/refman/5.7/en/create-table.html ) but
  rejected for the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ). However, the
  server did not evaluate DEFAULT values used for inserts or updates
  against the current sql_mode. In the example, the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ) succeeds and
  inserts '0000-00-00' into the DATE (
  http://dev.mysql.com/doc/refman/5.7/en/datetime.html ) column.

  The server now applies the proper sql_mode (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) checks to generate a warning or error at insert or update time.

  A resulting incompatibility for replication if you use statement-based
  logging (binlog_format=STATEMENT (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
  )) is that if a slave is upgraded, a nonupgraded master will execute
  the preceding example without error, whereas the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ) will fail on the
  slave and replication will stop.

  To deal with this, stop all new statements on the master and wait
  until the slaves catch up. Then upgrade the slaves followed by the
  master. Alternatively, if you cannot stop new statements, temporarily
  change to row-based logging on the master (binlog_format=ROW (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
  )) and wait until all slaves have processed all binary logs produced
  up to the point of this change. Then upgrade the slaves followed by
  the master and change the master back to statement-based logging.
  (Bug #68041, Bug #16078943)


MySQL replication slave using percona and docker I am trying to run a MySQL replication slave in a docker container. We are running MySQL 5.7.24-27-log in production and it's from the percona repository (Ubuntu 18.04).

I have used xtrabackup to backup, prepare and ship a starting data set for replication, then I started the percona docker image (docker pull percona) like so:

$ docker run --name mysql-replication -v /replication/data:/var/lib/mysql -v /replication/docker.cnf:/etc/mysql/docker.cnf:ro -e MYSQL_ROOT_PASSWORD=xxxx -P -d percona

My docker.cnf simply notes the server-id (I copied it from the percona image).

[mysqld]
skip-host-cache
skip-name-resolve
bind-address    = 0.0.0.0
server-id       = 4

After then using CHANGE MASTER etc. I have the replication running just fine.

My intention (as per the volume mount -v /replication/data:/var/lib/mysql) is to keep all of the MySQL data on the host machine, and treat the replication docker container as ephemeral, i.e. no state held in the container. It should also be easy to start up another replication container should I need one by stopping the existing container, copying the data elsewhere, changing the server-id and running a new container.

To test this, after it was set up and running properly (I watched Seconds_Behind_Master drop down to 0), I figured I should be able to delete the container and recreate it, and the replication would still work fine. I therefore tried this:

$ docker stop mysql-replication
$ docker rm mysql-replication
$ docker run ... // same command as before

When I do this and connect to MySQL running in the container I find that Slave_IO_Running is No, and after starting it (START SLAVE;) I get the following (as seen in SHOW SLAVE STATUS;):

Last_Error: Could not execute Update_rows event on table databasename.tablename; Can't find record in 'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000681, end_log_pos 9952

(databasename and tablename are real database and table names)

At first I thought that I had probably mucked something up but I have tried this a number of times now to try and solve the problem. Using docker diff mysql-replication shows no changes to the running container that seem to be of significance:

$ docker diff mysql-replication
C /run
C /run/mysqld
A /run/mysqld/mysqld.pid
C /var
C /var/log
A /var/log/mysql

Googling has suggested that I need to use RESET SLAVE; and START SLAVE; but this doesn't seem to resolve it - it's like the data (outside the container) is no longer in sync with the master and replication therefore cannot continue.

Can anyone pick holes in what I'm doing please?

Thanks so much.

The root cause of this issue was the absence of the relay-log option in the mysql.cnf file (or in this case, due to the docker volume mounts, the docker.cnf file). This lead to the creation and usage of files such as 89726507f176-relay-bin.000002 initially, where 89726507f176 is the host name of the machine (randomly assigned by the docker daemon when an image is created). When the container was stopped, removed and recreated, a new set of files was created and used (e.g. be0c801d95bc-relay-bin.000407) but this caused sync issues.

By explicitly specifying a value for relay-log in the docker.cnf file the container was able to be removed and recreated without problems.

As a side note, I suggested also that there was a problem with the /var/log/mysql directory not being mounted - this is not the case. If however you specify a value of log_bin = /var/log/mysql/mysql-bin.log for example, then this is a requirement. If you do not specify this path, it seems the binary logs are stored locally in /var/lib/mysql which is already mounted outside the container.

My final docker.cnf file is as follows:

[mysqld]
skip-host-cache
skip-name-resolve
bind-address        = 0.0.0.0

binlog-ignore-db = mysql
replicate-ignore-db = mysql

log_bin = /var/log/mysql/mysql-bin.log

relay-log   = replication-1
server_id   = 1

Note: server_id = 2 on the replication slave.

Also note that without the relay-log option the command SHOW MASTER STATUS; returned no results on the master database container.

There is a possible outstanding issue yet which is that by default when you use docker stop it asks the container to terminate (by sending a SIGHUP to the docker entrypoint command) and if it doesn't terminate within 10 seconds it is forcefully stopped. I need to ensure taht this is given sufficient time to shut down as it could take a little while to sort itself out while under load, possibly resulting in data loss as a result.


Semicolon in SELECT query I cannot find documentation about the usage of semi-colons in a select query, for example:

SELECT TEST.PARTIAL_PATH + ';' + G.ICON FROM GADGETS G
.... some inner joins...

Can somebody point to the documentation that gives some examples or try to explain it here?

This is just concatenating a semicolon between two fields. Think of it like selecting LNAME + ', ' + FNAME or something similar.

It's generally fine to have presentation layer items like this in the select list, but it's important to avoid them as predicates (anything in a join or where clause), because in SQL Server, they're not SARGable.

SQL Server uses +, which is different from other platforms, that may use || to concatenate things, which is an issue I've written about in the past.


Avoiding repetition without creating a view Suppose that I have a query Q1 and I need to run a query like the following:

Q1
union
select *
from (some query that uses Q1 outcome)

I would like to do that:

Without creating any view
Without running twice the Q1 query.

How can I do that on PostgreSQL?

You can use Common Table Expressions for this:

WITH q1 AS
  ( SELECT ... )
SELECT *
  FROM q1
UNION
SELECT *
  FROM (some query that uses q1 outcome)
;

or even:

WITH q1 AS
  ( SELECT ... )
TABLE q1
UNION
SELECT *
  FROM (some query that uses q1 outcome)
;

You can also have more than one CTEs used in sequence:

WITH q1 AS
  ( SELECT ... )
  , q2 AS
  (some query that uses q1 outcome)
  , q3 AS
  (some other query that uses q1 and/or q2 outcome)
SELECT *
  FROM q1
UNION
SELECT *
  FROM q2
UNION
SELECT q3.*
  FROM q2 JOIN q3 ON ...
;

No view created and no running twice of the Q1 query - well, that's implementation dependent. What path of execution is actually followed depends on the optimizer and several other factors. The query is more compact and elegant but it doesn't mean it's more efficient than expanding it.


PostgreSQLpgagent "Running at: is not runing" I have a problem to run pgAgent job for PostgreSQL 9.6 under ubuntu 16.04.
The job is never executed.

I already configured .pgpass and added:

localhost:5432:*:postgres:mypass

Also modified the permission for .pgpass:

$ touch ~/.pgpass
$ chmod 0600 ~/.pgpass

After that I run pgAgent with no problem:

$pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres

When I check the process for pgAgent I would get this result:

user@gis-srv:~$ ps aux | grep pgagent
user   153179  0.0  0.0 113844  9392 pts/26   S    17:15   0:00 pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres
user   153220  0.0  0.0 113844  9360 pts/26   S    17:16   0:00 pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres
user   153227  0.0  0.0  12944   936 pts/26   S+   17:16   0:00 grep --color=auto pgagent

Here is my job setting:
 Job Setting:

Schedule setting:

Step Setting:

My propblen that the job is never executed and there is no error in the errors log for PostgreSQL.
Please help me to find out why jobs never run.

I am receving the following errors:
from postgres.log:

2017-11-03 16:01:32.269 EDT [15344] postgres@postgres LOG:  could not receive data from client: Connection reset by peer

from pgagent.log:

(attempt 9): fe_sendauth: no password supplied
Fri Nov  3 16:01:32 2017 : WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied
Fri Nov  3 16:01:32 2017 : ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server.

Also I modified the permission .pgpass to allow everyone to access it. (Just for testing)

since you're specifying

pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres

change .pgpass to match it

127.0.0.1:5432:*:postgres:mypass

or specify

*:5432:*:postgres:mypass

Edit: And change the permissions of .pgpass back to 0600 else you'll get the message:

WARNING: password file "~/.pgpass" has group or world access; permissions should be u=rw (0600) or less

and for command line, it prompts for password


How to script permissions for all logins/users/tables/schemas Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be accurate in every instance. I am simply looking for a way to generate all permissions for a given database along with confidence that the script is correct and able to be used after a backup/restore from another instance. Does anyone have a method they have confidence in and use regularly

I have a couple of stored procedures that I wrote and use on a regular basis.

sp_SrvPermissions
sp_DBPermissions

They each have three outputs.

Principals : A list of the database/server principals and associated info including create/drop scripts.
Role membership : A list of what roles each database/server principal belongs to along with add/drop scripts.
Permissions : A list of individual permissions (EXECUTE, INSERT, UPDATE, CONNECT etc) for each database/server principal.

Obviously the Srv sp is for the Server principals and the DB sp is for the database level principals.  The DB sp also has an 'All' option for the database name.  With this option it runs through and pulls all permissions for all databases.

Note: This was originally intended as a research tool so there are a fair number of options that will let you parse down your data.  Just roles, everything but roles, just a specific principal name etc.


Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table? I am running the following version of Postgres on CentOS 7:

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

I am new to database operations and need some help understanding if/how I should do the following:

I have a single table "Table1" that has three columns of data:

| timestamp | Value1 | Value2 |

The Value1 and Value2 columns contain decimal values like the below:

| 1.6732 | 2.78093 |

I have around a million rows of data in this table.

I would like to be able to deduct Value1 from Value2 and insert the result of this calculation in a new column in the same table, so that the table permanently stores the computed value and ends up looking like the below.

timestamp | Value1 | Value2 | Value3

I am not sure if I can or should do this, and if it is possible, how I would create  a query to make this happen.

Please can anyone give any advice, examples or pointers on how I should go about doing this or point me in the right direction of how I can learn to do this myself?

Thank you...

I found the below which seems to have done the trick...

Firstly, I added the Value3 column to Table1 using the below SQL:

ALTER TABLE "Table1" ADD COLUMN Value3 NUMERIC;

Then I ran the below update and set statement:

UPDATE "Table1" SET Value3 = Value2 - Value1;

This updates the Value3 column with the result of Value2 - Value1.


Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods I got a question if i create a login and database user for a database named TEST, and i have given permissions to user as db_datareader only.

1) Where does this permission evaluated when a user connects and runs a DML query?
2) If permission is checked during early stage of Relational engine will execution plan be created and stored in cache?
3) My answer is transaction manager in storage engine which controls the locks and permissions am i right?

When your user without DML permissions submit the query, it's compiled. So nither parser nor optimizer controls permissions. The compiled plan (CP) can be seen using smth like this:

select *
from sys.dm_exec_cached_plans
     cross apply sys.dm_exec_sql_text(plan_handle) t
     cross apply sys.dm_exec_query_plan(plan_handle)
where t.text like '%your query text%'

But it's just a compiled plan, not executable plan.
Make note of plan_handle and paste it into

sys.dm_exec_cached_plan_dependent_objects()

It will show nothing, no exacutable plan is built.
I have no idea what name has a piece of code responsible for controlling permissions but the exact moment when the permissions are checked is when we passing from copiled plan (CP) to execution plan (MXC)


Insert data in multiple tables I have three tables and I want to insert data into that three tables as per their respective data.

Query:

with acupressure_point_data as (
    insert into acupressure_point(id, url)
        values('LL909', 'ww/33/11')
    returning id as acupressure_point_id
),
acupressure_sub_type as (
    insert into acupressure_point_region_sub_type
        (acupressure_point_id, region_sub_type_id)
        values(acupressure_point_id , '9898')
    returning id as acupressure_sub_type_id
);

But it gives the error as:

  "ERROR: syntax error at end of input Position"

I can not understood this. Please help me. Thanks

For PostgerSQL:

with acupressure_point_data as ( -- insert into first table with output
                                 insert into acupressure_point(id, url)
                                 values('LL909', 'ww/33/11')
                                 returning id as acupressure_point_id )
-- insert into second table, use CTE output
insert into acupressure_point_region_sub_type(acupressure_point_id, region_sub_type_id)
SELECT acupressure_point_id , '9898'
FROM acupressure_point_data
-- return id for both tables
returning acupressure_point_id, id as acupressure_sub_type_id;

MySQL does not allow INSERT in CTE. Use separate queries and LAST_INSERT_ID().


Measuring how long a query is IDLE IN TRANSACTION (postgresql + Django app) When looking at pg_stat_activity, how can I tell whether the state IDLE IN TRANSACTION is transient?

Can the table's columns give that information to me (e.g. xact_start or query_start)? Btw, waiting is set to f for all these queries. Maybe that means they're not waiting on any locks, and so everything is fine?

I'm trying to ascertain how bad the IDLE IN TRANSACTION problem is on my app. There are lots of rows in pg_stat_activity with this state, but every time I re-query the table, there's new information. I need a more precise way to measure how long certain queries are IDLE IN TRANSACTION.

A session that is "idle in transaction" can't have waiting = true because otherwise it wouldn't be "idle".

The difference between current_timestamp and  the timestamp in state_change should give you the time that the session was in "idle in transaction".

In general, a session that is just "idle" is not a problem. A session in "idle in transaction" might very well be a problem, as it prevents vacuum of cleaning up the rows that the transaction was using.

A short (a few seconds) "idle in transaction" is not a problem, only very long running  (minutes, hours) transactions that are not ended properly (and thus stick in "idle in transaction") are a problem.

With 9.6 you can force the server to disconnect long running "idle in transaction" sessions using the property idle_in_transaction_session_timeout


Oracle Historical Session Information I want to know the view or query which provides the total number of active and non-active session for a specific snapshot. Currently I am using the below query.

SELECT INSTANCE_NUMBER, count(*)*10 "DB Sessions"
FROM DBA_HIST_ACTIVE_SESS_HISTORY
where snap_id = '78131'
GROUP BY INSTANCE_NUMBER;

Any help will be appreciated.

Is not nice, is not elegant, but still it works.
Hope this helps to any lost DB Adventurer in a desperate crusade for a sessions historical report.
COL METRIC_NAME FOR A40
COL BEGIN_TIME FOR A20
COL END_TIME FOR A20
COL METRIC_UNIT FOR A20
set lin 330 pages 5000

SELECT
AVG_SES_COUNT.SNAP_ID,      AVG_SES_COUNT.DBID,     AVG_SES_COUNT.INSTANCE_NUMBER,
AVG_SES_COUNT.BEGIN_TIME,   AVG_SES_COUNT.END_TIME,
--AVG_SES_COUNT.METRIC_NAME,
AVG_SES_COUNT.AVERAGE AVG_TOTAL_SESSIONS,       AVG_SES_COUNT.MAXVAL MAX_TOTAL_SESSIONS,
-----
AVG_ACT_SES.AVERAGE                AVG_ACTIVE_SESSIONS,     AVG_ACT_SES.MAXVAL            MAX_ACTIVE_SESSIONS,
-----
AVG_SERIAL_SESSIONS.AVERAGE        AVG_SERIAL_SESSIONS,     AVG_SERIAL_SESSIONS.MAXVAL    MAX_SERIAL_SESSIONS,
-----
AVG_PARALLEL_SESSIONS.AVERAGE      AVG_PARALLEL_SESSIONS,   AVG_PARALLEL_SESSIONS.MAXVAL  MAX_PARALLEL_SESSIONS,
-----
AVG_PQ_SESSIONS.AVERAGE      AVG_PQ_SESSIONS,   AVG_PQ_SESSIONS.MAXVAL  MAX_PQ_SESSIONS,
-----
AVG_PQ_SLV_SESSIONS.AVERAGE      AVG_PQ_SLAVE_SESSIONS,     AVG_PQ_SLV_SESSIONS.MAXVAL  MAX_PQ_SLAVE_SESSIONS
FROM
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2143
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SES_COUNT,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2147
order by INSTANCE_NUMBER,SNAP_ID ) AVG_ACT_SES,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SERIAL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PARALLEL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2137
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT
from dba_hist_sysmetric_summary
where  METRIC_ID=2138
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SLV_SESSIONS
WHERE
    AVG_SES_COUNT.SNAP_ID           = AVG_ACT_SES.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_ACT_SES.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_ACT_SES.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_SERIAL_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_SERIAL_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_SERIAL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PARALLEL_SESSIONS.SNAP_ID         (+)
AND AVG_SES_COUNT.DBID              = AVG_PARALLEL_SESSIONS.DBID            (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PARALLEL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SLV_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SLV_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SLV_SESSIONS.INSTANCE_NUMBER (+)
order by INSTANCE_NUMBER,SNAP_ID;