Insert new value into table if it does not exist I am just trying to teach myself SQL so appreciate any help or advice on this one.

I have a table for suppliers which has an attribute for payment method.  The payment method includes but is not limited to a number of items. e.g. paypal, visa.  I was thinking of having a small table for payment method containing valid options but I would like to update the table with information entered in the suppliers table if the payment method doesn't exists.  How would I go about doing this?

I am using SQLite.

Thanks

You have a few options.

You can handle this programmatically, in the code that sends the supplier info to the database.

You can create a stored procedure that finds payment methods in the supplier info that do not exist in the payment method table, and inserts them.

You can create a BEFORE trigger on supplier info, check if the payment method exists, and add it.

That said, I would advise against automatically creating something as critical as payment methods based on user input (or even on imported files).

First, the supplier info presumably will not have more than the payment method name. You may create a record, but critical info like payment type (credit card vs. checking account mean different data that you need to collect) will be missing.

Second, something as simple as a typo will create a new, invalid payment method in your system, instead of properly forcing the invalid data to be corrected before being added to the database.

Third, you would be allowing potential maliciously bad data to be created in your system. Without proper controls, you might allow someone to actually place valid fillable orders, and charge them nothing.

Screens that allow data to be added to your core look-up tables should be tightly controlled, only accessible to users who know what to add, and how.


Should I disable HyperThreading BACKGROUND I've recently been looking into some fairly high CXPacket wait times recently which has had me using SQL Sentry to monitor processor activity pretty closely.

One thing that I've noticed as a result is that we have massive spikes in context switching. Below is a 5 minute sample but this pattern is very common throughout the day.

As you can see it spikes pretty regularly. Now my understanding of this would lead me to believe that this would be the result of CPU pressure. However during that time barely get over 60%.

After some research this led me to believe that this is happening as a result of hyper threading. I know I'd read earlier some of the dangers of hyper threading. However that was written a looooong time ago.

To make a long story short. Is hyper threading likely to be the culprit for this spikes in context switching? Is it possible that context switching is negatively impacting my parallel queries? Should I disable hyper threading in my environment?

UPDATE although this specific thing is happening in my environment, the question at it's core is more universal. How impactful can high levels of context switching be on parallel queries? Can hyper threading cause this sort of issue?

Ultimately most of what I find on the internet suggests that hyper threading and SQL Server are not good friends, however most it that info is extremely dated.

My System There were a lot of configuration questions so I'll address those here so they can be ruled out. We have the power settings on performance at both the OS an bio level. Our Maxdop is set to 8 and the cost threshold for parallelism is 25. We have 32 logical cores and 16 physical. Also this is for the most part a data warehouse load scenario.

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem.

If the server hosts a data warehouse or reporting type of database that receives a low volume of queries but processes large amounts of data, parallelism can substantially reduce the time it takes to execute those queries. In contrast, however, if the server hosts an OLTP database that has a lot of small queries and transactions, then parallelism can kill throughput and negatively impact performance.

Whenever possible, it is best to isolate and troubleshoot the underlying wait type, since this will lead to overall system throughput improvements. Again, the CXPACKET waits are simply a symptom of a problem in most cases, not the actual problem

The sys.dm_os_latch_stats DMV contains information about the specific latch waits that have occurred in the instance, and if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking during query execution, and reducing the 'max degree of parallelism' sp_configure option may be required to resolve the problems.

This TechNet Magazine article is old but it does say to try turning off hyperthreading if you are exceeding the 5000 per sec per processor:

Optimizing SQL Server CPU Performance by Zach Nichter


Why are SELECT queries in sleeping status holding open transactions Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted transaction?).

Using the query below, I noticed that all of these open transactions are SELECT queries in a "sleeping" status. Why would a SELECT open a transaction? Could these be in the middle of a stored procedure somewhere, and the [TEXT] field is only showing the last statement in the procedure that was executed? There is no wait_info on any of the open transactions.

 SELECT [TEXT] as SQLcode
 FROM SYS.SYSPROCESSES SP
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1

what about you start using this query below, and then tailor it for your own purposes:

select *
from sys.dm_tran_database_transactions t

join sys.dm_tran_session_transactions st on t.transaction_id = st.transaction_id

join sys.dm_exec_sessions s on s.session_id = st.session_id

join sys.dm_exec_connections c on c.session_id = s.session_id

cross apply sys.dm_exec_sql_text(most_recent_sql_handle)
where 1=1

and text <> 'begin tran'

and text not like '%sp_MS%'

and datediff(minute, last_request_end_time, getdate()) > 60

and database_transaction_begin_time is not null

I use this one below, it helps in most cases:

    SELECT
     es.session_id AS session_id

    ,CASE WHEN eS.LOGIN_NAME = eS.ORIGINAL_LOGIN_NAME
     THEN eS.LOGIN_NAME
     ELSE eS.LOGIN_NAME + ' (' + eS.ORIGINAL_LOGIN_NAME + ')'
     END AS LOGIN_NAME

    ,es.host_name AS hostname
    ,es.status
    ,es.last_request_start_time
    ,es.last_request_end_time
    ,es.CPU_TIME AS CPU_TIME_MS
    ,es.MEMORY_USAGE AS MEMORY_USAGE_PAGES
    ,es.ROW_COUNT
    ,NULL AS blocked_by
    ,NULL AS waittype
    ,NULL AS waittime
    ,NULL AS lastwaittype
    ,NULL AS waitresource
    ,dbid=trans.database_name
    ,NULL as  cmd
    ,t.text AS [QUERY]
    ,NULL AS PARENT_QUERY

   ,TRANSACTION_ISOLATION_LEVEL = NULL

    ,NULL as cpu

    ,NULL AS physical_io

    --,es.open_transaction_count as [open_tran]
    ,es.program_name

    --,es.login_time
    --,ota.task_state
    --,ota.pending_io_count
    --,ota.pending_io_byte_count

    FROM sys.dm_exec_sessions AS es
    INNER JOIN sys.dm_exec_connections AS c
        ON es.session_id = c.session_id

    --LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id

    CROSS APPLY (

        SELECT MAX(DB_NAME(dt.database_id)) AS database_name
        FROM sys.dm_tran_session_transactions AS st
        INNER JOIN sys.dm_tran_database_transactions AS dt
            ON st.transaction_id = dt.transaction_id
        WHERE is_user_transaction = 1
        GROUP BY st.session_id
        HAVING es.session_id = st.session_id

    ) AS trans

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
    WHERE es.session_id NOT IN (

            SELECT session_id
            FROM sys.dm_exec_requests

        )
        AND es.session_id IN (

            SELECT request_session_id
            FROM sys.dm_tran_locks
            WHERE request_status = 'GRANT'
        )
        AND STATUS = 'sleeping'
        AND is_user_process = 1
        AND es.session_id <> @@spid

  Please avoid using SYS.SYSPROCESSES


How to upgrade SQL Server 2008 R2 RTM to SQL Server 2012 SP3 on the same server? We want to upgrade SQL Server 2008 R2 to SQL Server 2012 sp3 on the same server A. The SQL Server 2008 R2 is on Windows Server 2008 R2 Standard and it has six user databases.

First we will upgrade Windows Server R2 Standard to Windows Server 2012 R2 Standard. Then we will uninstall SQL Server 2008 R2 from server A.  We will install SQL Server 2012 SP3 on Server A and restore the backup of SQL Server 2008 R2 databases. I have script out the SQL Server Agent Jobs and linked server. So I can run after the restore.

There are only four logins. I script out the login too.

My question is: do I need to restore system databases too?

Unless you have user-created objects in the system databases, I would not try to restore them.

In fact, if you do have user-created objects, I'd script those out and add them into the new system databases as part of the restore process.

Some of the views, functions, procedures, etc. in the system databases may have version-specific code in them, so it's safest to just use the ones from the new version you're installing.


USER_TAB_PARTITIONS Query Runs Incredibly Slow I seem to be seeing incredibly slow (5-10 seconds) to run a query against USER_TAB_PARTITIONS (Oracle) intermittently.  When I do an explain plan on :

SELECT * FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='MY_TABLE'

One of the steps shows a Nested Loops/Merge Join (Option = CARTESIAN) operation against TABPART$.  The TABPART$ system table shows it has been analyzed in the last day.  Is there a known problem with 11.2 and USER_TAB_PARTITIONS?

By the way, I am running Oracle v11.2.0.1

Found in support.oracle.com:

Bug 8334498 Query on DBA_TAB_PARTITIONS does full table scan on TABPART$

This bug fixed in version 11.2.0.2.


Help on joining 3 tables Oracle XE 11g I am trying to return the first name, surname, subject and mark from the following tables:

SQL> desc students;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 SURNAME                           VARCHAR2(20)
 FORENAME                          VARCHAR2(20)

SQL> desc modules;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 MODULE_CODE                       VARCHAR2(8)
 MODULE_NAME                       VARCHAR2(20)

SQL> desc marks;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 MODULE_CODE                       VARCHAR2(8)
 MARK                              NUMBER(38)

I have tried the following SQL query within SQL*Plus
SELECT DISTINCT FORENAME AS "First Name", SURNAME, MODULE_NAME AS "Subject", MARK
  FROM STUDENTS, MODULES, MARKS; but this displays duplicate entries.

The following simply does not work.

SELECT st.FORENAME AS "First Name", st.SURNAME, mod.MODULE_NAME AS "Subject"
  FROM STUDENTS st JOIN MODULES mod ON (st.STUDENT_NO = mod.STUDENT_NO);

