Unexpected scans during delete operation using WHERE IN I've got a query like the following:

DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
    SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)

tblFEStatsBrowsers has got 553 rows.
tblFEStatsPaperHits has got 47.974.301 rows.

tblFEStatsBrowsers:

CREATE TABLE [dbo].[tblFEStatsBrowsers](
    [BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
    [Browser] [varchar](50) NOT NULL,
    [Name] [varchar](40) NOT NULL,
    [Version] [varchar](10) NOT NULL,
    CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)

tblFEStatsPaperHits:

CREATE TABLE [dbo].[tblFEStatsPaperHits](
    [PaperID] [int] NOT NULL,
    [Created] [smalldatetime] NOT NULL,
    [IP] [binary](4) NULL,
    [PlatformID] [tinyint] NULL,
    [BrowserID] [smallint] NULL,
    [ReferrerID] [int] NULL,
    [UserLanguage] [char](2) NULL
)

There's a clustered index on tblFEStatsPaperHits that does not include BrowserID. Performing the inner query will thus require a full table scan of tblFEStatsPaperHits - which is totally OK.

Currently, a full scan is executed for each row in tblFEStatsBrowsers, meaning I've got 553 full table scans of tblFEStatsPaperHits.

Rewriting to just a WHERE EXISTS doesn't change the plan:

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)

However, as suggested by Adam Machanic, adding a HASH JOIN option does result in the optimal execution plan (just a single scan of tblFEStatsPaperHits):

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)

Now this isn't as much a question of how to fix this - I can either use the OPTION (HASH JOIN) or create a temp table manually. I'm more wondering why the query optimizer would ever use the plan it currently does.

Since the QO doesn't have any stats on the BrowserID column, I'm guessing it's assuming the worst - 50 million distinct values, thus requiring quite a large in-memory/tempdb worktable. As such, the safest way is to perform scans for each row in tblFEStatsBrowsers. There is no foreign key relationship between the BrowserID columns in the two tables, so the QO can't deduct any info from tblFEStatsBrowsers.

Is this, as simple as it sounds, the reason?

Update 1
To give a couple of stats:
OPTION (HASH JOIN):
208.711 logical reads (12 scans)

OPTION (LOOP JOIN, HASH GROUP):
11.008.698 logical reads (~scan per BrowserID (339))

No options:
11.008.775 logical reads (~scan per BrowserID (339))

Update 2
Excellent answers, all of you - thanks! Tough to pick just one. Though Martin was first and Remus provides an excellent solution, I have to give it to the Kiwi for going mental on the details :)

"I'm more wondering why the query optimizer would ever use the plan it currently does."

To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many).

The inner side of the join is essentially running a query of the following form for each correlated value of BrowserID:

DECLARE @BrowserID smallint;

SELECT
    tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
    tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);

Note that the estimated number of rows is 185,220 (not 289,013) since the equality comparison implicitly excludes NULL (unless ANSI_NULLS is OFF).  The estimated cost of the above plan is 206.8 units.

Now let's add a TOP (1) clause:

DECLARE @BrowserID smallint;

SELECT TOP (1)
    tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
    tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);

The estimated cost is now 0.00452 units.  The addition of the Top physical operator sets a row goal of 1 row at the Top operator.  The question then becomes how to derive a 'row goal' for the Clustered Index Scan; that is, how many rows should the scan expect to process before one row matches the BrowserID predicate?

The statistical information available shows 166 distinct BrowserID values (1/[All Density] = 1/0.006024096 = 166).  Costing assumes that the distinct values are distributed uniformly over the physical rows, so the row goal on the Clustered Index Scan is set to 166.302 (accounting for the change in table cardinality since the sampled statistics were gathered).

The estimated cost of scanning the expected 166 rows is not very large (even executed 339 times, once for each change of BrowserID) - the Clustered Index Scan shows an estimated cost of 1.3219 units, showing the scaling effect of the row goal.  The unscaled operator costs for I/O and CPU are shown as 153.931, and 52.8698 respectively:

In practice, it is very unlikely that the first 166 rows scanned from the index (in whatever order they happen to be returned) will contain one each of the possible BrowserID values.  Nevertheless, the DELETE plan is costed at 1.40921 units total, and is selected by the optimizer for that reason.  Bart Duncan shows another example of this type in a recent post titled Row Goals Gone Rogue.

It is also interesting to note that the Top operator in the execution plan is not associated with the Anti Semi Join (in particular the 'short-circuiting' Martin mentions).  We can start to see where the Top comes from by first disabling an exploration rule called GbAggToConstScanOrTop:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

That plan has an estimated cost of 364.912, and shows that the Top replaced a Group By Aggregate (grouping by the correlated column BrowserID).  The aggregate is not due to the redundant DISTINCT in the query text: it is an optimization that can be introduced by two exploration rules, LASJNtoLASJNonDist and LASJOnLclDist.  Disabling those two as well produces this plan:

DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

That plan has an estimated cost of 40729.3 units.

Without the transformation from Group By to Top, the optimizer 'naturally' chooses a hash join plan with BrowserID aggregation before the anti semi join:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

And without the MAXDOP 1 restriction, a parallel plan:

