Upgrade certification from MCSA sql server 2012/2014 to MCSE SQL 2016

In order to move from MCSA to MCSE you would need to take one of the following exams (As of Jan-2017) which one you take is entirely up to you - though if you want to use SQL on Azure I'd recommend taking one of the cloud-based exams:

Exam 70-473: Designing and Implementing Cloud Data Platform Solutions
Exam 70-475: Designing and Implementing Big Data Analytics Solutions
Exam 70-464: Developing Microsoft SQL Server Databases
Exam 70-465: Designing Database Solutions for Microsoft SQL Server
Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server
Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server
Exam 70-762: Developing SQL Databases
Exam 70-767: Implementing a Data Warehouse using SQL
Exam 70-768: Developing SQL Data Models
Exam 70-773: Analyzing Big Data with Microsoft R
Exam 70-774: Perform Cloud Data Science with Azure Machine Learning
Exam 70-775: Perform Data Engineering on Microsoft Azure HDInsight

You can find more information on the Microsoft Learning page for that exam track which also has recommended learning material and optional courses.

Regardless of which exam or training method you decide to take, there is no substitute for experience and you will find it much easier to pass the exams if you have hands-on experience with the product and specialization that you choose.


Benefit of having time dimension in a star schema?

Let me answer this question with a scenario starting with a simple Transaction table.  When our business started, management wanted to know the 'name' of the month, so I've included that information in the table.

DECLARE @Transactions TABLE (
    TransactionId INT
    ,UserId VARCHAR(10)
    ,CountryId INT
    ,TransactionDate DATE
    ,[MonthName] VARCHAR(20)
    ,SalesAmount DECIMAL(18, 2)
    )

Business has been good and we already have 1 million rows in our Transactions table.  In fact, business is so good that management is now asking more in depth questions about our sales.  They wanted to know what 'quarter' the sale was made.

ALTER TABLE Transactions ADD [QuarterName] VARCHAR(10)
UPDATE Transactions SET QuarterName = ...

We just updated 1 million rows.

As time goes by, management starts asking more and more questions about our sales.

  What DayOfTheWeek was that sale made?
  Was that a holiday?
  Was the moon full on that day?

ALTER TABLE Transaction ADD ...

UPDATE TABLE SET ...

Hopefully you can see where this is going.  Additionally, all of that redundant data on each and every Transaction row can contribute to reduced performance and increase resource utilization (memory, disk space, etc.).  Our databases are bigger and take longer to back up.  All of the redundant data takes up memory.

With a Date Dimension table, all of that information is stored in one place.  A Date Dimension table with dates from 2000-01-01 to 2100-01-01 contains just 36525 rows.  Anytime we want to track a new attribute of a date, we only have to alter that table by adding the additional attribute and update 36525 rows.

When we want specific information about the 'Date' attributes of a sale, we simply join up against the Date Dimension table

Additionally, the data in a Date Dimension is consistent.  January is spelled correctly, Saturday is spelled correctly, etc.  Storing this kind of data in the Transaction table can lead to all kinds of discrepancies with incorrect spellings, etc.

For more information on the creation of a Date Dimension table, check out
Creating a date dimension or calendar table in SQL Server


oracle database filter with full outer join and coalesce intersect

You can simply group by IPDL:

SELECT
    RAP.IDPL,
    MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41,
    MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42,
    MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL
GROUP BY RAP.IDPL

Are you sure you need the FULL joins? Try changing all to LEFT, might return the same result.

If this can be simplified further depends on the actual PK/FK and data...


MySQL Master-Slave replication foreign key constraint error

I see that you have used a multi-master configuration for m1 and m2 and a master-slave config for m1-s1 and m2-s2. Currently, the multi-master configuration does not support write actions on the tables with foreign keys. Thus, when there is a write action on either m1 or m2, the error occurs.

test to debug: set up a master-slave config for m1-s1 and m2-s2 without connecting m1 and m2. If both of them work as expected, then you can confirm that the foreign keys issue is due to the multi-master m1-m2 config.


Permission Error when trying to import SQL

That error is fairly straightforward, the Import Wizard is checking if the destination table exists and trying to create it because it is missing, but your login doesn't have sufficient permissions on the database or server to create a table.

I'm assuming you're not a DBA based on the question, so you're probably best placed to have your DBA look at either creating the table for you, running the import for you or granting you the required permissions.


In what range can you expect a cloud based Sql Server data reading in mb/s?

So measuring if a server can handle your specific workload by the amount of money it costs when weighing different implementation options is as as much of a moot point as measuring how much a car costs when comparing their MPG between different cars to figure out if they can drive you from point A to point B.
My analogy above is meant to convey that we can't really provide a meaningful solution based on $ spent since there are different types of server configurations for the same amount of money, and the same amount of money spent on the server will generally result in the same output performance of any implementation you choose (RDBMS, NoSQL, memory-cached, alternative DB system, etc) for equivalent problems respective of their domain. It is not qualitative enough for an accurate answer to be provided.
That being said, knowing things like how frequently the data is changing, how frequently new data is being added, total size of the Tables and database, and how concurrently active your database will be help paint the picture, which you've provided some of already.
Other things that will be key in helping clarify a good solution for you are what kind of data is it and how is your data structured currently? (E.g. is it highly relational, is it not very concretely defined, is it mixed.) What kind of queries are typical during your off-peak and peak concurrent time-frames? Examples are very helpful here in communicating what your data looks like and what the querying of that data looks like.
For example if your data isn't very well structured / not concretely defined and it's only typically accessed by the key then a NoSQL solution is probably best. If the type of querying your users do is analytical (such as aggregative querying) then it might make sense to scale up a data warehouse or use things built into Azure SQL to improve analytical querying.
Finally I'll add from my previous experience with a not too far off server of similar data size and concurrency, $300 was not enough to support the hardware behind our server. Sure, we definitely could've made some design and implementation improvements, but I still think $300 just wouldn't cut it (for a cloud based solution). I'm going to assume the same will be true for you, but that's a big assumption since my example is only anecdotal and as David Browne mentioned, you're best off testing to see what works within your price range.


What I want to know is, this does not affect the time that the query
  is actually committed does it? It only affects its recovery ?

It only affects recovery.

  I just want to make sure that when I do an insert or update that the
  query will run right at the time of processing and not 1 second later,
  no matter what the flush_log is set to.

Correct.  There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control).  If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

  My understanding is that in the case of a crash , setting to 1 will
  allow it to recover all queries run on the server, where as setting it
  to 2 may lose the last second or two of data when trying to recover
  from the crash, is this correct?

Correct.  To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance.  I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

  Also, if there is slow periods of updates/inserts (ie, not much
  happening on the server) does setting it to 2 add additional overheads
  on the server, or is it a case of the benefits of applying
  innodb_flush_log_at_trx_commit=2 to the database when the database is
  busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance.  It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?


MongoDB presplitting chunks for compound shard key

Just a slight issue with how you are passing the $minKey values in, try this instead:

db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : MinKey , "bar" : MinKey } } );
db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region2", "foo" : MinKey , "bar" : MinKey } } );

This got me the following layout:

sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 4,
    "minCompatibleVersion" : 4,
    "currentVersion" : 5,
    "clusterId" : ObjectId("53a2cd9d98b4ace818666544")
}
  shards:
    {  "_id" : "shard0000",  "host" : "localhost:30000" }
    {  "_id" : "shard0001",  "host" : "localhost:30001" }
    {  "_id" : "shard0002",  "host" : "localhost:30002" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "mydb",  "partitioned" : true,  "primary" : "shard0001" }
        mydb.mycollection
            shard key: { "region" : 1, "foo" : 1, "bar" : 1 }
            chunks:
                shard0000   1
                shard0001   2
            {
    "region" : { "$minKey" : 1 },
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : "region1",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} on : shard0000 Timestamp(2, 0)
            {
    "region" : "region1",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : "region2",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} on : shard0001 Timestamp(2, 2)
            {
    "region" : "region2",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : { "$maxKey" : 1 },
    "foo" : { "$maxKey" : 1 },
    "bar" : { "$maxKey" : 1 }
} on : shard0001 Timestamp(2, 3)

The use of the $minKey (MinKey) and $maxKey (MaxKey) values is a bit tough to tease out (they are rarely used except internally), but there is a decent, and illustrative example here in the docs.


You need to put your current query into a subquery, and include an expression for the "lead last_reset" you want to put into the where clause.

Something like the following (untested):

select interval, bytes_diff
from (
  select
    lead(update_time) over w - update_time as interval,
    lead(bytes_tx) over w - bytes_tx as bytes_diff,
    lead(last_reset) over w as lead_last_reset,
    last_reset
  from
    table
  window w as (partition by t_id order by update_time)
)
where lead_last_reset = last_reset;

NB "lead"ing by 1 is so common that it's the default and can be left out.

NB2 All of your window functions are operating over the same window. So I made that explicit by defining the window in its own clause.


Optimistic Paging and separate query for COUNT?

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows:

CREATE VIEW dbo.MyView
    WITH SCHEMABINDING
AS
    SELECT
        COUNT_BIG(*) AS NumberOfRows
        FROM [dbo].[Products];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView
    ON dbo.MyView(NumberOfRows);

