Should I create a separate database table for temporary users I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These temporary users will have a token that times out, never to be used again. These tokens will likely outnumber the general users by a great degree. The question I have is whether these temporary users should be stored in the general table or in their own table.

My inclination is the same table as the userid is used elsewhere as foreign keys and is still useful for a temporary user. The uniqueness across the ids would be important. However, I'm not delighted that the user table will be filled with many records that never again need to be used and will thus slow down the table.

Another option I've considered is to create a user record, capture the id, delete the record and then use the id in another table. I therefore retain the uniqueness of the id but reduce the bloating of the table. I don't mind if the foreign keys reference different tables.

Anyone had a similar issue and have any thoughts?

Why don't you just store it in the main user table (keeping a unique ID will be a nightmare otherwise), and have an ACTIVE bit field that you include in your indexes (you can filter if you like).

It won't really slow down access to the table since your indexes will be able to very quickly sort by active/inactive but you also avoid the headaches caused by trying to "roll your own" identity field.


Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4 I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.

During the SQL Server upgrade process, everything showed green checks at the end (including Integration Services), so nothing to indicate that there was an issue with upgrading the Integration Services (or any other features or components for that matter).

After doing the necessary reboots, now the SQL Server Integration Services 12.0 service will not start.

The Event Viewer Application log shows the following for Source SQLISService120 (Event ID 260):

  Microsoft SSIS Service failed to start. Error: The SQL Server
  Integration Services service requires Integration Services to be
  installed by one of these editions of %SQL_PRODUCT_SHORT_NAME%:
  Standard, Enterprise, Developer, or Evaluation. To install Integration
  Services, run SQL Server Setup and select Integration Services.

I have a legacy 3rd party ETL solution that utilizes CLR and Service Broker that are hard-code dependent on the 12.0 Integration Service, so I need to get this running again as those ETLs are failing (despite having the newer SSIS version installed). Let's assume that I cannot change the application code, so I need to get this service running again.

I have already tried to use the SQL S014 (with Service Pack 2) installation media to try to install only the Integration Services feature (as suggested by the Event Viewer), but Integration Services is already checked and grayed out, preventing me from attempting to re-install it.

This 3rd party ETL solution throws the following error when they execute and fail:

  Integration Services evaluation period has expired.

However, I'm not sure if that is a misleading error because the 12.0 service is offline, or if it found the binaries but thinks it is expired?

I wasn't here when this was initially implemented 6 years ago in 2014, so I don't think I have a way to confirm if this environment started as Evaluation then was upgraded to Standard.

I have already tried changing the CommonFiles DWORD from 1 to 3 at  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\150\ConfigurationState, described in both "SSIS Evaluation Period Has Expired" on Dev Instance? and Microsoft guidance at https://support.microsoft.com/en-us/help/971268/evaluation-period-has-expired-error-message-when-working-with-sql-serv described in Case 3 Option 2 section and re-running the SQL 2019 Upgrade Edition to try to get it to Standard Edition.

Not sure if this is a clue, but following the question posed by the answer in this post https://stackoverflow.com/questions/527839/ssis-package-fails-on-execute-integration-services-evaluation-period-has-expire/528479#528479 :

  Check what version of DTEXEC you are using as default (start-run: cmd, dtexec)

When I do dtexec from a cmd prompt, it says it's Version 12.0.5659.1 for 64-bit which seems to indicate the default dtexec is the old one.

Is there a way to change the default dtexec to be the new SQL 2019 version and perhaps that will allow the 3rd party ETLs to work (without needing the IS 12.0 service)?

Otherwise:

What other options or ways do I have to get the Integration Services 12.0 service to start successfully so that I can run these legacy ETLs?

I was able to successfully start the Integration Services 12.0 service after running a Repair using the SQL Server 2014 Installation Media. I then installed 2014 SP3 and 2014 SP3 CU4 to get it back to where it was before the upgrade, and all is well now.


MongoDB permission to view collections not working I have 2 dbs:

admin
ops

I have a user with a readWriteAnyDatabase role but he cannot list the collections in the ops db.

I have another user with a root role and he succeeded in listing the collections for the ops db.

I try to view the collections in ops

The error I am getting:

  Not authorized to execute command: listCollections...

using mongodb: 3.4.7

In the mongoDB config I only added auto=true.

What can be the cause?

You need to give that "readWriteAnyDatabase" role to the user for "admin" database.

use admin
db.createUser({user:"test", pwd:"test", roles: ["readWriteAnyDatabase"]})

after that you can login with that user and see all collections

mongo -u test -p test --authenticationDatabase admin opt
Mongo> db
opt
Mongo> show collections
test_collection
Mongo>


SQL server - parsing element of delemiated string I have delimited string like this 'test1:test2:test3:test4:test5'.

I need to get the third and forth values one value at a time. The string always has five values.

You can take a SUBSTRING of your string to ignore the first element, then use PARSENAME. Then, you don't have to use a function.

declare @var varchar(64) = 'test1:test2:test3:test4:test5'

select
    ThirdElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),3)
    ,FourthElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),2)


Vertica DB user permissions becomes active only after connection is closed I am using Vertica Analytic Database v7.1.1-12. I faced the issue then I am granting permissions to the role and then assigning role to the user and the permissions are finally granted only after I close the connection. Is there any way to flush privileges or something like that?

To enable the newly granted role you need to issue the command SET ROLE mynewrole in that user's session. If you need this role to be effective every time you may want to change the user's default role: ALTER USER ... DEFAULT ROLE ....

References:

Enabling roles
Changing default role


Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load.

It took me 15 minutes to loaded 2 million rows, a performance I thought was disappointing for the LOAD DATA INFILE. My statement looks like this

  LOAD DATA LOCAL INFILE 'path/file.csv'
  INTO TABLE table FIELDS
  TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES (column1, column2, etc);

I'll bet that you are currently I/O bound.  This means that nothing can speed it up.  (And Rolando's suggestions may be futile.)

Let's look deeper.  Is this LOAD a recurring task?  If so, how often?  Is everything blocked waiting for table to be reloaded?  Simple solution:  Load into a different table, then do a double RENAME TABLE to swap it in.  Only milliseconds of downtime.

Is the data coming from another machine?  Use the network for the "input" side of the LOAD rather than having the one disk fighting for reads versus writes.

Do you have a lot of indexes?  There are several directions to take this question.  Let's see SHOW CREATE TABLE before barking up these tree(s).