Am I missing something? Maybe a subquery?
Please help, total database noob.

The first query you have used returns Cartesian product of the rows from these three tables.
For details: Join

The following is the simple example to join three tables.

SQL> select * from student;

STUDENT_ID FIRSTNAME        LASTNAME
---------- -------------------- --------------------
     1 Will         Smith

SQL> select * from module;

 MODULE_ID NAME
---------- --------------------
     1 Computing

SQL> select * from marks;

STUDENT_ID  MODULE_ID       MARK
---------- ---------- ----------
     1      1         40

SQL>

SQL> select s.firstname, s.lastname, mo.name Module_Name, m.mark
     from student s
         join marks m on (s.student_id=m.student_id)
         join module mo on (m.module_id=mo.module_id);

FIRSTNAME     LASTNAME     MODULE_NAME     MARK
------------- ------------ --------------- --------
Will          Smith         Computing      40

SQL>


Slow SELECT in Drupal CMS I have a slow query from a table with 7000 rows.
After a long time trying to optimize the MySQL conf nothing has changed.
The query:

| 38 | root | localhost | nrj | Query | 28 | Sending data |

SELECT node.nid AS nid, workflow_node_current.stamp AS workflow_node_current_stamp,
    'node' AS field_data_field_rh_username_node_entity_type,
    'node' AS field_data_field_text_no1_node_entity_type,
    'node' AS field_data_field_membership_number_node_entity_type,
    'node' AS field_data_field_text_1_255_n12_node_entity_type
FROM  node node
LEFT JOIN  workflow_node_history workflow_node_current ON
    (SELECT max(hid)
     FROM workflow_node_history
     WHERE nid = node.nid AND sid != old_sid
    ) = workflow_node_current.hid
WHERE (((node.status = '1') AND (node.type IN ('membership_request'))
      AND (workflow_node_current.sid = '85')))
ORDER BY workflow_node_current_stamp DESC
LIMIT 10 OFFSET 0

Ouch!  I have never seen ON ( SELECT ... ) = ....  Rewrite that somehow.

Perhaps more importantly, remove LEFT.  You are probably forcing the query start with node, even though it would be much faster to start with a subquery.

  sid != old_sid

Which column is in which table?  Please use short aliases for all references.

Please provide SHOW CREATE TABLE so we can check that you have adequate indexes.

Please provide EXPLAIN SELECT ... so we can see what the optimizer decided to do.


Large cost change in execution-plan why? I had a query change execution plans for an unknown reason.  The stats were gathered about 24 hours before the change and the rate of data increase has not changed over the period in question. Here is the original GOOD execution plan:
GOOD   return about 428 row per run

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9)
SORT (ORDER BY) (Cost=9 Cardinality=1 Bytes=82 Time=1)
TABLE ACCESS (BY INDEX ROWID) OF DE.MY_TABLE (TABLE) (Cost=8 Cardinality=1 Bytes=82 Time=1)
INDEX (RANGE SCAN) OF DE.MY_TABLE_MY_COL_01_DATE (INDEX) (Cost=3 Cardinality=6 Time=1)

Here is the UGLY plan, FTS on 73 million rows spinning the CPU near 100% still returns about 400 rows

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4911)
SORT (ORDER BY) (Cost=4911 Cardinality=32894 Bytes=2697308 Time=59)
TABLE ACCESS (FULL) OF DE.MY_TABLE (TABLE) (Cost=4278 Cardinality=32894 Bytes=2697308 Time=52)

After rebuilding the index, creating a covering index and that not working we finally added a hint to use the original index and that worked well. This restored previous timings.  Working well the query runs in 22ms.  The Ugly plan ran at about 14 sec.

After the query hint this is the query plan:

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=56571 Cardinality=13697 Bytes=1164245)
SORT (ORDER BY) (Cost=56571 Cardinality=13697 Bytes=1164245)
TABLE ACCESS (BY INDEX ROWID) OF MY_TABLE (TABLE) (Cost=56297 Cardinality=13697 Bytes=1164245)
INDEX (RANGE SCAN) OF MY_TABLE_MY_COL_01_DATE (INDEX) (Cost=521 Cardinality=62447)

Notice the Cost and Cardinality of the 1st and the last plans both using the same index.  In the 1st the Cardinality is wrong (1).  The query returns a pretty consistent 400 rows. In the last plan the Cardinality is 13697.  That's wrong too.  But look at the Cost of the last plan 56571, no wonder it took a hint to force the use of the working index.

My research is pointing to a Statistics problem. I verified that the GATHER_STATS_JOB is running. I've used the dba_tab_stats_history table to verify the stats are actually updated on the table I'm interested in.

So my questions are: Why would the Cardinality and Cost of the query change so dramatically?  If it is how the statistics are gathered (I am using gather_database_stats(auto)) where should I start reading what Stats gathering options to use?

If it is unlikely the statistics where else should I look?

I'm running Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

EDIT NEW PLAN 03/21/2018
The latest status is the system no longer considers the hint index expensive.
Here is the latest plan detail:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |    41 |  3444 |   998   (1)| 00:00:12 |
|   1 |  SORT ORDER BY               |                        |    41 |  3444 |   998   (1)| 00:00:12 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MY_TABLE               |    41 |  3444 |   997   (1)| 00:00:12 |
|*  3 |    INDEX RANGE SCAN          | MY_TABLE_MY_COL_01_DATE|  1128 |       |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FO"."STATUSID"<6800 AND "FO"."PARENTID" IS NULL AND "FO"."DELETED"<>1)
   3 - access("FO"."MY_COL_01"=:AGENCY AND "FO"."EVENTDATE">SYSDATE@!-:TIMENUMBER AND
          "FO"."EVENTDATE" IS NOT NULL)

As far as I remember, 10g had issues with bind peeking, and symptoms are very similar to what you have.  I suspect that good plan picks index (MY_TABLE_MY_COL_01_DATE ) for fo.eventdate>sysdate - :2 when value of variable was small, or it was very few rows that satisfy that condition according to statistics.  Then either due to statistics change, or you ran it with different value of variable, or sql got evicted from SGA, either it generated a new plan . You might try to check V$SQL_SHARED_CURSOR , it shows why a new cursor was generated.  In theory, optimizer should switch between FTS and index scan, but in my memory it never happened in 10g - once it chose FTS, it would keep using it.  If upgrading Oracle is not an option, I'd try using literal in this particular case instead of bind variables, i.e. fo.eventdate>sysdate - 1 . (or maybe combine literals and bind variables to help optimizer, say if it's never greater than 10 days you can do fo.eventdate>sysdate - 10 and  fo.eventdate>sysdate - :2


How to recover the data from Redo Log Group which has not been archived yet I have a normal situation where we have 3 Redo Groups (A,B & C) with 1 member (100 MB) each and database is also in Archive log Mode.

In normal scenarios whenever there is redo log switch, there will be a archive file generated and if we have all backups and archive files we can have our database recover till the point we have archive logs.

My question is - when writing to redo log group C (A & B has been archived already), let say 50 MB is written and database crashes, so definitely C group has not been archived yet.

So is it like, while recovering my database again on a different server, i will be losing 50 MB of data? or my understanding is wrong.

Is there any way we can also get that 50 MB of data somehow?

If you have access to the logfile in redo group C, and you can copy it to the new server, then you will be able to use it for recovering the database on the new server, so you will not lose the data in it.

If you no longer have access to the redo logfile (because not only the database, but the server or storage also crashed, and you are unable to start/repair them), and all you have is your backup, then yes, you will lose the data created after the last backup.


What's the most efficient way to reclaim unused DB hard drive space? Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP NewFG;
GO

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = FG2,
    FILENAME = 'G:\DATA\FG2.ndf',
    SIZE = 500MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 250MB
)
TO FILEGROUP NewFG;
GO

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID)
WITH (DROP_EXISTING = ON) ON [NewFG]
GO


Configuring MySQL for Power Failure I have an embedded device running a MySQL database with a web based application.

The problem is when I shutdown my embedded device it just cuts off the power, and I can not have a controlled shutdown. Given this situation how can I configure MySQL to prevent it from failures and in case of a failure, I should have maximum support to recover my database.

While searching this, I came across InnoDB Engine as well as some configuration options to set like sync_binlog=1 & innodb_flush_log_at_trx_commit=1. I have noticed my default Engine is InnoDB and binary logs are also enabled.

What are other configurations to make for best possible failure & recovery support?

I have absolutely no experience with databases and MySQL.

You can do many things to prevent this.

Move DB server to somewhere else (hosting)
Buy an UPS
Change to a simpler DB software (SQLite, Files)
Make your code to always save the latest changes, so when power up, programs keep running from last saved point.


How are SQL backups different from IT regular nightly server backups? Our IT department backs up the entire server every night (an SQL Server instance is installed on this server), which should be backing up that server as well as the entire network, in case something goes wrong...

So my manager asked what is the significant of my Full, Differential and Log SQL backups versus whatever the IT department backs up? To save more space on our server rather than keeping these files for a couple of weeks and deleting them, she thinks IT will just provide them!

I know it's not right, as I can restore up to the last 30 minutes with my log backups, IT restores it the next day, but is this the only difference?

Since I save/send my database backup files to the same server, IT will restore them but if I don't have these backup jobs in my maintenance plan then IT can just restore the SQL instance without any of our tables, transactions... etc.
Am I getting this right?
Any advice would be really appreciated.