(Note: an ID column can't be added to this view and then the clustered index created on it.)

Yes, this will add a small amount of overhead to writes to the base table; it doesn't sound like that's going to be a problem in this case.

Then it's really easy and efficient to read a transactionally-consistent row count:

SELECT NumberOfRows FROM dbo.MyView WITH(NOEXPAND);

which can either be included in the same query, or separately. Personally, I'd try to keep it separate and return the value as an OUTPUT parameter. In either case, for an exact count, I'd strongly recommend using snapshot isolation to avoid out-of-sync issues.

This type of structure could also be created with a user table and a trigger, or a "business logic" approach... but because of the number of moving parts in either of those solutions, I really wouldn't recommend them unless there's no other choice.

If you do go this route, be sure to patch SQL Server to at least:

Cumulative Update 7 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2014 Service Pack 1
Cumulative Update 8 for SQL Server 2014 RTM

See the Knowledge Base article:

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

For more details and background, see An Indexed View Bug with Scalar Aggregates by Paul White.


Is it possible to restore a SQL Server Linux Backup on SQL Server Windows

I just validated it. Yes. Not only is it possible, other than using the WITH MOVE to change where the files are located, there's literally nothing special involved. I tested it using a shared volume to get the backup out of my docker instance. I also tested it using the docker file copy command. Both worked just fine. It's possible that the backup is corrupt or that the versions are off.


Monitoring Query which executes thousand of run/minute and is generally fast

For the cumulative DMVs, if you're capturing them routinely, then you just need to compare the differential changes between the timeframe of when your query that you're tracing slows down. Then you should be able to pinpoint abnormalities.
You can also look into the sp_Blitz procedures, specifically sp_BlitzFirst during the moment of contention.


Clustered index creation fail on table creation

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016):

The other instances, where this syntax is working for you, must be on SQL Server 2014 or newer.

On 2012, regardless of compatibility level, you'll need to create the index separately.


Multiple subqueries in columns with joins to return multiple rows

why you need use subqueries ?

You can use joins for get data

SELECT u.name AS user, d.name AS departement, m.year
FROM main AS m
INNER JOIN department AS d ON m.departement_id = d.id
INNER JOIN users AS u ON m.user_id = u.id
;

I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


Is it normal that a data flow task's OnError event handler fires 3x and how to avoid that?

In my SSIS packages, I enclose the entire control flow in a sequence container, with a "Send Mail" task of the container's "on failure" flow.

In my error handlers, I collect the error information and append it to a string variable.

Then, in the "Send Mail" task, I include the string with the accumulated error information in the body of the message.

Now, this only works as is if the failure in your data flow is fatal to the package as a whole. If you're (for example) processing several files, and want to send out one error message for each file that fails its processing, you'd need to modify this to send the mail at the correct time, and clear the error detail variable; however, the same principle should apply.


Syntax error using preparedstatements with postgresql

The common solution is to express this as:

interval '1 day' * ?

with ? as a placeholder for a numeric value (possibly with a fractional part).

The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write 3.? and pass .1415926 as an external parameter to form the PI number.


Create window (PARTITION BY?) out of column values

WITH cte AS (
SELECT *, COUNT(CASE WHEN v=0 THEN 0 END) OVER (PARTITION BY k ORDER BY d DESC) r
FROM test
)
SELECT k AS key, MIN(d) AS "date", MAX(d) AS date_of_zero
FROM cte
WHERE r > 0
GROUP BY k, r
ORDER BY 1, 2

fiddle

If there are 2 adjacent "zeros" then 2nd record (by date) forms a separate group. The same for a "zero" with the most first date.

If there can be "zero" and "non-zero" with the same date you must decide what is their priority.

The final records are not listed if the most last record is "not zero".


High number of Checkpoint pages/sec and memory pressure

How does a high number of checkpoint pages/sec indicate memory pressure?

It does not directly indicate memory pressure. The value itself is cumulative and does not give much insight into memory pressure.

In the blog mentioned by you I would not rely on following 2 counters for memory pressure.

Buffer cache hit ratio(BCHR)
Checkpoint pages/sec

For BCHR the reason is already explained by Jonathan Kehayias in This Blog. It would give you incorrect interpretation if you rely on it because of SQl Server feature of Read Head.

The work of checkpoint in SQL Server is to decrease the overall recovery time of SQL Server. More frequent checkpoint means less amount of transaction log record needed to roll forward and hence shorter recovery. So I would not equate high checkpoint pages/sec to memory bottleneck. It would need further analysis or simply on a very busy system it can be ignored. It is just the SQL Server trying hard to make sure when database goes through crash recovery it comes online in minimum time letting you meed the RTO.

If I would want to look at memory pressure I would fire following perfmon counters

SQLServer:Buffer Manager object Page reads/sec
SQLServer:Buffer Manager object Free Pages
SQLServer:Buffer Manager object Page Life Expectancy
SQLServer:Buffer Manager object Free List Stalls/sec
Memory Manager: Total Server Memory (KB)
Memory Manager: Target Server Memory (KB)
Memory Manager:  Memory Grants Pending
Memory Manager: Free Memory (KB)


Database design - need someone to guide me

Learn Table Inheritance (aka subtype/supertype)

Individual   : Legal Party (":" meaning "inherits")
Organization : Legal Party

Email          : Address
Phone          : Address
MailingAddress : Address
WebURL         : Address

LegalParty -< PartyRelationships >- LegalParty
LegalParty -< ContactInfo >- Address

Read up on existing data model patterns. Don't re-invent the wheel


First, I would check the "effective" permissions of the Service Account used to run the MSSQLSERVER service for that NTFS share. This is mainly to ensure that no "deny"s exist for that account, or any Active Directory group in which that the account exists, in a parent folder that are getting inherited down to the path in question.

Second, I would consider attempting to enable Kerberos Delegation for that service account, as per the Microsoft recommendation for BULK INSERT (see below). Now, I would have expected that granting the service account the appropriate read/execute permissions and using a SQL Server Login would have worked (delegation shouldn't be needed in this case because the logon event is local and the external resource is only a single hop away), BUT since it isn't, some other factor could be getting in the way (perhaps Kerberos and SPNs are required, even when not using Delegation). Either way, it seems like this is a common-enough scenario for Microsoft to have documented it across two MSDN pages.

If you currently are using NTLM instead of Kerberos, you try switching to Kerberos and setting up the necessary SPN first, prior to enabling any account for Delegating, and see if that works.

  Security Account Delegation (Impersonation)

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

  When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

  To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
  For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

And if you follow that link at the end to the "Import Bulk Data..." page, you will see the following section:

  Security considerations

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service).

  To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

  For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.
  SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.


You might want to query relevant tables like:

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.server_principals
sys.databases
sys.dm_exec_sql_text()
sys.dm_exec_query_plan

...to have an overview of what is happening even if nothing is happening.

Query Running Tasks (and Other Stuff)

Run something like this to see what is going on in your system and which ressources are being used.

SELECT des1.session_id AS Session_ID_S,
       --des1.context_info as Session_Contxt_Info,
       --dec1.session_id AS Session_ID_C,
       dowt.session_id AS Session_ID_WT,
       dowt.exec_context_id AS Exec_Contxt_ID,
       sdb.name AS DatabaseName,
       ssp.name AS SQL_Login_Name,
       des1.nt_user_name AS NT_User_Name,
       --'--kill ' + cast(dec1.session_id as nvarchar(20)) as killcommand,
       dowt.wait_duration_ms AS Wait_Duration_ms,
       dowt.wait_type AS Wait_Type,
       dowt.blocking_session_id AS Blocking_Session_ID,
       dowt.resource_description AS Ressource_Description,
       des1.program_name AS Program_Name,
       dest.[text] AS SQL_Text,
       deqp.query_plan AS Query_Plan,
       des1.cpu_time AS CPU_Time,
       des1.memory_usage AS RAM_Usage
FROM   sys.dm_exec_sessions AS des1
       LEFT
       JOIN sys.dm_exec_connections AS dec1
            ON  des1.session_id = dec1.session_id
       LEFT -- comment out LEFT to display only sessions that have gone parallel
       JOIN sys.dm_os_waiting_tasks AS dowt
            ON  des1.session_id = dowt.session_id
       LEFT -- comment out LEFT to display only sessions currently executing statements
       JOIN sys.dm_exec_requests AS der
            ON  des1.session_id = der.session_id
       LEFT
       JOIN sys.server_principals AS ssp
            ON  des1.login_name = ssp.name
       LEFT
       JOIN sys.databases as sdb
            ON des1.database_id = sdb.database_id
       OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest -- Retrieve Actual SQL Text
       OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp -- Retrieve Query Plan (XML)
WHERE  des1.is_user_process = 1
ORDER BY
       des1.session_id, dowt.exec_context_id

Query Output

It will provide you with an overview of all the tasks running and under which account the "root" session was made if you have parallel processes (CXCONSUMER/CXPACKET). An example of an output will look like the following screenshot:

Your output will be different depending on the number of LEFT commands that you comment out. If you leave them all in, then you will have a full list of all sessions (without system sessions session_id < 50) and you should be able to pinpoint the issue.

Answering Your Question

  Does it happen when the user logs into application and switches to another module and afterwards comes back to the same module and a duplicate session is created and leaving the actual session open?

That is something we cannot answer. We don't know how your application is designed and whether or not a module switch will initiate a new connection.

If your application has some form of threading in the background, then you might be just observing the threads for each connection in the database as you can see in my example output. OR you might be observing that the query is running in parallel, because the query optimizer has decided that your query is best processed (partly) in parallel.

If you are still seeing sessions without a login name as in the next example:

...then you at least can see which Application / Program / Statement / Wait Type is causing the issues. Armed with this information you can dig further.


How to store a database table in multiple disks with assigned ratio?