Does the entire load need to be a single transaction?  Multiple transactions may be faster because of not overflowing the log file.  (I've seen 2x.)


MySQL return rows ONLY if ALL the rows in another table have a specified value I have two tables
Table1 :
efd_details( id )

Table2 :
efd_detail_status( detail_id  , status )

Now the data as following :
select * from efd_details;

Output :
ID
1
2
3
4
5

select * from efd_detail_status ;

Output :
DETAIL_ID | STATUS
1         |   's'
2         |   's'
3         |   's'
4         |   null
5         |   'f'

What I need to do is get all the details (efd_details) ONLY if ALL the statuses in efd_detail_status = 's'. In the example above, I would retrieve ZERO records since two of the status are != 's'. If ALL the status = 's' then return ALL rows from cms_efd.
I have tried variations of EXISTS and the following:
select e.*
  from cms.cms_efd e
where exists(select 1
               from cms.cms_efd_status s
              where s.detail_record_id = e.detail_record_id
                and IFNULL(s.contrib_calc_status,'f') != 's'
             having count(e.detail_record_id)=count(s.detail_record_id))
    and e.earnings_file_id = 400676;

Appreciate the help.
-Branden

Turn it around and do something like
WHERE NOT EXISTS ( SELECT 1 FROM cms.cms_efd_status
                   WHERE ... != ... )
  AND e.earnings_file_id = 400676


Including clustered index key in non-clustered index We're running SQL Server 2008.  In looking over the results of a query I ran on sys.dm_db_missing_index_* views, I see a curious suggestion for an included column.  I have the following two tables:

create table foo (
    id int not null identity(1,1) primary key
    /* many more fields, not relevant to question */
)

create table bar (
    id int not null identity(1,1) primary key
    foo_id int not null,
    param_name nvarchar(50),
    param_value nvarchar(255)
)

The table foo has a 1:M relationship with bar; bar is used to store miscellany associated with some of the records in foo.

Back to the sys.dm_db_missing_index_* query.  Several times a day, the optimizer is looking for an index on bar with foo_id in equality_columns and id in the included_columns.  My question is, what is the point of including the table's clustered index key in a non-clustered index?  Since the leaf level of the non-clustered index includes the clustered index's key value anyway, isn't it redundant to include the clustered index key in a non-clustered index?

EDIT:  The only existing index on bar is the clustered index with the PK as the key.

Thanks in advance.

Yes, it is redundant.

On the other hand, it won't hurt anything (since the optimizer will ignore it and not add the data twice).

This is one of the many reasons not to take the suggested indexes at face value.


how to list all features installed in a sql server instance? There are other Installation related questions with similar details here and here.

I could not find anything related to listing the installed features in sql server 2016 though.

I can do it manually looking at the logs - as per the info below, but I would like to automate it.

After installing SQL Server 2016 I get a log file - located somewhere in these folders:

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20200109_205540

A file which the name starts with Summary_ +my_server_name

in that file, amongst other things I can find a list of the Sql Server Features installed:

After installation, later on when the systems are already in use, logins and permissions applied, firewall fixed, etc.

Is there a simple way to get hold of the list of features installed on a sql server instance?

You can run "Installed SQL Server features discovery report"

It is documented there: Validate a SQL Server Installation

Some examples: how-to-find-out-what-sql-features-are-installed


Tuning Java Pool after getting ORA-04031 I'm running Oracle 12.2.0.1 Standard edition on Windows 2012 RC2.
I'm NOT running Oracle's Automatic Memory Management.

We are seeing these in the alert log:

ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","prv//////LBKLAGIMAAAAAAAA","JOXLE^aff7380e",":SGAClass")

First thing I thought to try was flushing the shared pool to resolve the immediate problem, while the dev team is fixing the SQL code, so I ran:

ALTER SYSTEM FLUSH SHARED_POOL;

Only then did I realise that the Java Pool isn't actually part of the shared pool. So I tried to take a look at the Java pool.

I think this tells me that my Java pool is roughly 32MB:

SQL> select * from v$javapool;

CATEGORY                                            MEMUSED     CON_ID
------------------------------------------------ ---------- ----------
:Intern                                              973440          0
:SGAClass                                          32103240          0
:SROState                                             81120          0

I ran this tuning tool, which if I can interpret correctly, says that there's not much benefit from increasing the Java pool size:

    select
       estd_lc_size,
       java_pool_size_for_estimate,
       java_pool_size_factor
    from
       v$java_pool_advice;

ESTD_LC_SIZE JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR
------------ --------------------------- ---------------------
           8                          16                    .5
          24                          32                     1
          25                          48                   1.5
          25                          64                     2
          25                          80                   2.5
          25                          96                     3
          25                         112                   3.5
          25                         128                     4
          25                         144                   4.5
          25                         160                     5
          25                         176                   5.5

I ran the following query taken from the Oracle docs here and it seems to suggest that the SGA_TARGET value is fine!

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

SGA_TARGET
----------
5167382528 = 4928 MB i.e. same as what it currently is!

I can see that:

    MEMORY_TARGET = 0
    SGA_MAX_SIZE = 4928M
    SGA_TARGET = 4928M
    JAVA_POOL_SIZE = 0
    SHARED_POOL_SIZE = 0
    DB_CACHE_SIZE = 0
    DB_KEEP_CACHE_SIZE = 0
    MEMORY_MAX_TARGET = 0

...so I'm bit confused how to tune this when we're not using AMM.

So how to resolve this issue?

is my best option to increase the Java pool size?
if so, how to do this when not using AMM?

With SGA_TARGET set and JAVA_POOL_SIZE=0 it you are at least using Automatic Shared Memory Management, so the Java Pool is already self-tuning. You can specify a JAVA_POOL_SIZE value that will become the minimum pool size and Oracle may choose to tune it higher if necessary. Changing the JAVA_POOL_SIZE will require a restart of the database. You may need to increase the SGA_TARGET value as well, as increasing the default JAVA_POOL_SIZE will take memory away from other SGA pools.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/JAVA_POOL_SIZE.html#GUID-F24C4770-0B7C-42BD-9AAD-0EC1E92A90F6


Deny drop permissions for all other users except actual administrator - SQL Server 2008 I have a database "myDB" running in a Windows Server machine. Users (coming under Administrators group) remotely access this server and log-in to this database using Windows Authentication mechanism.

I want to deny the drop object permissions for all these users, except for the actual Administrator in this machine

P.S - All the users whose drop permission is to be revoked comes under the Administrators user group.

Use a DDL trigger to prevent the drop if the login name doesn't match what you expect. Modify the database name and login name as appropriate.

USE myDB
GO

CREATE TRIGGER no_drop ON DATABASE FOR DROP_TABLE
AS
IF SUSER_SNAME() != 'DOMAIN\username'
BEGIN
    RAISERROR('No table dropping.', 16, 1)
    ROLLBACK TRANSACTION
END


Where MySQL is taking time either in disk read or computation? From SQl case study

System configuration is

Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G)

Query is select yeard, count(*) from ontime group by yeard where yeard is indexed column

The query is simple, however, it will have to scan 150M rows. Here is the results of the query (cached):
The query took 54 seconds and utilized only 1 CPU core

My understanding :- Per my calculation it should have finished in much less time based on above system shared configuration and data to scan.
I know I am wrong and missing something but what is that ?

Here is my calculation for seconds

For HDD,  average time taken to read 100MB of data from disk is around 1 sec. For SSD is 5 to 10X faster. But still conservatively if I assume the speed of
100MB per seconds , time to  read the data will be size of data in MB/100 seconds . Based on this size of data is 150 * 10^6 * 4/10^6= 600 MB
assuming each year is of 4 byte long. So total time to read the data from disk should be 600/100 = 6 secs
Now it 2.13GHz CPU which means that 2 billion cycles per seconds which on average means 2 billion instruction per second per core. Now  actual
time taken to execute the query was 54 seconds which means time taken to compute the instruction was approx 54-6 = 48 seconds. Does it mean to  it had to execute  around 48 * 2 = 96 billion instructions just to calculate
the count and group by year or am I missing something ?

May be more time in disk read or count looks simple but internally it involves number of instructions ?

Rule of Thumb:  If the necessary data is cached, the query will run 10x faster than if not.  I have seen this in a wide variety of queries.  (Of course, the "10" varies a lot, too.)

Assuming

 SELECT yeard, COUNT(*) FROM t GROUP BY yeard;

 INDEX(yeard)

The Optimizer will scan the entire INDEX(yeard) of 150M 'rows'.  It does not have to do much more (at a high level) than count rows until yeard changes.  That is the INDEX and the GROUP BY work well together; they are not two separate steps.

About 400 index entries per 16KB block.
100 blocks/second is a more realistic estimate for InnoDB hitting HDD.  (This goes away for any blocks that are cached.)
150M/400/100 = 3750 seconds.  So, 54s for SSD seems about optimistic.  Or some of the blocks were cached.

If you run the query a second time (after all the blocks are cached), it may run 10x faster.

It's hard to say how many CPU cycles a query will take.  The code to "get the next row" (even of an index) is somewhat generic, and meanwhile, it is building (probably) an in-memory hash of the results.  There are also steps to parse the query, decide how to optimize it, deliver the results, etc.

The EXPLAIN in this example will say "Using index", meaning that it only used the index's BTree and did not need to touch the data's BTree.

The BTree is really a B+Tree, meaning that consecutive blocks are linked, making linear scans (as with your query) efficient.


Using unnest in multiple rows I have an array column containing information like

{a,b,c,a}
{a,b,a,b}
{b,a,a,b}

I want to run unnest on this array column, get the occurence of of each character.

a 2
b 1
c 1
a 2
b 2
b 2
a 2

I run the following query which works when considered for one row:

SELECT i, count(*) AS count
FROM   unnest(array::text[]) i
GROUP  BY i
ORDER  BY i;

You can use the query below;

SELECT
  unnest(i),
  count(*)
FROM (
  VALUES
    ('{a,b,c,a}'::text[]),
    ('{a,b,a,b}'),
    ('{b,a,a,b}')
) AS t(i)
GROUP BY 1, i   -- group by column value, you can use any unique field here
ORDER BY i


Deploying and running SSIS packages to SQL Server with protection level EncryptSensitiveWithPassword I have an SSIS package that connects to both a SQL Server instance and a MySQL instance so it can feed data from the SQL Server to the MySQL instance. When I deployed this package to the SQL Server and ran it from SSMS, everything went fine. When I tried to run this package as a Job where it was being run by the SQL Server Agent Service Account, it broke. I found out that this was because I was using EncryptSensitiveWithUserKey as the package protection level, and that it will always fail unless I am the one running it. Okay.

So among the other protection levels, I am instead trying to use EncryptSensitveWithPassword. In my SSDT that I run from Visual Studio, I do not have ServerStorage as an option for a protection level.

This opens up a different problem for me. Once I deploy the package to SQL Server and run it using SSMS, How do I insert the password to the job? I have seen the command line as an option. Is this the only way to do it in SSMS?

If I require the command line being used as an option, then it requires the package source be the SSIS Package Store rather than the SSIS Catalog. This ends up being a problem if I get 'Class not registered' errors when trying to connect SSMS to Integration Servers — but this is beside the point.

Having had only several weeks of exposure to SSIS, the underlying conceptual question I am wondering is, what are my options for (and how do I go about) getting a MySQL connection enabled in an SSIS package on SQL Server for a user other than myself?

(FYI, I am using SQL Server 2014 and it has Integration Services 2005 on it, and I understand the version mismatch causes the 'Class not registered' errors.)

Addition: Is there any completely different ways to supply a datasource password to a package run by the SQL Server Agent? If I choose DontEncryptSensitive as the protection level, is there a way to supply the datasource password within the job step properties?