Database backups give you point-in-time restore capability (provided you have FULL recovery model). Even if your IT people take backups every few minutes, which is extremely unlikely, you'll still have a gap.

Server backups do not replace database backups, they complement them by "archiving" the database backup files long-term (i.e. more than just today).

In the end you and your management have to decide your RPO (the recovery point objective - how much you need to be able to recover in a crash). With only daily server backups and no database backups, you stand to lose a full day's work in the worst case.

Edit: @Sting has a valid point in that shadow copies (the mechanism most likely used to make server backups) are not very likely to take an exactly simultaneous copy of all your database files (including log files), which may lead to inconsistencies when you restore the backup. For instance, if the shadow copy reads the transaction log a few milliseconds before it reads the database file, the database file could contain an uncommitted transaction, but as the transaction was committed a millisecond later, the log will not have any record of it.


Can oracle use more memory than memory_max_target? My system has 34GB RAM, however, I have set

memory_max_target = 3G
memory_target = 3G

Oracle memory parameters are specified using G (GB), M (MB) or K (KB)
...and I use AMM (Automatic Memory Management).

Even though that's the case when I check the current usage of my memory with

**free -m**

...it shows that around 33 GB of the total has been used. So I guess, Oracle also uses some of it (yes, I mean more than memory_max_target size, which is 3 GB).

So, my questions are:

Can Oracle use more memory than what we have set for memory_max_target?
Or is that a "hard" limit that Oracle never changes, even though the system has plenty of free memory?

The output of free includes caches and buffers, and a lot of people fall into the trap of thinking that their memory is used while it is not. Visit this site for explanation: http://www.linuxatemyram.com/

On the other hand though, yes, Oracle can definitely use more memory than configured with the memory_max_target parameter. Parameters, such as memory_max_target, memory_max_size limit the size of the SGA only.
Prior to 12c, PGA could not be limited, even though there is the hidden parameter _pga_max_size. That parameter limits the PGA size per session, but it does not account for all kinds of allocations, thus even 1 "rouge" session can eat up all the memory of the database server.

Starting with 12c, there is the parameter pga_aggregate_limit, which is a real hard limit for the PGA.

References:

Using Automatic Memory Management (Oracle Docs 11g)
6.3 Using Automatic Memory Management (Oracle Docs 12c)
6.4.4 Using Automatic PGA Memory Management (Oracle Docs 12c)


Standards or Best Practices for Error Handling in SSRS I have encountered a recurring issue with SSRS where "bad" reports go out to clients. The issue is that we have reports with a number of subreports, and one or more subreports will fail to process. When the subreport fails to process, the report sends with an error message in place of the sub report and no actual data.

My question is, is there a way to prevent a report from sending if a subreport fails?

Ideally I would like the outer report to just return an error. Is there a way to have error states in an SSRS report escalate to an outer scope? Is there a coding standard we can enforce that could prevent this issue?

We are currently using both SSRS 2008r2 and SSRS 2016.

As billinkc noted in his comment there is no way to propagate the subreport errors to the main reports.

As evidenced in this MSDN post you could try to use data driven subscriptions to drive the subscription, but that could get messy if there is any complexity involved real quick.

If you look at the SQL Agent jobs for a server using SSRS subscriptions you will see a bunch of SQL Agent jobs with a GUID as name, you could modify those jobs to include an extra step running some data validation steps before actually running the report failing the job before the report is sent out (risking your job definition being overwritten by SSRS) or calling the job from another job after validating the data logic.

You could identify the GUID associated with the report using this query:

SELECT
            b.name AS JobName
            , e.name
            , e.path
            , d.description
            , a.SubscriptionID
            , laststatus
            , eventtype
            , LastRunTime
            , date_created
            , date_modified
    FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b
          ON a.ScheduleID = b.name
            JOIN ReportServer.dbo.ReportSchedule c
            ON b.name = c.ScheduleID
            JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
            JOIN ReportServer.dbo.Catalog e
            ON d.report_oid = e.itemid
    WHERE e.name = 'Sales_Report'

(taken from here)

TL/DR: there is no clean solution but you could get away with some hacks, you would need to implement logic in a data driven subscription or create an extra job/modify the generated job

PS: I didn't test any of these approaches


Is someone trying to hack into my SQL Server Database? I was asked by a friend, who isn't technical at all, to help her with her application.

I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot more than that. Needless to say I've learned a lot.

I've stumbled upon the Server Logs in SSMS. To my surprise there are TONS of failed login attempts. The login attempts are primarily using the username 'sa' but there are others, for example: 'KHB', 'wwo' 'IIS' 'hpv7' 'N200user' 'kisadmin'. These usernames make no sense. I am the only one who should be logging into this database. The error is either.

Error: 18456, Severity:14, State: 8 or  Error: 18456, Severity:14, State: 5

State 8 only happens when the login attempt is for the username 'sa'.

The majority of these attempts are happening between 1am-4am.

This seems real fishy.

Any suggestions on what I should do?

Looks like somebody is really trying to hack into your SQL Server with brute force attacks. I recommend taking a look at this Whitepaper on SQL Server security, that will help you a lot.

Since most attacks happen at the same time, make sure you don't have a security scanner on your network that runs its checks at that time. Products such as Nessus can perform security scans and simulate brute force attacks on your servers in order to help you fix the security issues they find.


How does Postgres compute the row count estimate during query planning I have a simple query such as:
SELECT
    *
FROM
    example
WHERE
    filter_1 = ?
    AND filter_2 = ?
LIMIT
    10