To use multiple disks for IO, your best bet is to use RAID, preferably hardware RAID, giving both increased reliability and performance both.

Trying to do it by hand is horrifyingly bad; you're unlikely to get a good performance balance, that performance balance probably, won't survive table growth, and a single disk failure destroys your data - you're effectively suggesting RAID 0 (striping) with all the disadvantages, but without most of the benefits.

If you insist on the mindbogglingly crazy idea you're looking at, then MS SQL Server Enterprise Edition (and likely other products) has partitioned tables, and that would allow you to have one table with partitions on different filegroups, and then you could put the different files of the filegroups on different partitions.  You'd control proportions by the choice/control of the data the partitioning is done on.

The budget crazy idea is multiple tables to store different percentages of the data (by hand partitioning), and then UNION them into a view.

Again, don't do this - use RAID*.  Heck, even use software RAID if you have too low a budget for hardware RAID!

* RAID 1, 10, 5, 50, 6, and 60 are approved variants for databases.  Do NOT use RAID 0.


select   i.id,
         i.name,
         i.description,
         s.quantity stock,
         (select purchaseRate
          from itemPurchaseDetail
          where item_id = i.id
          order by PurchaseDate desc
          limit 1) lastPurchaseRate
from     item i
join     (select distinct item_id, branch_id
          from itemPurchaseDetail) pd
on       pd.item_id = i.id
join     stock s
on       s.item_id = i.id
and      s.branch_id = pd.branch_id;

id | name  | description | stock | lastPurchaseRate
-: | :---- | :---------- | ----: | ---------------:
 1 | item1 | desc 1      |   100 |              222
 2 | item2 | desc 2      |   200 |              444
 3 | item3 | desc 3      |   300 |              666

dbfiddle here


MySQL integer column with string search returns the invalid result

A column declared to be INT is designed to hold a number, but not a "phone number".  It can be in the range of about +/- 2 billion.

For a phone number, use VARCHAR(20) CHARACTER SET ascii; that will probably be long enough (20 characters) to handle country-code, dashes, extension, and other kruft that the user might enter.

If you desire to validate that the number is a "valid" string, do that in your application code before trying to store it.


can I use many-to-many relation with weak entity

I'd say that this is pretty standard stuff ;

Create table employees (id int primary key,
                        name varchar(100)
                        -- Plus some additional columns
                        )

Create table responsibilities (id int primary key,
                               description varchar(100)
                               --Plus some additional columns;
                               )

Create table employee_responsibilities ( employee_id int foreign key references employees(id),
                                         responsibility_id int foreign key references responsibilities(id)
                                         --Plus some additional columns;
                                       )

You might want to have a surrogate primary key on the relationship table ;

Create table employee_responsibilities (id int primary key,
                                        employee_id int foreign key references employees(id),
                                        responsibility_id int foreign key references responsibilities(id)
                                        --Plus some additional columns;
                                        )


Building a ColumnStore index hogs system resources

Run the index build in a resource governor group that limits the resources you want to give. Columnstore building is CPU and memory intensive. Resource governor should be your preferred way to throttle resources, no matter the scenario.


Is there a way to see the access log of a table?

You need to be granted the necessary permissions on the table that contains the audits.  The minimum for you to achieve what you want is you need to be granted SELECT on that table.

As far as check if something is being inserted/deleted, you could always create a trace with SQL Profiler.  At the same token though, you need ALTER TRACE permissions on the server, and if you don't have the former SELECT permission I doubt you have ALTER TRACE.


How to optimize a large join to create a WHERE IN

The abstract_mesh table has no index.
As this EXPLAIN QUERY PLAN output (from SQLite 3.8.10.2) shows, searching in this table is so slow that it is worthwhile to create a temporay index as part of the execution of the query:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|0|SCAN TABLE erpubtbl AS e USING INDEX erpubtbl_year_idx
1|2|1|SEARCH TABLE abstract_mesh AS am USING AUTOMATIC COVERING INDEX (pmid=? AND mesh_id=?)

You should create such an index explicitly so that the query does not need to:

CREATE INDEX abstract_mesh_pmid_mesh_id_idx ON abstract_mesh(pmid, mesh_id);

However, if you create some other indexes:

CREATE INDEX abstract_mesh_meshid_pmid ON abstract_mesh(mesh_id, pmid);
CREATE INDEX e_pmid ON erpubtbl(pmid);

this allows the database to use a different execution plan, which might or might not be faster for your data:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|SEARCH TABLE abstract_mesh AS am USING COVERING INDEX abstract_mesh_meshid_pmid (mesh_id=?)
1|2|0|SEARCH TABLE erpubtbl AS e USING INDEX e_pmid (pmid=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY


Please use Visual Studio Community Installer and install .Net Development.  After this, you will be able to install MySql for Visual Studio 1.2.7.


Oracle - capture row commit order in a table

It isn't possible to determine the order in which rows are committed.  Either a sequence or a timestamp can allow you to determine the order in which rows are inserted.  But neither will tell you the order in which those transactions were committed.

Since you have a process that is trying subscribe to changes in a table, however, you have a few options that don't require you to identify this order.  You can use Oracle Change Data Capture (CDC) and then have the reading process register as a subscriber.  Or you can use Streams to publish changes to the table and have the reader process act as a custom apply process.

Both CDC and Streams will send the subscriber the data in commit order.  The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order).

Normal CDC involves the reader process controlling a window that shows one change set at a time, consuming that change set, and then advancing the window.  The reader consumes data in commit order and never needs to know specifically what the SCN of a change is.  You can hit the change tables directly as well to get the actual SCN if you want to complicate the reader's pull process.

Your reader process can hit the change tables, get the SCN from the change table, move it to the sequence_id column of your existing table, and then do whatever else you need to do with the data in the change table to move it into your existing architecture.  It's a bit messy but doable.  Your reader process could also be a Streams consumer and do the same thing (grabbing the SCN from the Streams change record rather than the CDC change table).


Restoring a single database from mysql enterprise full backup

Try this:

mysql -u root -p --one-database destdbname < alldatabases.sql

In the above code substitute destdbname with the database name you want to restore, and alldatabases.sql with the name of your full DB backup.

If you would rather just extract the database dump of the single database from the --all-databases dump file, you can do this with sed using this command:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Where dbname is replaced with the database name of the database to extract, and alldatabases.sql is the name of your dump file. The result will be saved into the file output.sql.


We've made some tests. It turns out that auto-increment thing works completely different, so my concerns were meaningless.

With three nodes each insert into the table updates auto-increment index of a table completely. So you're always have not used values, only in rear cases where two inserts from different nodes are coming simultaneously then ids can appear one after another.

This brings us to another thought. If we have five nodes we're gonna be out of INT(11) five times sooner than with an ordinary non-cluster table.


Table does not exist even if there is one

Removed ibdata1? You can* remove iblogfiles but not the ibdata1!!
They're system tablespaces storing tables, indexes, metadata, undologs....

*=> Removing iblogs is okay once you stop mysql but not while it's running!

Update: Removing iblogs is okay only after shutting down mysql with innodb_fast_shutdown disabled.

You might like to review this answer to understand what is wrong! You might want to do a restore from the snapshot may be.


What is difference between internal and external load balancer listener with respect to azure (IaaS)

External - Uses the public Virtual IP address of the cloud service that hosts the Virtual Machines.     Use when you need to access the listener from outside the virtual network, including from the internet, i.e. public facing

Internal - Uses Internal Load Balancing (ILB) with a private address for the listener.  Use when you only access the listener from within the same virtual network. This includes site-to-site VPN in hybrid scenarios, i.e. not public facing


InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row.

b)  sequential vs random order of fetches only matters when you read data pages from disk, but when your query by birthday fetches first row from new page, innodb loads the entire page anyway and fetching next row of following born is "free" - no read from disk is needed because the page has already been loaded - it does not matter that mysql does not know the primary and secondary indexes are "parallel", it will have the right effect even without this knowledge - but the effect might be diminished if you have long rows so not many of them in one page. And MySQL won't use such index if it leads to reading more than ~20% of the table because it will prefer the clustered index for full scan in such case.

c) clustering by primary key does not actually mean that all the rows are stored totaly sequentialy in some file. It means that the table is organized as a B-Tree ordered by the PK. That B-Tree is stored as separate pages (nodes), each one containing some number of rows (depending on row size). So one page contains rows whose PK values are close together. But there is no guarantee that the logically next page will be stored physically next to the previous one. It may be "anywhere" in the table file (or in the big ibdata file if tables are not stored separately). When you only append to the table and never delete, you may expect that the new page will usually be allocated at the end so after the previous one, but there are possibilities of page splits even in that case and more so if the records are updated and deleted. That leads to "fragmentation" and non-continuous storage of the B-Tree pages. Afaik there is no direct defragmentation the way MyISAM offers (but rebuild might do that) for this but from what I gathered in the most extreme cases it may slow down table scans significantly. This is separate from file-level fragmentation but is very similar to that.

Mostly I agree with your assesment of the situation, the 3-query version looks promising and might be worth benchmarking for some cases. But you asked for some thoughts and this is what I got.


With SQL Server 2005 & 2008 the first schema that is searched is the users default schema.
from SQL Server Best Practices – Implementation of Database Object Schemas

Default Schema
Users can be defined with a default
schema. The default schema is the
first schema that is searched when it
resolves the names of objects it
references.

and