You can try following:

With EncryptSensitiveWithUserKey - Deploy the project with the same ID through which the agent job will run (I always use SSIS Proxy account created). Also, change the Job owner to same ID or SysAdmin.
You can try to execute the package through T-SQL code (there are stored procedures for this).
Try the command line option in agent to execute the dtexec to run the package.


Snapshot on Always On Availability Group in suspect mode Our configuration is 2 SQL Servers 2016, enterprise edition, 13.0.5026.0, running on Windows Server 2012 R2.
Created availability group with one database in it.
Database has 3 data files in it, all together around 2,5 TB in size
We are creating 2xsnapshots on the database on secondary replica.

After some time our snapshots are turning into suspect state with following errors recorded in the error log:

Error: 3420, Severity: 21, State: 1.
G:\Snapshots01\Data\Data1.ss: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
Error: 17053, Severity: 16, State: 1.
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x0000a8c4c60000 in file 'G:\Snapshots01\Data\Data1.ss'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I've checked snapshot file sizes with following query:

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],sd.name AS [Snapshot],
mf.name AS [Filename],size_on_disk_bytes/1024 AS [size_on_disk (KB)],mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd ON mf.database_id = sd.database_id
JOIN sys.master_files mf2 ON sd.source_database_id = mf2.database_id AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1AND mf2.is_sparse = 0

and it seems that snapshot is broken when the largest snapshot file reaches around 60GB.
Snapsot drive is 1,35 TB, formatted with NTFS 64KB.

What can we do to avoid errors like this?
Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?

Operating system error 665(The requested operation could not be completed due to a file system limitation)

This is a common issue with large sparse files as it causes NTFS metadata fragmentation. You're not hitting a SQL Server issue, you're hitting (as the error states) a filesystem limitation (NTFS).

  What can we do to avoid errors like this?

Not use snapshots. Not use NTFS (ReFS instead).

  Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?

Yes, changing to ReFS should alleviate the issue, assuming you actually need the snapshots. Changing less data in the source database would also help alleviate the issue.

No, there aren't really any other NTFS settings that can be changed.


Is it possible to block a specific query, or return a static result? Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on every page, is absolutely murdering the database.

SELECT
    SUM(amountin-amountout)
FROM tblaccounts
WHERE invoiceid IN (
                    SELECT
                      id
                    FROM tblinvoices
                    WHERE
                      userid=*userid* AND status='Unpaid'
                   )

This generates the logged-in user's total unpaid invoice sum. Problem is, due to the nested queries, each pageload is running over 450k queries (the amount of rows in tblaccounts), each of which is filtering over 750k rows (the amount of rows in tblinvoices).

Can this be blocked at the database server level? Or ideally, set to return a specific value?

Server version: 5.5.41-0+wheezy1-log - (Debian)
Protocol version: 10

The tables are using the MyISAM engine. Here are the CREATE TABLE statements:

tblaccounts

CREATE TABLE `tblaccounts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `currency` int(10) NOT NULL,
  `gateway` text NOT NULL,
  `date` datetime DEFAULT NULL,
  `description` text NOT NULL,
  `amountin` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fees` decimal(10,2) NOT NULL DEFAULT '0.00',
  `amountout` decimal(10,2) NOT NULL DEFAULT '0.00',
  `rate` decimal(10,5) NOT NULL DEFAULT '1.00000',
  `transid` text NOT NULL,
  `invoiceid` int(10) NOT NULL DEFAULT '0',
  `refundid` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `invoiceid` (`invoiceid`),
  KEY `userid` (`userid`),
  KEY `date` (`date`),
  KEY `transid` (`transid`(32))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

tblinvoices