The table is quite large (about 100 millions row) and it has an index similar to the following (the actual index has one more column on the right side but it shouldn't make any difference):
CREATE INDEX example_idx
ON public.example
USING btree (filter_1, filter_2, (...));

So now let's describe the issue:
when I run my query in a prepared statement, the first 5 executions use a custom plan. Then the generic plan is seen as less costly and it is used for the reminder of the prepared statement's lifetime.
Here is an EXPLAIN ANALYZE when Postgres uses the custom plan:
Limit  (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
  ->  Index Scan using example_idx on example c0  (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
        Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms

Here is an EXPLAIN when Postgres uses the generic plan:
Limit  (cost=0.00..11.31 rows=10 width=78)
  ->  Seq Scan on example_idx c0  (cost=0.00..3469262.28 rows=3067235 width=78)
        Filter: (((filter_1)::text = $1) AND (filter_2 = $2))

Here, we can clearly see that the cost of the generic plan is lower.
My problem is how the row count estimate in the Index Scan and the Seq Scan are computed.
The documentation explains how and if I follow their calculation, I arrive at 3183, which is the estimated row count for the custom plan:
rare_value_1 and frequent_value_2 are both in the MCV list. And their frequency is 0.00002667 and 0.99783 respectively. Also, the estimated table row count is 119622152.
0.00002667 * 0.99783 * 119622152 = 3183

The remaining question is, how is it done for the generic plan?
I found that, for some unknown reason, the MCV frequencies are ignored. And Postgresql just looks at the n_distinct values for columns filter_1 and filter_2 (13 and 3 respectively):
estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
                    = 119622152 / (13 * 3)
                    = 3067235

My question is why? Why does Postgresql use such a primitive way to estimate the row count since it has access to better statistics in the form of MCV frequencies?
Postgresql version: 11 (so using the "force_custom_plan" option is not possible for us at the moment).

There is no better way to estimate the row count for the generic plan.
If you chose to go by the frequencies of a most common value (MCV), there would be several problems:

Which MCV do you choose?

The estimate would be better assuming that both parameters are MCV, but that is an unfounded assumption.  If both were rare values, the estimate would be even farther off the mark than the current estimate.

Choosing the average is the best we can do: it is something of a middle ground.
Your case is so far off the mark because one of the values is extremely rare, which lowers the custom plan estimate, but since there are few distinct values and one of them is very frequent, the selectivity is bad at average, and the generic plan estimate is far too high.
You have two choices:

Use dynamic SQL so that you always get a custom plan.

Change the query to read
WHERE filter_1 || '' = ?

so that it cannot use the index.


difference between assingment and : what is the difference between these two ,second is arithmetic expression assigne to sal , first one is ?  how they differ exactly. if salary has intial value of 10 both result same answer. when to use them in oracle based on what  ?
if this is not a good question please disregard .

sal :=sal+300

sal=sal+300;

The second expression is syntactically invalid (at least on its own, it could be a valid part of some other expression).

sal := sal + 300;

assigns a value to the local variable sal that is 300 more than the current value of that variable.

sal = sal + 300

does not change the value of sal.  It compares sal to sal + 300 and evaluates to a boolean.  But it would only be valid in something like an if statement or in a SQL statement, i.e.

IF( sal = sal + 300 )

or

SELECT *
  FROM emp
 WHERE sal = sal + 300

Neither of those really make sense, though, since that is, by definition, not going to evaluate to true.


Compare two oracle .dmp files Ok calling an Oracle expert, DBA or general good guy

The problem is simple but trawling Mr Google has not come up with an easy to use solution.

I am testing data input.

I want to compare master.dmp with testers.dmp these have been created using exp in Oracle 11g  the schema will be the same it is only the data input that will change.

The master.dmp was created when I put the data in myself using some sql and typing. Now I have to use the app and a tester(me) to see if the results are the same.

I can use ODBC and import data in Excel and uses Excel Compare Ok but it would be quicker to just compare the two .dmp files and highlight the changes .

Has any on done this?
Has anyone a link to a tool or software that will compare two dmp files?

or is there a nice easy way to compare the data.

Comparing dmp files like that is probably quite difficult to do, as the format is quite complex, with snippets of embedded SQL and all sorts.  Having said that, although it is a binary format, it is a stream format (i.e. no internal pointers), so it's quite amenable to processing with sed.

I think loading the contents of he dmp files into two schemas and comparing the two is by far the easiest way you're going to do this, barring the existence of some tool that I'm not aware of.


Oracle Text - Changing SYNC to MANUAL not working always Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below query is run using CallableStatement for this purpose

{call CTX_DDL.REPLACE_INDEX_METADATA('IXFTS_ABC_TABLE','REPLACE METADATA SYNC (MANUAL)')}

In some environments this works fine but in production environments the SYNC option is still "ON COMMIT". No error is thrown when this is not updated. The index is not a partitioned index. Any pointers to troubleshoot further will be helpful.

[Update] I could reproduce this in my local machine rarely. Looks like running the same sql 2 or 3 times is working. Not sure why this fails the first time.

Try this alter option.

alter index IXFTS_ABC_TABLE parameters ('REPLACE METADATA SYNC(Manual)');


In postgres is there any real drawback on using a json field over multiple tables and one to many relationships? In our current application we are basically going to provide something called "translations" for each field. So a table would originally be like:
CREATE TABLE organisation (
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL DEFAULT '',
)

But instead the name would now be language dependent.
The basic solution would be to no longer store the name field in the organisation table but instead in an "organisation_language" table:
CREATE TABLE organisation (
   id SERIAL PRIMARY,
);

CREATE TABLE organisation_language
(
  id SERIAL PRIMARY KEY,
  organisation INTEGER NOT NULL,
  field_name TEXT NOT NULL,
  field_language TEXT NOT NULL,
  field_translation TEXT NOT NULL DEFAULT '',

  FOREIGN KEY (organisation)
    REFERENCES public.organisation (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE CASCADE
      NOT VALID
);

(And yes I'm deliberately ignoring I could add a lookup table for language keys like en-US and refer to that).
However, now I can no longer "guarantee" that every field (like name) is defined. Of course the code could make sure it does but that's another layer. It also adds quite a bit of complexity as it is no longer easy to see what fields even belong to a datamodel. Or what translations are given for a certain organisation.
If I look at the JSON I would actually "like" my backend to send to the frontend upon requesting "data from organisation 1" it would look like:
{
    id: 1
    name: {
        "en-us": 'hello world',
        "nl-nl": 'hoi wereld'
    }
}

On top of that, the main actions that happen are "insertion" - but then insertion of all data at once (so all translations at once), and retrieval of all language data, not ever a single language. (Due to "paths", like if a field doesn't exist in en-gb check en-us, and this calculation happens in backend).
Modification happens so rarely that we are even considering to just "not support it" and instead go for copy-on-write and deactivating the old "organisation".

This made me think, is it a bad idea to change the type of the "name" field to JSON, instead of having a language table? (and do this for all fields). It would then look like:
CREATE TABLE organisation
(
  id SERIAL PRIMARY KEY,
  name JSON NOT NULL DEFAULT '{}'::JSON
);

And inserted data into the name field would be (backend has to verify the schema):
{
    "en-us": 'hello world',
    "nl-nl": 'hoi wereld'
}

Is there an actual drawback to using JSON fields, as opposed to using a language table? It has the advantage of easier lookup, and insertion (or deletion). And since modification is not supposed to happen anyways there's no drawback - correct?
Or will PostgreSQL choke on this later?

The first normal form requires columns to be single, atomic values. I'd argue that if you really treat these JSON objects as something to store and retrieve, they are atomic from a database point of view, and what you plan to do is fine.
If you want to use parts of the JSON in a WHERE condition, that could still be alright. If you want database constraints based on parts of the JSON, or you want to join based on JSON attribute, I'd advise against using such a data model.


Whole numbers from sql column using SSIS Below is the sample SQL.

From Source1 tb, I need only whole numbers of Value column into ValueNEW of Destination1 tb as shown in the output pic.

--Source1
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source1]') AND type in (N'U'))
DROP TABLE [dbo].[Source1]
GO
CREATE TABLE [dbo].[Source1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Source1] VALUES(1, '45.212')
INSERT INTO [dbo].[Source1] VALUES(2, '12/45')
INSERT INTO [dbo].[Source1] VALUES(3, '6')
INSERT INTO [dbo].[Source1] VALUES(4, '100.56')
INSERT INTO [dbo].[Source1] VALUES(5, 'ADFJJ 12/34 F=10.4')
INSERT INTO [dbo].[Source1] VALUES(6, '+. 0.5')
INSERT INTO [dbo].[Source1] VALUES(7, '300')
INSERT INTO [dbo].[Source1] VALUES(8, '90')
INSERT INTO [dbo].[Source1] VALUES(9, '80.5')
INSERT INTO [dbo].[Source1] VALUES(10, '13): @FD E 19.50 32')

--Destination
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Destination1]') AND type in (N'U'))
DROP TABLE [dbo].[Destination1]
GO
CREATE TABLE [dbo].[Destination1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL,
    [ValueNEW] [varchar](255) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Destination1] VALUES(1, '45.212', NULL)
INSERT INTO [dbo].[Destination1] VALUES(2, '12/45', NULL)
INSERT INTO [dbo].[Destination1] VALUES(3, '6', '6')
INSERT INTO [dbo].[Destination1] VALUES(4, '100.56', NULL)
INSERT INTO [dbo].[Destination1] VALUES(5, 'ADFJJ 12/34 F=10.4', NULL)
INSERT INTO [dbo].[Destination1] VALUES(6, '+. 0.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(7, '300', 300)
INSERT INTO [dbo].[Destination1] VALUES(8, '90', 90)
INSERT INTO [dbo].[Destination1] VALUES(9, '80.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(10, '13): @FD E 19.50 32', NULL)

 SELECT * FROM [dbo].[Source1]
 SELECT * FROM [dbo].[Destination1]

ValueNEW needed as output.

I think you're asking about using IsNumeric and CharIndex in your source query and then throwing away the things that are not whole numbers.  I would change my source query to be

SELECT
    S.*
,   CASE
        WHEN
        -- This filters for only the numeric type of data
        ISNUMERIC(S.Value) > 0
        -- this filters the non whole numbers
        AND CHARINDEX('.', S.Value) = 0
            THEN S.Value
        ELSE NULL
    END AS NewValue
FROM
    dbo.Source1 AS S;

Otherwise, you're looking at either using a series of Derived Column Components or a Script Component to do the same actions.

References

CHARINDEX
ISNUMERIC


Write concern in PostgreSQL In MongoDB it is called write concern, that is the number of the servers in a replica set, that the data should be committed before the cluster acknowledges the write request. How is it called in the Postgres?

PostgreSQL works differently. As soon as the data are committed on the single primary database, the write is effective. If you use synchronous replication, that commit waits until the required standby servers (configured by synchronous_standby_names) have received the information.


Views created by SQL server Database Tuning advisor Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy.

I implemented the suggestions, and it created several views with names like _dta_mv_xxx

I have never written a query against those views, and I wonder if they are used in any way by SQL-Server. I can't see any indexes on the views either.

Is it safe to delete those views or will it impact performance?

The database is around 400 gb so working with creating and recreating indexes is slow.

The database is SQL Server 2016 Enterprise.

The short answer is that it's impossible to tell, but it's not terribly likely.

Views without indexes are just obfuscated queries. Unless you've written queries directly against those views, there's generally no advantage for the optimizer to decide to use them.

The safest thing to do would be to

Script their definitions
Script the DROP commands you plan to use
Check them into change management or source control

That way, if you drop any, and something odd happens (like queries failing), you can add them back. If you have a dev/QA environment, make the changes there first and let them burn in for a week.

I'm not sure why you brought up index creation speed. If you're under the impression that creating a view that isn't indexed requires some physical structure gets built, it doesn't.

On the flip side, the views created by DTA are often created with the SCHEMABINDING option, which makes altering the underlying tables and columns that they touch throw errors that are confusing to track down.

Hope this helps!


Trigger to change Database collation on creation I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger?

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @databasename varchar(200)
set @databasename =db_name()
    ALTER DATABASE @databasename COLLATE xxxxxxxxxxxxxxxxxxx
GO

Obviously, this is not working.

You would need to use dynamic SQL, and the EVENTDATA() function.

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON;
DECLARE @databasename NVARCHAR(256) = N''
DECLARE @event_data XML;
DECLARE @sql NVARCHAR(4000) = N''

SET @event_data = EVENTDATA()

SET @databasename = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)')

SET @sql += 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' COLLATE al''z a-b-cee''z'

PRINT @sql

EXEC sys.sp_executesql @sql

GO

Just sub in your collation for my fake one.

Now when I create a database...

CREATE DATABASE DingDong

I get this message (from the print):

  ALTER DATABASE [DingDong] COLLATE al'z a-b-cee'z

Just note that if other databases (including tempdb) use different collations, you can run into problems comparing string data. You'd have to add COLLATE clauses to string comparisons where casing or accents matter, and even when they don't you can hit errors. Related question where I ran into a similar code problem here.


Triggers corrupt merge replication I have merge replication (SQL server 2014) and a table that have 3 triggers that monitor delete, insert and update.
All of the triggers are after trigger not instead of.

I'm updating the value on one side and it doesn't update on the second server and when I'm disabling the triggers it's all working.
insert and delete doesn't work as well.
Any thoughts ?

Well as McNets answered all i had to do is alter the script.

ALTER TRIGGER [dbo].[TRG]
ON [dbo].[Table] AFTER DELETE
NOT FOR REPLICATION
AS
    BEGIN
        BLA BLA BLA
    END


Why does nested loops join only support left joins? In Craig Freedman's blog, Nested Loops Join, he explains why the nested loops join cannot support a right outer join:

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

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

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

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

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

What I don't like in the linked article is the statement that "nested loop join algorithm does not support the logical join operator of right join".

While there is a limitation, the wording at this point is a bit confusing. I hope the following explains things better:

The nested lop join algorithm involves two tables (whether base tables or result sets of previous operations is irrelevant) which are named outer and inner table and they are treated in a different way by the algorithm (the "outer" table is traversed at the outer loop and the "inner" table at the inner loops).

So, lets say we have have a join:

A (some_type) JOIN B

The algorithm can be executed as either:

outer-loop-A  nested-loop  inner-loop-B

or:

outer-loop-B  nested-loop  inner-loop-A

Now, if (some_type) is INNER or CROSS join, then there is no limitation, the planner can choose between either of the two ways (with different performance characteristics, depending on the size of the sets, distribution of values of the joined columns, indexes, etc. Usually the smallest table will be chosen to be the "outer" table in the algorithm).

But when some_type is LEFT join, it can only use:

outer-loop-A  nested-loop  inner-loop-B

and not

outer-loop-B  nested-loop  inner-loop-A

And since a RIGHT can always be rewritten as a LEFT join, it has the same limitation, in reverse. For A RIGHT JOIN B (which can be rewritten a B LEFT JOIN A) it can only use:

outer-loop-B  nested-loop  inner-loop-A

and not the other way around*.

The same limitation applies for left-semijoin, left-anti-semijoin, right-semijoin and right-anti-semijoin.

The FULL join on the other hand cannot be directly handled with a nested loop join algorithm. The article explains very well (it's near the end) how a full join can be rewritten (and is by the optimizer) to a union of a left join and a left anti-semijoin which then might be planned as two nested loops (and a union).

* As Dudu Markovitz explains in his answer, the reverse way would be able to be used but only if we modified the nested-loop join algorithm to have an extra structure and an extra step in the end.


Expdp with metadata_only gives way too large dump. Why? There is an oracle 11g schema with amount of data about 700 Gb. There are tables/indexes only in that schema, no procedures/packages.

Doing expdp on that schema

expdp schemas=TheSchema directory=dir dumpfile=dump logfile=log content=metadata_only

, I expected quite a litte dumpfile because of metadata_only. Doing tools/export-user-objects in pl/sql developer gives an sql file with text size a half of megabyte.

But instead there appeared a 20 Gb dumpfile!

Is there something large in dumpfile created by this expdp, like index contents?

What could be done to minimize the dumpfile done with expdp, if I want just to create tables like in TheSchema in another schema?

UP: There is quite a few number of tables/indexes in the text-sql-dump of schema:

> grep "create table" TheSchema.sql | wc -l
523

> grep "index" TheSchema.sql | wc -l
501

> grep "partition" TheSchema.sql | wc -l
96

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema:

16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner;

OWNER                                  GB
------------------------------ ----------
USER                           1156.41901

Here's the export statement: expdp system/x schemas=user directory=DATA_PUMP_DIR content=metadata_only logfile=test_meta.log

Here's the sizing on the dump file:

oracle@db01:(db1) ~]$ ls -ltrh /u01/exports/db/expdat.dmp
-rw-r----- 1 oracle oinstall 25M Nov 20 16:40 /u01/exports/db/expdat.dmp

Make sure you're using SYSTEM to do exports, as sysdba privs are not recommended for running data pump (see Invoking Data Pump Export. I wonder if that might be the problem.


postgresql 10 for Ubuntu 17.10 artful? Is PostgreSQL 10 available on Ubuntu 17.10 artful?

I checked that the folder on APT actually exists: http://apt.postgresql.org/pub/repos/apt/dists/artful-pgdg/10/

But after I do:

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ artful-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-10

I get an error:

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-10

The above steps worked for Ubuntu 16.04 (with xenial-pgdg), but not for 17.10.

Any pointers?

I have replaced artful-pgdg with zesty-pgdg in /etc/apt/sources.list.d/pgdg.list and it worked for me

https://wiki.postgresql.org/wiki/Apt/FAQ

I am using a non-LTS release of Ubuntu

Non-LTS releases of Ubuntu are only added to the repository if the packages from the latest LTS release are incompatible with the release in question. Using the latest LTS release instead generally works, unless library dependencies cannot be fulfilled, in which case we will provide updated packages for the non-LTS release.


Implementing 2 keys table in sql Considering this table >
TRIP Table :
( Code [pk] , Origin , Destination , Distance )
how to make (Origin , Destination) attributes has the constraint of composite key "can repeat if one of them is different" .

Not sure where there complexity is. Use a UNIQUE constraint on a composite key.

CREATE TABLE foo (
  a int,
  b int,
  UNIQUE(a,b)
);
INSERT INTO foo VALUES (1, null), (2, null);
INSERT INTO foo VALUES (null,1), (null,2);

But,

INSERT INTO foo VALUES (2,2), (2,2);
ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
DETAIL:  Key (a, b)=(2, 2) already exists.

If you're not creating the table but modifying one already made, you can use this:

ALTER TABLE foo ADD CONSTRAINT foobar UNIQUE (a, b);


How does InnoDB access disk? I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page size?

Default InnoDB page size is 16K. Pages go into a larger 1MB data structure called an extent. MySQL allocates 4 extents at a time.

All of these are stored in one large file, so boundary issues have minimal effect on InnoDB.

You can read more about MySQL file space management.

Modern hard drives have read caches, and they read ahead, so contiguous sectors are already available when you ask for them without having to perform another seek.

Given enough working space (innodb_buffer_pool_size), InnoDB will store much of your indexes and data in memory, only waiting on disk for update transactions and commits.

According to MySQL documentation, it seems MySQL reads an extent (1MB) at a time from disk to the buffer pool, where it will access it a page at a time.


.mdf vs .ldf Performance considerations I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for backups or audits.

Anyway, our server right now has the .mdf on a SSD and the .ldf on the main HDD with Windows. I set it up like that because I assumed the .mdf was the most performance critical file location.

But now it seems like the .ldf is actually the first place data is written in the event of an UPDATE, and then the .mdf is written to later on.

Should I put both files on the SSD? Or is it better if they're on different drives, even if one of them is an HDD? (and if so, which file should go where?)

If it makes a difference, SELECT performance is more important to me (and more common) than UPDATE.

These are the general rules for the data (mdf) and log (ldf) files:

Tune your mdf files for random read/write access in 64 KB chunks.
Tune your ldf files for sequential write access in 64 KB chunks.

Because the mdf files are accessed randomly, and the log files accessed sequentially, you can see substantial write performance drops if the logs are sharing disks with other data that's frequently accessed. This is largely because of the time lost waiting on the heads to seek around the disk. Obviously with an SSD you don't have this seek penalty, but the disk will still have a maximum write throughput to consider.

In a data warehouse/OLAP type of server, this may be less of an issue, as most day-to-day use will probably be read-only, and ETL batches will probably run during non-peak hours. The data files and log files likely won't compete with each other as much.

So, the moral of the story is if you're not happy with the write performance of your database, I'd recommend moving the logs to a dedicated volume with higher sequential write throughput. Often, this will mean using a RAID10 volume with traditional spinning disks (not SSDs). The more disks you use, the faster it will be. Don't use RAID5 for logs, as the write performance is generally quite bad, unless your array controller has a large enough cache to mask the effect.


Identify top logins\users consuming resources Is there a way to identify top database users who are actually consuming high resources with their executed queries ?

There's nothing built in to SQL Server that will do this. If you don't feel like using sp_who3 and partying like it's 2002, here's another method.

Grab sp_WhoIsActive

If you just want to see what's happening now, run it like this:

EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_locks = 1

If you want to use it long term to find out what users are up to, follow the instructions here to set it up to log results to a table. I'd probably skip collecting query plans here on a busy server, that could really bloat things.

How does this help? It returns wait info, tempdb use, blocking, cpu, reads, writes, locking, memory use, along with login name, host name, and program name so you can (if you choose to log it to a table) group by connection info to aggregate resource usage over time. The aggregations won't be perfect, but it might get you close enough to what you need (for free).

If you want to spent money on it, buy a monitoring tool. Sentry One Performance Advisor and Quest Spotlight are both pretty nifty.

Hope this helps!


Large MySQL table, inserts with primary key check I have a table with 12 million of rows and a following task I should frequently perform:

Get search results from somewhere (50 rows). Each result has a key looking as a md5 hash and a table's PM is built on this field.
Check what rows are currently stored.
Store all other rows

Question is what is the best way to perform steps 2-3. I use PHP and Doctrine so not all tricky queries are possible to use. For example I can't use bulk inserts so need to run INSERT up to 50 times in a row.
I see two possible ways:

run SELECT ... WHERE id IN(...) with all 50 IDs and see what is returned, then run as many inserts as I need
run 50 inserts and catch duplicated ID error

I think what you're looking is simply to use INSERT IGNORE. Forget about steps 1 and 2, just insert and ignore :)

  If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.


Multi-column and single-column index on the same column I have two indexes on the same column:

store_deleted_idx (store_id, deleted)
store_idx (store_id)

DDL:

CREATE TABLE `stores_shoppers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL,
  `store_shopper_id` varchar(128) NOT NULL,
  `country_code` varchar(30) DEFAULT NULL,
  `zip_code` varchar(50) DEFAULT NULL,
  `email` varchar(128) NOT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `modified_on` datetime NOT NULL,
  `is_marketing_allowed` tinyint(1) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `is_guest` tinyint(1) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `total_spent` decimal(12,2) DEFAULT NULL,
  `orders_count` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `store_idx` (`store_id`),
  KEY `store_deleted_idx` (`store_id`,`deleted`),
  CONSTRAINT `_stores_shoppers_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`),
  CONSTRAINT `_stores_shoppers_ibfk_4` FOREIGN KEY (`group_id`) REFERENCES `stores_shopper_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=56909900 DEFAULT CHARSET=utf8;

My query:

SELECT s.*, t.*, g.*
FROM shoppers s
LEFT JOIN shopper_tag t ON s.id = t.shopper_id
LEFT JOIN shopper_group g ON s.group_id = g.id
WHERE s.store_id = '4494' AND (s.deleted = 0 OR s.deleted IS NULL)
ORDER BY s.created_on DESC
LIMIT 10 OFFSET 0;

If I run the explain, it shows the query is using only store_idx index, but if I remove store_idx index and run the explain, it shows store_deleted_idx is used.

Why isn't store_deleted_idx used in the first case?

I can't remove the single-column index.

Get rid of OR

(s.deleted = 0 OR s.deleted IS NULL)

Make up your mind.  Either use 0 (and make it NOT NULL) or use NULL.  It is probably better to have

TINYINT UNSIGNED NOT NULL DEFAULT '0'

Until you get rid of the OR, an index including deleted is not useful.

Composite index

After that, INDEX(deleted, store_id), in either order, becomes optimal.

As a rule, INDEX(a) is unnecessary if you also have INDEX(a,b).

Even better is

INDEX(deleted, store_id,   -- in either order
      created_at)          -- last

This will avoid the sorting that is currently necessary.  (See EXPLAIN SELECT ...)

I can't remove the single-column index.

Do you mean that your fingers don't work?  Your boss will slap your hand if you try?  You don't have permissions?  It comes back when you do remove it?  The FOREIGN KEY gets in the way?  What?

Cardinality

Fake news.

Within a composite index, cardinality is irrelevant.

When comparing two indexes, cardinality is important, but there are usually better ways to optimize, such as using a composite index.  So, again, fake news.

Back to the question

Your experiments showed that either INDEX(store_id) or INDEX(store_id, deleted) would be used.  Given a choice between the two of them, the Optimizer will pick the smaller one (on the presumption that the amount of effort is slightly less).  Without a choice (ie, when you had deleted the smaller one), the Optimizer happily picked whichever one is left.

So, to avoid wasting disk space, DROP the shorter (single-column) INDEX.  Meanwhile this query will continue to work efficiently.

"Why isn't store_deleted_idx used in the first case?" -- Because of the OR.

More on indexing

http://mysql.rjweb.org/doc.php/index_cookbook_mysql discusses how getting all the way through the WHERE (after getting rid of OR) lets the index get to the ORDER BY, and if you get through the ORDER BY (by tacking on created_by), you can get to LIMIT.  Then it will read only 10 rows, not the whole table!


Is that possible to backup MongoDB sharded cluster without LVM or mongodump? There is a "LVM-solution" in MongoDB documentation: http://docs.mongodb.org/manual/tutorial/backup-sharded-cluster-with-filesystem-snapshots/

Can I do the same thing, but without using LVM snapshots, simply copy the files of my secondaries, and include a "mongodump" of my config data?

The LVM method works they way it does because snapshots are really fast, and the outage is really small and easily within the replication-log window. Using file-copy methods extends how long the replica-member is not replicating, so how long you can take to back up is constrained by the maximum replication-log allowed on the master node.

Secondly, snapshotting takes a point-in-time backup of all files at the same time which improves the consistency of the backup. For a file-copy style backup the files will be read sequentially, so the use of db.fsyncLock() is massively important; if you don't, the chance you're backing up inconsistent files is really high. It's the use of db.fsyncLock() that starts causing the replica to fall behind and the replica-log to increase on master.


How should I config the my.ini(or my.cnf) file for mysql? Thanks for taking your time to look at my question, and I hope that you can help me with this.

I have a Windows Server 2012 server, it has:

Memory : 3840MB Ram

Processor: Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz (2 CPUs), ~2.8GHz

But still mysql is going up to 100% cpu usage I ran the MySQLInstanceConfig.exe configuration Wizard to help me config everything for mysql and this is the my.ini config file that it created:

[client]

port=3306

[mysql]

default-character-set=latin1

[mysqld]

The TCP/IP Port the MySQL Server will listen on
port=3306

basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/data/"

character-set-server=latin1

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=192M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=384M

key_buffer_size=332M

read_buffer_size=64K read_rnd_buffer_size=256K

sort_buffer_size=256K

* INNODB Specific options *
innodb_additional_mem_pool_size=14M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=7M

innodb_buffer_pool_size=643M

innodb_log_file_size=65M

innodb_thread_concurrency=8

But Something is still wrong, it's taking to much CPU

Percona has an extremely awesome wizard for initial server configuration files (I've used it on our own extremely busy production servers). It requires registration, but usage is free.


Create a query with multiple databases Let say I have DB1, a database in PostgreSQL, and DB2, a database in Oracle.

How can I make a query joining these databases?

Is this even possible?

You can use a foreign data wrappers in Postgres to make Oracle tables appear like local tables in Postgres.

Then you can use the Oracle tables in a query running in Postgres and join them to the "real"  Postgres tables in a single query.

A list of foreign data wrappers (not included with Postgres itself) is available in the Postgres Wiki


What can become a reason of database failure? I'm the new one with SQL Server, so, please don't beat me.

It's known that one of the best ways to protect your database from the failure is to make regularly scheduled backups.

But my question is: what can become a reason of database failure?

I know that this is a quite wide question, but I would like to know something from your experience.

The number one reason is operator error. The 'whops, I dropped the wrong table' moment.

Next are catastrophic hardware failures (lost, unrecoverable, disk). Non-disk failures are actually less problematic because the data can usually be retrieved by attaching the (still working) disks to a different host.

Disk I/O errors (corruption of media at rest) used to be a big issue, but nowadays all DBs have page checksum enabled by default and can catch media corruption.

Memory and processor errors can cause corruption in-memory, before writing to disk, and thus defeat checksums, but such errors manifest all over the host process(es) and are usually detected as 'the server is crashing randomly'. Unfortunately, often times by the time action is taken the data is already randomly corrupted.

Application logical corruptions are often a big deal ("the app has written all fields with 'foo' when it should 'bar' ") but those are seldom seen as 'corruption' and can be corrected.

And then there are engine code defects. You can't really defend against them, other than keep an eye on the community pulse and be aware when issues are discovered and publicized, keep your servers patched up to date.


My first database schema design I'm new to programming and databases and working on my first application using .Net, Entity (for SQL Server) and Identity.

My application will allow users to create html pages for online courses. Think of it like a course content management system. Instead of users (instructors) having to know HTML markup to create pages for their online courses they will use form fields to create html elements paragraphs<p>, headers<h1> etc. Then they can preview and export these pages for use in their online courses on another system.

A user in the application must have an overall role which will have permissions to do admin things like add users, create courses etc. Users need to be assigned to Courses to access them and can have different roles across Courses. E.g. Person A can be an "Instructor" in Course A, and "Observer" in Course B.

So there are 2 sets of roles and permissions - One at the organization level to do admin tasks and one at the Course level to create and manage content.

Since course development is a collaborative effort with multiple people working on a course, there will be a commenting system next to html elements that users have created.

Schema Design

Am I on the right track? Does it look normalized and are the relationships correct?

I think you're one the right track. Make your database model the process. Try to think of People, Security Roles, and Courses as separate entities that have relationships - not always directly.

In your scenario, you're describing a many to many relationship between roles, courses, and people.

In one table you have people - all the demographical data like name, address, and phone number. A second table that has different roles a person may have with in the system - instructor, student, auditor, teaching assistant, etc. The third table has courses - all the demographical data course name, department, cost, etc. The final table has Security permissions - by web pages, functions, or whatever makes sense in the context of your application.

In between you're going to have tables that break these relationships into 1 to many relationships. A person with the Role of Instructor has multiple courses assigned to them, a table with a surrogate key for a specific instructor and the surrogate key for their course load.

I would like at building something like this - https://en.wikipedia.org/wiki/Swim_lane before building your schema. When you understand who does what, and in what order, you get a better understanding of what your data model should look like.


Remove mixed extents in SQL Server In SQL Server you can make sure that the engine uses the whole extent for a single object (i.e. a table). I know how to enable the trace 1118, but that only applies to newly written data as I recall.

How can I make sure that all the data in the database is using uniform extents instead of mixed extents?

Can it be done by rebuilding the clustered indexes, and when not present creating and dropping a clustered index?

Plain answer is yes, if you have enabled trace flag 1118 on your instance a rebuild of those indexes will move them to full extends instead of mixed. You can get more information on this from Paul Randal's post: Are mixed pages removed by an index rebuild?

A synopsis of the comment raised by ray herring on July 10, 2014 and Paul's response:

Ray asked if enabling this flag, and performing rebuilds would move all the pages in mixed extends to dedicated extends. Paul stated yes it would. However one thing I would note is his second question, on if it was worth worrying about for existing tables and indexes. Paul stated, no, since mixed pages only really make a difference in performance is in tempdb.

So you are not likely going to see a great deal of performance increase by worrying about the current pages that are mixed. Just do your normal index maintenance and at some point they will get moved.


Can I configure MySQL Replication as Master - Master - Slave? I am considering how to configure our MySQL replication. Hope I can get any advices from community.

In fact, I am using MySQL 5.7, and have

01-Master
02-Slave
03-Slave

My Applications are using only 01-Master. They don't know 02-Slave, and 03-Slave.

Next, I want to promote 02-Slave to 02-Master. But want to keep 01-Master. So, I have:

01-Master
02-Master
03-Slave

Then I will set 01-Master for some Apps and 02-Master for other Apps.

But, I am not sure about how to position 03-Slave? Can I do that?
Please give me any advices.
Thanks a lot!

M1 <-> M2 -> S is essentially the only configuration.  Pros/cons...

Even old versions of MySQL could be configured that way.  M1 and M2 are configured as slaves to each other; S is configured as a slave to M2 (and knows nothing about M1).  Each server needs a distinct server_id.
By having dual master, you have easier failover than if you had simply M->S.
It is probably safer to do what you plan, namely having different apps talking to different Masters, than to have all apps talk to either Master.  This is because of issues with auto_increment and duplicate keys.
The Slave is handy for backups, heavy reads, etc, but...
If M1 crashes and you failover to M2, use S to reconstruct M1, but...
If M2 crashes and you failover to M1, S cannot easily be connected to M1.  It would probably be best to assume that you lost S at the same time and rebuild both.  (There are issues of binlog positions, etc.  GTIDs, if turned on, may make this "not an issue"; this is something to research.)
Since you seem to have 3 servers, why not go with Galera?  That eliminates most of the issues mentioned -- you can write to any server; loss of any server is automatically repaired; etc.  Group Replication is another smart idea.


Joining table from Linked Server is very slow I have a production SQL server and a linked server (Azure SQL Database)

I join two tables to do update

Table A - small table A in TempDB that has only 100 rows on SQL Server
Table B - on linked server, is about 90 MB in size and 334,000 rows total

When I run below query

update A
    set A.ColumnA = B.ColumnB
from #Table A
    join [LinkedServer].[DB].[dbo].[Table] B on
         A.ID = B.ID

The update of 100 rows takes about 18 seconds! 1000 or more rows take much more time

I made sure below is true:

Column B.ID is indexed
Collation Compatible setting in Linked Server Options is set to "True"

I even tried to significantly scale up Azure SQL DB (from 20 DTU to 800 DTU), but the speed of query (100 rows update) went from 18 sec to 8 sec which is still not acceptable

What am I missing ?
Is there any workarounds in this situation ?

Regards,

Put an index on A.ID

Add a <> so it can avoid taking locks

update A
    set A.ColumnA = B.ColumnB
from #Table A
join [LinkedServer].[DB].[dbo].[Table] B
  on A.ID = B.ID
where A.ColumnA <> B.ColumnB or A.ColumnA is null


General structure advice for beginner I am starting my first DB-Driven website project for a mock real estate agency and would like a bit of feedback on my general approach to how I will drive the site with the DB.

Specifically I'm worried about two things:

If my primary and foreign keys are correct/logical
If my approach to using image resources in conjunction with the DB is a good approach (See comments beside the tables in code below.)

A bit about the site:

Generally, the site structure will be categorized into Condo Building, and each Condo Building has a 1..* relationship with Condo Units. A user can browse by building and be presented a building overview when selecting a particular Condo Building, and a list of all Condo Units in that building which this agency is selling. There will be pictures for Condo Buildings and pictures for Condo Units and my job naturally is to chain them together with relationships.

Regarding list item #2:

I have done some googling and it seems the general consensus is that although MySQL supports storing of BLOB types, it is considered bad practice as it taxes the database. So my approach is to have an image directory for Condo Buildings and an image directory for Condo Units, and each image filename therein will be the primary key ID so that I have a way to hook into the images in my PHP

--------------------
| CondoBuildings   |
--------------------
| ID (Primary)     |  // Auto increment int
| //fields         |  // only that which pertains to the building and NOT the units
--------------------

--------------------
| CondoUnits       |
--------------------
| BLDG_ID (Foreign)|  // CondoBuildings Primary Key (ID)
| ID (Primary)     |  // auto increment int
| //fields         |  // only that which pertains to an individual unit
--------------------

--------------------
| UnitImages       |
--------------------
| UNIT_ID (Foreign)|  // CondoUnits Primary Key (ID)
| IMG_ID (Primary) |  //
--------------------

--------------------
| BldgImages       |
--------------------
| BLDG_ID (Foreign)|  //CondoBuildings Primary Key (ID)
| IMG_ID (Primary) |  //
--------------------

So basically this is my plan, but I need to be sure that this is a logical approach or if there's some substantial drawbacks to this approach that ppl with more experience could point out.

Ensuring images are properly named would be a concern perhaps...

Your design is sound.

I have a concern about using the image filename as the key.  Although academically acceptable there could be performance implications.  A long string value like that is not going to make for an efficient index i.e. one with lots of IDs per page.

Secondly, there's nothing certain but change.  At some point you will have to re-locate these files onto a different directory/ server/ domain.  At that point you will be updating the primary key of a table which, while do-able, is rarely pretty.

I understand the example you've shown only has four tables.  I would anticipate you will have more before your system goes live and then these concerns will become more significant.  I'd use a surrogate key instead.


Sql Server 2005 - sqlcmd with output to shell and file I do some command line batch (.bat) with sqlcmd as this way:

sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE

and i need an output file (it's works currently) and also the output trought the screen to do something like:

@echo off
echo The result of the query was:
    sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE
pause
CHOICE /C:YN /M "Is the result accord?"
IF ERRORLEVEL 2 GOTO ENDWITHERROR
IF ERRORLEVEL 1 GOTO STEP2

Note:

Yes, the script works suscefull, it's not an issue question.
And yes, I've a "print "something" on my sql. The log file gets the
output.

Thanks a lot!

In order to implement what you want you need something functionally equivalent to tee or grep on unix.  Windows does ship with a utility called find.exe that has similar functionality to grep, so you may be able to use that to extract relevant lines from your output file.  As far as I am aware there is no equivalent to tee shipped with base windows builds.
This sort of thing is trivial in a unix shell scripting environment but can cause issues on a raw Windows build.  If you can't implement what you want using find.exe, three basic approaches come to mind:

cmd.exe actually has significant pipelining capabilities, although not as rich as a unix shell.  You can get Win32 ports of tee and grep from the GNU Win32 collection, or from Microsoft Services for Unix (SFU).  The GNU Win32 ones are better because they use msvcrt.dll and understand native Windows paths.  SFU places an emulation layer over the base O/S in much the same way as cygwin, so it emulates /cygdrive/c style paths that behave in a more unix-like manner.
An alternative to this would be to use WSH, powershell or even a small .Net console app to implement a small utility to read the output from the log file and present your prompt to the user.

Reimplement the process in a way that allows the control to be automated.  If you can agree business rules for this control it can be run automatically and simply log the result, generating a status report when the process has finished running.

It seems quite strange that you are developing a server-side process that requires interactive input from an operator.  If this process has to be run interactively, is there any reason why the interactive parts could not simply be run from somebody's PC?  This would probably allow you considerably more flexibility in installing third party components or bespoke client-side code.


Setting up PostgreSQL Connection with DBVisualiser Apologies for the rudimentary question, I'm new to setting up databases. Am aiming to create a new Postgres connection and load some CSV files into a table using DBVisualiser. Working on a Mac running El Capitan 10.11.3

I enter all the details in the Connection Wizard as follows:

But get the following 5432 error when I try to ping the server.

Where am I going wrong? I also tried using 127.0.0.1 as my local host but that didn't work either.

Did you checked your postgresql configuration, mainly the pg_hba.conf? Can you login to your server with psql? http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html


Difference between GROUP BY B,A and GROUP BY COALESCE(B,A) I have been getting myself very confused.

Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE?

My guess is that I would use it if I wanted to conditionally group a set of data by column B (if B was not null) and by column A otherwise.  Does that sound right?

With GROUP BY b,a the tuples (null, 1), (1,1), (2,1) and (17,1) would end up in four different groups.

With GROUP BY coalesce(b,a) the tuples (null,1), (1,1), (2,1) and (17,1) would end up in the same group.

If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.


Basic metric information for views I would like to output some basic metric information about my MySQL database.  Such as the names and number of rows for all tables and views in a given database.  Tables are no problem as I simply have to query the information_schema.tables.table_rows, but views pose a different problem as most all it's results are all NULL in information_schema.tables.

Does anyone have a good method besides writing a procedure that queries each view each time it is called?  To do so each time is highly inefficient.  I would assume the database engine would store this information for performance reasons.

Views do not store any data, they are no more than saved queries. Subsequently, the question "how many rows are there in the view" is equivalent to "how may rows does the query return", to answer which you by definition have to execute the query.

When querying a view, the optimizer will determine the query cost by looking up statistics of the view's underlying tables.


How to generate globally unique ids for different tables of the same database? In a production system on SQL Server all ids (mostly PKs) in all tables are generated automatically and I am informed that they are unique globally. I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.

Back in the day we had an ID table. Single column, single row with an int value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.

Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe. There's an example in the documentation that illustrates this:

CREATE TABLE Audit.ProcessEvents
(
    EventID int DEFAULT (NEXT VALUE FOR Audit.EventCounter), -- same sequence, different table
    <other columns>
);

CREATE TABLE Audit.ErrorEvents
(
    EventID int DEFAULT (NEXT VALUE FOR Audit.EventCounter), -- same sequence, different tables
    <other columns>
);

I've edited the example to highlight this usage.

An identical outcome could be achieved by generating the globally unique numbers in the application code, before they are passed to the database. Were I to implement this I imagine it would be as a static method of some utility class compiled into the executable (though other implementations would be possible). Say the application needs to write a customer's details to the database. As it is marshaling the customer name, address, telephone number etc. it also generates a new global ID. The ID is passed to the INSERT statement (or stored procedure) as just another parameter value.

Whether the ID values are produced by the application architectural tier or the database tier would depend on the specific design considerations. If the app can scale out coordination between instances becomes problematic. After an application re-start the code must figure out the next value to use. The DB server has these features, and others, written into it already.

What I would definitely not do is have the application call the database just for the next ID, then marshal that with the business data into an INSERT. That's too many round-trips to the database when only one is required.


Problem JOINing three tables I want to show all the categories and the items inside that each category which should store in items_this_category.

Note, I have created an associative table item_categories because one item can be listed in more than one category.

Here is a screen of NaviCat with structure and the query

What you need can be achieved via a simple combination of two joins, Let's have a look at a sample query

Select < Fields as per your requirements >
From items i
Inner join item_categories ic On i.item_id=ic.item_id
Right outer join categories c On c.category_id=ic.category_id
Where <Any conditions you may have>

I hope this is what you were looking for.


Is there a way to avoid duplicating the definition of a foreign key? I was wondering about this example

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

The definition of city as varchar(80)  is duplicated here. Is there a PostgreSQL syntax that allows not to duplicate varchar(80), only basing itself on references cities(city) so as to create the weather table?

There is no way to do that. And, frankly, I don't see the need.
Remember that referenced and referencing column don't have to share the same data type. They just have to have an = operator defined between them.

If your aim is to shorten the syntax, you could omit the column (or column list) of the referenced column(s) if it's the PK:

CREATE TABLE weather (
    city      text references cities,
    temp_lo   int,
    ...

Data type in the example is compatible but not the same (textvs. varchar(80)), referenced column is assumed to be the primary key of the table cities.

I quote the manual on CREATE TABLE:

  If refcolumn is omitted, the primary key of the reftable is used


Transaction isolation level - Read committed and select on large tables I am trying to troubleshoot an issue in a report and wondering if the below scenario is possible.
I have a tables which is 548GB and there are reports which sometimes select 5 years of data which could be alomost the entire table.  The table has a non unique clustered index.

I read about read committed isolation levels and was testing it to see how it was locking the table during selects and from my understanding it looks like it puts a series of shared page lock.   Acquire shared page lock, read page, release shared page lock, move to next page and so on.

If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past.  What I mean by beginning of table is that since the table is having a clustered index and since it has to be ordered if an lower value index id record gets inserted then it needs to be inserted into the starting half of the leaf level of the clustered index.

In general, the storage engine has two options to perform a clustered index scan: an allocation order scan or an index order scan. For an allocation order scan, the storage engine reads the data in file order. In your scenario, this could result in the data from the insert showing up in the first query. For an index order scan, the storage engine reads data in linked list order. In your scenario, the new data from the insert will not show up in the first query. Even if the page that contains the new row splits the new page will not be read.

The storage engine decides between an allocation order scan or an index order scan depending on the requirements of the query and the isolation level. For example, for a query that requires data to be in order an index order scan is the right choice. For a query with a table lock that does not require the data to be in order, an allocation order scan is the right choice. Specifically, you will end up with an allocation order of the unsafe category (a scan can return multiple occurrences of rows or skip rows when page splits occur) when all of the following are true:

The query is running under the read uncommitted isolation level
Changes are allowed to the data (a table lock was not taken by the query)
The plan shows Index Scan, Ordered: false (the query does not require the data to be sorted)
The index size is greater than 64 pages.

In your example you have an isolation level of read committed. You also did not take a table lock with your clustered index scan query because the second session was able to insert a row. Therefore, the storage engine will perform a scan of the index order scan variety. The new data from the insert will not show up in the first query that does the scan.

My source for this is pages 65-69 from T-SQL Querying by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee.


InnoDB issue after power outage a few weeks ago - no backup A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have been analysing the logs over the past few days to try and optimise as I noticed increased load recently - after exploring the mysql .err file I can see continual loading error messages such as the following -

  Cannot find or open table example_example/table1 from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.
  See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
  how you can resolve the problem.

We have tried the suggested link on MySQL but doesn't seem to be working.

We do not have backup of the databases from a couple of weeks ago, so not quite sure how to diagnose or resolve this but have come to a brick wall.

Any advice would be greatly appreciated.

The InnoDB dictionary contains information about all tables in your database(s).
The dictionary is a set of InnoDB tables hidden from a user.

Looks like a records about example_example/table1 is missing in the dictionary.

I'd explore content of SYS_INDEXES and SYS_TABLES to see if the table is in it. https://twindb.com/how-to-recover-innodb-dictionary/


Query to update one column of a table based on a column of a different table I have two tables with below structure :
Create table Customer_info
(Customer_num  varchar(50),
 Customer_Branch  int);

Create table Customer_Transaction_Info
(Customer_num     varchar(50),
 Branch_Code      int,
 Trns_Measure_One int,
 Trns_Measure_Two int
 );

Sample data for each table:
insert into Customer_info(Customer_num , Customer_Branch)
values('A',1),
      ('B',2),
      ('C',3);

   Customer_num     Customer_Branch
------------------- ---------------
     A                      1
     B                      2
     C                      3

insert into Customer_Transaction_Info (Customer_num,Branch_Code,Trns_Measure_One,Trns_Measure_Two)
Values ('A',1,10,0),
       ('A',2,20,5),
       ('A',3,14,0),
       ('B',2,10,10),
       ('B',1,36,0),
       ('C',3,14,0),
       ('C',1,10,18);

 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0
    A               2           20               5
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0
    C               1           10               18

What I need to do is this:

For each Customer_num in Customer_Transaction_Info , Trns_Measure_Two must be assigned to the correct Branch_Code for that customer which is the one in the first table , Customer_info

For each Customer_num in Customer_Transaction_Info, Trns_Measure_Two must be updated to 0 for  incorrect branches.

So based on these the desired result is below:
 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0---> Update to 5
    A               2           20               5---> Update to 0
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10---> This record is Correct
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0 ---> Update to 18
    C               1           10               18---> Update to 0

We must take these facts into consideration that:

There's only one Customer_Branch for each Customer_num in Customer_info table.
There's only one non-zero Trns_Measure_Two for each Customer_num in Customer_Transaction_Info

Thanks in advance

This should solve the first requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = CTI2.Trns_Measure_Two -- Set it to the correct value (the only non-zero value for that Customer_num)
FROM Customer_Transaction_Info AS CTI
INNER JOIN Customer_info AS CI -- Inner joining Customer_info to ensure we only update for the correct Customer and Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
INNER JOIN Customer_Transaction_Info AS CTI2 -- Need to re-join to Customer_Transaction_Info on Customer_num and Trns_Measure_Two <> 0 to get this customer's non-zero correct value
    ON CTI.Customer_num = CTI2.Customer_num
    AND CTI2.Trns_Measure_Two <> 0

This should update the incorrect branches for your second requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = 0 -- Setting incorrect values to 0
FROM Customer_Transaction_Info AS CTI
LEFT JOIN Customer_info AS CI -- Left joining to Customer_info so we can see where there's no match by Customer_num and Customer_Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
WHERE CI.Customer_num IS NULL -- This filters Customer_Transaction_Info to only the bad records (where there's no match to Customer_info)

Please see the comments for information on how these two update statements work.


T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008 After creating roles and granting privileges to them, I want to grant the privileges of a specified role to a user with grant option so that the user can grant privileges to others, how to do with it?

EXEC sp_addrolemember 'db_owner', 'sabertazimi'

Doing like this can't make 'sabertazimi' to grant his privileges to others.

For the specific code you used in the question you cannot use WITH GRANT OPTION which is normally used to grant privilege so the the user can grant same to other users.

For details and example see this:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql

  Permissions

  Adding members to flexible database roles requires one ofthe
  following:

  Membership in the db_securityadmin or db_owner fixed database role.
  Membership in the role that owns the role.
  ALTER ANY ROLE permission or ALTER permission on the role.

  Adding members to fixed database roles requires membership in the
  db_owner fixed database role.

For privileges using GRANT statement see these links:

https://blog.sqlauthority.com/2010/04/03/sql-server-difference-between-grant-and-with-grant/
http://sqlity.net/en/1884/grant-option-delegate-permission-management/
http://sqlity.net/en/1893/grant-using-grant-grant-option-roles/