creating the base tables in
the dbo schema will enable table
objects to be referred to without an
explicit schema name. This is because
an object will be located by searching
the default schema first, followed by
the dbo schema. So:
SELECT * FROM Table1
Will assess the following statement first:
SELECT * FROM <defaultschema>.Table1
If it cannot find the object, the server will assess the following statement:
SELECT * FROM dbo.Table1
The assessment process can be improved by using either the fully qualified name
or the DEFAULT_SCHEMA option described earlier. By setting a value for
DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA
first, removing an unnecessary ownership checking process.
This can improve performance considerably on heavily utilized systems.

Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting


Manually installing sample schemas in Oracle 11g

Manually Installing Sample Schemas

  Install Oracle Database Examples (Companion CD, part of the media kit)
  to include these scripts in the demo directory under $ORACLE_HOME.

The scripts for installing sample schemas is a seperate download and they are not installed by default.

Download Oracle Database Examples

Follow the instructions there.


Relationship with a table without unique/primary key

Primary keys (and foreign keys) should be unique, otherwise how does the database know which row the foreign key relationship is pointing to?

  I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I wouldn't worry about hitting the limit for ints. The mysql documentation lists the limits of various int types. If you're concerned about having more than 2 billion rows anytime soon, you can always use bigint and have over 9 quintillion rows before overflowing. 18 quintillion if you used unsigned.


How to resolve red invalid icon for Informatica mapping

This was due to an incomplete mapping because my expression transformation didn't have output ports (only input ports) just before the target definition.


How to handle concurrency during an INSERT-SELECT cycle?

Not sure if that's the best approach, but it seems to be a quick and reliable as long as application[s] consistently uses it.
Create a new table, access_attempt (user_id integer not null primary key).
Modify you workflow :

begin ;
insert into attempt_access (user_id) values (:user_id);
-- do you verification, raise an error if needed
insert into access (user_id, dt) values(:user_id, current_timestamp) ;
delete from access_attempt where user_id = :user_id;
commit;

A brief description.  First of all, a new transaction is started and we insert a user_id
 into the new table attempt_access which has just one column. Until transaction completes , other transactions will not be able to insert row with the same user_id and will wait until the first one complete.  Then verification is performed, record insert into access table, and finally the record inserted in the very beginning is removed. Now transaction can be committed . If transaction is rolled back the record in attempt_access will be removed automatically .


This issue is to do with corrupt/invalid statistics.

A specific case where it can occur is mentioned in this connect item

  The Metadata of disabled Non-Clustered Indexes becomes invalid, if you
  change the keys of the Clustered Index, and this caused the failure
  that you experienced.

With the workaround being to drop the disabled indexes.

If the situation above doesn't apply to you then try and find out the specific table with the problem hopefully dropping the statistics on this specific table or both tables if you can't pinpoint the problem one should do the job (and then recreate the stats if required).

If neither of the above work then probably time to open a case with Microsoft PSS.

Also you appear to be on SP2 + Security Bulletin MS15-058. There have been a lot of cumulative updates and another service pack since then. Possibly the issue you are experiencing is already fixed in one of these.


Design datastore persistence based on state of the Entity

Mongo allows the content of a record to change. You can add any values you need and remove any you don't each time you persist a record.

The application could have an object class for each state. To mutate a record read it into an instance of whatever class corresponds to its current state. Then map the values you intend to retain to an object of the new state. This new-state object will not have attributes for the values which are no longer relevant. Now persist the new object. The on-disk representation will have only those values applicable to the new state.

Whether you have a collection per state or hold all the records in a single collection is, to a large extent, an operational concern. One risk with separate collections is that the system may fail after the new record is written but before the old one is removed, leaving duplicates. You'll need some clean-up logic on start-up to account for this.


In Sybase, what can I do to figure out why my Transaction Log is filling up?

The transaction log is where Sybase stores all changes made to a database. It can be dumped separately from the database. Dumping the transaction log basically provides some kind of incremental backup functionality.

The first thing you need to figure out is whether you need the transaction log on this database i.e. is it OK for you if all data created since the last database dump are lost in case of a crash. If it is the case, a solution would be to enable truncation of the transaction log on checkpoint.
If you need the transaction log, you'll have to create a threshold action to dump it to a file. This will empty it and you can proceed.


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

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


I think you might be able to get the results you want with:

select j_title, k_title, l_title, sum(wrk_hr) from
(
  SELECT
   J.J_Title,
   K.K_Title,
   L.L_Title,
   L.Work_ID,
  SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
  FROM C, L, K, J
  WHERE J.Project_ID = 'ABC'
  AND K.Project_ID = J.Project_ID
  AND L.Project_ID = K.Project_ID
  AND C.Work_ID = L.Work_ID
  GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
)
group by j_title, k_title, l_title;


AWS performance of RDS with provisioned IOPS vs EC2

I have done little testing on EC2 instances, but I have done extensive bench-marking of Oracle RDS instances with and without provisioned IOPS.

In my experience the provisioned IOPS deliver close to the advertised performance. Eg. if you provision 1000 IOPS, you will likely peak close to a thousand IOPS with a response time of less than a millisecond, depending on queue depth.

If your workload is I/O-bound then provisioning IOPS may lead to a significant increase in performance.

Note that IOPS provisioning is also available on EBS backed EC2 instances, presumably with comparable performance.

When comparing RDS to EC2 I suppose it mostly comes down to how much control you are willing to give up for the convenience of using RDS over self-managed databases.


Mysql backup of remote server in local machine using percona xtrabackup

You need to install Percona Xtrabackup on the remote server and then SSH from your local server to the remote server. From this position, you can initiate the backup and stream to your local server.

Xtrabackup requires access to MySQL's data directory (and MySQL instance as well), so it can't be run on another server. However, it can be triggered by another server with SSH (or alternative solution).

For example:

ssh serverA "innobackupex --stream=xbstream /var/lib/mysql" > backup.xbstream


Difference between inline view and WITH clause?

There are some important differences between  inline views (derived tables) and WITH clause(CTE) in Oracle.  Some of them are quite universal, i.e. are applicable to other RDBMS.