Another way to 'fix' the original query would be to create the missing index on BrowserID that the execution plan reports.  Nested loops work best with when the inner side is indexed.  Estimating cardinality for semi joins is challenging at the best of times.  Not having proper indexing (the large table doesn't even have a unique key!) will not help at all.

I wrote more about this in Row Goals, Part 4: The Anti Join Anti Pattern.


What's the meaning of "resources external to the SQL Server computer are needed" in Microsoft's SQL documentation? In Microsoft's SQL Server documentation on Windows service account configuration, the decision to use either a VA (virtual account) or a MSA (managed service account) hinges on whether

resources external to the SQL Server computer are needed

What exactly does this phrase mean here? I'm seeking an explanation that makes sense to an 'accidental DBA' with minimal experience of SQL Server, or Windows Server, configuration. What counts as a 'resource' here, and what kinds of 'need' are relevant?
Context: I have a fresh SQL Server 2019 VM on Azure, which was configured with VAs out-of-the-box, and I'm trying to decide whether we need to switch to using MSAs. I have found multiple other questions concerning this same documentation page and/or the same basic decision between VAs or MSAs (or regular AD Accounts) - but none really explain this specific phrase in a way that helps me apply it to my particular scenario. Which is essentially a data warehouse use-case: data will be coming into this SQL instance from external sources, but that'll be managed by a third-party DW automation application running SSIS scripts, not directly by the SQL engine. (This application has its own AD service accounts.)
Whilst I've explained my specific scenario here to try to clarify the question, I'm keen for a generic answer so anyone with any SQL Server use case can evaluate this "resources external to the SQL Server" phrase for their needs. Specific answers for my use case are also welcome.

This documentation page gives an explanation for those curious enough to scroll down:

A Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service.

Virtual accounts [...] are managed local accounts. [...] Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

Emphasis above is mine.
In other words, virtual accounts are local to one server and cannot control resources on other servers, while managed service account are domain-scoped and can, therefore, be given access to resources on multiple servers.
For a standalone SQL Server instance there is no difference in functionality. In a cluster, however, the account should have authority to access and/or modify various resources on more than one server, so a domain account would be appropriate.


Optimising an Update statemement with a very large Where clause I have a table (TableOne) with a field called [ReviewNote], this table has more than 1.5 million record, I have to assign keywords to each record based on the content of the [ReviewNote], using another table [KeywordsTable] which contains of more than 17000 record of [SearchTerm] and relevant [Keyword].

So the objective is searching for the [SearchTerm] in [ReviewNote] and update the record and assign the appropriate [Keyword].

The problem is, it takes toooooo long to do it in conventional ways, so finally I came up with following script ( I am not sticking to this, just for you to know what I am talking about), it has been generated using the [SearchTerm] from The [KeywordsTable] (like clause).
I used where Clause, joining, temp tables.... before, all too damn slow for this... and field are indexed as as well.

So my question is, is there a way to do what I want to do in a much faster way? optimization? anything ?

Thanks, Zac

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = (
        CASE
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')
                 THEN 'X Ray'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')
                 THEN 'BMD'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Cons Dr Reitsma -%')
                 THEN 'Consultation'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- consult -%')
                 THEN 'Consultation'

            -- 17000 more WHEN clause
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zostavix -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster @ Safeway Pharmacy -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster Immunization -%')
                 THEN 'Immunization'
        END
    )
GO

Instead of one massive update statement, use multiple update statements one for each where clause.

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'X Ray'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'BMD'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')


SQL Server 2008 R2 Standard Edition not using more than 32GB for BufferPool I had SQL Server 2008 R2 Standard running on Windows Server 2008 R2 Standard  with 32 GB of installed Memory.

Since SQL Server 2008 R2 Standard can use 64 GB memory, Windows was upgraded in-place to Enterprise Edition and memory increased from 32 GB to 64 GB.

SQL Server 2008 R2 Standard max memory was then increased from 28 GB to 52 GB.

SELECT name, value [mem_MB]
FROM sys.configurations
WHERE name IN('max server memory (MB)','min server memory (MB)')

Result:

name                    mem_MB
min server memory (MB)   49152
max server memory (MB)   53248

7 day after change, "Total pages" and "Target pages" show 32 GB.

select counter_name, cntr_value
from master..sysperfinfo
where [object_name]='SQLServer:Buffer Manager'
and counter_name in ('Total pages','Target pages')

Result:

counter_name    cntr_value
Total pages          4096000    <--32 GB
Target pages         4096000    <--32 GB

Why is SQL Server only using 32 GB for buffer pool?

Can someone confirm SQL Server 2008 R2 Standard can actually use more than 32 GB for buffer pool?

In case someone reads this article in the future, SQL Server 2008 R2 on Windows Server 2008 R2 Enterprise (upgraded in-place) can and does use 64 GB RAM. Windows was patched last week and rebooted.

After reboot:

select counter_name, cntr_value

from master..sysperfinfo

where [object_name]='SQLServer:Buffer Manager'

and counter_name in ('Total pages','Target pages')

Result:

counter_name      cntr_value

Total pages          7077888    <--54 GB
Target pages         7077888    <--54 GB


Can I configure MySQL replication to break when I run a "drop database" or "drop table" command on the master? Basically, what the title says.  This would enhance the failover quality of our systems, becuase we have run into issues of people thinking that they were in a test environment running drop statements on the master, which then (obviously) resulted in data loss.

Instead, I was wondering if there was a db option to just have replication refuse to run the drop table command, and have replication stop running at that point.  (assume statement based replication for now).  MySQLs FAQs seem to indicate that the answer to this is 'no'.

The answer to your question is no -- the replication SQL thread implicitly has the SUPER privilege, so any command that was entered in the binary log can be executed by the slave.

You might be able to help your scenario by setting up a slave that is deliberately lagging behind the master. That way if someone accidentally drops or deletes something they shouldn't, you have a grace period to go to the slave, stop the replication thread, and run a backup of the data that was dropped.

In MySQL 5.6, you can configure a slave with delay:

mysql> CHANGE MASTER TO MASTER_DELAY=3600; /* 1 hour delay */

In MySQL 5.5 and earlier, you can simulate this behavior with the free tool pt-slave-delay.

But I agree with the comment from @Phil -- if you have people working on the production master database who can't be careful about which instance they are using before they run DROP TABLE, then you should revoke their privileges.

Another tip: you can set the MySQL client's prompt to indicate which host they're connected to.  Edit $HOME/.my.cnf:

[mysql]
prompt = mysql[\h]>


Hundreds of sessions queued in suspended state We experience hundreds of sessions going in suspended state on rare occasions.
When it happens there is no increase in terms of resource usage - CPU or memory. Rather there is a small amount of decrease 5-10% on CPU.
There is no single error on the SQL log.
I know this will be a blind question but what might be causing this? Any suggestions to check in SQL Server like plan cache etc..?

It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.


Limit balancing results by a column I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.

I also have a function that returns me the next 20.000 (order by priority, last_started_execution limit 20000) tokens to search.

My problem is that sometimes the function returns me too much results from the same location and i need this 20.000 balanced by location.

For example: If i have 100.000 tokens in line to process, 5.000 from each location (20 locations in this case), I'll need the function get_next to return me 1.000 from each location, returning the 20.000 limit balanced.

The total line of tokens to process can be huge like millions, but I only need the next 20.000 as balanced as possible.

Also consider that I can have in line few tokens like 30.000 from location A and 2.000 from location B, in this case I will need 18.000 from location A and 2.000 from location B in a total of 20.000 being this the best possible result.

CREATE TABLE search_token (
    id SERIAL,
    search_token varchar(255),
    fk_location integer,
    priority integer,
    last_started_execution timestamp
);

Something like this, then?

SELECT  fk_location,
        search_token,
        priority,
        ROW_NUMBER() OVER (
            PARTITION BY fk_location
            ORDER BY priority, last_started_execution )
          AS Ranking
FROM    search_token
ORDER BY Ranking, fk_location
LIMIT 20000;


Installing SQL Server Express instance so common users can access I need to install a SQL Server Express instance (as an admin) which can be later on read and modified by regular users.

I run into http://msdn.microsoft.com/en-us/library/ms254504.aspx, and I'm trying to make it work like it says there, with this connection string:

Provider=sqloledb;Data Source=.\\DUBOCALC;Integrated Security=true;User Instance=true;AttachDBFilename=|DataDirectory|\\" + HelperClasses.DuboCalcSettings.dbname + ".mdf;Initial Catalog=DUBOCALC;Trusted_Connection=yes;

The problem I have is that the instance is created under C:\Program Files\Microsoft SQL Server\MSSQL10.INSTANCENAME, but all this under the Administrator's folder, so regular users can't have access there, where the .mdf files are.

In my bootstrapper file, I have the following line to install SQL Express:

<Command PackageFile="SQLEXPR32_x86_ENU.EXE"
         Arguments='/q /hideconsole /action=Install /features=SQL /instancename=DUBOCALC /enableranu=1 /sqlsvcaccount="NT Authority\Network Service" /SQLSYSADMINACCOUNTS="Builtin\Users" /skiprules=RebootRequiredCheck'
         EstimatedInstalledBytes="225000000"
         EstimatedInstallSeconds="420">`

How could I do that, please?

I think it's a very common use, to install a SQL Server Express instance as admin, then running it as a user, but I can't find any useful information which will help me get this working.

thanks a lot everybody in advance,
Manuel.

/SQLSYSADMINACCOUNTS adds users and groups to the SQL Servers sys admin group (all powerful).  That sys admin group is a sql server level security concept.

SQL Server and SQL Server Express runs as a service (and what it runs as does not dictate end user security).  Therefore, permissions on data directory (and the .mdf files) does not control how end users connect.

Your connection string should look something like:

Data Source=computerName\SQLExpress;Initial Catalog=myDataBase;Integrated Security=SSPI;

By default SQL Express is installed under a named instance called "SQLExpress" so the data source is the machine name where the service is running and then \ instance name.  Integrated security tells the client to connect with it's current credentials.  If that user is in the group that has sys admin role membership, it will be powerful.

Here's a link with more connection string samples:

http://www.connectionstrings.com/sql-server-2005

You can also use sql accounts (not integrated) and then embed those in the connection string:  https://stackoverflow.com/questions/997169/c-sharp-2008-sql-server-express-connection-string


How to find current roles available on a database I am looking to provide grant to one of my function in my database.
However, I am not sure what roles I should give access to.

I searched for it but can't find list of roles for all the users.

Any idea guys as which table I should be looking into.

I am using Oracle 11g.

Thanks in Advance :)

You can find this information in the DBA_* views in Oracle.

To find all roles in the database:

select * from dba_roles

To find what roles a particular user has:

select * from dba_role_privs where grantee=<user name>


Microsoft SQL Replication Good day, I know very little about this topic and need some advice on where to start.
I have two Microsoft SQL production databases that I would like to replicate so that I can run my reporting on them instead of the production databases.

Question 1: Does it make sense to use replication for the above scenario?
Question 2: What do I need in terms of Microsoft licenses for replication?

Thanks for reading.

Transactional replication works well in this scenario for replicating your data to your reporting databases.

It's cheaper than availability groups as you can replicate back to your single server instance to create a reporting database, rather than having an availability group where you have to pay for a second licence if you are reading the secondary data.

SQL Server 2014 Standard edition supports transactional replication for the publisher (you can use a lower edition for the subscriber as per the editions here), whereas AlwaysOn Availability Groups require Enterprise edition.

Your options for replication setup would be

Production Instance > Separate Distributor > Reporting Instance
Production Instance > Separate Distributor > Production Instance
Production Instance > Distributor on Production Instance > Production
Instance

Option 1 requires a licence for each instance, however would remove reporting load from the production server if required (remember you can use lower editions for subscribers)

Option 2 requires a licence for the production instance and the distributor, but gives a balance between cost and load on your production instance

Option 3 just requires the single licence but can cause additional load if you're replicating a lot of data.

You can read more about it here, although I'm sure you have checked it out already. others may have views on other replication methods, but this works in a environment I work on well. It all depends on balancing cost vs up time (AlwaysOn AGs vs replication)


MYSQL MyISAM data recovery implementation I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or adding some rows.

Scenario would be like: some PHP script fills several tables with information, there will be about 10 tables affected.  I'm thinking about creating temporary tables and filling them. After whole fill process succeeded, it will put actual rows to permanent tables and if not it will clean temporary tables.

I am wondering if there is easier way to have full recovery on fail? I cannot edit existing table's structure, or change table's engine. Thanks in advance for your time.

Edit: I have made very basic table of reference for simple understading of what I am dealing with. http://creately.com/diagram/h7yqs7qg2/UP3fno4Rzw3DTW3IdDa5uVDZQHE%3D

Real case scenario would be: I am creating order for user, but I cannot be sure that user  creation didn't failed on behalf of creation(let's say created row only on main user table) then when I'll check if user exists for order it will return true. So when I'll start order creation an I'll need it's address but it won't exist. Thus order creation will also fail. That's why there has to be rollback if failed any of data. Data cannot be simply deleted by row id because row could be modified(Invoicing order or change of order status).

Sorry for my bad Englist.

I have BAD NEWS / GOOD NEWS for you

BAD NEWS

MyISAM is simply not suitable for you because...

MyISAM is not a transactional storage engine
Writes to a MyISAM cannot be rolled back
MyISAM tables that are open crash very easily if the mysqld process or DB server crashes

GOOD NEWS

Using InnoDB is absolutely in your best interests. You will have to do some work for this to become a reality.

STEP 01 : Create a Script that will Convert all tables to InnoDB

Conversion of all tables to InnoDB is incredibly straightforward.

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /root/ConvertMyISAMToInnoDB.sql

STEP 02 : Configure InnoDB Settings for Multithreading and Caching

If the DB Server has 8GB RAM, use the following settings in /etc/my.cnf

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_open_files=16384
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
key_buffer_size=8M

STEP 03 : Create new InnoDB Logs

cd /var/lib/mysql
service mysql stop
rm -f ibdata1 ib_logfile0 ib_logfile1
service mysql start

STEP 04 : Perform InnoDB Conversion

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Make changes in your code for transactional support

You will have to employ the use of three commands : 1) START TRANSACTION; 2) COMMIT; 3) ROLLBACK;

Before you starting writing new data, precede it once with

START TRANSACTION;

START TRANSACTION; will create an initial checkpoint in the event you want to

commit all changes to the database (via COMMIT)
abandon all subsequent changes (via ROLLBACK)

Thus, in your PHP code, you must strategically place COMMIT; commands in places your know you want to fully submit all INSERTs, UPDATEs, and DELETEs of all InnoDB tables. In like fashion, you must also place ROLLBACK; commands in places where you wish to abandoned compiled changes to InnoDB tables.

CAVEAT

If you still want to the data as MyISAM, I have an additional suggestion.

Get another DB Server. Load all data into the second server as follows:

STEP 01 : service mysql stop

STEP 02 : Add this to /etc/my.cnf

[mysqld]
skip-innodb

STEP 03 : service mysql start

STEP 04 : Load the data into the second server

mysql -hIPofSecondServer ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Setup replication from InnoDB Server to MyISAM server

That way, you can change back to MyISAM by just failing over to the server that's MyISAM


Loading XML to table producing nothing I'm trying to import part of an xml file into a SQL Server database, but while the query successfully executes there is nothing in the table.

XML:

<?xml version="1.0" encoding="utf-16"?>
<config>
<company>
0514
<Name>Company A</Name>
<ContactName>Alan Smith</ContactName>
<ContactTel></ContactTel>
<ContactMail>random@random.net</ContactMail>
<System>type A</System>
</company>
</config>

Then here is the query I run

DECLARE @XML XML = 'C:\Users\Ramdon\file.xml';
INSERT INTO #tmp_Company2
SELECT
Code = Events.value('@Code', 'char(6)'),
Name = Events.value('@Name', 'varchar(100)'),
ContactName =Events.value('@ContactName', 'varchar(100)'),
ContactTel =Events.value('@ContactTel', 'varchar(15)'),
ContactMail =Events.value('@ContactMail', 'varchar(100)'),
System =Events.value('@System', 'char(20)'),
FROM @XML.nodes('//config/company') as  XTbl(Events);

select * from #tmp_Company2;

Everything I've read suggests it should work but I get nothing.

As I pointed out your XML file isn't well formed. If you can convert your XML text into this:

declare @XML xml =
'<?xml version="1.0" encoding="utf-8"?>
 <config>
   <company>
     <Practice>
       0514
       <Name>Company A</Name>
       <ContactName>Alan Smith</ContactName>
       <ContactTel></ContactTel>
       <ContactMail>random@random.net</ContactMail>
       <SystemType>type A</SystemType>
     </Practice>
   </company>
 </config>';

Then you could use next query to get data content:

select T2.lin.value('(./Code)[1]', 'char(6)') Code,
       T2.lin.value('(./Name)[1]', 'varchar(100)') Name,
       T2.lin.value('(./ContactName)[1]', 'varchar(100)') ContactName,
       T2.lin.value('(./ContactTel)[1]', 'varchar(15)') ContactTel,
       T2.lin.value('(./ContactMail)[1]', 'varchar(100)') ContactMail,
       T2.lin.value('(./SystemType)[1]', 'varchar(100)') System
       into #tmp_Company2
from   @XML.nodes('/config/company/Practice') T2(lin);

select * from #tmp_Company2;
GO

Code   | Name      | ContactName | ContactTel | ContactMail       | System
:----- | :-------- | :---------- | :--------- | :---------------- | :-----
0514   | Company A | Alan Smith  |            | random@random.net | type A

db<>fiddle here


What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change? A user (Windows login) just complained he was denied the execution of a procedure. I went to check and verified he had the privileges to execute it. I didn't change anything (and right now I'm the only one with admin privileges do to so if needed) and after two unsuccessful attempts he tried to run the SP for the third time and it worked.
I have XE configured to catch error messages and it captured twice the error code 229:

The EXECUTE permission was denied on the object 'storedProcedureName',
database 'databaseName', schema 'schemaName'.

Is there any situation where this behavior is expected?

Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)

If the Windows user account was added to an Active Directory (AD) group in the meantime, and that AD group had permission to run the procedure, then that could create this scenario where a user gained access to a procedure with no changes within SQL Server.

Also get the network admin to check if the user logged off and on again. Group membership only gets assigned at logon so if the user was added to a group and had not logged off he would not have had that group membership in his token. -  Spörri

Related Q&A: How do I assign an entire Active Directory group security access in SQL Server 2008?


Should dbo schema be avoided? When it comes to the dbo schema:

Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own the dbo schema?

It may be a good practice because when you have other users using the database you want to be able to limit their access with schemas. For example in a database you have the following tables.

HR.Payhist
HR.Payscale
HR.Jobdesc
IT.username
IT.useraccesslevel
ENG.jobsite
ENG.trainings

As the HR director I am able to access anything in the HR schema, as the IT director I can see employees usernames and access levels. The Engineering department can see what job sites are active, etc. If dbo was the set schema for all the tables I would have a harder time segmenting out my data and providing access roles.

The idea, I believe, in SQL Server is to offer a product that can be access and queried by different departments. In reality only DBAs/DBDevs really access the database and it typically only stores application data.

It also helps with readability and manageability. At first blush I can easily identify what table holds what data and how the data is separated.

Personally I prefer defining schemas as a general practice. Remember schema is greek for plan, having a laid out schema structure helps you to plan and identify data.


When restoring a SQL Server DB from dump files on a remote NAS share, copy before restore or restore directly? My team is considering backing up all DBs to an Isilon NAS and we're conducting time trials to see if the restore time from the NAS is quicker than performing a copy to local storage before restoring. Besides considering the throughput of the source and target storage and the network link, what else should we consider before making a selection? Thank you!

In my experience, there has been nothing to gain by copying files locally before carrying out the restore. If the target of the restore has sufficient IO capacity, you should be able to restore from the network share at very close to the speed of a file copy.


database design for car part site. (relational database) I am working on a directory website for the car's part, in this site the shop owner can register themself and then the user can search for the part based on the year, model submodel and type of the engine of the car after he/she searched. the user chooses the specific part for example Door ..

in this case, the user should see the door only for the car which he/she already searched for (specific car search) .. now for the tables I have created are:

1- shop owner table like:

 user_Id, Name, Info, location

a car's part table which is 6 tables is like this:

 Door table :
 user_Id (reference from shop owner id),side, front/rear, color, price, car
 model, car submodel, car year.
 Fender table:
 user_Id (reference from shop owner id), side, front/rear, color, price, car
 model, car submodel, car year.

my question is: a
car model, car submodel, car year attribute for all the tables, is there any way to get rid of this redundancy (repeating attribute)?  I must use relational database

you should have a table for the brands, another one for the models related to their respective brand, another one for the submodels related to the submodels and in the last table use compound keys with the keys of the tables and the year and create views for queries.


Does the SQL/JSON Path specification allow accessing the property of a filter expression's result I noticed a difference in the evaluation of a SQL/JSON path expression between Oracle and Postgres and I wonder which one is correct.

Consider the following JSON structure:

{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}

To get the price of the product with ID = 1000, Postgres allows me to use the following JSON path expression:

jsonb_path_query(doc, '$.* ? (@.id == 1000).price')

However, when using Oracle's json_value() (or json_query() - doesn't matter)

json_value(doc, '$.* ? (@.id == 1000).price')

I get the following error (using Oracle 18c):

 ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price')
 JZN-00209: Unexpected characters after end of path
 at position 21

There is workaround in Oracle 18c:

json_value(json_query(doc, '$.* ? (@.id == 1000)'), '$.price')

So my question is:

Does the SQL standard allow the property access as done in Postgres' implementation?
Or is this simply not specified in the standard and marked as "implementation dependent"?

(Given Postgres' track record with sticking to the standard as much as possible, I suspect Postgres is correct and Oracle is not implementing the full standard)

I do not know about the standard, but it works just fine in 19c.

Script:

[oracle@o73 ~]$ cat x.sql
with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(c1, '$.* ? (@.id == 1000).price') x
from d;

with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(json_query(c1, '$.* ? (@.id == 1000)'), '$.price') x
from d;

exit
[oracle@o73 ~]$

18c (18.10):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [oracle] ? RYMIN18
The Oracle base has been set to /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 5 10:50:17 2020
Version 18.10.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0

with d as (select
*
ERROR at line 1:
ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price')
JZN-00209: Unexpected characters after end of path
at position 21

X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0
[oracle@o73 ~]$

19c (19.7):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [RYMIN18] ? RYMIN19
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 5 10:50:31 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

X
--------------------------------------------------------------------------------
42

X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@o73 ~]$


PostgreSQL SQL: updating summaries I'm trying to optimize a query like:

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

Where the master table is large, so running this results in a huge sequential scan. On the other hand, this count doesn't change so often, so my idea is to rewrite it like this:

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
   WHERE count !=
     (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

EXPLAIN tells me this results in two subplans, which I'd expect. Is there a way to avoid the two executed subplans?

An attempt to rewrite the above as

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) AS cnt
   WHERE count != cnt

fails with syntax error at "AS".

This should be faster:

UPDATE master
  SET "count" = t.cnt
FROM (
   SELECT master_id, COUNT(*) as cnt
   FROM detail
   group by master_id
) as t
where t.detail.master_id = master.id
  and t.cnt <> master."count";

This assumes that master.id is unique.


How to create an index view? I have a view which is doing summation of several columns:

Select cola, colb, cola + colb + colc from table tbl_a

Can I know what are the indexes I should create for the view to improve its performances?

Some thoughts...

what filters or joins will you use on the view? These determine what indexes you'll need on the table
have you considered a computed column to pre-sum cola+colb at the table level?

Personally, I avoid views because a view doesn't have persistence (unless it is indexed itself): it is simply a macro expanded by the query optimiser. That is, say you have 2 views that join (one with 3 table, one with 4 tables): the actual query plan will show all 7 tables.

In the case above, you have no filters. This means that no index can help you: you'll get a clustered index (which is actually the table and you do have a clustered index, right?) scan


Deleting old rows when a new one is added and there are more than 100 rows For a school assignment we need to keep the number of rows in a mysqldb under 100. The newest rows must stay. I've tried using a trigger but with no success.

CREATE TRIGGER delete_rows
AFTER INSERT ON `check`.log
FOR EACH ROW
BEGIN
   IF(SELECT COUNT(*) FROM `check`.log)>100
   THEN
      DELETE FROM `check`.log LIMIT 1;
   END IF;
END

This resulted in the following error:

ERROR 1442 (HY000): Can't update table 'log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Then I tried monitoring /var/lib/mysql/check/log.ibd with incron and let it execute a script. when opening log.ibd with nano, incron executes my script but when I use mysql to insert something into it, nothing happens.

The incron instruction:

/var/lib/mysql/check/log.ibd IN_ALL_EVENTS /etc/scripts/test

can somebody please help me.

Your trigger is not specifying which row to delete and is only limiting it to deleting a single record. You are going to want to update the trigger to specify which record to delete and I would guess that you want to delete the oldest one.

https://dev.mysql.com/doc/refman/5.6/en/delete.html

  The LIMIT clause places a limit on the number of rows that can be deleted


Mysql - How to Optimize Retrieval Time in a Table I have query which has 200 million Records in a single table. I am using BTree indexes in my table:

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
|    8242063
+------------------------------+
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing.

How can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!

Maybe GROUP BY can help. See this MySQL reference on DISTINCT optimization

SELECT COUNT(*)
FROM (
  SELECT 1
  FROM [tablename]
  WHERE [columnname] >= 3
  GROUP BY id
) q


SQL Server update from SP1 to SP3 Can I move directly from SQL Server SP1 to SP3 or do i need to install SP2 then go to SP3. I was assigned to upgrade my servers 2014 just to make sure I don't fall into problems

Yes, you can upgrade directly to Service Pack 3. Service packs are cumulative; SP3 contains everything in SP2 and SP1.

This has always been true for every version of SQL Server I’ve worked with, and I'm old. But for those needing to read this from MS  documentation on their web site instead of peers answering questions here, here is one official source:

  Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. You do not have to install a previous service pack before you install the latest service pack.

You will need to upgrade to SP3 before applying any SP3 Cumulative Updates. The name is a little confusing but a cumulative update is only cumulative from the relevant service pack. SP2 CU1 can only be applied to SP2; it won’t install on RTM, SP1, SP3, etc.

This acronym definition page may have useful info too.


Extract entries from table where specific datetime criteria are satisfied SQL I have a table DATATABLE that contains a timeseries that looks something like this:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 09:45:00.000
2018-01-11 09:45:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:15:00.000
2018-01-11 10:15:00.000

So if I want to extract the timeseries, I would query:

SELECT * FROM DATATABLE

Now what I want, is to be able to extract part of the timeseries that satisfies specific criteria, which changes each time. The criteria are stored as ranges of values, and so far I have created a procedure that extracts those criteria. One sample criterion would be that the time part of the timeseries should have a value that is contained int he following:

00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
02:30:00
03:00:00
03:30:00
04:00:00
04:30:00
05:00:00
05:30:00
06:00:00
06:30:00
07:00:00
07:30:00
08:00:00
08:30:00
09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00

In other words, the values 2018-01-11 09:45:00.000 and 2018-01-11 10:15:00.000 should not be part of my final result. The final result would therefore look like:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000

The criterion that I want to set, in English would be:

''Select all the values from my timeseries that have a time part that is (exactly) contained in the series of times I provide you.''

What I wrote so far is:

SELECT * FROM DATATABLE
WHERE DATEPART(MINUTE,[DATATABLE].[ISSUETIMES])=(SELECT IssueTimes FROM @IssueTimes)

where @IssueTimes is a temporary table variable where I store the range of values I want to use as criteria and [DATATABLE].[ISSUETIMES] is the column that contains the timeseries that I want to filter.

The error I get is the following:

Operand type clash: time is incompatible with int

So i get that this probably means that am trying to compare times with int, but i do not know how to proceed as I am kind of new to SQL.

I hope my formulation was good enough to be understandable :)


Does SQLCMD "!!" command work in Azure Data Studio In SQL Server Management Studio, it is possible to run batch commands when in SQLCMD mode, using the !! command. However, in Azure Data Studio, this does not seem to work (SQLCMD has been available in ADS since version 1.13.0 in November 2019), I'm running ADS as Windows Administrator.
How can I execute batch commands in Azure Data Studio using SQLCMD mode?
Did I miss something, or is there some of the (scarce) documentation I need to read?
- In SQL Server Management Studio:

- In Azure Data Studio

This is a known open issue with SQLCMD in ADS, where SQLCMD mode fails to parse some sqlcmd keywords. The issue is currently on the backlog.
ADS includes a terminal window (View-->Terminal) so  you can easily run OS commands on your client under the same query tab without SQLCMD.


SQL Server installation errors assembly I have been trying to install SQL Server and now I am tired of it. I tried to version SQL Server 2008 and SQL Server 2008 R2. SQL Server 2008 gives the following error (.Net Framework installed)

  Application requires .Net Framework v4.0.30319 AND invoke or BeginInvoke cannot be called until the window handle has been created

SQL Server 2008 R2 was giving the following error:

  The following error has occurred:

  An error occurred during the installation of assembly 'Microsoft.SqlServer.GridControl,fileVersion="10.50.1600.1",version="10.0.0.00000",culture="neutral",publicKeyToken="89845DCD8080CC91",processorArchitecture="x86"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F.

and after that a new error

  The following error has occurred:

  An error occurred during the installation of assembly 'Microsoft.VC80.CRT,version="8.0.50727.4027",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="x86",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x80070002.

EDIT:

Where in the world can I find .Net Framework v4.0.30319? Its so annoying and confusing.

Peter Saddow has written about this issue, and it's more likely a problem with the setup program rather than your OS (though it can't hurt to make sure you've installed all Windows updates too):

Invoke or BeginInvoke cannot be called on a control until the window handle has been created.

So instead of installing RTM from RTM media, try slipstreaming with the final service pack for 2008 R2 (Service Pack 3).

I came across this issue on Windows 7, even when slipstreaming, but in my case only a reboot was required to get past the issue (simply closing setup and re-launching was not enough).

Peter has also written about slipstreaming:

How to slipstream SQL Server 2008 R2 and a SQL Server 2008 R2 Service Pack 1 (SP1)

That post was about SP1, but you can follow the same basic steps to slipstream a newer service pack.


Simplify and optimize a complex query Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take 250ms, while some the queries for users who don't have that many events take 30 seconds.

CREATE TABLE `events` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `enactor_id` int(11) unsigned NOT NULL,
  `enactor_type` varchar(20) NOT NULL DEFAULT '',
  `subject_id` int(11) unsigned NOT NULL,
  `subject_type` varchar(20) NOT NULL DEFAULT '',
  `result_id` int(11) unsigned NOT NULL,
  `result_type` varchar(20) NOT NULL DEFAULT '',
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `enactor_id` (`enactor_id`,`enactor_type`),
  KEY `subject_id` (`subject_id`,`subject_type`),
  KEY `result_id` (`result_id`,`result_type`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here is a query that showed up in our slow logs (it ran for 35 seconds):

SELECT `events`.`id`
FROM (`events`)
WHERE (
(
    `subject_id` in (235799, 23987, 294828746, 234897224, 23429847) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Product') AND
    `result_type` in ('Comment')
) OR (
    `result_id` in (1503) AND
    `enactor_type` in ('User') AND
    `result_type` in ('Search')
) OR (
    `subject_id` in (3523) AND
    `subject_type` in ('User')
))
AND `events`.`date_created` <=  '2012-12-13 19:44:48'
ORDER BY `events`.`id` desc
LIMIT 100;

Explain shows:

id  select_type table   type     possible_keys                      key      key_len  ref   rows  Extra
1   SIMPLE      events  index    subject_id,result_id,date_created  PRIMARY  4        NULL  200   Using where

As a reference, this query completes in 70 ms:

SELECT `events`.`id`
FROM (`events`)
WHERE (
(
    `subject_id` in (1234, 23876234, 234234, 232, 2342, 23424234, 2, 456456, 567, 56756756, 567567) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Product') AND
    `result_type` in ('Comment')
) OR (
    `enactor_id` in (879,486,11154) AND
    `result_type` != "Search" AND
    `result_type` != "Recommendation" AND
    !((`result_type` = "Product" AND `subject_type` = "Store")) AND
    `enactor_type` in ('User')
) OR (
    `subject_id` in (29) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Store') AND
    `result_type` in ('Product')
) OR (
    `subject_id` in (285) AND
    `subject_type` in ('User')
))
AND `events`.`date_created` <=  '2012-12-13 22:12:47'
ORDER BY `events`.`id` desc
LIMIT 100;

The events table has ~30 million rows. The database server has 15GB of memory, and the events table is ~1GB. I have to imagine that some sort of indexing will cure this, but I have been playing around with different index choices to no avail.

Edited #1:

@Erwin Smout - You bring up a good point. For users without very many matching rows, the DB will need to go through all 30 million. That would explain the reason that heavy users have faster queries than new users or non-heavy users. The question, then, is how to speed up the worst case scenario, right?

@Pieter B, the indexes are listed in the create table definition above.

@ssmusoke, our site has a following model like many "social" sites. The events table is basically a chronological table of actions that have been taken on the site, and that query pulls events of different types that are relevant to the user based on who that user follows. I will look into changing the *_type's to tinyints to see what kind of performance gains that garners.

What does that "LIMIT 100" do in a query? What might its effect be for "heavy users" (who have lots of matching rows in the database?), and what might its effect be for "non-heavy users" (who will not have that many matching rows in the database?)? How many rows will need to be inspected by the system before the 100 LIMIT is reached for "heavy users", and how many rows will need to be inspected by the system before the 100 LIMIT is reached for "non-heavy users"? How many rows will have to be inspected by the system if a user doesn't even have the full 100 matching rows in the database?

"The question, then, is how to speed up the worst case scenario, right?"

The answer to that is that you either have to try to make more "intelligent" estimates of your hardcoded number 100, or you could try to rewrite your OR predicates as UNION queries, hoping that it's your engine's poor optimizer who doesn't do that automagically (and provided that all individual parts of those UNION queries can be served rapidly by existing indexes - if your predicates are generated dynamically, then you'll have to index as good as every column that could ever possibly appear in your WHERE clause).  (Disclaimer : I don't know about what optimizations your engine does and does not do.)


Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another transaction puts X lock on a key value of the index.

Let me start with a small example (TSQL2012 DB from 70-461 cource used):

CREATE TABLE [Sales].[Orders](
[orderid] [int] IDENTITY(1,1) NOT NULL,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[shipperid] [int] NOT NULL,
... )

Columns [custid], [empid], [shipperid] are corelated parameters for [Sales].[Customers], [HR].[Employees], [Sales].[Shippers] accordingly. In each case we have a clustered index on a referred column in a parrent table.

ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([custid]) REFERENCES [Sales].[Customers] ([custid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([empid]) REFERENCES [HR].[Employees] ([empid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([shipperid])REFERENCES [Sales].[Shippers] ([shipperid])

I am trying to INSERT [Sales].[Orders] SELECT ... FROM another table called [Sales].[OrdersCache] which has the same structure as the [Sales].[Orders] except foreign keys.
Another thing might be important to mention the table [Sales].[OrdersCache] is a clustered index.

CREATE CLUSTERED INDEX idx_c_OrdersCache ON Sales.OrdersCache ( custid, empid )

As expected when I`m trying to insert a low volumes of data LOOP JOIN works fine making index seek on the foreign keys.

With high volumes of data MERGE JOIN is used by query optimizer as a most efficient way to maintain foregn key in the query.

And there is nothing to do with it exept using OPTION (LOOP JOIN) in our case with foreign keys or INNER LOOP JOIN in explicit JOIN case.

Below is the query I`m trying to run in my environment:

INSERT Sales.Orders (
        custid, empid, shipperid, ... )
SELECT  custid, empid, 2, ...
FROM Sales.OrdersCache

Looking at the plan we can see that all 3 foreing keys validated with MERGE JOIN. It`s not an appropriate way for me since it uses INDEX SCAN with whole index locking.

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN (I think the regression will be greater with data volumes growing).

In the SELECT statement you can see single value for shipperid attribute  for the entire inserted set. In my opinion there must be a way to make the validation phase for the inserted set faster at least for the immutable attribute.
Something like:

make LOOP JOIN,MERGE JOIN,HASH JOIN if we have undefined subset for
JOIN validation
if there is just a single explicit value of the validated column we make the validation only once (INDEX SEEK).

Is there any common pattern to overpass the situation above using code structures, additional DDL objects, etc?

Added 20/07. Solution. Query Optimizer already makes a 'single key - foreign key' validation optimization by using MERGE JOIN. And makes only for Sales.Shippers table, leaving LOOP JOIN for another joins in the query at the same time. Since I have a few rows in the parent table Query Optimizer uses Sort-merge join algorithm and compare each row in the inner table with parent table only once. So that is the answer on my question if there is any particular mechanism to effectively process single values in a set during single key validation. That is not so perfect decision but that`s the way SQL Server optimizes the case.

Performance affect investigation revealed that in my case MERGE JOIN and LOOP JOIN insert statement became approximately equal with 750 simultaneously inserted rows with the following superiority of the MERGE JOIN (in CPU time resource).
So using OPTION (LOOP JOIN) is an appropriate solution for my business process.

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN

The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the alternatives with your workload, measuring whichever metrics are most important to you (elapsed time, CPU usage and so on).

In my experience, the optimizer switches from loops join to merge join for foreign key validation far too early. In all but the largest operations, I have found loops join to be preferable. And by "large" there I mean tens of millions of rows at least, certainly not the thousand or so you indicate in the comments to your question.

  In my opinion there must be a way to make the validation phase for the inserted set faster at least for the immutable attribute.

This makes sense in principle, but there is no such logic today within the plan-building logic that foreign key validation uses. The current logic is deliberately very generic and orthogonal to the wider query; specific optimizations complicate testing and increase the likelihood of edge-case bugs.

  Is there any common pattern to overpass the situation above using code structures, additional DDL objects, etc?

Not that I am aware of. The deadlock risk with merge join foreign key validation is well known, with the most widely-used workaround being the OPTION (LOOP JOIN) hint. There is no way to avoid shared locks being taken during foreign key validation, because these are required for correctness, even under row-versioning isolation levels.

There is no better general answer (than the loop join hint) if you want to retain the ability for multiple concurrent processes to add rows to the parent and child tables transactionally. But, if you are willing to serialize the data modifications (not reads), using sp_getapplock is a reliable and simple technique.


Where to start with Oracle? This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL.

You may find your question has been closed as a duplicate of this one, if so, please don't be offended: we are trying to be as helpful as we can without diluting the expert focus of this site. Closing a question as a duplicate of this one is us saying "What you really need is to spend some time reading some of the best resources available rather than have your specific question answered right now. If you do get through these resources, you'll soon be back with more advanced questions!

Oracle's official documentation library has some great resources, including:

The Database Concepts guide, which is essential reading for beginner and Oracle ninja alike.
The 2 Day DBA
The 2 Day Developer's Guide

Oracle 12c official PL/SQL documentation:

PL/SQL Language Reference
PL/SQL Packages and Types Reference
Chapter 5 Developing Stored Subprograms and Packages from 2 Day Developer's Guide

PL/SQL 101

This is a twelve part series of articles on understanding and using PL/SQL. It is written by Steven Feuerstein and published in Oracle Magazine.

Building with Blocks
Controlling the Flow of Execution
Working with Strings
Working with Numbers in PL/SQL
Working with Dates in PL/SQL
Error Management
Working with Records
Working with Collections
Bulk Processing with BULK COLLECT and FORALL
The Data Dictionary: Make Views Work for You
Wrap Your Code in a Neat Package
Working with Cursors


Multi Tenancy and shared customers I am developing multi-tenancy application and I have no problem with the architecture itself. I find difficult on how to make customers table shareable among tenants. This is because same customer can subscribe to multiple tenants. I don't want to have same duplicate data and so user should register only once and be able to subscribe to any of tenant with the same credentials.

I have tried to look over the internet but could not find any good resource (may be I am using wrong keywords). I would appreciate any help (including the architecture name if any) to solve this problem.

If I am not clear please let me know.

Multi-tenancy can be achieved in several ways; opaque multi-tenancy doesn't necessarily mean a design with one schema per tenant. It can also be achieved by adding a "tenant ID" column to every single table. As an example, this is the way that SAP has done it for a very long time already.

Applying this principle to your design, you would add "tenant ID" to all tables that need to be multi-tenant, while the "customers" table won't have it. Instead you need to add a "tenant_customers" table with two columns; "tenant ID" and "customer ID" to associate customers with tenants. Wikipedia calls this concept an Associative Entity.

Alternatively, you can store your tenant-specific data in one schema per tenant and have the customer table and "tenant_customers" associative table stored in its own schema. Instead of "tenant ID" you could use a string storing the "tenant schema name".


Unique constraint to enforce max one null per item I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of manufactured items, there may be rows where manufactured_until is null - those are the items produced currently.

create table item
(
  id int,
  type_id int,
  manufactured_from timestamp,
  manufactured_until timestamp
)

Sample data:

1  | 101  | 1.1.2000    | 31.12.2012
2  | 102  | 1.4.2003    | 1.1.2010
3  | 101  | 1.1.2013    |
4  | 102  | 2.1.2010    | 4.5.2014
5  | 102  | 5.5.2014    |

The following logic should hold: for each item type, there should be at most one item being produced at the moment (manufactured IS NULL). In the example, I should not be able to add the record (6, 101, 27.8.2014, NULL).

I'd like to write a UNIQUE constraint that will guard this. Is it possible? For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type?

A partial unique index should do this:

create unique index max_one_null
   on item (type_id)
where manufactured_until is null;

  For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type

Look into range types and exclusion constraints. They were specifically designed for this problem.

Something like this (untested):

create table item
(
  id int,
  type_id int,
  manufactured_during tsrange
);

The inserts will look a bit different as there is only a single column now:

insert into item
  (id, type_id, manufactured_during)
values
  (1, 101, '[2000-01-01,2012-12-31]'),
  (1, 101, '[2013-01-01,)');

[2000-01-01,2012-12-31] defines a closed interval that includes both dates. [2013-01-01,) defines an open interval without an end (maps to manufactured_until is null with your current table design)

Then add an exclusion constraint to guard the range:

alter table item
  add constraint check_manufactured_range
  exclude using gist (type_id with =, manufactured_during with &&);

You don't need a unique index any more because the constraint will make sure that nothing will overlap.

More information in the manual: http://www.postgresql.org/docs/current/static/rangetypes.html

If you search the internet for "Postgres exclusion constraints" you will find several presentations and blog posts regarding this topic.

P.S.: it seems that you actually want a daterange (instead of a "timestamp range") because you probably don't want to include the time of day with the intervals that define when something was manufactured.


os_username vs username in Oracle audits I have recently been given the task of building reports from the audits of our Oracle 11g database. While not being the DBA and him being on vacation for a few weeks I have troubles understanding how some informations are recorded.

My main concern is about the os_username and username field. When reading Oracle documentation I see that:

  os_username: Operating system login username of the user whose actions were audited

  username: Name (not ID number) of the user whose actions were audited.

In the audit_trail I found that os_username contained mostly login username which sure sounds like the definition but also contained entries by "oracle". While the obviously correct os_username had username of what seems to be our systems or things like OEM, the records with a os_username "oracle" all had username entries of login username making this confusing.

In the end, what is the real difference between those 2 columns and on which should I base my query to know how many times a user was recorded?

Any other references I could read on the subject would also be appreciated

The os_username is the name of the user that runs the application. The username is the name of the user defined in the database.

Suppose that UserA is logged on his desktop as UserA and runs an application to modify the data. When he is asked to connect he enters the credentials of UserB. In the audit you will find UserA as os_username and UserB as username.

This explains while you find oracle as os_username since this is the user that is used to run Oracle itself.


What should every DBA know? As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like StackExchange) when working on databases/SQL. But there's always something I do and I'm never sure about such as security & perfomance in large environments.

If a DBA were to fully design/create a database/schema from scratch and maintain it from then on, what do you expect a DBA know? What are things a DBA should never do, or always ensure it is done? What have you experienced in a professional environment that education never prepared you for?

First - you must be able to backup and restore a database. You must know how to set up a recurring schedule to backup both the database and the transaction logs.

You should know what other maintenance is required periodically such as updating statistics.

You should understanding indexing - how to create them and when to create them and when not to create them.

You should understand how to read query plans - execution plans or explain plans depending on the db backend.

You should understand datatypes and why using the correct one is important. You should also understand why every table needs a PK and how to set up PK/FK relationships and you should never allow application developers to think this stuff should be handled by the application and not the db.

You should be familiar with database normalization.

You shoud read about performance tuning and database internals for your particular database and be proficient in advanced SQL.

You should know how to monitor performance of your database.

You should know how to set up new users and use roles based security.

You should be able to install the database on a new server.

I'm sure there's more, but this is a starting point.


Can't create user in SQL Server Hi guys when I try to create user for my database I always encounter this kind of error.

This is the sequence of task I done.

I manage to create new login using windows authentication of SQL then I use that login to access SQL Server again then I create my database and set the owner of that database to the login that I was created. After creating the database, I try to create new user and assign the login I use but I was halted by this error.

It is normal you cannot associate two different user to a single login for a database. You must understand the difference between a login and a user.

A login authenticate you on the server. Once you are authenticated, the login will impersonate a user to access the specified database of the connection string.

So a login can have only one user per database.

Sample : login [myDomain\myUser] has access on SqlServer. This login can have a user per database : userA for databaseA, userB for databaseB.

If you want to add an other user to the login for the same database, you have to drop the first user. Instead, you have to create a different login for the second user.

Hope this help you.


Retrieving the top row of data I have very limited SQL knowledge and need to report on the top most current row of data within a table.  The table holds Effective Date, Information and a Renewal Date.

select DATA_KEY_ID,
TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE", DATA_KEY_REF, DATA_KEY_ID
from TABLENAME
where SCREEN_REF = 'TEST' and FIELD_REF = (select REF from REF_TABLE where SCREEN_REF = 'TEST' and ID = 'EFFDTE')

This brings the correct effective date, however it also brings out all the rows that have been entered.  I need to only bring out the top row.

Sorry if this is a bit random.

Have you tried something like this (syntax is for SQL Server):

SELECT TOP(1)
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID
FROM TableName
WHERE SCREEN_REF = 'TEST'
    and FIELD_REF = (
            select REF
            from REF_TABLE
            where SCREEN_REF = 'TEST'
                    and ID = 'EFFDTE'
            )

The TOP(1) clause just after the SELECT tells the database engine to return a single row.  In order for this to return the same row each time it is executed, you should also include an ORDER BY clause at the end such as:

ORDER BY DATA_KEY_ID;

If you are using MySQL, the syntax would be something like:

SELECT
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID
FROM TableName
WHERE SCREEN_REF = 'TEST'
    and FIELD_REF = (
            select REF
            from REF_TABLE
            where SCREEN_REF = 'TEST'
                    and ID = 'EFFDTE'
            )
ORDER BY DATA_KEY_ID
LIMIT 0,1;

If you specify the type of RDBMS you are using, that would help identify the actual syntax you should use.

For Oracle, which seems like the platform you may be using, you need to add a reference to ROWNUM to the WHERE clause, like:

SELECT *
FROM
  ( SELECT
        DATA_KEY_ID
        , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
        , DATA_KEY_REF
        , DATA_KEY_ID
    FROM TableName
    WHERE SCREEN_REF = 'TEST'
        and FIELD_REF = (
                select REF
                from REF_TABLE
                where SCREEN_REF = 'TEST'
                        and ID = 'EFFDTE'
                )
    ORDER BY DATA_KEY_ID
  ) x
WHERE ROWNUM = 1;


What ongoing maintenance is needed for a MySQL environment? I'm an MSSQL DBA, and I'm inheriting a 3-server MySQL 5.6 environment feeding a (currently) low traffic website.  I'm having a hard time finding what kind of regular / preventative maintenance a MySQL env needs.  I've done a fair amount of self-studying, but the guides etc I've run across mainly talk about installation and backups.  My environment is installed, and we have backups going.  Now what?

So what sort of care and feeding does a MySQL environment need?  Corruption checks?  Index rebuilds?  Aromatherapy?

It just works!

Seriously though, if you got a master with two replicas environment, you probably want to do occasional consistency checks with pt-table-checksum. Replication does not guarantee consistency (and writes can happen on replicas too).

Otherwise:

Verify your backups to make sure you can actually recover (most of our data recovery customers thought they have backups, but they were actually not working).
Query reviews - definitely worth learning how to do that so you can review them proactively.
Do check error logs - any corruption errors will show up there.

There are many other things you can do, but I try to live by the rule: don't fix what ain't broken.

Good luck!


How do I set the external editor for PostgreSQL's psql client? What do I put in ~/.psqlrc file to set the default editor?

The editor I want to use (Textmate) is at /Users/username/bin/mate.

I found the place in the docs where they talk about it, and tried several syntax variations, but can't seem to get it to work.

For example, this didn't work...

\set PSQL_EDITOR "/Users/username/bin/mate"

In psql when I enter \e I get...

sh: /usr/local/bin/mate: No such file or directory
sh: line 0: exec: /usr/local/bin/mate: cannot execute: No such file or directory

Contrary to my comment above (which was the result of insufficient testing), you should put a line into .bashrc (or any similar place):

export PSQL_EDITOR="/Users/username/bin/mate"

In psql coming with PostgreSQL 9.2, there is a \setenv command, it is supposed to work from .psqlrc as well.

(Credits to Pedro Romano.)


Prevent inserts to a non existing Synonym I have a Synonym called VirtualTable which points to RealTable1.

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

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

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

Invalid object name 'dbo.VirtualTable'

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

I know that I cannot rename or repoint a Synonym.

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

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

Check if the synonym exists before the insert/update.

IF EXISTS (SELECT * FROM sys.synonyms
WHERE name = 'VirtualTable')
 PRINT 'do your stuff'


Recovering SQL Express 2016 RC1 database This afternoon, I tried to log on to my local SQL Server 2016 RC1 instance and it failed. Looking at the logs I learn that the service won't start because the "Evaluation Period has ended", which was great, I didn't get any warnings about this period ending which has led me into my current predicament.

I had quite a large database on there which I was working on locally while travelling and made a few significant changes to which I'd rather not have to go back and remake.

What options do I have to recover this database into a new 'fully fledged' SQL Express Instance?

I've tried the activation idea here but it doesn't upgrade the actual database engine, just the other elements. Which is similar to what happens when I try repairing it, just that more elements are repaired.

The actual Primary Database file and log are available, is there anyway I can import these?

You should be able to attach your database files using the instructions in the documentation: How to: Attach a Database File to SQL Server Express


Database design for bidirectional relationship I am modeling a bidirectional relationship between users with a relationship table. Each relationship deals with two and only two users. Each direction of the relationship allows for 3 different statuses (Pending, Blocked, Allowed).

To reduce memory overhead, I have decided on the following system to keep the entire relationship represented by a single row instead of one row per direction.

The smaller user_id is always stored as user_a and the larger user_id is stored as user_b so there is no risk of errors.

I am looking for feedback on whether I should represent both directions with a single column of (3x3 choices) or two horizontal columns of 3 choices each.

i.e.

... | status_a_b

A_Pending_B_Pending
A_Pending_B_Blocked
A_Pending_B_Allowed
A_Blocked_B_Pending
A_Blocked_B_Blocked
A_Blocked_B_Allowed
A_Allowed_B_Pending
A_Allowed_B_Blocked
A_Allowed_B_Allowed

vs

... | status_a | status_b

Pending | Pending
Blocked | Blocked
Allowed | Allowed

Thank you!

To reduce memory overhead,

That should never be factor in database design. Let the engine worry about that; just create your design based on how you will use your data.

  I have decided on the following system to keep the entire relationship represented by a single row instead of one row per direction.

That's fine, but in both cases you'll have to do two separate queries (or two criteria) to find all relevant relationships for a user:

SELECT *
FROM RELATIONSHIPS
WHERE From_User = @UserID OR To_User = @UserID

Note that this might be a little different if there is a meaningful difference between the two types of users. Say, between VENDORS and CLIENTS or something. But it sounds like the two are interchangeable.

  I am looking for feedback on whether I should represent both directions with a single column of (3x3 choices) or two horizontal columns of 3 choices each.

I wouldn't cram them into a single column, you'll just have to parse them back out when you need to use them, and that's a violation of First Rule of Normalization.

EDIT: Here's a (contrived) example to show what I mean:

Let's say you're designing a database for a social network, one more like Twitter (user_a can follow user_b independent of whether user_b follows user_a), and less like Facebook (user_a and user_b are either "friends" or not).

So if Marsha follows Joe, but Joe doesn't follow Marsha, we have two ways to create a "follows" table:

Followed     Follower    Status
---------    --------    ------
Joe          Marsha      T
Marsha       Joe         F

or

User1     User2    RtL_Follow_Status   LtR_Follow_Status
-----     ------   -----------------   -----------------
Joe       Marsha   T                   F

Sure, the second might save you a byte or two (or not, since I might not even need a row indicating that Joe doesn't follow Marsha), but the first is vastly easier to query, especially for the most likely application needs:

--Show all users following Joe
SELECT [Follower]
FROM [Follow_v1]
WHERE [Followed] = 'Joe'
AND [Status] = TRUE

vs

SELECT [User2] as [Follower]
FROM [Follow_v2]
WHERE [User1] = 'Joe'
AND [RtL_Follow_Status] = TRUE

UNION ALL

SELECT [User1] as [Follower]
FROM [Follow_v2]
WHERE [User2] = 'Joe'
AND [LtR_Follow_Status] = TRUE

Now your application might be nothing like this, or there might be reasons that I always need to know the reverse-follow-status when pulling up a relationship, but all I'm saying is that "saving a few bytes" is a low priority compared to making a database easy to understand and easy to query.

Note also the first example is likely to perform better as well, which might be more important as the application grows than saving a few bytes.


How can I update statistics adding the data of the last day only? I have been tuning some queries that had a warning on the sort operator, which is generally an expensive operator:

Operator used tempdb to spill data during execution with spill level 2

The way to improve the performance and get rid of the warning on the Sort Operator,was to update the statistics.

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

But as you can see it took over an hour to update the stats.

Now I have a similar query with the same problem.

when the @toDate is today, or yesterday I get the same warning:
Operator used tempdb to spill data during execution with spill level 2

The query is below,
the plan is here.,
Picture below for a quick look.

    DECLARE @FromDate DATETIME = getdate()-1
    DECLARE @ToDate DATETIME = getdate()-0

    SELECT DISTINCT
             ap.strAccountCode
            ,em.strEmail
            ,em.dtmLastUpdated
    FROM    Bocss2.dbo.tblBAccountParticipant AS ap

            LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
                   ON ap.lngParticipantID = em.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
                    ON ap.lngParticipantID = ah.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBOrder AS o
                    ON ap.lngParticipantID = o.lngAccountParticipantID

    WHERE   o.sdtmOrdCreated >= @FromDate
      AND o.sdtmOrdCreated < @ToDate

OPTION (RECOMPILE)

But when @toDate is 2 days ago, I don't get any warning at all.

The query is below,
the plan is here.,
Picture below for a quick look.

    DECLARE @FromDate DATETIME = getdate()-3
    DECLARE @ToDate DATETIME = getdate()-2

    SELECT DISTINCT
             ap.strAccountCode
            ,em.strEmail
            ,em.dtmLastUpdated
    FROM    Bocss2.dbo.tblBAccountParticipant AS ap

            LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
                   ON ap.lngParticipantID = em.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
                    ON ap.lngParticipantID = ah.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBOrder AS o
                    ON ap.lngParticipantID = o.lngAccountParticipantID

    WHERE   o.sdtmOrdCreated >= @FromDate
      AND o.sdtmOrdCreated < @ToDate

OPTION (RECOMPILE)

Updating the stats of the order table solves this problem, however, it takes over an hour to run.

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

I have good reasons to update the stats manually.

I also found this link interesting:
Why does SQL Server refuse to update these statistics with anything but fullscan?

Question:

Instead of updating the stats with full scan, is there a way I can just update the stats for the last day?

that would be something I could run on a daily basis.

this table in particular get 50k new records every day on average.

The first thing to try is experimenting with sampling instead of performing a full scan every time.

If lower samples are causing poor plans or still taking too long you could consider partitioning and if you are on SQL Server 2014 or higher you could use Incremental Statistics.

Incremental Statistics allow you to rebuild statistics at the partition level. There is still just one statistics object for the whole table but the statistics for the partition are merged into the histogram.

If you're not running on Enterprise edition or you are on a version prior to 2014 you might consider using partitioned views. This way you can partition your data into completely separate tables with their own statistics objects.

Partitioning and partitioned views bring their own sets of complications but are usually required for very large tables.

You may be suffering from the ascending key problem, see here. If you are then one last option could be to update statistics less often and use the trace flags 2389 and 2390 to improve cardinality estimates in the time between updates.


Recursive CTE Multiple Levels I have a table showing which roles recursively grant access to which resources within a database. For example:

The Default_Role grants access to the App_Role, the App_Role grants access to the Security_Role, and the Security_Role grants access to three tables (customers, sales, users). So a member of the Default_Role is granted all of these, but Default_Role is not granted access to the Sys_Role, and is not granted access to the System or Admin tables.
CREATE TABLE SQLTest(
     DBName     NVARCHAR(100) NULL
    ,Privilege    NVARCHAR(100) NULL
    ,PrivilegeType NVARCHAR(100) NULL
    ,PrivilegeDetail NVARCHAR(100) NULL
    ,TableName NVARCHAR(100) NULL
)

INSERT INTO SQLTest
VALUES
('TSDB','Default_Role','Role','App_Role',NULL),
('TSDB','App_Role','Role','Security_Role',NULL),
('TSDB','Sys_Role','Role','Security_Role',NULL),
('TSDB','Security_Role','Table','Customers','Customers'),
('TSDB','Security_Role','Table','Sales','Sales'),
('TSDB','Security_Role','Table','Users','Users'),
('TSDB','Sys_Role','Table','System','System'),
('TSDB','Sys_Role','Table','Admin','Admin')

What is the best way to flatten this out so you can see all the access (roles and tables) granted to the Default_Role, without showing additional access not granted to the Default_Role? Like this:

Tried putting together this example, but doesn't work.
http://www.sqlfiddle.com/#!18/f9a27/2

As CTE's are for the first test quite difficult to understand, i wrote a approach, with all Levels at the end
The Option at the end is nit needed, but to reduce espeically when you are trying to figure out what happens, you shoukld add it at a low number
WITH MyTest as
(
  SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P.PrivilegeDetail AS VarChar(Max)) as Level
  FROM SQLTest P
  WHERE P.Privilege = 'Default_Role'

  UNION ALL

  SELECT P1.DBName, P1.Privilege,p1.PrivilegeType,P1.PrivilegeDetail,p1.TableName , CAST(P1.PrivilegeDetail AS VarChar(Max)) + ', ' + M.Level
  FROM SQLTest P1
  INNER JOIN MyTest M
  ON M.PrivilegeDetail = P1.Privilege
 )
SELECT * From MyTest

OPTION (MAXRECURSION 50);

REsult
DBName  Privilege       PrivilegeType   PrivilegeDetail     TableName   Level
TSDB    Default_Role    Role            App_Role            (null)       App_Role
TSDB    App_Role        Role            Security_Role       (null)       Security_Role, App_Role
TSDB    Security_Role   Table           Customers           Customers    Customers, Security_Role, App_Role
TSDB    Security_Role   Table           Sales               Sales        Sales, Security_Role, App_Role
TSDB    Security_Role   Table           Users                Users       Users, Security_Role, App_Role


ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table I know that there are other questions about this topic but reading and trying them they cannot solve my problem. Mainly because select * from DBA_DATA_FILES; shows me an error when executing, because the table doesn't exist in the database.

I know the different tablespaces using the following query, also looking not standard :

SELECT * FROM USER_TABLESPACES;

The ORA error ORA-01652 was thrown when executing a request which should use many temporary space.

The tablespace which have the problem is R1XQDTM1. Below the tablespace info :

The problem is that i don't know the datafiles location for this tablespace to execute the solution request ALTER TABLESPACE ADD DATAFILE

EDIT/ !!! Now i see you wrote this:

  Mainly because select * from DBA_DATA_FILES; shows me an error when
  executing, because the table doesn't exist in the database.

In my opinion you just don't have enought rights. You should login as sysdba.

Use this select first, you should see location of datafiles (Change the "EXAMPLE" at the end of this command):

select file_name, AUTOEXTENSIBLE, round(BYTES/1024/1024,1), round(MAXBYTES/1024/1024,1), INCREMENT_BY, INCREMENT_BY/1024  from dba_data_files where tablespace_name='EXAMPLE';

Then add datafile to Tablespace, for example:

alter tablespace EXAMPLE add datafile '/oracle/data/EXAMPLE02.dbf' size 100m autoextend on next 100m maxsize unlimited;


PostgreSQL CREATE INDEX memory requirement I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3.  I then want to create an index on the table.

The table and the resulting index together (or twice the table total size) can fit into 5GB of RAM and I set maintenance_work_mem to 5GB, still CREATE INDEX uses external sort with about 1.4GB of temp files.  Why is that so?

Is my expectation that it should be able to sort in RAM unreasonable?

test=# set maintenance_work_mem to '5GB';
SET
test=# create table t1 as (select i::int, random() as f from generate_series(1, 50000000) i);
SELECT 50000000
test=# select pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 2111 MB
(1 row)

test=# create index on t1(f, i);
CREATE INDEX
test=# select pg_size_pretty(pg_relation_size('t1_f_i_idx'));
 pg_size_pretty
----------------
 1504 MB
(1 row)

In the server log:

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp22623.1", size 1073741824
STATEMENT:  create index on t1(f, i);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp22623.2", size 327622656
LOG:  external sort ended, 171065 disk blocks used: CPU 6.78s/268.73u sec elapsed 313.18 sec

Is there a way to calculate CREATE INDEX memory requirement before actually running it?

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation.  This created an artificial limit on the number of tuples which could be sorted in memory.  Once that limit was reached, it had to switch to a disk sort, even if there was memory left over.

This restriction was removed in version 9.4.


SSIS Promote Package - Move Some Stored Procedures I am trying to create an SSIS Package in Visual Studio that I can use when front end deployments are made.  Quick rundown -

copy prod DB to temp backup
move new/updated stored procedures from prod to test
run some queries to update prod tables with new version info, error messages, etc.
validate
drop temp backup if validation passes.

I'm stuck on #2... Is there a way I can compare PROD and TEST stored procedures for differences and update/add to PROD if there is a difference.  I don't want to have to add a SQL Task for each stored procedure for each deployment.  I'd also prefer to not wipe all stored procedures and replace them all...

This brings me to a related question, I have another SSIS package (runs nightly) that wipes TEST, recreates TEST from PROD, runs any table changes, then I manually add SQL tasks to the package for new/updated stored procedures one by one... is there a better way to go about it than manually adding each SP through a SQL task?

In both cases I'd like to be able to automatically identify and resolve the discrepancies of stored procedures between databases and resolve without having to manually add SQL Tasks for each new/altered procedure.

Have you looked at a third party tool such as SQL Compare from Redgate? This is designed to compare schemas and ease the process of deploying changes.
I don't think SSIS is the most appropriate tool for this task.


Stuck outside MySQL / XAMPP I have been using XAMPP (on Windows XP/XAMPP 1.7.4 /PHP 5.3.5) for quite a while now, and last morning I started to get some weird messages when I was developing my website. It happened yesterday and started as something I thought I could handle but apparently I couldn't. These messages were some about timeouts, some were about "can't connect" for my connection string inside my website.

I was thinking that the site might be under attack as last morning was pretty much like any other morning. I can actually start the admin (the button near the "start/stop" apache) page, but can't login to MySQL. I think root was not set, so I tried to use the documentation to reset it.

What happens now is that I just can't login into MySQL Server no matter what I do. I keep getting access denied for ''@'localhost' and 'root@localhost' if I try logging as root.

What can I do?

You need to restart mysql from the Linux command line like this:

service mysql restart --skip-grant-tables --skip-networking

If you are using Windows, add the two options in the mysqld section of my.ini:

[mysqld]
skip-grant-tables
skip-networking

and then run

net stop mysql (wait 10 seconds)
net start mysql

This will allow immedidate login without authentication and without any remoye connection sneaking in on you.

INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';

then

service mysql restart

At least you will have a localhost user named mynewuser with enough privileges to login.

You should then run this query:

select user,host,password from mysql.user;

and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:

DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;

If you are using Windows, please remember to remove skip-grants-tables and skip-networking from my.ini, then stop and start mysql service.

CAVEAT

Make a backup of /var/lib/mysql/mysql before you do anything.

UPDATE 2011-09-26 10:41 EDT

If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.

In order to know the correct INSERT query to user, please run this query:

desc mysql.user;

You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.

To get the exact columns to set to Y for all privileges, user this query:

SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G

This will present to columns to set regardless of the version of MySQL you are using.


Select from sys.server_file_audits returns wrong filename I activated an audit on a SQL Server 2016 istance.
Version i SP1 CU3, Enterprise Edition.
With this query:

SELECT * FROM sys.server_file_audits

I get as log_file_path: O:\auditfiles\
In log_file_name: logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit

But when I query:

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit',default,default);

It returns nothing.
Checking on file system the real name of the file is:

  logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7_0_131451051332700000.sqlaudit

Where the filename part _0_131451051332700000 is not retrived from query on sys schema.

When I select with correct filename i get the results.
I need to automate this in order to collect data from external queries.

How can I retrive the correct file name?

Reviewing the documentation for sys.server_file_audits, the log_file_name is (highlighting mine)

  Base name for the log file supplied in the CREATE AUDIT DDL. An
  incremental number is added to the base_log_name file as a suffix to
  create the log file name.

Depending on how quickly your sqlaudit files are being created, it can be extremely difficult to determine what the 'current' audit file is.

If you want a quick one-off select of audit information, you can use wildcards of '*.sqlaudit' to read ALL files in the directory.

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins*.sqlaudit',default,default);

Reading sqlaudit files that are currently being written to presents challenges in the fact that rows can be written to that active file immediately AFTER you have just read it, so you might miss future audit rows if you don't continually select them.  Also, be advised that audit files will continue to be written to your directory path and the wildcard select will run longer and longer unless you implement some kind of pruning process of the sqlaudit files.

If your intention is to track and save this audit information, I'd suggest implementing a solution similar to an answer I gave for 'How to load data to a table from SQL Server Audit File?'

It uses a Sql Agent job that executes a stored procedure to process the sqlaudit files.  In summary, the stored procedure:

Uses Powershell to move any audit files (that are not currently being
written to) to a staging fileshare. The powershell command uses the
-ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to
the staging fileshare. Eventually, the 'locked' files will become
available to be copied on a future interval.
Now that I have available SQLAudit files to read from the staging
fileshare, we're able to use sys.fn_get_audit_file to process all
SQLAudit files from the staging fileshare by using wildcards.
After consuming the files from the staging fileshare, I use
Powershell to delete the files from the staging fileshare.


Best data upload formats Most of our automatically generated files contain integers, decimals, date, time, and sometimes varchar. This is instrumentation data. Since we will be uploading those files directly to a SQL Server database, we were trying to decide what is the most efficient file format for automatic generation from our instrumentation.

What is the most efficient file format for uploading data to a SQL Server database? I thought it would be Text format but when I did an experiment with CSV, CSV tends to take less space and is also easier for users to analyze.

Data file size would vary from 50K to 250K. Only a few range in MBs.

I am working on creating an application that would upload data into the db. We use LabView for our instrumentation data, so that is where we want the standardized file format.

I am leaning towards CSV. I do not have much experience with XML, so I might consider it that if the advantages outweigh those of CSV by a lot. Readability is my concern only in terms of quality control. There might be cases where the user would want to change or fix some data before they upload. Speed is also important but with such small file sizes, I am not too worried about it.

Generally, CSVs are the smallest file size for non-binary (text) data. XML adds a great deal of xml codes that bloat the file size. So CSVs tend to import faster. But XML importers can handle exception cases, etc., with more complexity.

In my career, I've worked with instrument imports at four different kinds of laboratories. Where there was no direct LIMS (Laboratory Information Management System)-to-instrument interface system in place, we found that delimited text files are the fastest to write working import scripts around.

CSV is a comma-delimited text file. If your input data will include commas, then using a different delimiter may make more sense, like a pipe character for example: | A CSV file or pipe-delimited file generally follows the rule that each row/line of text from column 1 to the end-of-line carriage return represents one row of sample data in your database. Then within that row, each column of data in your database is separated from other columns by your delimiter character(s), which could be commas or pipes or anything you want to use.

Text files are great as long as the data is consistent over time.