CREATE TABLE `tblinvoices` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `invoicenum` text NOT NULL,
  `date` date DEFAULT NULL,
  `duedate` date DEFAULT NULL,
  `datepaid` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subtotal` decimal(10,2) NOT NULL,
  `credit` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `tax2` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `taxrate` decimal(10,2) NOT NULL,
  `taxrate2` decimal(10,2) NOT NULL,
  `status` text NOT NULL,
  `paymentmethod` text NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `status` (`status`(3)),
  KEY `userid-status` (`userid`,`status`(3))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

As far as I can tell the answer to this question is no.  You can't block this SELECT at the database level, or rewrite it in versions before 5.7.6 (dev milestone 16 for the 5.7 release).

If the offending statement were an UPDATE, INSERT, or DELETE then you could use the tactics described in this StackOverflow answer to nullify the statement via a trigger.

As established in this answer (also on StackOverflow), you can't put a trigger on a SELECT statement.

Given the above is true, you will have to do one of three things:

Put a proxy like MySQL Proxy (that proxy was discovered by Dr. McKay) between the database and the application that will replace the query and return a cached/static result as suggested by jkavalik initially.
Wait for your support ticket to work it's way through WHMCS's bug system.
Figure out how to optimize this query until it isn't a problem anymore or option 1/2 are in place.

We can't help with option 1 or 2, but we can help you optimize your query.  Optimizing the query may bring the execution time down from tenfold, which will lessen your organization's pain, and make you look like a hero.  I vote you take this route while you are waiting for the bug ticket if you can't install the proxy or get it working.


Full outer join problems I'm trying to create an xml with customer specific prices for a webshop I am working on atm.

I have to join data from 6 tables in order to acomplish that.

I ran into a problem I am unable to solve for a couple of days now.

The thing is that I join customer ID with discount group (works fine),

then I join it with data that are stored in another table that connects item groups with customer discount groups (this table is not complete - it has data if someone entered discount for specific customer for specific group of items).
The end result lacks all items from item groups that have no data in this table.

then I join it with item discount group table where most items have item discount group written in it.

then i tried to full outer join it with a table of items that have webshop tag enabled (so I would get all items in the result that are included in the webshop but have no discounts for a customer entered) - this thing does not work

lastly I cross apply latest prices with items. (works fine)

Any help would be gravely appreciated.

Code:

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and osnArtikel.SpletObjava='D'
and stranka.StevilkaSifranta=2
and strFaktura.StevilkaSifranta=2
and popust.StevilkaSifranta=2
and artProdaja.StevilkaSifranta=2
and strFaktura.SifraGrucePopusti<>''
order by stranka.SifraStranke, osnArtikel.SifraArtikla

The problem is in your WHERE clause.  This piece of code in your WHERE clause for example.

and osnArtikel.SpletObjava='D'

is going to restrict your output whenever osnArtikel.SpletObjava is NULL.  Including whenever there wasn't a match on it.  (your OUTER results)

One method to fix it is to move your restrictions into the ON clause.  Not generally something you want to do since the ON clause is a JOIN but with OUTER JOINs it's not unreasonable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
    and strFaktura.StevilkaSifranta=2
    and strFaktura.SifraGrucePopusti<>''
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
    and popust.StevilkaSifranta=2
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
    and artProdaja.StevilkaSifranta=2
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla
    and osnArtikel.SpletObjava='D'

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Another method is to include checks for the column being NULL in your WHERE clause.  This may or may not be the way you want to go because if the column  you are checking is also nullable you could end up with some unintended results.  The best method is to check if a non-null column (primary key column for example) is nullable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
and (osnArtikel.PrimaryKeyColumn IS NULL
        OR osnArtikel.SpletObjava='D' )
and (strFaktura.PrimaryKeyColumn IS NULL
    OR (strFaktura.StevilkaSifranta=2
        and strFaktura.SifraGrucePopusti<>'')
    )
and (popust.PrimaryKeyColumn IS NULL
    OR popust.StevilkaSifranta=2)
and (artProdaja.PrimaryKeyColumn IS NULL
    OR artProdaja.StevilkaSifranta=2)
order by stranka.SifraStranke, osnArtikel.SifraArtikla


scramble the value of one column and unscramble it? I want to change all the values in one column, as it is a production database and I don't want to give the data to testers without obfuscation.

Example:

Name             Name
----     -->     ----
ABC              BCD
PQR              QRS

I have searched scrambling and other things but my issue is I want to change the obfuscated data back to their original values when needed.

I need a generic script so I can apply it to an arbitrary table if required.

There are plenty of generic data masking script, but the only problem is that no one understands your data better than you.

You have to write your own masking script based on what data you consider sensitive.

When you do a backup/restore of PROD data on UAT/QC environments for testing, its advisable to mask the data with dummy data. Masking of data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.

You can even log what data is going to change in tables with different schema. For e.g

If person.Employee table is going to be masked, then the before value can be written to maskPerson.Employee table. This way, you will have before values as well. (dont really need this as in UAT or QC you dont care of the before value, which is PROD value you are masking).

Some good references :

Data Masking Script
Obfuscating your SQL Server Data
Data Generator for SQL SERVER 2000,2005 and MSDE

I found this connect item wherein Microsoft is going to provide a data masking feature in future release ... (still could not make it for 2012).


SQL Server: what additional permissions for EXECUTE for implicit result codes? Tested on: 2014+SP2+CU7, 2016+SP1+CU5, 2016+SP2

Short description: User with EXECUTE permission still gets

  Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

when successfully EXECuting the stored procedure (example below).

We are finally moving application access from db_owner to a more limited role. Everything has been fine with the user as a member of db_owner. We crated a new role db_webAppAccess and granted SELECT, UPDATE, DELETE, and EXECUTE. The problem occurs when the user executes most if not all of our stored procedures. The procedure runs, but at the very end fails with

  Msg 15151, Level 16, State 1, Procedure sp_fakeProcedureName, Line 14 [Batch Start Line 2]
  Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

We know it executes because when performed in SSMS the expected data is returned. Also, if we cope the contents of the stored procedure and put it into SSMS it executes without error.

Now to the confusing part: if we add an explicit return value, the error disappears. Ex add return(0) as the line of the SP below. Sadly, we have a few hundred stored procedures that would need to be examined and set to have explicit return values in all cases.

I am at an absolute loss on exactly what is different in executing a stored procedure when there is and is not an explicitly defined return code. Is there some additional grant I can give to allow access to the result codes?

Thank you for any help you can provide!

CREATE PROCEDURE [dbo].[sp_fakeProcedureName]
AS
BEGIN
  SET NOCOUNT ON;

  SELECT    fakeTablePKIdentity, column2, column3, column4
  FROM fakeTable
  ORDER BY column2
END

GRANT EXEC ON [sp_fakeProcedureName] TO PUBLIC
GO

Here is the procedure I used to setup the role and user.

USE [fakeDatabse]
GO

CREATE USER [usr_fakeUser] FOR LOGIN [DOMAIN\fakeUser] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE ROLE [db_fakeRole]
GRANT CONNECT TO [db_fakeRole] AS [dbo]
GRANT DELETE TO [db_fakeRole] AS [dbo]
GRANT EXECUTE TO [db_fakeRole] AS[dbo]
GRANT INSERT TO [db_fakeRole] AS [dbo]
GRANT SELECT TO [db_fakeRole] AS [dbo]
GRANT UPDATE TO [db_fakeRole] AS [dbo]

ALTER ROLE [db_fakeRole] ADD MEMBER [usr_fakeUser]
GO

What has happened here is that you have inadvertently included the GRANT statement in your stored procedure definition.

Most people assume that the BEGIN/END block contains the definition of the stored procedure, but it is actually through the end of the batch in which the CREATE PROCEDURE statement is executed (i.e.: the next GO or the end of the script, whichever comes first.)

In my experience, it is common to omit a GO between the stored procedure definition and the following GRANT statements in build scripts. I've done it myself! And it often goes unnoticed because a lot of people run their apps with db_owner role membership, and db_owner can execute the GRANT with no problem.

So, simply remove the GRANT from the procedure definition, and handle permissions in a separate batch or script:

CREATE PROCEDURE [dbo].[sp_fakeProcedureName]
AS
BEGIN
  SET NOCOUNT ON;

  SELECT    fakeTablePKIdentity, column2, column3, column4
  FROM fakeTable
  ORDER BY column2
END
GO


What are the downsides of using PARTITION for multi-tenant access? We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores.  Let's say we have 1MB per store and 10k stores so we're talking about 10GB of data.

We're making some changes to how the system works in general and rethinking some of the pieces.  One possibility that came up is to create partitions for each store so they will be completely isolated.  It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.

A few notes, the queries are always done on a per-store level - for this data set we don't need querying across stores.  Most of the stores will be active most of the time - so this isn't like historical data where old partitions won't be written to.  There isn't another dimension like time that would make sense to partition on.

Is there anything we should be concern about with a partition/schema per store or any areas that might be a problem?

UPDATE:
I found some more references to this:

https://stackoverflow.com/questions/44524364/postgresqls-schemas-for-multi-tenant-applications
https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/
https://www.quora.com/What-are-the-pros-and-cons-of-using-schemas-to-support-multi-tenancy-in-PostgreSQL-How-scalable-is-it

Another (perhaps important) detail I forgot to add is that the individual terminals at each store have their own copy of the database (and sometimes multiple terminals/databases per location) so that can continue to run completely offline for extended periods of time.  It's an asynchronous master-master data-level replication - writes can happen via an app to the cloud or on the terminals and we handle the syncing and conflict resolution.

The thought to migrate to a schema per store relates more to simplicity in the replication and backup logic to treat each store as an independent entity.  It also could make it easier to scale because we can more easily move stores to different cloud databases.  It also might help us with migrations if we could make schema changes per store as the upgrade to new versions (not sure if/how this would work with PG partitions.) It's not related to security concerns.

10k stores so we're talking about 10GB of data. One possibility that came up is to create partitions for each store so they will be completely isolated. It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.

10,000 partitions is insanity. Partitions are done for performance not for security. Their use has planner, index, and underlying file-system implications. There is absolutely no reason to ever do that. You can however create 10,000 users, or authenticate against LDAP and manage security like that. For more information see this, and examples.

https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html

Ultimately, I probably wouldn't do either unless I absolutely needed too. It's easier to design abstracted secure app-level interfaces (database-models/middleware etc) to authenticate and to fly with that. I've seen row-level permissions used in production code, but typically that's for a class of data access.

For example. Perhaps your application has two levels of access one for unauthenticated forward-facing web servers; and, one for on-premise access. In this case, you can ensure with pg_hba that admins are on site (or are otherwise hackers clever enough to have compromised on-site equipment).


Insert Array of JSON into Postgres Table PART 1:

I am using Postgres 9.5, and I am trying to figure out how to INSERT into a postgres table using an array of JSON. I have created a table with the following commands:

CREATE TABLE inputtable (
data_point_id SERIAL PRIMARY KEY NOT NULL,
chart_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
properties jsonb NOT NULL
);

The json data format looks like this:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three}
]

Due to some changes, I now must be able to insert a json object per row based on user_id. In the end, I want the output to look like this:

data_point_id  | chart_id | user_id |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 1, "col3": 1, "col4": "one"}
    2          |    1     |     1    | { "col1": "b", "col2": 2, "col3": 2, "col4": "two"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "three"}

I tried to INSERT the data into the table by using unnest, where :

INSERT INTO inputtable (user_id, chart_id, properties)
    SELECT (1, 1, unnest('{ "col1": "a", "col2": 1, "col3": 1, "col4": "one"},{ "col1": "b", "col2": 2, "col3": 2, "col4": "two"},{ "col1": "c", "col2": 3, "col3": 3, "col4": "three"}')::json)

but I get an error about type ERROR:  could not determine polymorphic type because input has type "unknown".

PART 2:

I would also like to know how a table like this can be updated. For example, I make changes to the data point, in the JSON format, and want to change the properties, and I expect an output of:

data_point_id  | chart_id | user_id  |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 6, "col3": 7, "col4": "eight"}
    2          |    1     |     1    | { "col1": "b", "col2": 10, "col3": 11, "col4": "twelve"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "new"}

Using data like so:

[
    { col1: a, col2: 6, col3: 7, col4: eight},
    { col1: b, col2: 10, col3: 11, col4: twelve},
    { col1: c, col2: 3, col3: 3, col4: new}
]

How do can this be accomplished? I am thinking the part 2 question can be solved by jsonb_populate_recordset, but I am not sure. Using Postgres for JSON is new to me, but it looks very powerful and I appreciate the assistance in helping me figure this out!

PART1 - INSERTING

You don't have to use unnest() but jsonb_array_elements() and add square brackets to the JSON data structure. I sugget you using a JSON validator website like JSONlint to test the correctness of your JSON data.

This codes inserts 3 new records in inputtable:

WITH json_array AS (
    SELECT 1 AS user_id,
           2 AS chart_id,
           jsonb_array_elements('
               [
                 {
                    "col1": "a",
                    "col2": 1,
                    "col3": 1,
                    "col4": "one"
                 }, {
                    "col1": "b",
                    "col2": 2,
                    "col3": 2,
                    "col4": "two"
                 }, {
                    "col1": "c",
                    "col2": 3,
                    "col3": 3,
                    "col4": "three"
                }
               ]'::jsonb) AS properties
)
INSERT INTO inputtable (user_id, chart_id, properties)
SELECT * FROM json_array

PART2 - UPDATING

In order to update you must specify data_point_id values, so you have to know them a priori.

This works perfectly, but probably there are other naive solutions:

WITH update_table AS(
   SELECT unnest(ARRAY[1, 2, 3]) AS data_point_id,
          jsonb_array_elements('
            [
              {
                "col1": "a",
                "col2": 6,
                "col3": 7,
                "col4": "eight"
              }, {
                "col1": "b",
                "col2": 10,
                "col3": 11,
                "col4": "twelve"
              }, {
                "col1": "c",
                "col2": 3,
                "col3": 3,
                "col4": "new"
              }
           ]'::jsonb) AS properties
    FROM inputtable
)
UPDATE inputtable
SET properties = update_table.properties
FROM update_table
WHERE inputtable.data_point_id = update_table.data_point_id


Hash Match in Query has high cost and high number of excecutions I have the following query:

      declare @jobID NVARCHAR(20)
    declare @dataGroup BIGINT
    declare @lastSyncVer BIGINT

       set @jobid='P-0001'
       set @dataGroup= 5637144576
       set @lastSyncVer = 316244734

SELECT us.[DATASTOREID], us.[UPLOADSESSIONID], us.[UPLOADPATH], us.[STATUS], us.[DATEUPLOADED], us.[DATECREATED], us.[CHECKSUM], us.[FILESIZE], us.[ID], us.[RERUNFOR]
        FROM [dbo].[UPLOADSESSION] us
            INNER JOIN CHANGETABLE(CHANGES [dbo].[UPLOADSESSION], @lastSyncVer) AS CT ON us.[ID] = CT.[ID]
            INNER JOIN [dbo].[DATASTORE] ds ON us.[DATASTOREID] = ds.[ID]
            INNER JOIN [dbo].[DATAGROUP] dg ON ds.[DATAGROUPID] = dg.[ID]
        WHERE dg.[ID] = @dataGroup
          AND us.[JOBID] = @jobID
                AND us.[RERUN] = 0
        ORDER BY us.[ID]

When I run a query cost analyze of the query I see that the Hash Match takes up 83% of the cost. I also see that the hash match has a really high number of executions(over 800k). The main problem with the query is that it is very slow. I have let it run for over 40 minutes, and it still didn't finish. The query is written by Microsoft, and is part of their functionally in their ERP system Microsoft Dynamics AX. Can anyone come up with some ideas how we can speed things up? This query used to take under 30 seconds.

Perhaps the problem isn't in the query itself, but in other problems with the DB? I have tried updating stats.

If it used to run in 30 seconds, that means that something changed: you have to identify what that something is and fix it.

That something is most probably the query execution plan. There must be a better execution plan than the one that you're getting now, but the optimizer doesn't feel like giving you that plan any more. The reason can be only one: outdated statistics.

Statistics become stale when you modify data in your tables and from time to time you need to update them. SQL Server will try to update statistics automatically when the number of changed rows exceeds a threshold that can be either static (20%) or adaptive (needs TF 2371 turned on). Either way, it's impossible to control the number of rows that SQL Server will scan to compile the statistics. This is particularly problematic when the distribution of the data is not uniform and you have skewed data. In that case, you'd better disable auto update statistics for the particular table/index and make sure that you update your statistics manually with FULLSCAN.

Another possible reason for getting an inefficient plan is parameter sniffing, but the reasoning behind is the same: your plan gets compiled for a non representative set of parameters, that determine  a performance nightmare when the plan is compiled for a more representative set of parameters. In this case, OPTIMIZE FOR hints might help, but usually fixing the stats is a better idea.

That said, if it used to run in 30 seconds you might still have the efficient plan lying around somewhere in the cache: try to find it in sys.dm_exec_cached_plans and compare it with the plan that you are getting now.


SQL Server 2008 R2: From a SQL Server Log file, how do I find out what is causing an event Problem:

The attached image is a screen shot of the SQL Server log showing an event that happens every Tuesday at around 1:20 AM. This event is causing the transaction log backup chain to break and all subsequent transaction log backups to fail.

Question:

How can I find out what is causing/doing this?

For the purpose of not leaving this question open and in hopes that it helps someone in the future, I wanted to detail the steps I followed to solve my problem.
In following the advice given about running a trace around the time the Recovery model is changed, I found that there was indeed a task that was shrinking the log.

Summary

Using SQL Server Profiler trace allowed me to find out what was causing the problem.


How to handle "many columns" in OLAP RDBMS I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it.

I tried to design a star schema, but I'm not sure how to handle this many columns. Normalising it sounds wrong, but I can't just have flat columns either. The combination of attributes are also too diverse to have a simple dimension table for this, even if I'd reduce the numerical values into categories (ranges), which is an option. I thought about storing them as XML or JSON for each row, but that doesn't sound great either.

If it helps, I'm planning to use Amazon's redshift for the DB.

Note: We have strong preference for RedShift as it fits perfectly for at least other few operations we do on this data. Hence I want to avoid other technologies like HBase if possible.

Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns.

Consider MonetDB, which allows for a practically unlimited number of columns.

Redshift maxes out at 1600 columns.

Another option would be to use Principal Component Analysis and only choose the top 1600 components, but that can make interpretation difficut.

Another option would be to use Postgres and store the tokenized strings or n-grams in a string array field, but your OLAP server would need to support that.


Oracle, Stored and External procedures Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures.

Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...) ?

Stored Procedures and External Procedure are distinct ones.

Stored procedures are stored in database and used from there. You can write them in different languages. Also note that you can pre-compile PL/SQL instead of interpreting

SQL/PL-SQL , most common
Java
.NET only in windows environment.

External Procedures are external functions/procedures in Operating System. You write a stored procedure in PL/SQL, use  call specification to call other languages in operating System. This potentially gives you power to call any function in other languages. Following usages come to my mind.

Use a Data Mining algorithm which is not implemented in Oracle Data Mining
Use ftp function to transfer files to another server
Use a graphic functions of Gnu Plot to create graphics and store them in database for later use.

These usages in my opinion are extreme and unnecessary. All of them can be accomplished with application programs. Nowadays nobody uses database as it is. There are always applications built using Java, .NET, PHP etc. Whenever possible, these other languages should be used in application.


Automatic Failover without data replication I have a need to be able to automatically failover my databases and most need data replication. However, one database does not need data replication and so, for performance reasons, we do not include that in the auto failover plan since it requires data replication. Is there a way to disable data replication for certain databases?

Judging by your comment, it sounds like you are looking to make the move to AlwaysOn Availability Groups.  The way that availability groups work is that you choose which databases are added to the availability group and are therefore part of the high availability implementation.

So in other words, if you want Database1 and Database2 to be highly available with automatic failover, then you configure a partner to have synchronous commit and be an auto failover partner, and when you create your availability group you add those two databases to the availability group.  But if you don't want Database3 to be part of that topology, then you simply don't do anything with that database regarding the availability group.


Efficient INSERT INTO a Table With Clustered Index I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER.

E.G.:

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, COL_C
FROM STAGING_TABLE

My question is - does it help to use an ORDER BY clause in the SELECT statement for the clustered index column, or would any gain acheived be negated by the extra sort required for the ORDER BY clause?

As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert.

This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted).

If you find that SQL Server is underestimating this for whatever reason you might benefit from adding an explicit ORDER BY to the SELECT query to minimize page splits and ensuing fragmentation from the INSERT operation

Example:

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N

SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;

SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;

Shows that T is massively fragmented

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

But for T2 fragmentation is minimal

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

Conversely sometimes you might want to force SQL Server to underestimate the row count when you know the data is already pre-sorted and wish to avoid an unnecessary sort. One notable example is when inserting a large number of rows into a table with a newsequentialid clustered index key. In versions of SQL Server prior to Denali SQL Server adds an unnecessary and potentially expensive sort operation. This can be avoided by

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

SQL Server will then estimate that 100 rows will be inserted irrespective of the size of Bar which is below the threshold at which a sort is added to the plan. However as pointed out in the comments below this does mean that the insert will unfortunately not be able to take advantage of minimal logging.


Performance of bitwise operators on byte column vs individual bit columns I'm designing a database schema at the moment, but have a performance query.

Our table needs to have (at least) two flags, or boolean values.  We can call these IsComplete and IsExportable.  The obvious way to put these into a table is as two separate bit columns, but it occurred to me that we could put it into a byte column and use bitwise operators to query against it.

Which method would yield the best performance?  Both flags will be queried often, and so filtering on them should be as fast as possible.  Would an index on the byte column improve its performance?

One reason I like the byte column is because if another flag is required in the future, it can easily be made so.  Whereas with separate bit columns, an additional column will need to be added.

For clarification, the database will be stored in SQL Azure.  I suppose this might make some difference!

The answer to any performance question is "it depends."  Discrete columns can be fast and byte column flags can be fast too.  In absolute terms you can probably save a bitwise operation by having discrete columns here and there so discrete columns are theoretically faster.

A theoretical bump shouldn't be the main reason to choose a strategy. To paraphrase Jeff Atwood "storage is cheap and BDA's are expensive."  I would avoid the flags compressed into a byte column for the simple reason that it makes your life more complicated by making your data more cryptic.  Discrete columns will be straightforward to query, filter properly, debug, and pass on to future teammates.

For completeness there are other options than bit flags, or discrete columns.  One other option is the EAV (entity-attribute-value) model, even though it doesn't feel like this is your best option based on your description.

  Entity–attribute–value model (EAV) is a data model to describe
  entities where the number of attributes (properties, parameters) that
  can be used to describe them is potentially vast, but the number that
  will actually apply to a given entity is relatively modest.


improving join performance on sql server table I have a table on sql server lets say TableA.
and a another table as TableB.Both of these tables are heavily loaded.

Table A is loaded with records 35883788. It is holding a lot of duplicates on column program_id.

Table B is loaded with records 27343331. It is also holding a duplicate on column on program_id.
For some business reason we need to keep duplicate records on program_id on both the tables.

Now I am performing a left join on given table as:

select
a.*,b.date_of_birth,
datediff(year,b.date_of_birth,a.from_date)-
(case when dateadd(year,
           datediff(year,b.date_of_birth,a.from_date),b.date_of_birth)
           > a.from_date then 1 else 0
                   end) as age_final,
case when datediff(yyyy,b.date_of_birth,a.from_date) is null then ''
     when datediff(yyyy,b.date_of_birth,a.from_date) <=1 then 'less than 1 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=17 then 'then 1 - 17 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=29 then 'then 19 - 29 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=39 then 'then 30 - 39 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=49 then 'then 40 - 49 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=59 then 'then 50 - 59 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=64 then 'then 60 - 64 year'
else 'More than 64 years' end as Age_band
from TableA a
left join ( select program_id,date_of_birth,max(process_date)
            from TableB
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

my inner query is giving me a unique set of program_id as using group by statement.

None of these tables are indexed. If I have to create an index on both the tables, what kind of index do I need to have on program_id for both the tables.

what all other things I can take care of to improve the joining performance.

need suggestion to optimize the join and indexes.

DDL & DML at the bottom

Standard indexes that could be created based on the information given

CREATE NONCLUSTERED INDEX IX_program_id_date_of_birth_process_date
ON dbo.TableB (program_id,date_of_birth,process_date);
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id);

Running the query

SET STATISTICS IO, TIME ON;
select
a.*,b.date_of_birth
from dbo.TableA a
left join ( select program_id,date_of_birth,max(process_date)  as maxprocess_date
            from dbo.TableB
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

The NC index on TableA is used since ID is included in the NC index and no other columns are in TableA except the program_id coluln, which is a key column of that index.

But, since you are using select a.* you will probably have to add all these columns to the
NC index on TableA as included columns, which will increase your index size depending on the amount of columns in the table.

If we add a varchar(3) column and populate it with a value:

ALTER TABLE dbo.TableA ADD bla varchar(3)
DEFAULT ('bla')
WITH VALUES;

The index is no longer used:

Because of the additional bla column added:

To fix this, recreate the index with the bla column included.

DROP  INDEX IX_TableA ON dbo.TableA;
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id)
INCLUDE(bla);

Additionally

  None of these tables are indexed.

Heap tables are not ideal, you should consider adding a clustered index that makes sense and reading up on indexing.

More on the difference between heap tables and tables with a Clustered index here

  select  a.*

Using   Select * is not a good practice, consider naming all columns used in your select statement separately.

Bad habits to kick : using SELECT *

DDL + DML used for testing

CREATE TABLE
dbo.TableA(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT);
CREATE TABLE
dbo.TableB(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT,
           date_of_birth date,
           process_date datetime2)
-- Dataset reduced by / 10 ~= 3 588 378

INSERT INTO
dbo.TableA WITH(TABLOCK)  (program_id)
SELECT TOP(3588378) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 3588

-- Dataset reduced by / 10 ~= 2 734 333
INSERT INTO
dbo.TableB WITH(TABLOCK) (program_id,date_of_birth,process_date)
SELECT TOP(2734333) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000,
CAST('01/01/1990' AS date),
DATEADD(MINUTE,-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()) -- one minute less to get distinct process_dates
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 2734


SQL Server 2008 R2 Express Silent Install - FullText Service Not working I'm trying to build an installer for an app that installs SQL Server 2008 R2 ADV with fulltext already enabled, but couldn't sort it out.

It must support Windows XP, so that's the first OS I'm testing. It already has all the prerequisites installed.

The command I'm using to install is the following:

SQLEXPRADV_x86_ENU.exe /QUIETSIMPLE /SkipRules=RebootRequiredCheck
    /ACTION=install /IAcceptSQLServerLicenseTerms=True
    /FEATURES=SQL,BC,SSMS,FULLTEXT /INSTANCENAME=NAMED
    /SQLSVCACCOUNT="NT AUTHORITY\NetworkService"
    /AGTSVCACCOUNT="NT AUTHORITY\NetworkService"
    /FTSVCACCOUNT="NT AUTHORITY\NetworkService"
    /SECURITYMODE=SQL /SAPWD="SomePass"

The result of the installation is that the service for the instance (MSSQLFDLauncher$NAMED) is DISABLED and the account used is NT AUTHORITY\LocalService, which is strange because that's not the account I passed, and the service is disabled.

Is there a way to call the installer so it just RUNS with the fulltext? If I need to change the accounts for the services (I only need SQL and FULLTEXT), that doesn't require me to create accounts, I can do so, but I really need it to work.

The documentation is poor, and I've tested all the combinations I found on the web.

Really appreciate the help!

I solved by doing the following:

Using the original command of the post
Running a command to change the credential of the services (using sc.exe)
Rebooting so the services would start (calling sc start  was not working, even if called multiple times, don't know if it's related to changing the service credentia


Using postgres database from another computer I have PostgreSQL version 9.5 installed at my machine (Windows 7 Enterprise x64 bit version). I have several databases in schema 'public'. I would like to use/access one of these databases from another machine (Windows 7) using LAN connectivity (so that this database may act as a shared database between two machines). I was wondering whether is it possible to do through "listen_addresses" option in postgresql.conf dialogue window? If not, is there any alternative way to do this?

Read the article Configuring PostgreSQL to Accept Connections From Computers on Your Network where it is explained with screen shots.


CTE returns an empty set even though anchor is non-empty I'm got an adjacency list consisting of two tables:

CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    UNIQUE KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

When I run the following CTE I get an empty set:

WITH RECURSIVE cte (parent_id, child_id)
AS
(
  (
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
  )
  UNION ALL
  (
    SELECT recursive.parent_id, recursive.child_id
    FROM cte, permission_graph recursive
    WHERE recursive.child_id = cte.child_id
  )
)
SELECT cte.parent_id, cte.child_id
FROM cte

But if I run the anchor condition:

SELECT anchor.parent_id, anchor.child_id
FROM permission_graph anchor
WHERE anchor.child_id = -32763

I get:

[parent_id = -32767, child_id = -32763]
[parent_id = -32768, child_id = -32763]

Why is the CTE returning an empty set when the anchor result is non-empty? Shouldn't the CTE result contain the anchor result?

I figured it out. (Apologies to those of you who tried to help me. There was no way you could have figured this out.)

After a lot of experimentation, I noticed that dynamic queries (Connection.createStatement()) were returning results but parameterized queries (Connection.prepareStatement()) were returning an empty set. You had no way of knowing this because my question incorrectly listed the query as:

  anchor.child_id = -32763

whereas in actuality it was a PreparedStatement

  anchor.child_id = ?

with a value of -32763.

Deep within H2's release notes I ran across this wonderful sentence:

  Parameters are only supported within the last SELECT statement (a workaround is to use session variables like @start within the table expression).

In short, this looks like an H2 limitation/bug. I really wish H2 would have thrown an exception instead of returning an empty set.

https://groups.google.com/d/msg/h2-database/OJfqNF_Iqyo/Z748UP7W3NAJ confirms this issue (I am getting an empty set instead of null, but otherwise the problem description is identical).

Thank you to those of you who tried helping!


Pass OLD & NEW to a function I've got 2 triggers. triggerA and triggerB that acts upon changes on tableA and tableB respectively. Inside both those triggers I want to use the same function that uses the OLD & NEW variables. I don't know how to do so because it throws me the error:

OLD hasn't been assigned yet.
The tuple structure of a not-yet-assigned record is indeterminate.

Then I tried assigning null to a variable to get around that and it's:

old_v hasn't been assigned yet.

trigger 1 & 2 (this part is common in both triggers):

DECLARE
    added added_scores;
    old_v record;
    new_v record;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            old_v = null;
            new_v = NEW;
        WHEN 'UPDATE' THEN
            old_v = OLD;
            new_v = NEW;
        WHEN 'DELETE' THEN
            old_v = OLD;
            new_v = null;
    END CASE;

    select _i_compute_added_scores(TG_OP, old_v, new_v) into added;
        -- some computation, return result

function :

CREATE OR REPLACE FUNCTION public._i_compute_added_scores(
    tg_op_p text,
    old_p anyelement,
    new_p anyelement)
  RETURNS added_scores AS
$BODY$

DECLARE
    new_score integer;
    old_score integer;
    res added_scores;
BEGIN
    CASE TG_OP_p
        WHEN 'INSERT' THEN
            new_score := new_p.score;
            old_score := 0;
        WHEN 'DELETE' THEN
            new_score := 0;
            old_score := old_p.score;
        WHEN 'UPDATE' THEN
            new_score := new_p.score;
            old_score := old_p.score;
    END CASE;

-- some more computation and return

You need to assign the RECORD type before sending it as parameter, and a very simple way to do that in your case would be simple assigning it to NEW or OLD even when it wouldn't be necessary, like:

CASE TG_OP
    WHEN 'INSERT' THEN
        old_v = NEW;
        new_v = NEW;
    WHEN 'UPDATE' THEN
        old_v = OLD;
        new_v = NEW;
    WHEN 'DELETE' THEN
        old_v = OLD;
        new_v = OLD;
END CASE;

Notice that now it is very important to use TG_OP and control which variables you must read/use.


Insert into multiple tables with one query I would like to add data to at least three tables with one query. I thought of something like this:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;

which works perfectly for exactly two tables.

Main problem here is, that all additional queries require the id value from the first INSERT query, which seems not manageable this way. I think it could be easily done with a stored procedure or transaction, but I would like the solution to be a simple and solid query.

Am I overlooking something? Is it possible that way or another (no stored procedures* or transactions)?

Note

* Altough, strictly speaking, Postgres does not have stored procedures, only functions, as @Erwin Brandstetter rightly pointed out via comments. See this series of posts for relevant information.

I think you can easily pile the CTEs on top of each other, like so:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id),
ins2 AS (
  INSERT INTO someothertable
    (id, something)
  SELECT id, 'something' FROM ins
  RETURNING id -- this is necessary for CTE, but not used
)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;


ORA-00205 error during the startup of physical standby database Steps I have followed:-

Created a physical standby database by using DUPLICATE TARGET DATABASE.
Then checked the log gap between these two databases which was fine and in sync.
After that standby database closed using the shutdown abort command.
Then when tried to open the standby in mount mode it was showing this error.

  ORA-00205: error in identifying control file, check alert log for more info)

Checked the alert log where in the mentioned path there is no control file but in initSID.ora file, controlfile path mentioned correctly and which was different from what is showing in the alert log.

This problem occurs when your auxiliary (the clone target) database instance is started using a static init file. Smarter is to use the more dynamic spfile so the control_files parameter can be changed by the cloning procedure to where it is restored on the target.

I guess the question was: why this happened? ;-)


storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there? I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for Thunderbird.
It is now becoming impossible to do so and I have been thinking about moving the emails to a DB as they come in and process them there and then, the processor needs to access the body and header. What database engine is best for this task mysql,postgre,etc?

You could try DBMail as local IMAP storage, and either use any of various IMAP syncing solutions or regular SMTP forwarding to get the mail in there.


Should order of column in where clause be same as the index order? I was reading this use-the-index-luke.com which explains in detail how indexes work. One of the things this person has re-iterated that the order of indexes matter a lot and to make the query fast the where clause columns should be same as that in index.
Today, I was just corroborating this theory and created a table (id int , name nvarchar(100) ) on SQL Server 2008.
I inserted some 5000 rows in it and created a index
create index abc on test (name, id )

and fired the query

select ID, name
from test
where ID = 10
and name = '10'

I was expecting a full table scan to be followed by a select in the query plan
but to my surprise the output of plan was a index scan folowed by select.

So, my question is does the order of columns in Where clause matters or does SQL Server re-arranges them as per the index definition ?

Thanks !!

So, my question is does the order of columns in Where clause matters

No. The order of columns in the WHERE clause does not matter at all.


Query Progress postgreSQL When I run a query on my table, I have no clue how long the process will take. I am working with a table with 11 million rows so queries typically run for a few minutes (AWS RDS). Is there a way to get a progress bar or a better sense of how long queries will take? I typically execute a query to 1/10 of the table to get an idea of the time required.

EDIT: I added my specific situation in a notebook

The query I like to run calculates a moving average for several columns. A testing query is copied below (1 column only, only for annual scores, limited output):

SELECT year,month, ptotww_m_30spfaf06, temporal_resolution,
    SUM(ptotww_m_30spfaf06)
        OVER(ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ptotwwma_m_30spfaf06
FROM y2018m05d29_rh_total_demand_postgis_30spfaf06_v01_v01
WHERE temporal_resolution = 'year'
LIMIT 200

The sample already takes 52s to run and does not work with the monthly values. The monthly 10 year moving average of Jan 1969 should be the average of jan 1960, jan 1961 ... jan 1969.

No, currently there is no such feature in Postgres.

Some work has been done to prepare for that in future releases. One result of that is the view pg_stat_progress_vacuum introduced in 9.6 which makes use of that new infrastructure.

But currently (2018) nothing of that infrastructure is used for regular SQL queries (including the upcoming Postgres 11).

Postgres Pro Standard (a fork of PostgreSQL) provides the extension pg_query_state which can display the execution plan of a running statement and the information which step is currently executed. But you need to run Postgres Pro, the extension is not compatible with the "normal" PostgreSQL version.


How to reclaim unused space from clustered index table I have a clustered index table with more than 11,000,000 records running on SQL Server 2008 R2 Standard Edition.  I have noticed the table has about 90 GB of free space.

This is the table definition:

CREATE TABLE [dbo].[nsPDFDocument](
    [DocumentID] [varchar](100) NOT NULL,
    [DocumentDate] [char](10) NOT NULL,
    [DocumentTime] [char](10) NOT NULL,
    [DocumentSize] [int] NOT NULL,
    [PDFData] [image] NULL,
    [FileName] [varchar](255) NULL,
    [OwnerID] [varchar](50) NULL,
    [Title] [varchar](150) NULL,
    [CreationDate] [char](10) NULL,
    [CreationTime] [char](10) NULL,
    [Provisional] [char](1) NULL,
    [PreviousVersionID] [varchar](100) NULL,
    [isLockedBy] [varchar](50) NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL,
    [PurgedDocumentHash] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have tried:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET NOCOUNT ON;

CREATE TABLE #PDFsToFix(
       ID    INT IDENTITY NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL
);

CREATE TABLE #OneBatch(
       ID    INT          NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL

);

INSERT INTO #PDFsToFix( DocID )
       SELECT DocumentID
       FROM dbo.nsPDFDocument
       WHERE CAST(PDFData AS VARBINARY(MAX)) = 0x00;

DECLARE @processedID INT = 0;
DECLARE @batchSize   INT;
DECLARE @changed     INT

WHILE EXISTS( SELECT * FROM #PDFsToFix WHERE ID > @processedID )
BEGIN
       TRUNCATE TABLE #OneBatch;
       INSERT INTO #OneBatch( ID, DocID )
              SELECT TOP 50 ID, DocID FROM #PDFsToFix WHERE ID > @processedID ORDER BY ID;
       SELECT @batchSize = COUNT(ID), @processedID = MAX(ID) FROM #OneBatch;

       BEGIN TRANSACTION
       UPDATE dbo.nsPDFDocument SET PDFData = NULL
       WHERE DocumentID IN( SELECT DocID FROM #OneBatch )
          AND CAST(PDFData AS VARBINARY(MAX)) = 0x00;
       SET @changed = @@ROWCOUNT;
       IF @changed <> @batchSize
       BEGIN
          ROLLBACK;
          RAISERROR( N'Updated %d rows, expected %d', 10, 1, @changed, @batchSize );
          BREAK;
       END
       ELSE
       BEGIN
          PRINT 'Updated 50 rows';
          COMMIT;
       END
END

After running that query I did not get good result.

What steps should I follow to regain space from the table?

Note: I run a "Reorganize" index maintenance job every weekend; that does  not help at all.

You may need to run an index Rebuild (not reorganise) on the clustered index - note in standard edition this will lock the table for writes for the duration of the rebuild (and reads may be affected), which could be some hours.

Otherwise a simple DBCC SHRINKFILE might work, but be aware of this: https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/


How to keep Mongodb log small via the conf file (or other persistent method) This is related to my question in stackoverflow where vb.net simply hang on updating a mongodb query.

https://stackoverflow.com/questions/12780258/insert-mongodb-using-vb-net/12796593#12796593

After long soul searching we check the log. The log is 3GB in size.

A log with that size is absolutely useless. I cannot have any text editor to open it.

How to keep the log of mongodb small, say 50 mb max.

We are only interested in the last few commmand anyway not the whole log.

Is there a setting for that in MongoDB? If so what settings:

Note: The question is simmilar with:
https://stackoverflow.com/questions/8777449/mongodb-log-file-size

However, that question doesn't ask

How do I do so by changing some text files or something persistent so that the setting remain every time I start mongodb.

You might already know the answer just posting the answer here for someone else like me ending in this post :D

vi /etc/mongo.conf
smallfiles = true

This would set smallfiles true once you restart your mongodb.

UPDATE:

Rounding a little bit more the answer smallfiles option in mongo what does is reducing the initial and journal data files size, this is useful if your machine do not have to much space or if you are gonna have multiple databases with small data each.

I my personal opinion smallfiles do not increase performance but like mentioned before could help for this specific scenarios another example is with Docker if you are not using -v "path/to/local/drive:"/data/db" then is better to set smallfiles true in my case if i don't the container breaks because it can't allocate the default data file space.

To add smallfiles to the mongo.conf you can do:

echo "smallfiles = true" >> /etc/mongo.conf
service mongod restart

More detailed info here: http://docs.mongodb.org/manual/reference/configuration-options/#storage.mmapv1.smallFiles

Hope this helps :D

Important UPDATE:
Starting in version 4.2, MongoDB removed smallfiles option.

More details at: https://docs.mongodb.com/manual/reference/program/mongos/#bin.mongos

So for latest available options, check the updated manual at: https://docs.mongodb.com/manual/tutorial/rotate-log-files/


Displaying only the most recent activity of an item I have made a database that tracks the shipping and receiving of the tools that my company has. I am now wanting to make a query that shows only the most recent activity of all the tools. I have a current query that works pretty well but does not do exactly what I would like.

This is currently what the query is showing:

This shows roughly what I want. The only problem is that as you can see bellow it allows Serial_Number to be listed twice. Once for when it was sent and once when it was received. I only want it to display the most recent of the sent and receive.

For example instead of having the serial number 050675 listed once on 2/25/2015 as sent and once on 3/3/2015 as received I would only have the activity on 3/3/2015. And I cannot just limit it to looking for when the item is received because if you look at Serial_Number 1 the most recent item is Sent.

So far I have tried nesting IIf statements and also tried the DMax function and i was unsuccessful on both of them.

This is what I am thinking it should look like. I know this is very very rough but I really do not know how to or what I should use in coding this.

If Serial_Number <> Unique Then Select Serial_Number Where Recent Date is greater

This is the table that the data is being pulled from:

Thank you for any help you might provide.

Try this:

    SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM <tablename> a
    INNER JOIN
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM <tablename>
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Update:

In your specific case:

SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM tblSend_Recieve a
    INNER JOIN
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM tblSend_Recieve
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]


Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x? I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.
mysql -u username -p test < DB-210508.sql

The import starts but fails with the following message:
ERROR 1824 (HY000) at line 518: Failed to open the referenced table 'table-name'

I am wondering why there is a problem with foreign keys and if it is not more due to the fact that I am importing into 8.0.x
The MySQL 8.0.x instance is configured like this:
[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728
collation_server        = utf8_unicode_ci
character_set_server    = utf8

What could be the reason for the failed import?

This error happens when you have a classic “chicken and egg” problem. The most common situations for this would be:

populating a self-referencing table where a lower ID requires the existence of a higher ID⇢ a good example of this would be an Employee table where record 33 had a manager with an ID of 999. 33 could not be recorded until 999 existed in the table.
populating a table that depends on another table that has not yet been created⇢ this can happen when you have an Employee table that has references to User, but User isn’t created until after Employee.

The error you received was:
… Failed to open the referenced table 'table-name'

This points to the second item as being the most likely culprit.
The simplest way to resolve this would be to remove the foreign key checks when importing the data, then restore the checks after the import is complete. You can do this be editing your 12GB .sql file to have this at the very start:
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;

Then, at the very end of the import file:
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

Note: With the foreign key check disabled, there is a possibility of recording bad data. If this is something you need to avoid for regulatory purposes, do not follow this “quick” merhod.


Calculate number for each row My table structure is below:

CREATE TABLE [ACC].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [SalesCompanyFinancialPeriodID] [int] NOT NULL,
    [DocumentTypeID] [int] NULL,
    [Number] [int] NULL,
    [Title] [nvarchar](200) NULL,
    [ConfirmStatusEnumID] [int] NULL,
    [SignedPrice] [money] NOT NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
    [DocumentID] ASC
)

I want to set Number column value automaticaly increment number started from 1 for each SalesCompanyFinancialPeriodID column. In other word if new record inserted in this table, value of Number column calculated in instead of trigger, such as below:

 ISNULL( (Select Max(A.Number)
          From Acc.Document A
          Where A.SalesCompanyFinancialPeriodID = Inserted.SalesCompanyFinancialPeriodID),1)

What is the best practices for this problem.

Why do you need to store this value in the Number column? Much better to derive this information at query time than to bother storing redundant information and constantly have to update it as rows are added, changed or deleted.

SELECT DocumentID, SalesCompanyFinancialPeriodID,
  Number = ROW_NUMBER() OVER (PARTITION BY SalesCompanyFinancialPeriodID
    ORDER BY DocumentID)
FROM ACC.Document;


Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten? I deploy multiple SQL Server jobs to a fleet of servers using a PowerShell script. I can create the job steps, success fail action, and even the output file. This all works on multiple servers. What I cannot determine is how to specify that the output file is to be appended to instead of overwritten.

The following is the section from my main script. $Job is populated in a previous step.

$JobStep = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($Job, 'MyJobStepName')
$JobStep.ID = 1
$JobStep.SubSystem = 'PowerShell'
$Command = 'dir d:\'
$JobStep.Command   = $Command
$JobStep.OnSuccessAction = 'GotoNextStep'
$JobStep.OnFailAction    = 'GotoNextStep'
$JobStep.OutputFileName  = 'D:\MyJobFile.txt'
$JobStep.Create()

I have been using this MSDN page as a reference.

I modified one of the steps using SQL Server Management Studio and traced the result. This looks promising. I can issue a SQL command after I create the jobsteps.

EXEC msdb.dbo.sp_update_jobstep @job_id=N'12156dcb-c8cd-4267-901a-55175dd44960', @step_id=1 ,
        @flags=2

I think it is more along below lines

$jobStep.JobStepFlags = "AppendToLogFile"
  #call the alter to modify
$jobStep.Alter()
  #display it
$jobStep.JobStepFlags


AWS performance of RDS with provisioned IOPS vs EC2 Has anyone done a performance comparison of AWS RDS with the new provisioned IOPS vs EC2? I've found plenty of non-high IOPS RDS vs EC2 but nothing with the new high IOPS feature in RDS.

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

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

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

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

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


create a batch script on windows that periodically starts a sql function I need to create a batch script on windows that periodically starts a sql function written in 'plpgsql'.

I can use windows schedule task for creating a cron job but I don't know how to write the script.

Use psql with a command to execute the function, eg:

postgres=> create function f() returns void language plpgsql as $$begin null; end;$$;
CREATE FUNCTION
postgres=> \q

root@maidenhead:~# psql --command="select f()" postgres postgres
 f
---

(1 row)

...which is easily set up to run via Task Scheduler, no batch file required (thanks @Craig)


How to take SQL Server databases backup using .bat script file with time stamp I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file but need to place a time stamp at the end of backup file like test_13_April_2017.

cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlCmd -S SERVER02 -Q "Backup Database testdb To Disk='C:\backup\testdb.bak'"

Is there is any way to give time stamp at the end of every DB backup.

I'd recommend you look at some of the freely available backup code. Here are two possible options I would recommend, as both work on SQL Server Express Edition.

1) Ola Hallengren's backup code.

You can install Ola's code in a database on your server (ex, in a "DBA" utility database), and then backing up the databases will be done by executing a stored procedure. The stored procedure takes care of putting date/time stamps in the file name, and can also take care of cleaning up old backups (and more).

Then, you can use a PowerShell (or cmd file) to call Invoke-SqlCmd (or sqlcmd) to simply call the procedure with the proper parameters.

PowerShell:

Invoke-SqlCmd -ServerInstance SERVER02 -Database DBA -Query "EXEC dbo.BackupDatabase @Databases='testdb'"

cmd:

SqlCmd -S SERVER02 -Q "EXEC DBA.dbo.BackupDatabase @Databases='testdb'"

2) The Dbatools PowerShell Module

This module includes a simple Backup-DbaDatabase command that will perform the backup and include the date/time stamp in the file name. This has fewer bells & whistles than Ola's procedure, but if you don't need the bells & whistles, this simple solution might be just what you need.

Backup-DbaDatabase -SqlInstance SERVER02 -Databases testdb -Type Full -BackupDirectory \server2\backups


How to define destination for an append query Microsoft Access I'm trying to append two tables in MS Access at the moment. This is my SQL View of my Query at the moment:

INSERT INTO MainTable
SELECT
FROM Table1 INNER JOIN Table2 ON Table1.University = Table2.University;

Where university is the only field name that would have similarities between the two tables. When I try and run the query, I get this error:

Query must have at least one destination field.

I assumed that the INSERT INTO MainTable portion of my SQL was defining the destination, but apparently I am wrong. I am not a very frequent Access user, or database administrator at all, really - I was just tasked with this at work and do not want to disappoint, therefore, any help is greatly appreciated.

If MainTable has more that one colum (I guess it does), then you need to specify it like that:

INSERT INTO MainTable(ColumnName)

Also you have to specify the columns in the SELECT STATEMENT:

SELECT Column1, Column2
FROM TABLE2....


Microsoft SQL Joins and Where Clause I am sure a very simple question for people out there but my brain has shutdown.

I have joined two tables with a left join which has worked as hoped as I do need to bring back the NULLs in one of the tables as some of the rows will not have cosponsoring matches.

I need to put a WHERE clause on one of the columns that 'Could' be NULL and I want to bring back all rows that have 0 and the NULLs

Simply put like this so I want to bring back on rows with 0 or the NULL / Blanks
WHERE Expired = '0'

Depending on indexing and size of data, you may find performance is better if you use:

SELECT * FROM table WHERE (col = 0 OR col IS NULL)

Or

SELECT * FROM table WHERE col = 0

UNION ALL

SELECT * FROM table WHERE col IS NULL