WITH can be used to build recursive subqueries, inline view -not (as far as I know the same is for all RDBMS that support CTE)
Subquery in WITH clause is more likely be physically executed first ; in many cases, choosing between WITH and inline view makes optimizer to choose different execution plans (I guess it's vendor specific, maybe even version specific ).
Subquery in WITH can be materialized as a temporary table ( I'm not aware if any other vendor but Oracle supports this feature).
Subquery in WITH can be referenced multiple times , in other subqueries, and in the main query (true for most RDBMS).


I configured the max memory with the same number on each instance. Total 36 Gigs and I gave 17 to each

This is a poor configuration, change is ASAP. You have allocated 34 GB to SQL Server and just 2 GB to OS, in such scenario you are bound to starve OS from memory resulting in paging or memory trimming of SQL Server processes.

If you want to fix the memory for both the instances start with 15 GB for each instance and leave 6 GB for OS. This is considering that you do not have any other services being used like SSAS/RS/IS, if they are their, you need to give 2-3 GB more to OS so that these extra services can use it.

  Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between)

Target memory can change as per the load on the system, nothing alarming but, having said that you cannot just rely on one counter you have to look at lot of other counters related to memory.


PostgreSQL asynchronous replication (and possible data loss)

When the master comes back up, any left-over WAL must be synced to the slave, otherwise the slave won't be able to carry on as a replica. At that point the slave will replay the WAL and be caught up with the master. No data is lost so long as the master comes back up. You only lose not-yet-replicated data if you lose the master.

This is guaranteed by the timeline; the slaves simply refuse to replay WAL archives except in the correct and unbroken timeline order. So long as you're correctly monitoring your slave nodes you'll see them stop advancing and know things are broken. This shouldn't happen if WAL archiving is set up properly, though.

Consider using async streaming replication as well as WAL shipping, that helps reduce the potential loss window if you do lose the master.


Cant get rid of filesort in simple query

You can try to add an index on time_changed:

alter table texts add index (time_changed);

this could get rid of Using temporary and filesort. But you need to define indexes for your query because the query plan show Using where instead of more healthy Using index.

Updated

Maybe the main culprit for the temporary/filesort is the order of the join. It is not good practice to force the optimizer without a good reason, but you could try to force the join order to test if the temporary/filesort goes away. I have swapped the tables and added a straight_join clause:

SELECT straight_join
     t2.text_id
FROM
    texts AS t2,
    texts_to_categories AS t1
WHERE
    t1.category_id = 123
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10;

Let me know what is the query plan of the previous query.

I don't think that temporary/filesort is a big deal for few hundred rows. I would let the optimizer do its job. I would be more worried to get a better tables schema. If it is feasible for you, I suggest the following changes to your schema.

For texts_categories your table permits NULL value for text_id and for category_id. So I would delete NULL values:

delete from texts_to_categories  where category_id is NULL or text_id is NULL;

alter table texts_to_categories
change category_id category_id int unsigned not null,
change text_id text_id int unsigned not null;

Next if you don't need texts_categories.id column, I would get rid of it, and I would reorganize indexes:

alter table texts_to_categories
drop column id,
add primary key category_text (category_id, text_id),
drop index text_id,
drop index category_id,
add index text_id;

For table texts I will try time_changed column to do not permit null values:

update texts_bck
set time_changed = '2010-01-01' -- some default
where time_changed is NULL;

alter table texts
change column time_changed time_changed datetime not null
default current_timestamp;

I hope this is of help.


SQL Server Replication - Can destination db have stored procedures that source db lacks?

Yes, absolutely.

You would typically use transactional replication to move data between the publisher and subscriber(s) (although you can include other objects as well). Your subscriber (destination) database is fully writeable, and you can create stored procedures there, add indexes to replicated tables, and even create entirely new tables.

Be aware, you can also delete data from the replicated tables on the subscriber, which could lead to replication breaking and you having to re-snapshot.


Installing Anti-Virus on Availability Group databases

Theoretically, whatever box it's on shouldn't be doing to sort of activity that leads to virus and malware. If you follow that guide things for the most part should be fine to install the software but it will still be using some hardware resources.

I'd recommend you just follow the Microsoft laid path. You may also want to talk to the software vendor to make sure they don't have any specific recommendations. Beyond that just beware that the software will still have processor overhead as well. You'll likely not want to have SQL Server maintenance jobs at the same time as whatever scanning this virus software scheduled to run. - zane


Should a Sql Server be accessible through the service's virtual network name?

Given CLUSTER_1, with NODE_1 and NODE_2, and virtual network VNN_1 with IP_1, these will work:

VNN_1\INSTANCE
IP_1\INSTANCE

These will not work:

CLUSTER_1\INSTANCE
NODE_1\INSTANCE
NODE_2\INSTANCE

If you are unable to connect using VNN_1\INSTANCE then, can you ping it, and what does it resolve to? Is it configured for a single IP (i.e. not multi-subnet)?


SELECT
                 students.ID as 'StudentID',
                 course.ID as 'CourseID',
                 students.name,
                 course.course_name,
                 course.cost_fee,
                 students_Course.net_cost
        FROM
                 students_course INNER JOIN  students on students.ID     = students_course.students_ID
                 inner join course on course_ID = students_course.course_ID
        where
                 course.ID = cos_ID;


Why are tiny Innodb row updates causing so much disk IO?

Any UPDATE to a row requires the following (sooner or later):

Read the 16KB block containing the row (unless cached in the buffer pool)
Write the block back to disk (or cache it a long time)
The "doublebuffer" is written once per transaction
Do one write to the InnoDB log for transactional integrity.  (The
details here depend on various settings and whether you wrap things
in BEGIN..COMMIT.)
Since you say the buffer_pool is big enough (apparently for the entire table and any secondary indexes?), the first two items will not tally many IOPs.

But that was not your question.

The actual I/O (pages) related to the buffer_pool is (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)

You will probably find this very low because of good caching:
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests


What are the different ways to restore a database?

Backup scenarios are designed around the risk profile. These include resilience and time to restore as the most critical factors.

The backup needs to account for the potential risks to be encountered including physical destruction, a malicious insider, a technological competent criminal, or a sever database flaw.

Backups will include offsite/offline backup based on risk assessment.

Backups can include a bunch of logical (SQL dumps), and binary backups (copy of the entire datadir based on stopped server or xtrabackup), binary logs for point in time recovery, and can frequently involve a replication slave to assist with such mechanisms.


Are db_recovery_file_dest and db_recovery_file_dest_size parameter dynamic or static?

The isinstance_modifiable flag only tells you whether that parameter can be set differently for different instances (for RAC) or not. For the parameters you're looking at, it is false, meaning that all instances in a single RAC cluster must have the same values.

If you want to know when you can change a parameter, look at two other columns in that table:

isses_modifiable: if TRUE, the parameter can be changed with alter session
issys_modifiable: tells you about changes that can be made with alter system.

IMMEDIATE means yes and changes take effect immediately,
DEFERRED can be changed, but will only take effect at next start,
FALSE can only be changed if you used an spfile to start the instance, and changes only take effect on next start.

Complete details are available in the v$parameter documentation.


If your problem is timing out and you have no way of solving that issue, maybe you might want to process your records in chunks, but also with a single update. Here is a little demo, just to give you the idea, you can use another chunking method other than mod if you don't have an incremental key. And I have to add, for the good practise follow Philip's advice if you can.

create table BIG_UPDATE as
(select
    rownum COL1,
    'data' COL2 from ALL_OBJECTS);

--612 msec
update(
    select COL1, COL2 from BIG_UPDATE
    where
        mod(COL1, 8) = 0
)
set col2 = '1234';

--3secs
update(
    select COL1, COL2 from BIG_UPDATE
)
set col2 = '1234';

rollback;


Restore the database as you would normally do it.

But at the end, instead of:

alter database open resetlogs;

Open the database with:

alter database open resetlogs upgrade;

Then upgrade the database to the new release. Instructions can be found at:

Manually Upgrading a Non-CDB Oracle Database

Note that the above documentation is for version 12.1.

Direct upgrade from 11.2.0.2 to 12.2 is not supported, you need an extra upgrade step to 11.2.0.3 or 11.2.0.4 or 12.1 before upgrading to 12.2.


PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

You appear to be attempting to load uuid-ossp extension by loading the .sql file directly. You should not be doing that on any modern PostgreSQL, and it won't work on RDS.

Use

CREATE EXTENSION "uuid-ossp";

instead.


How do I append all tables from one Azure Database to another?

The easiest way to do it is going to be to SSIS to migrate the data over. The wizards alone should be able to do something this simple. The most efficient way would be to export everything to Azure blob storage and then use BULK INSERT. I'd do it all using Powershell so that you can control error handling better. It's going to be a lot more work though. If it's not that big and it's a one-time thing, I'd go with SSIS.


You will need to do a trace or Extended Event session to determine what application is attempting the backups. Be sure to include the client hostname and application name, those two generally help indicate what is taking the backup. The client hostname will most always show the local machine name, which if that is the case look at what is installed on the server.

Some examples of 3rd products that will use VSS:

Symantec NetBackup SQL Agent
Symantec Backup Exec ( I believe does this now as well)
VMWare/Hyper-V (both products have options to along with snapshot of server to also do database)


#5: transactional replication.

Why is this an option? Because the documentation explains exactly why it is possible there - once initialized, index changes are not replicated in either direction.

  Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

I also think you are reading the text far too literally. If you have configured replication at all, it is highly unlikely you should assume that it would be configured to replicate to another database inside the same instance of SQL Server - I'm not even sure if that is possible, or if there really is a plausible use case for that, but I don't think the wording of the question was slanted to point you that way. To be honest, I just think the quality of questions in those exams, in general, is shady. I wouldn't read too much into them.

Why are none of the other options possible? Because:

In no Availability Groups configuration can you create indexes on the secondaries, since the secondaries are essentially read only (though you can create statistics; these, however, live in tempdb). IIRC there have been requests to support something like this for indexes too, but no plans. This rules out #1, #2, and #4.
Log shipping isn't a good fit for the requirement, since you can't change the destination database and continue applying logs. So - since the question states that logs are restored every 10 minutes - you'd have to restore the database with recovery every 10 minutes, create the indexes, then kick everyone out 10 minutes later to do it again. This is not "up-to-the-minute" reporting. This rules out #3.
Snapshot replication can't be updated once initialized, since it is literally a snapshot - in order to create indexes, you'd have to do it every time you restored a snapshot, since this is an all-or-nothing operation. It is not possible to provide "up-to-the-minute" reporting in this model. This rules out #6.
In failover clustering, there is only one copy of the data, and only one instance of SQL Server, and the data is only visible to any workload (read or write) on the primary ("active") node. So, by definition, it is not possible to make changes to a copy of the data, since there is no "copy." This rules out #7.


If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0.

Nothing in that posted code or data indicates what could force all values of FunctionWeight to come out as 1. Since some of the FunctionWeight values are 1, I have to assume that the "snapshot of #Temp2" image was taken after testing FunctionName values that have non-1 values for FunctionWeight.

Another possibility is that we are not being shown all rows in the FunctionWeights table and that there is duplication of the FunctionName values where the duplicated rows have a value of 1 for FuncWeight. In that case the last record read by the select @constVal = FunctionWeight from dbo.FunctionWeights query would be the value stored in @constVal, though that would not always be the "duplicated" row since there is no ORDER BY on the query.


SQL Server: Exporting data objects While exporting Database.

there is no script that does

Insert * From DB1 Into DB2

The best way to copy objects is through SQL Server Export Wizard and select all the objects you wan to move.

Also another way is through script each object and run the script in new database.


You can't restrict by IP address. All you can really do is create a new user and only grant them SELECT on that one table.

create user newuser identified by 'p4ssword';
grant connect, resource to newuser;
grant select on yourtable to newuser;

It is actually possible to check the IP address a user is logging in from using a login trigger, and deny the login if the IP address is "wrong", but that is a different question.


frm-92100:your connection to the server was interrupted

As you said you did try with both option

 1. Set NLS_LANG to a valid value like AMERICAN_AMERICA.AR8MSWIN1256 in
    the default.env file or any custom environment file which is being
    used.

 2. You can go to regedit and also set the NLS_LANG value to the one
        mentioned above and check if this resolves the issue.

As per ABBASGOL'S BLOG FRM-92100 : your connection to the server was interrupted If the Above Solution Doesn’t Work For you Try The Below One :

1) FRM-92100 occurs when invoking a new forms session from existing form session, or another URL is invoked from the parent browser window of the forms applet?

To work-around use the applet parameter separateFrame=”True”. This parameter is set in the static html file or formsweb.cfg
2) If using Oracle Jinitiator, try clearing the Jinitiator JAR or jcache:

For versions 1.3.x.x.x : Control Panel – Jinitiator Control Panel – Cache Tab Page – Clear JAR Cache button

3) If using MS Internet Explorer check that permissions / security options are set correctly

Launch Microsoft Internet Explorer.
Select the Tools -> Internet Options from the top level menu.
Click the SECURITY Tab:
Choose Local Intranet Zone from the list.
Click Custom Level.
Select Java Permissions – Custom from the list.
Click Java Custom Settings.
Select the Edit Permissions tab.
Choose Enable for Run Unsigned Content.
Choose Enable for Run Signed Content.
Select the ADVANCED tab.
Scroll down to Java VM.
Check the Java console enabled option.
Select the CONNECTIONS tab.
Click the LAN Settings button.
If IE is using a proxy server : Check the Bypass proxy server for local addresses check box. Click the Advanced button, and enter the local domain in the Exceptions box. Use the form *.domain.com, where domain.com maps to your local domain.
Click OK to exit the dialog.
Click OK to save the changes and exit IE.
Restart the computer.


How to track blocking that happen for less than a second - SQL Server

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable.

With a filter on increment >= 200

CREATE EVENT SESSION [locks_lock_waits] ON SERVER
ADD EVENT sqlserver.locks_lock_waits(
        ACTION(sqlserver.sql_text)
            WHERE  ( [sqlserver].[is_system] = 0
                     AND [increment] >= 200
                     AND [counter] <= 1000 )
    )
ADD TARGET package0.ring_buffer;

GO

ALTER EVENT SESSION [locks_lock_waits]
ON SERVER  STATE = start;

The above gathers the statements waiting on locks for the threshold amount of time but doesn't give the specific lock resource.

I have never used this event and have no insight into how much overhead this session would cause on your production server.

I found this video on the topic. That does strongly recommend filtering on counter to reduce the number of events collected and I have done so above.

It also mentions an old legacy undocumented command

dbcc lock(StallReportThreshold, 200) -- 200 is threshold in ms

Which (if trace flag 3605 is enabled) dumps out limited information such as the below to the SQL Server error log.

  Process 53 waited 6844 ms for S lock on RID: 2:1:120:2 result: OKWAIT

I just mention this in passing as extended events would be clearly preferable anyway as it is documented and much more powerful.


Including count of a result in the main query?

You can try create a stored procedure and call it from the application.

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Or you can use subquery:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Edit.

Even better you can use OFFSET FETCH clause for paging puprose. You will get much cleaner code and higher performance (no window function here). For example somthing like this:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

Or with subquery:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

There is no significant diffrence between a subquery and a variable approach. It's more about the notation. SQL Server does almost the same work (additional "nocost" Nested Loop). In this particular sitiation I prefer to avoid window function (possible Table Spool - additional impact on tempdb).


From the wording of your question, I guess something like:

SELECT substr(agg_stmt,1,LENGTH(agg_stmt)-10) FROM (
    SELECT STRING_AGG(stmt, '') as agg_stmt FROM (
        SELECT 'SELECT ' || '''' || column_name
            || ''' as col, COUNT(NULLIF(' || column_name || ','''')) as cnt FROM '
            || table_name || '' || ' union all ' as stmt
        FROM information_schema.columns
        WHERE table_name = 't'
    ) AS TT
) AS TTT;

should do. For a table T (x, y) it will generate a stmt like:

SELECT 'x' as col, COUNT(NULLIF(x,'')) as cnt FROM t union all SELECT 'y' as col, COUNT(NULLIF(y,'')) as cnt FROM t

You can add an outer stmt like:

SELECT col FROM (
    SELECT 'x' as col, COUNT(NULLIF(x,'')) as cnt FROM t
    union all
    SELECT 'y' as col, COUNT(NULLIF(y,'')) as cnt FROM t
) AS Q
WHERE cnt = 0

to get all columns that have only null or ''


In general you should use sys.database_principals instead of sysusers, which was deprecated in 2005 (just look at the big warning in the docs). But in this case, you can do this without joining to anything - SUSER_SNAME() against the sid retrieved the Windows username for me both writing a proper DELETE query and also when using the GUI as a crutch:
DECLARE @TableName sysname = N'dbo.ObjectInstances';

SELECT UserName = SUSER_SNAME(l.[Transaction SID]), l.[Begin Time]
FROM sys.fn_dblog(NULL, NULL) AS l
INNER JOIN
(
    SELECT [Transaction ID]
    FROM sys.fn_dblog(NULL, NULL)
    WHERE AllocUnitName LIKE @TableName + N'%'
    AND Operation = N'LOP_DELETE_ROWS'
) AS deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
AND l.Operation = N'LOP_BEGIN_XACT';

You may also want to consider joining a 3rd time to be sure the sequence was finalized with a LOP_COMMIT_XACT (in other words, maybe you don't need to audit transactions that are still active or were rolled back, and this would also give you a better idea of when the change was actually committed vs. when they started thinking about making a change).
And just as an aside:

Ladies and gentlemen, start your semi-colons!


Get notified when user with administrative privileges logs in

If you are looking to be NOTIFIED INSTANTLY AND CONSISTENTLY when a person with a specific permission logs in, Triggers are the best route.

If you want an audit of successful logins or failed logins, SQL Audit is your best route.

You can see a similar question here by Sergey on Stack Exchange.

There are other methods to achieve this goal, but it strictly depends on the requirements.

You can query for active sessions and join to system tables to find users active, you can audit who successfully logged in or not with the audit events and then correlate to the system tables too. (This becomes a retroactive or historical view, not a real time.) You can also use tracing / extended events to obtain this information and schedule a job to run off of. Here's an example of the Extended Events Logon Capture by Steve Jones on Voice of the DBA.

If you needed it instantly when the person logs in and consistently, triggers are your best resolution.

MSDN on Logon Triggers.

Here is an example of creating a LOGON trigger for all Sysadmin.

CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
DECLARE @UserInfo VARCHAR(255);
SET @UserInfo = (SELECT SYSTEM_USER);
IF @UserInfo = (   SELECT TOP 1 p.name AS [loginname]
                   FROM   sys.server_principals p
                   JOIN   sys.syslogins s
                       ON p.sid = s.sid
                   WHERE  p.type_desc IN ( 'SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP' )
                          -- Logins that are not process logins
                          AND p.name NOT LIKE '##%'
                          -- Logins that are sysadmins
                          AND s.sysadmin = 1
                          AND p.name = @UserInfo)
BEGIN
    PRINT 'Hello';
END;
GO

You may want to create a way to avoid hitting the trigger, please see this answer if you want to incorporate it.

Note: Be careful of logon triggers, you don't want to lock yourself out.


I just noticed that you are at the Windows Command Line. You are trying to launch MySQL.

You cannot start MySQL because you are not an Administrator.

Please Open CMD.EXE as an Administrator. Then, you can run

net start MySQL56_1
mysqld --console

According to the latest update, mysqld is running just fine. That's because it says

mysqld: ready for connecitons

A prompt will not return to you.

You can now open another Command Line session and login using the mysql client.

UPDATE 2014-07-28 17:51 EDT

You need to run the following:

cd C:\Program Files\MySQL\MySQL Server 5.6\bin
mysqld --console

UPDATE 2014-07-28 18:28 EDT

When you ran mysqld --console you were not using the my.ini

Call it like this

mysqld -defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

If you want the prompt to come back, run this

start mysqld -defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

UPDATE 2014-07-28 18:48 EDT

My suggestion from the Chat Session

cd "C:\ProgramData\MySQL\MySQL Server 5.6\data"
move ib_logfile* ..
net start MySQL 56_1

Give the service time to regenerate the Log Files, and you should be good to go.


MongoDB geospatial query with sort - performance issues

A query in MongoDB can only use one index at a time, so it's a case of one or the other - it can't use the 2d index first, then do a sort on the _id index.  In order to use indexes for both the selection and the sort, you would need a compound index like this:

db.markers.ensureIndex( { latlng : "2d" , _id : 1 } );

Try that, or similar and see how it impacts the results bearing in mind that once you define it, you can remove the original 2d index to save space and that this new index will have to be loaded into memory to be efficient.

Update: as mentioned in the summary, the above did not improve things, and the resulting query results in a scanAndOrder result.  This also happens with range based queries, as explained in this excellent blog post:

http://blog.mongolab.com/2012/06/cardinal-ins/

As explained in that post, the usual resolution for range based query performance is to switch the order of the indexes.  However this is currently not possible with geo indexes.  There is a Jira issue already open for this here for voting and tracking purposes:

https://jira.mongodb.org/browse/SERVER-4247


MYSQL wont use index in "IN(SELECT ..)" sub clause

That's right, so just use a join - MUCH faster:

SELECT charge FROM ec_provider_account_option a,
                   ec_provider b
  WHERE a.accountTypeId = b.accountType
  AND a.accountPermissionId = '4'
  AND b.id = 3;


Joe Sack's answer on ServerFault is as good as it gets:

https://serverfault.com/questions/251551/sql-server-2008-mirroring-threshold-alerts-at-database-level/251989#251989

For some background, Joe runs the Microsoft Certified Master of SQL Server program.  He knows his stuff.  (I can vouch for him personally.)


MySQL : perform a big data migration between tables

Since you are using InnoDB I would like to suggest the following:
SUGGESTION #1
If you do INSERT, UPDATEs, and DELETEs, bulk load the table like this:
CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT * FROM table_b;
ALTER TABLE table_a RENAME table_old;
ALTER TABLE table_new RENAME table_a;
DROP TABLE table_old;

If you do nothing but INSERTs and SELECTs, load new entries into the table. Assuming the primary key of table_a andtable_b is id, perform the load like this:
CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT B.* FROM table_b B
LEFT JOIN table_a A USING (id) WHERE A.id IS NULL;
INSERT INTO table_a SELECT * FROM table_new;
DROP TABLE table_new;

SUGGESTION #2 : Tune InnoDB for Multiple CPUs
Make sure you are using MySQL 5.5. If you have MySQL 5.1.38 or above, you must install the InnoDB Plugin. If you have MySQl 5.1.37 or prior, just upgrade to MySQL.
Once you have do that (or if you already have MySQL 5.5), you must tune InnoDB for Multiple CPUs. Rather than reinvent the wheel, here are my past posts on how and why to do so:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!
I could suggest other things such as buffers, log files, and so forth. I only addressed just these two concerns.


Is there any way to set stored procedure options like ANSI_NULLS to ON or OFF outside of creation?

Haven't tested this on other versions of Visual Studio, but with version 12.0.3 (Update4) there is an option to override the ANSI Nulls config at the database-level specification for a single procedure in an SSDT project.

Just right-click the procedure in solution explorer and choose properties...


Hope This helps, DUPLICATE KEY UPDATE

create table machine(
  machine_id int not null primary key,
  machine_name varchar(50),
  reg_id int
);

insert into machine (machine_id, machine_name, reg_id)
values(1, 'my_machine', 1);

INSERT INTO machine (reg_id, machine_id, machine_name) VALUES (1, 1, 'test_machine')
  ON DUPLICATE KEY UPDATE machine_name=VALUES(machine_name);

Work on SQL Fiddle


Why is it specifically Immediate If (IIF)?

Within Access, IIf ( expr , truepart , falsepart ) is a function with three parameters.

Also available in Access is VBA (Visual Basic for Applications) and it has the usual If ... Then ... ElseIf ... End If conditional execution blocks, but in that case If and the other terms are all keywords of the Basic language.

The functional form IIF() can be used within SQL statements or as part of single executable statements within certain object properties, e.g. Control Source properties of form controls.  In both of those cases, a complete multi-line If construct (i.e. a full VBA code block) is NOT allowed.  Without the functional form, there would be no way to execute conditional expressions in such contexts.

One reason for the different names is to avoid the conflict of having a function with the same name as a keyword.  It is interesting to note that Excel also supports VBA, but it still has a spreadsheet function named IF().  Although some of the reasons are perhaps just convention (style) decided by the original application designers, there are likely other more subtle reasons.  For instance, Access is more likely to have mixed expressions in different contexts (as mentioned earlier), so it is important to avoid conflicts with VBA keywords.  On the contrary, it is not possible to execute Excel "spreadsheet commands" in any other context outside spreadsheet cells.  Although it is possible to execute properly-defined VBA functions from an Excel cell, one cannot execute VBA code directly from within the spreadsheet commands.  In other words, the execution contexts are completely separate in Excel, so there is never a conflict between function names and language keywords.

In the case of Access, the concept of "immediate" or "delayed" versions of If are not relevant.  Some languages (Java, C++, C#, etc.) support short-circuited logical operators (like && -- logical and) which only execute a 2nd statement when logically necessary.  But neither the IIF() function nor the VBA If statement support that behavior.  All expressions and/or function calls that are "passed" to either form will be fully evaluated/executed.  In some contexts, like within an SQL statement, such expressions might evaluate to NULL and so the overall expression might also be NULL and effectively ignored, but ignoring the result of the expression is not the same as skipping the execution of individual statements.


What is the SQL Azure equivalent of the server "public" role?

After much experimentation, I stumbled upon an answer on the Secure your Azure SQL Database.

Please correct me if I am wrong here!

It is strange that the documentation on securing SQL Azure does not mention the following in any detail.  The clues were here (emphasis mine):

  SQL Authentication, which uses username and password for logins and
  users that are valid only in the context of a specific database within
  a logical server.

  -snip-

  By default, the user can connect to the database, but has no
  permissions to read or write data.

In SQL Azure, it is possible to have database-contained users with a password (which is different from the on-premise approach of creating a user against an existing login account).  This makes sense, because the all-in-one contained approach allows for a seamless failover.

The solution is to use SSMS and not the Azure portal:

-- After connecting to SQL Azure within SSMS (as dbo)
CREATE USER [UserNameForApplication] WITH PASSWORD=N'543rg.....ehgdGDF';
GO
ALTER ROLE [MyExistingDatabaseRole] ADD MEMBER UserNameForApplication;
GO

My application can now access the SQL Azure database with zero additional rights that would otherwise be granted via the Azure portal.


How to return rows from multiple tables selected from information_schema

That's 0-fun. Just generate one statement using UNION ALL and now you don't need cursors.

CREATE TABLE foo_1(price) AS VALUES (1);
CREATE TABLE foo_2(price) AS VALUES (1);
CREATE TABLE foo_3(price) AS VALUES (1);

SELECT STRING_AGG(
  FORMAT(
    'SELECT price FROM %I.%I.%I',
    table_catalog,
    table_schema,
    table_name
  ), ' UNION ALL ') ||';'
FROM information_schema.tables
WHERE table_name like '%foo_%';

You can put it in a function like this (first we get the result as a query string, and then we execute it)

CREATE FUNCTION get_prices()
RETURNS setof int AS $func$
  DECLARE
    sql text := string_agg(
      FORMAT(
        'SELECT price::int FROM %I.%I.%I',
        table_catalog,
        table_schema,
        table_name
      ), ' UNION ALL ') ||';'
    FROM information_schema.tables
    WHERE table_name like '%foo_%';
  BEGIN
    RETURN QUERY EXECUTE sql;
  END;
$func$ LANGUAGE plpgsql;

As a special note, if the list of table_name ~~ '%price%'  AND table_schema ~~ 'stg0' is immutable (that means you're not still adding to it) you're better of doing this one time and saving it as a VIEW


TSQL SELECT statement to list a column calculated based on the value of a field

If the project id is distinct this is easy to achieve with two window functions. Maybe not the best performance but just flows out of my fingers. Feel free to improve:

Setting up

CREATE TABLE #Table1
(
 ProjectID int,
 EmployeeID int,
 StartingDate DateTime
);

INSERT INTO #Table1 (ProjectID, EmployeeID, StartingDate)
VALUES (1, 1001, '1/1/2005'),
       (2, 1003, '1/1/2019'),
       (3, 1007, '1/1/2012'),
       (4, 1001, '1/1/2017'),
       (5, 1002, '1/1/2018'),
       (6, 1003, '1/1/2015'),
       (7, 1001, '1/1/2016'),
       (8, 1002, '1/1/2018'),
       (9, 1004, '1/1/2018'),
       (10, 10001, '1/1/2018');

The query;

WITH cte_Count
AS
(
  SELECT t.ProjectID,
         COUNT(*) OVER (PARTITION BY t.EmployeeID ORDER BY t.StartingDate) AS project_count
  FROM #Table1 t
  WHERE t.StartingDate > '1/1/2016'
)
SELECT t.ProjectID,
       t.EmployeeID,
       t.StartingDate,
       MAX(project_count) OVER (PARTITION BY t.EmployeeID) AS project_count_since_2016
FROM #Table1 t
LEFT JOIN cte_Count c ON c.ProjectID = t.ProjectID

The first part just filters the projects that are not in the range you want. The second joins this information with the source table.


What would a "properly designed" schema for roles and permissions look like?

Let's start with GROUPS. In general, I use plural for table names unless there is a collective noun that works. You may have a different preference.

CREATE TABLE GROUPS
( GROUP_ID ... NOT NULL PRIMARY KEY
, ... ) ;

Since a user can be a member of several groups (and I assume that a group can contain more than 1 user), we need a n-n relationship. This is usually implemented via a junction table:

CREATE TABLE USERS
( USER_ID ... NOT NULL PRIMARY KEY
, ... ) ;

CREATE TABLE USER_GROUPS
( USER_ID ... NOT NULL
      REFERENCES USERS (USER_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (USER_ID, GROUP_ID)
);

Next we have PERMISSIONS:

CREATE TABLE PERMISSIONS
( PERMISSION_ID ... NOT NULL PRIMARY KEY
, ...
);

Since a permission can exists for several roles, we once again use a n-n relatyionship table:

CREATE TABLE ROLES
( ROLE_ID ... NOT NULL PRIMARY KEY
, ...);

CREATE TABLE ROLE_PERMISSIONS
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, PERMISSION_ID ... NOT NULL
      REFERENCES PERMISSIONS (PERMISSION_ID)
,   PRIMARY KEY (ROLE_ID, PERMISSION_ID)
);

Finally we can describe the relationship between groups and roles. If I got it right that is once again an n-n relationship:

CREATE TABLE GROUP_ROLES
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (ROLE_ID, GROUP_ID)
);

This is of course just a sketch. I invented attribute names blindly, if there is an attribute name that exists in reality, use that..


pt-table-checksum toolkit for few databases from file

I would use a 'for' loop for this, which I have found easier to use for things like this. Often, either line break, spaces, or combinations of those can cause issues with your method.
Also, remove the --quiet from your command line to see the errors that pt-table-checksum may be reporting.

#!/bin/bash

for db in `cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)"`
do
    pt-table-checksum [options] --databases $db --quiet
done

(Hope this reads ok, have answered on my phone!)


This can happen in several ways, for example as described in Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions:

Invalid or out-of-range data may have been stored in the SQL Server database in earlier versions for the following reasons:

Invalid data was present in the source while using bulk insert methods, such as the bcp utility.
Invalid data was passed through RPC event calls made to SQL Server.
Other potential causes of physical data corruption left the column value in an invalid state.

That article contains a lot of useful information on the topic. For the basics, see the documentation for DBCC CHECKDB and the DATA_PURITY option in particular.


For "dual-master", it is best to write to only one of them.

Failover involves (mostly) pointing the clients to the newly-writable master.

MHA automates the process; read about it.

In my opinion, dual-master only handles a limited number of failure scenarios, namely the crash of a server.  The next step is to separate the masters so that a power failure, earthquake, tornado, etc cannot take out both at the same time.  But then you get into the "split-brain" problem, wherein you can't tell which Master is the surviving one because you can't check both of them.  (And both might be surviving!)

Next comes Galera, which needs 3 nodes, and is self-healing.  It comes standard with MariaDB.

If you want the MySQL path, then you need to get beyond 5.6 in order to get a reasonably robust "Group Replication" / "Innodb Cluster".  (8.0 is desired for that.)


I know it's late but there is a lot that could be going on.

-Make sure your statistics look good:

select owner,table_name,sample_size,num_rows,last_analyzed
from dba_tab_statistics.

num_rows and last_analyzed should match if you use default settings.

select owner,table_name,index_name,sample_size,num_rows,last_analyzed
from dba_ind_statistics.

Again sample_size and num_rows should match.

Verify if you use CURSOR_SHARING=EXACT or FORCE (if SIMILAR, change it to FORCE),(will require sysdba privilege):

SQL>show parameter cursor;
SQL>alter system set cursor_sharing=FORCE;
Note that ideally you want to match the parameters you have on your production system. You may want to change these to test them as a cause of your problem however.
Verify that your SGA is sized properly:

select * from v$sga_target_advice
You would get something like this:

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

 10240             .25    131401926              1.2592          1.4876E+10
 15360            .375    117178544              1.1229          1.1815E+10
 20480              .5    112472201              1.0778          1.0648E+10
 25600            .625    109873799              1.0529          1.0250E+10
 30720             .75    107077125              1.0261          9638685159
 35840            .875    105929237              1.0151          9404059270
 40960               1    104353499                   1          9058914623
 46080           1.125    103706507               .9938          8926654470
 51200            1.25    102788197                .985          8724640673
 56320           1.375    102391653               .9812          8646734008
 61440             1.5    101807274               .9756          8515379746
 66560           1.625    101275077               .9705          8394896181
 71680            1.75     97987941                .939          7696453864
 76800           1.875     97967070               .9388          7696453864
 81920               2     97967070               .9388          7696453864

Where SGA_SIZE_FACTOR is 1, is my current SGA_SIZE.
You can look at column ESTD_DB_TIME_FACTOR and ESTD_PHYSICAL_READS to see how much benefit I get if I increase it to the sizes mentioned in SGA_SIZE. In my case, not much benefit which means I'm good.

Compare your query when executed with literal values vs bind variables.
You may want to see if you have a lot of hard parses for a given query. Check v$sql and v$sql_plan (have a look at column child_number for a given sql_id.
Do you have very skewed data on some of the columns in your where clause, joins and group by? If so you may be experiencing issues related to skewed data if bind variables are used.


Applying GROUP BY when using multiple CASE WHEN

I think you just need to aggregate your value columns

create table table1(e_id int, w_id int, fee int)

insert into table1
values(1,1,100),(1,2,150),(1,3,50),(1,4,150),
(2,1,100),(2,1,150),(2,2,50),(2,4,150)

select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,SUM(CASE WHEN w_id = 1 THEN fee END) as value1
,SUM(CASE WHEN w_id = 2 THEN fee END) as value2
,SUM(CASE WHEN w_id not in ('1','2') THEN fee END) as value3
from table1
group by e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END


You are right that table_a1_column is functionally dependent on the other columns, but PostgreSQL isn't smart enough to deduce that.

If view_a were a regular table, PostgreSQL would know that the columns in the GROUP BY clause contain the primary key, but since it is hidden behind the query rewrite rule of a view, it cannot infer that.

For PostgreSQL there are no functional dependencies between the columns of a view. It might be technically possible to improve that, but it is doubtful whether that would be worth the extra effort during query planning, particularly since the query can be fixed by simply adding all columns to the GROUP BY clause.


SELECT
    a.WorkCenter
  , a.ActionDate
  , a.Hours
  , r.Rate
  , r.Rate * a.Hours  AS Cost
FROM
    Activities AS a
  JOIN
    Rates AS r
      ON  r.StartDate =
          ( SELECT MAX(StartDate)
             FROM Rates
             WHERE StartDate <= a.ActionDate
          ) ;

or a GROUP BY solution:

SELECT
    a.WorkCenter
  , a.ActionDate
  , a.Hours
  , r.Rate
  , r.Rate * a.Hours  AS Cost
FROM
    Activities AS a
  JOIN
    ( SELECT
          a.ActionDate
        , MAX(r.StartDate) AS StartDate
      FROM
          Activities AS a
        JOIN
          Rates AS r
            ON r.StartDate <= a.ActionDate
      GROUP BY a.ActionDate
    ) AS grp
      ON  grp.ActionDate = a.ActionDate
 JOIN
    Rates AS r
      ON  r.StartDate = grp.StartDate ;


Which fillfactor is used by ALTER INDEX REBUILD?

When a new index is built is uses the default fill factor by default, unless a different fillfactor is specified.

When an index is rebuild it will use the same fill factor that was specified when the index was build or last rebuilt unless a different fillfactor is specified.


Is the following database design good in the following table for the situation explained below

I am wondering if I should ask the person who created this to change
  the database design? As far as I know, having duplicate values in the
  same column is a violation of some normal form?

If the table is on the "many" side of a relationship, it can be the case that duplicates are stored in a "foreign key" column (eg see the DEPARTMENT_ID column of Oracle's sample HR.EMPLOYEES table below).  Thus, without knowing all the other details of the design - and taking them into account -, it may not be advantageous to ask for any changes to be made.

-- using https://livesql.oracle.com ...
select employee_id, last_name, department_id from hr.employees ;

EMPLOYEE_ID  LAST_NAME     DEPARTMENT_ID
100          King          90
101          Kochhar       90
102          De Haan       90
103          Hunold        60
104          Ernst         60
105          Austin        60
106          Pataballa     60
107          Lorentz       60
108          Greenberg    100
109          Faviet       100
110          Chen         100
...

  Also, while programming, I am not sure how would I know that I need to
  stop at DATAID=2 to know that my first heading will be Capital,Summary
  etc and then again, I am not sure how would I know to stop at 6 to
  know that my second heading is First Reports.

In your test table, the headings are already mapped to DATADESC.  The only item changed in the example below is: the column called DESC is now: DESC_ (as the word DESC is a reserved word - this, by the way, is something that probably should be changed: the name of column DESC).

-- table and data
create table T ( desc_ , datadesc, dataid )
as
select 'Capital,Summary etc'
, 'Counts of capital and summary', 1 from dual union
select 'Capital,Summary etc'
, 'Testing of capital and summary ', 2 from dual union
select 'First Reports'
, 'Completed Successfully', 3 from dual union
select 'First Reports'
, 'Still Pending', 4 from dual union
select 'First Reports'
, 'Failed twice ', 5 from dual union
select 'First Reports'
, 'Re attempting ...', 6 from dual ;

-- data
select * from T ;

DESC_                   DATADESC                        DATAID
Capital,Summary etc     Counts of capital and summary   1
Capital,Summary etc     Testing of capital and summary  2
First Reports           Completed Successfully          3
First Reports           Failed twice                    5
First Reports           Re attempting ...               6
First Reports           Still Pending                   4

You can get this resultset in JSON format, too.  Eg if you are using Oracle version 18.0 (eg @ https://livesql.oracle.com):

Query

select
  json_object(
    'desc_' is DESC_
  , 'datadesc' is DATADESC
  , 'dataid' is DATAID
  )
from T
order by dataid ;

Result

{
"desc_" : "Capital,Summary etc",
"datadesc" : "Counts of capital and summary",
"dataid" : 1
}
{
"desc_" : "Capital,Summary etc",
"datadesc" : "Testing of capital and summary ",
"dataid" : 2
}
{
"desc_" : "First Reports",
"datadesc" : "Completed Successfully",
"dataid" : 3
}
{
"desc_" : "First Reports",
"datadesc" : "Still Pending",
"dataid" : 4
}
{
"desc_" : "First Reports",
"datadesc" : "Failed twice ",
"dataid" : 5
}
{
"desc_" : "First Reports",
"datadesc" : "Re attempting ...",
"dataid" : 6
}

Another example - using sqlcl and Oracle version 12c release 12.1.0.2.0:

set sqlformat json

select * from T order by dataid ;

-- line breaks added for clarity
{"results":[{"columns":[{"name":"DESC_","type":"NUMBER"},{"name":"DATADESC","type":"NUMBER"},{"name":"DATAID","type":"NUMBER"}],"items":

[
 {"desc_":"Capital,Summary etc","datadesc":"Counts of capital and summary","dataid":1}
,{"desc_":"Capital,Summary etc","datadesc":"Testing of capital and summary ","dataid":2}
,{"desc_":"First Reports","datadesc":"Completed Successfully","dataid":3}
,{"desc_":"First Reports","datadesc":"Still Pending","dataid":4}
,{"desc_":"First Reports","datadesc":"Failed twice ","dataid":5}
,{"desc_":"First Reports","datadesc":"Re attempting ...","dataid":6}
]}]}
-- 6 rows selected.