How to reduce the log file size without shrinking it in SQL server In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple mode or backing up the transaction log in full mode should do the trick, but it is not working in one of my scenarios. Any advice/links will help in preventing log file fragmentation.

Once the log claims disk space the only way to get it back is to shrink.  Checkpoints and truncation will free up space within the log file (now available for future use by the log) but the actual footprint of the file on disk stays the same.

Read these articles for good information not only about how this works but the pro's and con's of messing with it.  Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).

http://msdn.microsoft.com/en-us/library/ms365418.aspx

pros and cons

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057


Revoke delete from table for database owner I am trying to revoke the DELETE privilege on a table for a database owner.

I execute the command:

REVOKE DELETE on my_table FROM database_owner;

But when querying this via

SELECT has_table_privilege('users','delete');

I get true returned and am able to continue deletion.

How can I revoke deletion from this table?

Thanks.

You cannot revoke privileges that are not granted.

The table (not database) owner implicitly has full rights on the table. They cannot be revoked, except by changing the owner of the table.

What you probably want to do is connect with a user other than the owner of the database/tables, and GRANT that user only the rights it should have over the table(s).


Cannot continue the execution because the session is in the kill state. while building clustered index I get the following error while trying to create a clustered index

The statement has been terminated.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The index is:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO

CREATE CLUSTERED INDEX IX_CO_DES_INPUT
ON dbo.CO_DES_INPUT(DESIGN_ID, PRODUCT_INPUT_NUM, INPUT_NAME)
          WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.CO_DES_INPUT
  SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

I am using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
I have run
DBCC CheckDB ('concept-test') WITH NO_INFOMSGS, ALL_ERRORMSGS

and it found no problems
I am afraid that my database is corrupt since I am getting this error. How do I correct my issue and get this index put on the table?

Just a pointer re: SQL Server 2017 (where I was hitting this error) and some versions of 2016 to potentially save others some time:

Before SQL Server 2017 CU8 (also 2016 SP1 CU9 and 2016 SP2 CU1), there was an issue where partitioned tables with incremental stats would generate this error if the DML operation caused an automatic stats update.

Minimal KB page is here:

https://support.microsoft.com/en-us/help/4163478/fix-access-violation-when-incremental-statistics-automatically-updated

Installing a later CU than those listed above fixes the issue.


MySQL unique index only if column has specific value I have a table where I use a boolean column deleted to signify whether a specific record has been 'deleted' by the user (meaning, it appears in the trash list instead of the normal list). These items all have slugs which should be unique.

However, if the user wants to add an item with a slug that is the same as the slug of a deleted record, it should be allowed. I tried doing this with a UNIQUE index on deleted and slug, however, this gives a problem if this item would get deleted: there should then be two items in the trash list with the same slug, which would give a constraint violation.

So actually I need a constraint which is only applied for the records with deleted=0

I've seen the question Custom unique column constraint, only enforced if one column has a specific value, however, it only applies to PostgreSQL and I'm on MySQL. Is there a way in MySQL to do this, or a nice workaround?

I thought of making deleted an integer instead of a boolean, where 0 would signify not deleted and any other number deleted. Then deleted items with the same slugs could have a different value in the deleted column.

However, this would mean the interfacing software (written in PHP) would have to check what it could put in the deleted column if a constraint violation occurs, and of course I'd prefer a pure MySQL solution. If I'm going to write this in the PHP code anyway, I'd rather change the slug value, for example, to prepend the id of the record to the slug when an item has been deleted - or, create a second table for the deleted records, where no such constraint exists.

Anyway, is there a pure MySQL way to do this?

Note: of course, such a scheme will have difficulties when the user requests to restore a previously deleted item when its slug is already assigned to a new record. This will have to be done in the interfacing code in any case.

I had a similar question, and have a way to make it work, so if you don't mind the kludge, here it is:

Since MySQL allows multiple NULL values on a unique index, you can use NULL to indicate "deleted" and non-NULL value (42?) to indicated "not deleted".

You would then create a composite unique index on [slug, deleted].

The table would look like this:

slug   deleted
abcd   42    # This record is marked as not-deleted
abcd   NULL  # This record is marked as deleted
abcd   NULL  # This record is marked as deleted
xyz    42
xyz    NULL

If using non-NULL to signify "not deleted" seems a bit iffy, so if you can, you could reverse the values and change the column name to "available" or something? :

slug   available
abcd   42    # This record is marked as available
abcd   NULL  # This record is marked as not-available
abcd   NULL  # This record is marked as not-available
xyz    42
xyz    NULL

Hope that helps


Set up a SQL Server database based on an Excel file which refreshes automatically Can I set up a Server database based on an Excel file, which refreshes automatically with any changes in the source Excel file?

I am currently using SQL Server 2008.

You could use a FileSystemWatcher which triggers when the Excel file changes and runs simple code to push the revised file through to SQL Server.


Multiple SQL Server installations on ONE server? Across our business we have several low-volume SQL Server installations; added as we have developed/acquired new products for the business. Each one is located on a virtual server and for argument sake let's say there are 5 such servers and each has been allocated 8Gb RAM and 4 CPUs ... in my mind, I think that it would make more sense to collated these onto ONE server, beefed up with memory/CPU.

I see the PROs of this being that it makes it easier to keep track of the servers, to maintain them, upgrade (when required), assigning permissions would be easier and the performance may be increased (by adding additional RAM/CPU). Presumably, we could also have the server replicated/load balanced to give additional resilience.

Am I correct to think that?

What would be the considerations AGAINST doing this?

To assist with your research, this is often called "Instance Stacking" when discussing installing multiple instances of SQL Server on the same box.

You can read about some of the pros and cons here: Should I Install Multiple Instances of SQL Server?

The biggest downsides would be maintenance / availability, which Iłya Bursov mentioned in the comments:

  if this one server is down - all applications are down too (for example during upgrade)

Over time, it can become very difficult to schedule downtime as more apps are added to the machine.

The other tough one is performance.  Making sure that the max and min memory settings for each instance or configured to keep from oversubscribing the box.  Going all rocket surgeon with affinity masking to make sure CPU is distributed (or not doing that and letting one instance trash the server).

This also makes it tough to understand wait stats and other performance metrics, since they are usually reported at the instance level.  You'll need to aggregate them somewhere, or correlate the stats between the different instances.

You can weigh these challenges against the licensing savings / convenience of only having one Windows box.  It sounds like perhaps these applications are not super resource intensive, or not 24/7 or business critical, in which case you could be in for a quick win in terms of licensing and manageability!  But it's good to see all sides in order to make an informed choice.


How efficiently does iSeries DB2 use index with WHERE ? MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries.
I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects include unions and where exists/not exists clauses were used extensively.
Among other things there are many where clauses that specify column <> 0 etc.
Does iSeries DB2 efficiently use index with where column <> 0, <> '' etc?

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths.

One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up your queries even more.


SQL - Referential integrity seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as A,B,C.
C holds some columns and A & B refer to those columns.
A & B data can be deleted -> and here comes my question:
I'd like the referential to go as when there are no valid references to C, the referenced row will get deleted, since it's not in use.
I thought of adding an auto_increment to C, and make A & B refer to the index, and just keep multiple of the same data, and just delete the attached. This hardly seems like the most reasonable solution.
If anyone could point some light on my problem, that would be great.
Thank you.

EDIT: I'm using SQLite.

I would implement it using a reference counter:

CREATE TRIGGER new_in_b AFTER INSERT ON B
FOR EACH ROW
BEGIN
  UPDATE A SET refcounter = refcounter + 1 WHERE id = new.foreignkey;
END;

CREATE TRIGGER remove_from_b AFTER DELETE ON B
FOR EACH ROW
BEGIN
  UPDATE A SET refcounter = refcounter - 1 WHERE id = old.foreignkey;
  DELETE FROM A WHERE id = old.foreignkey AND refcounter = 0;
END;


SSIS: Get Date for Day of the Week In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date?

I assume I have to do some subtraction on the day-of-week, but not sure:

DATEADD("d", -DATEPART("DW", GETDATE()), GETDATE())

You can use the following code to get the date for any given day in the current week:

DECLARE @DesiredDay INT = 0;
DECLARE @OutputDate DATE;
SET DATEFIRST 1; /* Monday is day 1 */
SET @OutputDate = DATEADD(d, @DesiredDay - DATEPART(DW, GETDATE()), GETDATE());
SELECT @OutputDate;

I'm setting @DesiredDay to 0 since you requested Sunday in your question.  For Monday, set this to 1, for Tuesday set it to 2, etc.


Db2 REORGCHK returns the SQL2310N (Error "-668") message I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes.

I removed a column:

db2 "alter table SCHEMA.TABLE drop column COLUMN"

Then, I run REORGCHK:

db2 "reorgchk update statistics on table all"

and got the following message:

Doing RUNSTATS ....

SQL2310N  The utility could not generate statistics.  Error "-668" was
returned.

However, if I do not ask REORGCHK to update statistics, the command succeeds:

db2 "reorgchk current statistics on table all"

Further investigation shows that RUNSTATS on the altered table:

db2 "runstats on table SCHEMA.TABLE for indexes all"

fails with the same error as above (i.e., SQL2310N, -668).

I tried to increse values of same parameters (LOGFILSIZ, LOGPRIMARY, LOGSECOND, and STAT_HEAP_SZ), but that did not help.

What could be the reason for the error? How to debug it? How could I fix it?

I'm not sure REORGCHK UPDATE STATISTICS is the way to go after a destructive operation (drop column) against a table. I suspect that the drop statement put a Z-lock on some catalog tables which prevents STATISTICS from being updated. If you are only interested in whether you need to reorg a table due to the drop you can:

SELECT NUM_REORG_REC_ALTERS, REORG_PENDING
FROM SYSIBMADM.ADMINTABINFO
WHERE (TABSCHEMA, TABNAME) = (..., ...)

For some reason, it's really slow (don't ask for a table that does not exist), but it's faster than reorgchk.

Unless the table is really big, I usually add a REORG command to migration scripts just in case:

REORG TABLE ...;

Do note that REORG will commit any ongoing transactions, so you should end the current uow, before REORG's and put them in a separate transaction.


Best way to create a daily snapshotted read-only db in RDS? We have a PostgreSQL db instance in RDS. In our application we need to run a daily export in the early hours of every day, the export and transformation may take some time and one issue we've found is it can export incorrect data if the data is being modified while the export is running (we use limit/offset as our table has ~100 million rows). This is probably a common issue.

To get over this we want to create a snapshot of our instance at midnight and use this for the export.

What's the best way to do this in RDS? Ideally the snapshot shouldn't take too long to create.

I've researched some things to try:

I tried to create a stored procedure to duplicate tables, but then it takes time to reindex etc, and the table name has to be different (which I want to keep the same so the query, which is used in other areas of the app, doesn't have to change).
RDS has read only replicas, but from brief reading they have a lag time so it seems to me they are constantly updated from the master instance. Is there any way to stop updates, or update once a day at midnight?
We have daily snapshots ran at midnight already, and I think we could create an AWS lambda to restore this snapshot (which I believe creates a new instance). It seems clunkier than the read-only replica but doable.
Use pg_dump, but this seems more work than using the AWS snapshot.

What's wrong with bullet 3? Schedule a Lambda function written in your preferred language to run at midnight that calls the RDS API to perform a snapshot.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html

You want this part near the bottom:

https://rds.us-east-1.amazonaws.com/
    ?Action=CreateDBSnapshot
    &DBInstanceIdentifier=mydbinstance
    &DBSnapshotIdentifier=mydbsnapshot
    &SignatureMethod=HmacSHA256
    &SignatureVersion=4
    &Version=2013-09-09
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140423/us-east-1/rds/aws4_request
    &X-Amz-Date=20140423T161105Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=e9649af6edcfbab4016f04d72e1b7fc16d8734c37477afcf25b3def625484ed2


Movement of Standby database from one location to another location Looking for help on below mentioned process:

We are planning to move the standby database from Location A to Location B. I need your help to move this database using lift and drop method, by shutting down the database moving from one location to another and after movement bring up the server and sync with the primary database.

Need your help to identify the steps required / or to be followed for performing this activity. Mainly I am looking for details on backup and archive.

We are planning to shut down the Location "A" office and to want to reuse the existing server by physically moving the server to location "B". We have time and resource constraints for having a second standby.

More information on the primary and standby structure:

Currently the Primary is located in location "C" and DBA taking only archive logs in the server path. No backups are taken from the primary database.
2.The standby which we are planning to move to Location "B" in this server we are taking the latest FULL (netbackup) and placing in the same server.

In this current scenario primary is always relying on the standby for backup files.

The easiest solution would be to stop transport of the logs from the primary, stop redo apply on the standby, make the move, then re-enable both... This is usually accomplished via:

On Primary

alter system set log_archive_dest_state_N = defer scope=both;

*Replace N with the archive_dest number where your transport is defined... which you should be able to easily tell via

show parameter log_archive_dest;

Then look for a an entry that points to your standby... something like "SERVICE=UNIQUENAMEOFSTANDBY..."

On Standby

alter database recover managed standby database cancel;
shutdown immediate;

Any logs that are generated on the primary will remain on the primary while the standby is unavailable.

After the move:

On Standby

startup mount;
alter database recover managed standby database disconnect from session;

on Primary

alter system set log_archive_dest_state_N = enable scope=both;

Any accumulated logs on the primary are shipped over and applied to the standby.

You can check to see if the standby is up to date via:

On Standby

select current_scn from v$database;

On Primary

select scn_to_timestamp(<SCN_FROM_ABOVE_QUERY>) from dual;

If the idea of the standby being unavailable for an extended period of time is unacceptable, then create a second standby at the new site first while the current standby is still on-line and available.

It is also important to note that if your standby site is more than 100 miles from your primary site, it is recommended to either not use real time apply (due to network lag) or that you use Oracle Far Sync (12c only).


postgresql compare column value from first row only This is how my history table looks like

     start_date        |      end_date          |   name
-----------------------+------------------------+----------
2016-11-05 02:26:00+00 | 2016-11-10 01:55:00+00 | premium
2016-10-01 00:40:00+00 | 2016-11-30 01:55:00+00 | free

Records are ordered by start_date in descending order. I want to get the first row and then check if its end_date is greater then now(), if yes then return true else false.

I have written the below query by looking at some tutorials and its working fine but i don't know if this is efficient or not and how to write an optimised query for my question above.

SELECT history.end_datetime FROM history,
(SELECT id, end_datetime from history ORDER BY start_datetime
DESC LIMIT 1) subhistory WHERE history.id = subhistory.id AND
subhistory.end_datetime > now();

You could it much more simply:

SELECT end_datetime > now() AS result
FROM history
ORDER BY start_datetime DESC
LIMIT 1 ;

The query is similar to your subquery, it returns only one row, the latest start_datetime. The end_datetime > now() in the select list is a boolean expression so it retuns either TRUE or FALSE, depending on the value of end_datetime in that row. We don't need to join this result set to the table because all the information we need is there, in the one row.

You could modify the select list, to include other columns, too, or all the columns if you need them:

SELECT end_datetime > now() AS result,
       start_datatime,
       end_datetime
...

For efficiency, you need an index on (start_datetime). Your query would be efficient, too, despite the unneeded self-join.


How to pull a list of applied service packs and cumulative updates from SQL Server 2008 R2? I want to list all the SPs and CUs applied to a SQL Server 2008R2 instance.

Example:

10.50.4260 2720425 http://support.microsoft.com/kb/2720425 CU#1 for SQL Server 2008 R2 SP2
10.50.4000 2630458 http://support.microsoft.com/kb/2630458 Service Pack 2
10.50.2881 2868244 http://support.microsoft.com/kb/2868244 CU#14 for SQL Server 2008 R2 SP1

How can I get the above list from SQL Server 2008R2 instance?

Cumulative updates are exactly that, cumulative. CU2 for example contains all of the fixes included in CU1. As such, the only SP or CU that matters is the last one to be applied which you can determine with:

SELECT @@VERSION

and Google or a reference list of SQL Server versions. SQLServerBuilds is the popular unofficial list, KB321185 is the official list.

I'm not aware of any way to obtain a complete list of all patches that have ever been applied to an instance. It's likely buried in the registry somewhere but I haven't fished for it.


MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas! This evening I installed MySQL on a new clean install of Windows 2019 server. When the install finished, the installer offered the possibility of opening Workbench so I said yes.

Then I noticed that there were schemas missing - as per the question, the performance, information and mysql schemas were missing (see the screenshot below1)

But, when I log in using the mysql client, I get this

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

In Workbench, I can access the "missing" schemas via queries like so

SELECT host, user FROM mysql.user;

host,user
%,xxx
localhost,mysql.infoschema
localhost,mysql.session
localhost,mysql.sys
localhost,root

Can anybody explain to me what's going on here? How can I get the missing schemas to appear in MySQL Workbench?

1 I know that screenshots are normally not a good idea in this forum, but this time, it's the only way to show what I mean.

>mysqld.exe --version
\mysqld.exe  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)

and

Workbench 8.0

and

Windows Server 2019 Standard

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to be something controlled by one of MySQL Workbench settings. Specifically, Valeriy Kravchuk suggested this resolution:

  Please, check "Show Metadata Schemata" checkbox in the Query Editor group of the SQL Editor tab in the Workbench Preferences dialog box. Use Edit > Preferences... menu item to open it. Don't forget to click "Refresh all" in the Object Browser's context menu after that.


Will my query continue to execute after I shut off my computer I am running a very expensive query that I executed via pgAdmin 4 which is installed on my laptop. I am executing the query on a cloud server.

The message that I see is 'Waiting for query execution to complete...'

If I shut my laptop off right now, will the query continue to run until completed?

Thanks.

Probably not.

The server will detect that the TCP connection has been closed sooner or later and then try to cancel the query.

You may have success with suspend instead of shutdown, but it's not guaranteed to work.

Your best option is to connect to the server (or to some other always-on server) and run the query there using the psql command-line in a  tmux, or screen session etc.

PgAgent may also be worth considering.


Insert into int8range from columns of another table I have two tables in PostgreSQL:

Table indexces_grid:

+---------------+------------+
| Column Name   | Type       |
+---------------+------------+
| indexid       | bigint     |
| iprange       | int8range  |
+---------------+------------+

Table citydetails:

+---------------+-----------+
| Column Name   | Type      |
+---------------+-----------+
| ipfrom        | bigint    |
| ipto          | bigint    |
| cityname      | varchar   |
+---------------+-----------+

I want to insert the ipfrom and ipto into the single column iprange of the indexces_grid table.

It's so simple:

INSERT INTO indexces_grid ("Row2")
SELECT int8range(ipfrom, ipto, '[]')
  FROM citydetails;

The documentation linked above tells you all.

Note that one has to use the three-parameter form of the range constructor function (here int8range()) to include the upper bound, too.  Using the two-parameter form, the upper bound would be non-inclusive.

I've created an SQLFiddle to show this at work.


Why SSAS tries to connect to the default instance? I've installed SSDT and while tring to browse the cube I get this error

Here it tries to connect to port 2383 which is for the default instance, but in my case SSAS is installed for a named server under port 2382. How to correct this error, so the system could understand that this is a named server?
everything is installed locally, and port 2382 is open, firewall is turned off.
Should I change the port number in Visual Studio?
Thanks

It would be helpful to know if your running in a Clustered environment or not. However here are some suggestions:

1) Make sure that SQL Browser service is running and that the service user has local administrator rights (Local System Account). Please note that SQL Browser service listens for client requests on port 2382 and then redirects the client connection to the port used by SSAS instance. (In your case it would be 2383)

2) If Analysis Services is running on a Windows Server Failover Cluster (WSFC), the SQL Browser service has to be started on all of the nodes of the WSFC

3) The port information for the instance is relayed by the SQL Browser services only if property InstanceVisible is set to 1 (true). Verify this under General / Advanced settings of SSAS.

4) Test for connectivity by using the telnet command:

  TELNET InstanceName PORT

If you get a "Could not open a connection to the host, on port" error message, it could mean that the instance is running on a different port or something is blocking access to this port.


How do I move a PostgreSQL tablespace? Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks like ALTER TABLESPACE only lets you rename.

I'd like to avoid creating a new tablespace and moving the database to it. I assume that would be a slow copy process that requires a lot of time and disk space. I also want to avoid symlinking /old/dir to /new/dir.

I assume that your tablespace name is tblspc.

Get Oid of your tablespace

test=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname
-------+---------
 24580 | tblspc
(1 row)

Stop postgres

$ pg_ctl -D $PGDATA stop

Move directory from old one to new one

$ mv /old/dir /new/dir

or

$ cp -r /old/dir /new/dir
$ rm -rf /old/dir

Change tablespace's link to new directory

$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580

Start postgres

$ pg_ctl -D $PGDATA start

Then, you can see the tablespace's directory has changed.

test=# SELECT pg_tablespace_location(24580);
 pg_tablespace_location
------------------------
 /new/dir
(1 row)


MySQL CONVERT_TZ returns null MySQL CONVERT_TZ returns null, if timezone value is in plus.

example:

select CONVERT_TZ(now(),SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5),'+00:00');

it returns perfect time 2015-12-16 10:32:19 when the time zone is in minus value. The same is not working when the time zone value is in plus. For an example @@session.time_zone returns 05:30. If I concat plus manually, it is working. Please let me know, if any alternative solutions are available.

Manually concatenated code:

select CONVERT_TZ(now(),concat("+",SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5)),'+00:00');

Thanks in advance.

When the difference is positive, the SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5) part returns a positive values, thats without the +.

But CONVERT_TZ expects the offset to contain either + or -, so without it the value is not proper offset and the function returns NULL as specified.

Simply said - results of timediff are not proper timezone offsets.

You might use DATE_ADD instead, it allows for - too.


Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns? Suppose I have 3 columns:
Critical, Medium, Low
And I have data UUIDs to be inserted from another tables. And I want to disallow insertion of same UUID in any of the column if it is already present any one of these columns. How can I do that in postgres? How should I setup my table?
example:
--------------------------
|Critical | High  | Low   |
--------------------------
|   A     | null  | null  |
|   null  |  B    | null  |
---------------------------

Now when I try to insert B (again) in any column Critical, High or Low. It should ignore the insert.

How do I normalize it in postgres? could you please elaborate – Pax

Structure and predefined data:
-- Create table with priorities.
CREATE TABLE priorities (priority_id INT NOT NULL PRIMARY KEY,
                         priority VARCHAR(255));
INSERT INTO priorities VALUES (1, 'Critical'),
                              (2, 'High'),
                              (3, 'Low');

-- Create table for data. Series column added.
CREATE TABLE data (series INT NOT NULL,
                   priority_id INT NOT NULL,
       -- reference to priorities table
                   CONSTRAINT fk_priority FOREIGN KEY (priority_id) REFERENCES priorities (priority_id),
                   value VARCHAR(255) NOT NULL PRIMARY KEY,
       -- forbid duplicated values
                   UNIQUE (series, priority_id));

Insertion pattern:
INSERT INTO data
    VALUES {values list}
    ON CONFLICT DO NOTHING;

Retrieving the information:
SELECT series,
       MAX(value) FILTER (WHERE priority_id = 1) Critical,
       MAX(value) FILTER (WHERE priority_id = 2) High,
       MAX(value) FILTER (WHERE priority_id = 3) Low
FROM data
GROUP BY series
ORDER BY series;

fiddle
PS. The scheme does not allow to insert more than one value for the same priority in the same series also (see last insert).
PPS. priorities table is not used in shown code (it is used for reference integrity only), but you may...


Query PostgreSQL 9.0 table on foreign key value? Noob-type question:

Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code.

How do I query the candies table for all blue candies without an explicit join? For two years now I've been writing these queries with joins, like:

SELECT * FROM candies a JOIN colors o ON(a.color_code = o.color_code)
WHERE o.color_value = 'blue';

I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless.

Thanks.

"I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless." What makes you think that? RDBMSs are built to 'join'

If your aim is to make you SQL more readable then you might prefer the 'using' syntax:

SELECT * FROM candies JOIN colors USING(color_code) WHERE color_value = 'blue';

alternatively, seeing as you are hard-coding 'blue' into your query, you could consider hard-coding the code instead:

SELECT * FROM candies WHERE color_code = '02';

but this will decrease readability unless in your real-world scenario the codes are descriptive (eg 'BLU' for blue)


Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server I am having a bit of a linked server problem.

I created a SQL Server linked server and it's been working fine for last 2 years and suddenly not working.

SQL Server

  Microsoft SQL Server 2005 - 9.00.5324.00 (X64)   Aug 24 2012 18:28:47
  Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
  (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Oracle client version

  Oracle - OraClient11g_home1

Error 7302:

  Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server.

We have enabled Allow in Process in SQL Server for the Oracle Provider.

Has anyone got any ideas on this and what can be done to avoid this happening again?

linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.

tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file
C:\product\11.1.0\db_1\network\admin\sqlnet.ora

tnsping successfully connected to the Oracle database from the SQL Server box. Then check the Provider details while creating the Linked Server.

The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components.

Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues,to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry.

Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!


What do PCWP, PCWC, and P->S mean? Oracle parallel explain plans have a column named IN-OUT, and it often contains values P->S, PCWP, and PCWC. What do these values mean?

Here is an example explain plan with an IN-OUT column:

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN             |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,02 | PCWP |            |
|   5 |      PX SEND BROADCAST   | :TQ10000 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR  |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL | T3       |  Q1,00 | PCWP |            |
|*  8 |     HASH JOIN            |          |  Q1,02 | PCWP |            |
|   9 |      PX RECEIVE          |          |  Q1,02 | PCWP |            |
|  10 |       PX SEND BROADCAST  | :TQ10001 |  Q1,01 | P->P | BROADCAST  |
|  11 |        PX BLOCK ITERATOR |          |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL| T1       |  Q1,01 | PCWP |            |
|  13 |      PX BLOCK ITERATOR   |          |  Q1,02 | PCWC |            |
|  14 |       TABLE ACCESS FULL  | T1       |  Q1,02 | PCWP |            |
--------------------------------------------------------------------------

Paraphrasing from Explain Plan with Parallel Processing:

  The IN-Out column can take on the following values: PCWP, PCWC, P->S, S->P, and
  P->P.

  The values PCWP (Parallel Combine with Parent) and PCWC (Parallel Combine wth Child) indicate intra-operation parallelism.  For every PCWP there will be a PCWC. These pairs of operations are executed by the same set of parallel processes.

  The value P->P (Parallel to Parallel) indicates inter-operation parallelism.  This communication will between two different pairs of parallel processes. The value P->S (Parallel to Serial) always occurs in a SQL statement when the parallel processes send their data to the query coordinator.The value S->P (Serial to Parallel) occurs when a table is read in serial mode, such as a small table, and then its data is sent to an operation that is executed in parallel.

There are additional references available here and here.


Date of Last DBCC checkdb run Using T-SQL, I can get the last known good DBCC Checkdb through DBCC DBINFO().
My question is, can I find, somewhere in SQL, when the Last DBCC Checkdb Ran? (regardless of being successful or not)

I would need this info to compare the Last date it ran with the last known good DBCC checkdb so that I can take action if the dates don't match.

Since I'm on SQL Express, I can't use SQL Server Agent to run the DBCC checkdb so it can be run randomly.

Any help is greatly appreciated.
Thank you

One way to achieve is to log information from your default trace into a physical persistent table.

A default trace is like a black box recorder for sql server and is ON by default. It records 116  (EventID )    Audit DBCC Event (Event Description).

Only relying on dbi_dbccLastKnownGood for determining of checkdb ran is misleading as DBCC CHECKDB ('your_db_name') WITH PHYSICAL_ONLY will update the dbi_dbccLastKnownGood along with DBCC CHECKFILEGROUP.

The only way to determine of checkdb ran successfully is to actually run it and record all the error messages (if any).

Also, refer to : What event information can I get by default from SQL Server? from Aaron Bertrand.


Why changing the database owner doesn't change the owner in PostgreSQL dump? I was exporting a database using pg_dump command when I realized that the owner of the database had to be changed. So I change it, and make extra sure that the newuser is correct. Then try the command again:

pg_dump -h localhost -U postgres database_name -f mydb.dump

But this didn't work. The output file still identified the owner as the olduser. Why could this be happening? How can I make sure this doesn't happen?

The way you are invoking pg_dump (without -C), the database metadata is not dumped at all.

Use this:

pg_dump -h localhost -U postgres database_name -C -f mydb.dump

This will add CREATE DATABASE and ALTER DATABASE ... OWNER TO to your dump.


multiple groupings in select clause in postgres I have the following orders table in a postgres 9.6 db:

orders
item_id, created_at (a timestamp ie Rails app)

I'd like to get back a list of item_ids and distinct years and a count like this:

item_id  year   count
-------  ----   -----
23       2017   23
24       2017   45
24       2018   11

How would I do this? I tried something like this but am stuck:

select item_id, extract(year from created_at) from orders where group by item_id, created_at;

You were nearly there, but you need to group by the year, not the timestamp value:

select item_id, extract(year from created_at), count(*)
from orders
where ....
group by item_id, extract(year from created_at);


Not able to restore a backup to SQL Server 2019 I am trying to a restore a SQL Server from a .bak file and always get the same error :

The database was backed up on a server running version 15.00.2000.
That version is incompatible with this server, which is running
version 15.00.2000.

I have already seen the issue in Stackoverflow but I'm struggling because the versions here are the same ..
I am using SSMS and I already tried restoring to a SQL Server 2017, to the latest Cumulative update of SQL Server 2019 and to the first SP of SQL Server 2019. Wondering if I am missing something (configuration, code, ..) in SSMS to make it work.

Restoring it on SQL MI rather than Azure SQL DB or SQL Server worked perfectly


phpmyadmin to SQL Server I have scripts generated from a MySQL database using phpmyadmin to create database and insert data. The scripts were exported using compatible with MS SQL. Needless to say they are not compatible at all, transaction is open using

Start Transaction;

I tried compatibility level 100 (I'm using latest SQL Server Express), doesn't work.

I cannot install MySQL and cannot contact the software provider or database creator.

I am planning to use notepad++ to find and replace unless there is a better way. Questions are:

is there a better way?
What should I look for to find and replace (assuming someone has done that before)?

Similar questions are asked regularly on this site. This usually boils down to end of line characters. Is your MySQL Unix/Linux or Windows? Use a hex editor to examine your scripts and see if you can find inconsistencies at line ends.

If this fails, you could take a dump using mysqldump and then use sed/awk/grep (or the editor of your choice - i.e. Notepad++) to put the file into a format suitable for SQL Server Express (dates tend to be the tricky ones here).

If you can access the MySQL database using phpMyAdmin, you should be able to use mysqldump.


Extracting sum of time in a period I have a login table, which registers datahora_ini (system entry time) and datahora_fim (system exit time). Sample data:

CREATE TABLE login(cod_user,datahora_ini,datahora_fim)
AS
  VALUES
    ( 101::int, '2017-06-02 08:02:14'::timestamp, '2017-06-02 13:59:23'::timestamp ),
    ( 102,      '2017-06-02 08:03:38',            '2017-06-02 16:13:53' ),
    ( 103,      '2017-06-02 08:05:50',            '2017-06-02 11:44:39' ),
    ( 104,      '2017-06-02 08:06:03',            '2017-06-02 14:01:33' ),
    ( 105,      '2017-06-02 08:06:35',            '2017-06-02 14:00:23' ),
    ( 106,      '2017-06-02 08:10:05',            '2017-06-02 08:31:26' )
  ;

I need to set up a query where I can add the time of all the users that were logged into the system at that time! Fake example of the desired format:

time    time_logged ( Seconds )
07:00   1005
08:00   4980
09:00   8193
10:00   9259
11:00   25692
12:00   18823

My current query :

SELECT
 TO_CHAR(al.datahora_fim,'HH24:00') as Data_ini,
 ROUND(SUM(CASE WHEN al.datahora_fim IS NULL THEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - al.datahora_ini)) ELSE EXTRACT(EPOCH FROM (al.datahora_fim - al.datahora_ini)) END))as tempo_logado

FROM callcenter.agente_login al
LEFT JOIN callcenter.agente a ON ( a.id_agente = al.id_agente )
LEFT JOIN crm.usuarios u ON ( a.crm_cod_usuario = u.cod_usuario )
WHERE ( al.datahora_fim BETWEEN '2017-06-02 00:00:00' AND '2017-06-02 23:59:59' )

GROUP BY Data_ini
ORDER BY Data_ini ASC;


Could not find the Server Agent in SSMS I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8

  Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.

I cannot not find SQL Server Agent. Where can I make this SQL Server Agent visible?  I have given the current user all the access permission.

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc...

The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx


How to get list of all tables in SQL Server without specifying a db? I have a function that allows me to check if a table name is valid, that looks like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tbl

But this has to be executed in the context of the database within which you are checking. I would like to make this dynamic. i.e.

DECLARE @db VARCHAR(250) = @dbString;
DECLARE @tbl VARCHAR(250) = @tblString;
RETURN (
    SELECT TABLE_NAME
    FROM @db.INFORMATION_SCHEMA.TABLES // this is the line!
    WHERE TABLE_NAME = @tbl
)

But I have found that I can't execute dynamic SQL from within a function. This function is called from within an SP.

Alternatively, is it possible to get a return value from an SP when that SP is executed from within an SP?

I have found that this query:

select * from master.sys.databases

gives me a list of databases. Is there anything I can join this to to get a list of tables?

Other solutions that I have found all rely on executing an SP

There is very little metadata you can get across databases.  OBJECT_ID works, so you can see if an object with a specified name exists in a specified database, but that's about it.  EG:

select object_id('adventureworksdw2014.dbo.DimDate')

You can alternatively use a stored procedure with dynamic SQL, something like:

create or alter procedure table_exists @database sysname, @tablename sysname, @exists bit out
as
begin
   declare @sql nvarchar(max) = concat('select @exists=1 from ',@database,'.sys.tables where name = ''',@tablename,'''')
   exec sp_executesql @sql, N'@exists bit out', @exists  = @exists out
end
go

declare @exists bit
exec table_exists 'AdventureWorksDW2014','DimDate', @exists out

select @exists


Unable to connect to AWS redshift database I am trying to migrate to a new PostgreSQL database on Amazon Redshift and get the following error:

Connection to jdbc:postgresql://redshift-cluster-i-created:5439/dev wasn't established. ERROR: code 0

To connect I am using the AWS Schema Conversion Tool->Connect to Amazon RDS for PostgreSQL.

Server name: Cluster-name-as-created-on-redshift
Server port: 5439 as is the default AWS port
Database: left blank to connect to the default database I created
User name and password as created on AWS.

For the Server name I also tried the JDBC URL as per the cluster info on AWS but without the 'jdbc:redshift://' prefix or ':5439/dev' postfix.

Then I tried the public IP address of the AWS cluster for the Server name, then I tried the AWS cluster Endpoint as the Server name.

None worked. Any ideas?

I managed to connect using the full endpoint as given by AWS (less the port number), and then following the instructions to add your IP address to your EC2 vpc security group here:

https://stackoverflow.com/questions/19842720/cant-connect-to-redshift-database/19861418#19861418


Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level I'm using Oracle 11g (unfortunately not 12c, i.e. OFFSET n ROWS FETCH NEXT m ROWS ONLY is not available) and I'm trying to achieve pagination on a hierarchical query.

The aim is to sort the root level entries and retrieve for a given number of root entries all connected children. The pagination (offset and limit) should be performed by the root entries.

Is there any way to achieve this?

I managed to limit the number of root entries but I cannot achieve the sorting on the root level (START WHITH) or specify the offset only for the root level.

SELECT *
FROM my_table
START WITH (
    column_a = 'some value'
    AND column_b = 'some other value'
)
AND ROWNUM <= 10 -- limit rows on top level, however the sorting is arbitrary
                 -- and offset is not possible
CONNECT BY
    PRIOR childid = parentid
    AND column_b = 'whatever'
ORDER BY sort_column  -- applies to overall result, alternatively you could
                      -- use ORDER SIBLINGS BY to sort the entries by their level

Of course it is possible to to some pagination on the overall result by using a subselect like this:

SELECT * FROM (
    SELECT *, rownum as row_num
    FROM my_table
    START WITH (
        column_a = 'some value'
        AND column_b = 'some other value'
    )
    CONNECT BY
        PRIOR childid = parentid
        AND column_b = 'whatever'
    ORDER BY sort_column
)
WHERE row_num BETWEEN <offset> and <offset+limit>

however, this statement would not limit the number of root entries but the overall entries, i.e. it chops off child nodes, as I do not know, how many children exist for a root node.

With the help of Philᵀᴹ's and Joe Obbish's comments I was able to create a query based on recursive CTEs (Common Table Expressions) that allows pagination on the top level of a hierarchical table.

The SQL statement looks like this:

WITH my_hierarchy (<column1>, <column2>, ...) AS (
  SELECT <column1>, <column2>, ...
  FROM (
    SELECT <column1>, <column2>, ... , row_number() OVER (ORDER BY <order_column> ASC) row_num
    FROM my_table mt
    WHERE mt.<column1> = 'some value'
      AND mt.<column2> = 'other value'
  )
  WHERE row_num between <offset> and <offset+limit> -- pagination on top level
  UNION ALL
  SELECT mt.<column1>, mt.<column2>, ...
    FROM my_table mt
    INNER JOIN my_hierarchy mh   -- recursive self join
        ON mt.parentId = mh.id
)
SELECT *
FROM my_hierarchy


Despite of using GRANT ... ON ALL TABLES (or ALL SEQUENCES) TO user, user cannot access newly created tables (or sequences) For a production server, I wanted to separate the super user, admin and app roles to limit scope of mistakes.

So I've created an account that our app uses to access the DB, and an admin account. The admin account is also used to run migrations to create tables.

I thought I'd ironed out all the issues, the app user can access all existing tables (which were created by the super user), however having now run a migration that creates a table, the app has no access to that table.

The users were granted access with the following commands:

GRANT CREATE, CONNECT ON DATABASE test TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public
  TO admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin;

GRANT CONNECT ON DATABASE test TO api;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO api;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api;

Then the admin user ran CREATE TABLE new_table; and trying to access that table, the app gets permission denied for relation new_table

1) What grant am I missing to allow api access to the new table
2) Out of curiousity, if I didn't have a record of the previous grants, how would I examine the grants of the users and discover the privilege that's missing

ALL TABLES/ALL SEQUENCES in that context means all tables/sequences that exist now, at the time the grant is issued. It doesn't include tables/sequences created after the grant was issued.

Your first option is to include the grants explicitly in the migrate scripts. Every time you create a table/sequence also issue the respective grants to api.

Or you can alter the default privileges. In Postgres default privileges can be stored, that will be applied to a newly created object automatically. You can change them with ALTER DEFAULT PRIVILEGES.

In your case your probably after

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT SELECT,
                                  INSERT,
                                  UPDATE,
                                  DELETE
                                  ON TABLES
                                  TO api;

and

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT USAGE,
                                  SELECT
                                  ON SEQUENCES
                                  TO api;

Note that the migration scripts need to run under the same user, that did the ALTER DEFAULT PRIVILEGES for them to take effect.

The privileges on objects can be obtained via the pg_class catalog table. For example, if you wanted to query all regular tables in the schema public you could do:

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'r';

For sequences the relkind is S instead of r, so

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'S';

would give you the sequences in public.

The column relname displays the object's name and relacl holds an array of the privileges on the object in form of an aclitem. An ACL is represented by a string of the form <grantee>=<privileges>/<grantor>. <grantee> can be empty if the privileges are granted to public. <privileges> encodes the privileges in form of a string of letters. In your case you want to see api=arwd/admin for a regular table and api=rU/admin for sequences.

a is for "append" (INSERT),
r for "read" (SELECT),
w for "write" (UPDATE),
d for DELETE and
U for USAGE.

More details on the ACLs can be found in the manual on GRANT

Explicit granting is more "secure" in the way, that you don't "forget" the default privileges are in place one day when creating a table api shouldn't be able to access. You'd have to explicitly revoke the privileges after the creation of the object in such a case.

Using default privileges is more convenient of course.


Error 1041 "Out Of Memory" after pointing tmpdir to new disk space I want to index a very large column in a very large table. The default /tmp does not have enough disk space and is throwing no space left on device error.
I have a much larger filesystem partitioned where I can point innodb_tmpdir to.

I created a tmp dir in this partition: /mnt/partition/tmp
Changed the permissions of this directory to 777 chmod -R 777 /mnt/partition/tmp
Then I set the innodb_tmpdir variable to point to this directory via mysql client: SET VARIABLE innodb_tmpdir='/mnt/partition/tmp';
The variable is set correctly by executing: SHOW VARIABLES LIKE 'innodb_tmpdir';

The issue arises when I try to create an index with the following basic command structure:
create index a on b (c, d, e, f);

The following error is instantly thrown:

ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

What is with this error? Do I need to set some permissions/configs for my partition?
NOTE If I do not set innodb_tmpdir and keep everything to default, the index command runs without this issue. (obviously it crashes halfway through due to the limited disk space which is the actual reason why I am changing tmpdir
my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8
wait_timeout=604800
interactive_timeout=604800
local_infile=ON
binlog_expire_logs_seconds=600
skip-log-bin

swap size: 8.39GB. There is 7GB of memory on the server.
MYSQL Version 8 on Ubuntu 18.04. Machine is fully dedicated to MySQL.
After executing the create index command on new innodb_tmpdir error.log file is showing:
[ERROR] [MY-012792] [InnoDB] Cannot create temporary merge file

If innodb_tmpdir being set to the new location is causing an instant complaint from the server, there's a very good chance that apparmor is preventing MySQL from accessing the location or that the system is reacting to the 777 permissions, which are often rejected by a lot of server applications for obvious reasons. You will need to check your error.log file to see whether the innodb_tmpdir location is being used.

apparmor fixes are quite easy to fix from Oracle a blog a long time ago:

If you want to relocate the data directory in MySQL (in this example, to the /data/ directory), and AppArmor is not letting you, add the following two lines to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:
/data/ r,
/data/** rwk,

...and then reload the AppArmor profiles:
# service apparmor reload


Remove specific duplicates (all but latest) When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove?

DELETE FROM dbo.ATTENDANCE
    WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE
    GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

And this works great. The reason why I am using this one is because the only unique id available is one in the IDENTITY column. And to determine if there are duplicate rows I have to look at a combination of multiple columns.

But if I have a set of duplicate rows how do I / how does SQL Server decide which to remove? And how would I force it to remove all but the one with the highest IDENTITY value?

EMPLOYEE_ID     ATTENDANCE_DATE     AUTOID
A001            2011-01-01          1
A001            2011-01-01          2

If I would run the query now it happens to remove the second one, with AUTOID 2. But I am trying to remove all but this one (because this is the one latest added).

You could implement a query using row_number() to delete everything but the most recent row. This partitions the data by the employee_id and orders it by the autoId column, then you delete everything that is greater than the first row number:

;with cte as
(
  select [EMPLOYEE_ID], [ATTENDANCE_DATE], [AUTOID],
    row_number() over(partition by [EMPLOYEE_ID], [ATTENDANCE_DATE]
                      order by  [AUTOID] desc) rn
  from dbo.ATTENDANCE
)
delete
from cte
where rn > 1;

See SQL Fiddle with Demo


Migrating data from Firebird to CSV on OSX I have a .gdb Firebird database file that I would like to partially transform to .csv. From the CLI tool, I'm able to do the following:

CONNECT "/Users/jpmonette/Desktop/file.gdb";
OUTPUT /Users/jpmonette/Desktop/output.csv;
SELECT EMAIL,FIRSTNAME FROM ACCOUNT;

This seems to be working as expected - outputting stdout to a file. However, I would like to know if there is a way to output the data in a easy to consume format (JSON? CSV? SQL dump?). I had a look at fbexport and fbcopy, but they both seem to be not available for OSX.

Any clue on how I could get around that?

You can concatenate output to CSV, like select email || ',' || firstname from account with column header turned off using set heading


Oracle: Performance centric ways to notify user on reaching "logical" lock expiration event Current Setup:

My application uses Java (Spring) and Oracle 11g and has functionality where logical locks are placed on an object before updates are made in the table.

For example there are 2 tables EMPLOYEE and EMPLOYEE_LOCK. When any update is made to employee, an entry is inserted into EMPLOYEE_LOCK table to indicate that for next 30 seconds a particular employee is locked.

So EMPLOYEE_LOCK table looks like below (as of 10AM)

EMPLOYEE_ID | LOCK_TYPE | LOCK_UNTIL
-------------------------------------
100         | mandatory | 10:00:30 AM (30 seconds from 10AM)

After 30 seconds a notification needs to be sent out to all clients that a lock has expired and this employee is available again for updates.

Now to identify if LOCK_UNTIL duration has reached, application makes a SQL call to database every 2 seconds to see if 10:00:30AM has reached.

Performance Issue:

This call every 2 seconds is causing lot of overhead on the database and on the application server.

I am looking for a better ways where Oracle itself initiates a notification to the server when lock expiration time has reached. Is there any way I can achieve this?

Possible solutions:

One solution could be to use DBMS_SCHEDULER package and create a scheduled job. But I could not find anywhere in the documentation, some way for the job to notify application server. It can send an email but that wont help me much.
Second option could be to use "Database Change Notification feature" but this is triggered on a DML or DDL change on the DB object which is not happening in my case.

Can some one suggest some approach where Oracle some how notifies application server when lock expiration time is reached?

Edit (To answer questions raised by Gil Shabtai)

Its probably my bad that I tried leaving some of the points from the discussion which I thought were irrelevant to the question I was asking.
Here are the answers to your issues / questions raised

Concern:App knows lock expiry time but still checks
Answer: A 3rd party CRM application can also add lock in the table. So application is the not the only way to add lock. So application does not always knows what lock expiration times are.
Concern:30 seconds locking period Performance issue
Answer: This was just an example, the actual locking period is configuration and default is 5 seconds.
Concern:Caller needs to go to sleep if entity is locked.
Answer: Caller also has a mechanism to request a notification when locks are released. All these requests go to a queue. So it is critical that the caller be notified (in the sequence they requested for a lock) when the lock is expired. So caller going to random sleep may not be an option.

My primary goal (with this question) was to see if Oracle can give me some way to identify this expiration time trigger and initiate an activity rather than Application server initiating one.

seems like a serious design flaw. probably resulting from a pile of flaws. why is it that the application knows when the lock should be expired, and yet it checks again and again to see if it has expired? this means that the time for expiry is meaningless.
if you can change the complete design, the current situation is probably resulting from the fragmentation of the data handling parts of your application.
if you can't, you still need to realize the problemof a going for a time-based lock of a rigid constant (which goes as high as 30 seconds, a performance red flag), and in the same time creating a propagating mechanism notifying the release.
in my opinion, The much-less-than-perfect solution, in your case, is that those waiting for the logical lock to be released should sleep for the time-frame in which the lock is held, and then they should access and see if the record is still locked. if it still not free, they should probably sleep themselves into a random short period (realistic in your app's terms), before checking again to see if the record is locked. some upper threshold should be set for these waits as well.
in general, what you need is a messaging system between waiters.


Problem with Processing Dimensions with Multiple Facts I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts.

When I process one of the dimension, it tries to process the underlying measure groups, however, as the other dimensions are not yet updated with the new data, the measure group processing fails saying the key doesn't exist.

How should we trigger a dimension process in such a case?

Illustration:
3 Fact Tables C1, C2, C3;
8 Dimensions D1, D2, D3, D4, D5, D6, D7, D8

C1 is built on D1, D2, D3, D4
C2 is built on D1, D5, D6
C3 is built on D1, D2, D5, D7, D8

Assume that I have made updates in the Dimensions D1 and D5 and trigger a Dimension process one after the other using AMO.

When I process D1, it processes Dimension D1 and measure groups C1, C2 and C3. When it tries to process the measure group C2, it complains that the updated data in D5 is still not populated.
When I Process D5 first, it processes Dimension D5 and measure groups C2 and C3.
When it tries to process the measure group C3, it complains that the updated data in D1 is still not populated

Note:

It's SSAS 2012
We are processing dimensions using ProcessUpdate and
process using ProcessFull

Please do let me know if you need any additional information.

You need to process D1 and D5 in the same transaction. Then both will complete processing before measure groups are processed. Make sure you mark Transaction=true (the default unless you explicitly set it to false) and Parallel.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D1</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D5</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <CubeID>C1</CubeID>
      </Object>
      <Type>ProcessFull</Type>
    </Process>
  </Parallel>
</Batch>

I would highly recommend you digest this article.

Also are you processing all cubes as ProcessFull or only some cubes or some measure groups or some partitions. If you are processing all cubes and measure groups and partitions then don't bother with all this. Just do a ProcessFull on the database object. ProcessUpdate on dimensions is only for incremental processing which assumes you aren't processing all partitions.


Distinct Random select Hi can anyone help with this little MS SQL Puzzle.

I basically need to get a 1 random answer from each of the submitters.

id,   submitter,   answer

1,    James,        Blue

2,    James,        Red

3,    James,        Orange

4,    Ian,        Yellow

5,    Ian,        Pink

6,    Robert,       Red

I have tried the following but obviously this will always get the min(id) or Max(id)  what I need is a random(id)?

select * from #tmpData
 where id in (
  select min(id ) FROM #tmpData
   group by submitter
            )

Thanks in advance..

Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter:

; with r as
  ( select id, submitter, answer,
           rnd = row_number() over (partition by submitter order by newid())
    from #tmpData
  )
select id, submitter, answer
from r
where rnd = 1 ;

Test at: SQLfiddle


How can I count some rows before inserting? Here is my table structure:

// QandA
+----+----------------------------------------+------+---------+-----------+------------+
| Id |                   body                 | type | related | author_id | date_time  |
+----+----------------------------------------+------+---------+-----------+------------+
| 1  | content of question1                   | 0    | null    | 12345     | 1467468795 |
| 2  | content of first answer for question1  | 1    | 1       | 53456     | 1467469311 |
| 3  | content of question2                   | 0    | null    | 43634     | 1467469512 |
| 4  | content of second answer for question1 | 1    | 1       | 43665     | 1467470098 |
| 5  | content of first answer for question2  | 1    | 3       | 43324     | 1467471291 |
+----+----------------------------------------+------+---------+-----------+------------+
/* Columns:
   type: 0 means question, 1 means answer
   related: null means question, else it's the id of its own question
*/

I'm trying to implement this (something exactly like what stackoverflow does). An user can ask:

1 question per 90 minute
6 question per day
50 question per month

So I need to count the number of previous question for an user before letting him to ask. How can I do that?

I have two kind of query:

One:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
    SELECT NULL, :body, 0, NULL, :id, unix_timestamp
    FROM (select count(*) as num_month,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
          from QANDA
          where author_id = :id and
               type = 0 and
               date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
         ) a
    WHERE num_month < 50 and num_day < 6 and num_90min < 1;

Two:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM dual
WHERE user_id = :id
   AND NOT EXISTS (
        SELECT count(*) AS num_month
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
        FROM QandA
        WHERE author_id = :id and
              type = 0 and
              date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
        HAVING num_90min  > 50
            OR num_day    > 6
            OR num_month > 1
    )

Ok, which one is better? Or is there a third option which is better than them?

Counting and subqueries are somewhat costly; let's try to avoid them.

If the latest question is less than 90 minutes ago, disallow.  This involves MAX() and an index on the timestamp column.
If 6 or more today (or today+yesterday), disallow.  This involves a summary table, see below.
If 50 or more ...  This involves the same summary table, plus a SUM() of only 30 rows, not 50, in worst case.  (OK that does not help much.)

Summary table something like...

CREATE TABLE Summary (
    author_id ...
    day DATE NOT NULL,
    ct TINYINT UNSIGNED,
    PRIMARY KEY(user_id, day)
) ENGINE=InnoDB;

INSERT INTO Summary (author_id, day, ct)
    VALUES (:id, CURRENT_DATE(), 1)
    ON DUPLICATE KEY UPDATE ct = ct+1;

-- How many "since midnight":
( SELECT ct FROM Summary WHERE author_id = :id
       AND day = CURRENT_DATE() ) >= 6  -- disallow
-- How many "since midnight yesterday" (overkill; see below):
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 1 DAY ) >= 6  -- disallow
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 50 DAY ) >= 50  -- disallow

Sinece the second only checks for "6 in the last 2 days", it needs this to further check:

-- Exact count since this time yesterday:
( SELECT COUNT(*) FROM Qanda WHERE author_id = :id
       AND timestamp > NOW() - INTERVAL 1 DAY ) < 6

The query against the summary table should be faster, hence is probably worth doing as a quicker check.

I would define the limit as "6 since midnight" in order to simplify and speed up the checks.

Qanda needs INDEX(author_id, timestamp)


Delete Archive log file I am using oracle database 11g. Some day's back we have deleted our Archivelog Manually, and now it's getting full and due to that we are not able to connect to our database.

So how should i delete archivelog files :

Is there any way by which we can delete archivelog through rman without connecting to the target database.

Or can we increase the archivelog disk space.

Thanks for the reply.

Deleting archivelog data would normally be done as part of the backup operation removing older archive data after a new backup is taken.

You should read the following:

Oracle Concepts Guide (pdf), particularly chapter 11
Administrator's Guide, chapter 13 (which has useful information on Archive Logs).

Only after reading all that should you look at the Backup and Recovery guides section on Configuring an Archived Redo Log Deletion Policy. I purposely did not provide a direct link.  When you are ready, you can lookup the book in the Master Book List.


Conditional Select from Window Function I'm using lead() to calculate the difference in values between two records in the same table.  This works fine until I need to not report on one of the records based on some other value in the window.

Given the following table.

>  t_id   |     update_time     |     last_reset      | bytes_tx
> ----------+---------------------+---------------------+-----------
> 3680000 | 2019-04-28 19:58:05 | 2019-02-21 15:08:27 | 188960431
> 3680000 | 2019-04-28 20:13:06 | 2019-02-21 15:08:27 | 188991801
> 3680000 | 2019-04-28 20:28:08 | 2019-02-21 15:08:27 | 189022965
> 3680000 | 2019-04-28 20:43:07 | 2019-02-21 15:08:27 | 189057066
> 3680000 | 2019-04-28 20:58:06 | 2019-04-19 12:18:17 | 89088869
> 3680000 | 2019-04-28 21:10:11 | 2019-04-19 12:18:17 | 89114302
> 3680000 | 2019-04-28 21:13:03 | 2019-04-19 12:18:17 | 89120288
> 3680000 | 2019-04-28 21:28:05 | 2019-04-19 12:18:17 | 89151994
> 3680000 | 2019-04-28 21:43:06 | 2019-04-19 12:18:17 | 89183203
> 3680000 | 2019-04-28 21:58:03 | 2019-04-19 12:18:17 | 89215093

I can get what I want from this using lead() to report the difference of the current record, and the upcoming record.

select
    lead(update_time,1) over(partition by t_id order by update_time) - update_time as interval,
    lead(bytes_tx,1) over (partition by t_id order by update_time) - bytes_tx as bytes_diff
from table

 interval | bytes_tx
----------+----------
 00:15:01 |    31370
 00:15:02 |    31164
 00:14:59 |    34101

That works well right up until last_reset changes, which zeros my bytes_tx count resulting in a negative integer being returned.

I'd like to simply skip records where lead(last_reset) isn't equal to current last_reset, but I can't use a window function in the WHERE clause.

I'm essentially looking for the equivalent of

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

And I'm pretty stumped right now.

Any clues for the clueless?

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

Something like the following (untested):

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

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

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


Saving images as base64 encoded strings, why is it bad? I've seen this on one of the production databases I've come across and these images apparently cover a large portion of their DB. After researching a lot I couldn't really find a lot of good answers pertaining as to why it is actually bad in terms of performance.

Yes, it is not stream friendly and takes up ~33% more space: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

Sure, it gives wrong hints to DB: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

And other answers like: storing files on mongodb don't really explain why this is problem

But, what is actually going on that might cause databases that store images as base64 encoded string to lose performance? Why would a DB lose performance on selects(or any other aspect) due to such practices?

I would also like an answer that specifies a NoSQL(MongoDB) vs RDBMS(PostgreSQL) performance in this regard. Does the nature of having things in memory like MongoDB make the performance hit even worse? And if so, why? How is this image saved as a base64 string behaving on both systems?

The one reason I can see is that you are doing unnecessary work that way. PostgreSQL will compress and toast the base64 string, so you have to pay the price of compression and decompression, unless you set the column to EXTERNAL, then you don't compress, but you waste storage space and I/O bandwidth.

Hint: if you store compressed binary data in PostgreSQL, set the bytea column to EXTERNAL storage. Otherwise you waste CPU in a futile attempt to compress the data.


SQL Developer cannot connect to Oracle database, yet sqlplus can! I am having a problem connecting sql developer to my Oracle 10g database.  sqlplus is able to connect fine, but when I try to open the connection in sql developer, I get 'ORA-12514 TNS does not know of service' error.  I have tried modifying my tnsnames.ora file.  It has the following:

COTT3 =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVER_NAME = COTT3)
  )
)

Can anyone assist with any other files I may need to modify or let me know where im going wrong?

Thanks

UPDATE
Output of lsnrctl status

 [oracle@VM-COT-DEV1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2012 13:57:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                02-FEB-2012 13:01:25
Uptime                    0 days 0 hr. 55 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

enter code here

Try this:

COTT3 =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = COTT3)
  )
)

If that doesn't work, replace "SERVICE_NAME" with "SID".

Put this in $ORACLE_HOME/network/admin/listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = COTT3)
          (ORACLE_HOME = /opt/oracle/oracle/product/10.2.0/db_1 )
    )
  )

Then restart the listener with:

lsnrctl reload

Are you 100% sure that the database has started up?

Does:

ps auxww | grep pmon | grep -i cott3

... produce any output?


Show cassandra help documentation in terminal instead of browser I've just started playing around with Cassandra through the cqlsh cli. When using the help command for the topics the documentation opens in the browser. Is there a way to show the documentation in the terminal and not open the browser for this? I can't find any descriptions on how to do it and my google searches doesn't turn up anything. I'm using Apache cassandra cqlsh 5.0.1.

From cqlsh reference documentation:

  cqlsh help displays in the terminal. CQL help is only available online in HTML.

You can download offline documentation in PDF & HTML formats from DataStax documentation site.


Howto recover SQL 2016 cluster instance with bad tempdb path setting? Today I have changed the tempdb's file locations using the ALTER DATABASE MODIFY command and I have accidentaly set it to use the root of the volume. The SQL Server service failed to restart and came up with Access denied errors in eventlog. It is a 2016 Sp1 failover cluster.
I tried to workaround this using

NET START MSSQL$INST1 /f /T3608

but it does not work.

What can I do about this? How can I restart the service to get able to re-define the setting to use a subfolder.

Pinal Dave wrote up a nice article on how to fix this. Have you tried this?

How to Start SQL Server Service Without tempdb?

  NET START MSSQLSERVER /f
  Connect to SQL via SQLCMD
  Run ALTER DATABASE tempdb command
  Restart SQL Server

From the looks of things you are having an issue starting SQL that is a named instance. This Microsoft article explains how to use that command with a named instance:

How to: Start an Instance of SQL Server (net Commands)
Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service

Are you getting any errors in the error log for the service startup failure?


What is the correct way to define and query a 3NF Associative entity A subset of my database contains the tables contacts, networks and networkContacts which is my associative entity. I have defined it using a composite primary key composed of the primary keys of the first two tables. The tables are are defined as follows:

CREATE TABLE contacts (
  cid char(88) NOT NULL,
  alias varchar(255) NOT NULL,
  PRIMARY KEY (cid)
)

CREATE TABLE networks (
  nid int(11) NOT NULL,
  fingerprint char(88) NOT NULL,
  port int(11) NOT NULL,
  network_alias varchar(255) NOT NULL,
  PRIMARY KEY (nid),
  UNIQUE KEY fingerprint (fingerprint),
  UNIQUE KEY port (port),
  UNIQUE KEY network_alias (network_alias)
)

CREATE TABLE networkContacts (
  nid int(11) NOT NULL,
  cid char(88) NOT NULL,
  PRIMARY KEY (nid,cid),
  CONSTRAINT networkContacts_ibfk_1 FOREIGN KEY (nid) REFERENCES networks (nid),
  CONSTRAINT networkContacts_ibfk_2 FOREIGN KEY (cid) REFERENCES contacts (cid)
)

Is this correct and if so how should I correctly query the contacts table by inner joining it with the networks table as every example I have seen uses both fields in the primary key to conduct the join. As you can see I only have one field of the associative entity primary key in each of the other tables respectively so doing a join based on both components can't be done.

In order to JOIN contacts to networks you need to first JOIN to networkContacts.

select *
from contacts a
    inner join networkContacts b
        on a.cid=b.cid
    inner join networks c
        on c.nid=b.nid


How to sort records before inter-row calculation? I have a time sequence data set to analyze but the difficulty is that

the data set is very big.
the time sequence events are committed by different objects with affiliation.
its in mysql, there is no id like index column to join

e.g.

----------------
dt     obj        comp
----------------
t1,    object_a,   component_1
t2,    object_b,   component_1
t3,    object_b,   component_2
t4,    object_a,   component_1
t5,    object_b,   component_1
t6,    object_b,   component_2
----------------

I am trying to know the delay between each commit
from each component of each object.
i.e.
obj_a.comp_1.delay1 = obj_a.compoent_1.t4 - obj_a.component_1.t1

etc.

I tried to join

select timediff( t1.dt, t.dt ) as delay
from  table as t
join table as t1
on t1.comp = t.comp
and t1.obj = t.obj
and t1.dt = (
    select min(t2.dt)
    from table as t2
    where t2.obj = t.obj
    and t2.comp = t.comp
    and t2.dt > t.dt
)

which taking forever, so I am thinking if there is any way to sort on the results and do inter-row calculation with variables, it would be much faster.
but it failed every way with sub-query and views where the sort is always done after calculation. From what I read on order by, it seems impossible. So what options do I have to to achieve this reasonably fast?

To be more specific:
If I do the following:

SELECT
    obj
    , comp
    , dt - @prev
    , @prev := dt
FROM
   table
ORDER BY obj, comp, dt ASC

the result is

obja, comp1, t1-null
obja, comp1, t4-t3      # should be t4-t1
objb, comp1, t2-t1
objb, comp1, t5-t4      # should be t5-t2
objb, comp2, t3-t2
objb, comp2, t6-t5      # should be t6-t3

Since the calculation happens on unsorted data, the results do not make sense. The table in my case is actually a view, the component is calculated field. It doesn't have an index. The join-subquery way could takes half an hour.
But if I save the sorted results into a table and calculate with variable, it only takes minutes.
The data set is very big, I only tested with a small subset.
I am not sure the method of saving to extra table is a scalable solution.
So I am looking for advise on alternatives.

I have managed to successfully use variables and sorting to solve your problem. This is my test set-up:

CREATE TABLE atable
    (`dt` datetime, `obj` varchar(8), `comp` varchar(11))
;

INSERT INTO atable
    (`dt`, `obj`, `comp`)
VALUES
    ('2016-09-13 06:00:01', 'object_a', 'component_1'),
    ('2016-09-13 06:00:02', 'object_b', 'component_1'),
    ('2016-09-13 06:00:04', 'object_b', 'component_2'),
    ('2016-09-13 06:00:08', 'object_a', 'component_1'),
    ('2016-09-13 06:00:16', 'object_b', 'component_1'),
    ('2016-09-13 06:00:32', 'object_b', 'component_2')
;

And this is the query:

SELECT
  dt,
  obj,
  comp,
  diff
FROM
  (SELECT @obj := '', @comp := '', @dt := CAST('1970-01-01' AS datetime)) AS var,
  (
    SELECT
      TIMESTAMPDIFF(SECOND, IF(obj = @obj AND comp = @comp, @dt, NULL), dt) AS diff,
      @dt := dt AS dt,
      @obj := obj AS obj,
      @comp := comp AS comp
    FROM
      atable
    ORDER BY
      obj,
      comp,
      dt
  ) AS sub
;

As this SQL Fiddle demo will show, the query returns the following output:

dt                   obj       comp         diff
-------------------  --------  -----------  ----
2016-09-13 06:00:01  object_a  component_1  NULL
2016-09-13 06:00:08  object_a  component_1  7
2016-09-13 06:00:02  object_b  component_1  NULL
2016-09-13 06:00:16  object_b  component_1  14
2016-09-13 06:00:04  object_b  component_2  NULL
2016-09-13 06:00:32  object_b  component_2  28


Are duplicated NVARCHAR values stored as copies in SQL Server? I'm designing a table that will contain a lot of rows. So need to be careful not to store to much information. One of the columns is a NVARCHAR(MAX) column and it contains the address of our customers. As addresses do not change often, this column will contain many repeated values and thus contains quite some redundancy.
So I was wondering if I need to normalize this myself by maintaining some sort of look-up table to address strings (note that if an address changes I need to maintain history - so it's not a matter of usual normalization), or if SQL Server is pointing to the same reference of the string behind the scenes. Or maybe it offers a column option to do so. Another approach that came into my mind is to use COMPRESS but I guess this does not make sense as the data itself (i.e. the address) is not long.
Reading/writing performance is not so much of a concern as the data will be accumulated over time.

Under "normal" conditions, no, data in VARCHAR and NVARCHAR columns is not de-duped (although duplicate attribute and/or element names in a single XML value are reduced to a unique instance).
Using one of the Data Compression options is probably your best bet. Here are some things to consider:

Unicode Compression (part of Row Compression) only works on NVARCHAR(1 - 4000), not NVARCHAR(MAX) (please vote for / support: Unicode compression NVARCHAR(MAX)).
Page Compression can work with NVARCHAR(MAX), but only for in-row data. Off-row data (LOB pages) is not compressed.

Since the data won't really be changing, you should look into the Columnstore Index options (also available in Azure SQL Database):

Columnstore indexes: Overview
Columnstore indexes - Design guidance

Columnstore compression should be better than Page compression.

Also, you should probably avoid using the SPARSE option, due to:

The SPARSE option offers no benefit compared to Data Compression.
It is mainly intended for columns sets / wide tables (i.e. up to 30,000 columns).
It mostly helps with fixed-length datatypes (e.g. INT, DATETIME). So, prior to Data Compression being available, it was useful for CHAR and NCHAR, but not for VARCHAR or NVARCHAR as they don't take up space when NULL.
It only benefits columns set to NULL.
It slightly hurts non-NULL values by adding 2 bytes to each one.
You should probably have NULL for 50% - 60% of the rows in order to get enough savings for it to be worth using this option.

For more details on working with character data, please see the following post of mine:
How Many Bytes Per Character in SQL Server: a Completely Complete Guide


Firebird linked tables in Access 2010 I have multiple data sources in multiple databases that I have been tasked with correlating for business analytics.  I currently have ODBC connections to two (one Pervasive, one Firebird) that I can use successfully in Excel.  A DBA friend suggested I use Microsoft Access to create linked tables and perhaps make creating lookup tables between the data sets easier.

I've run into a problem.  Although Excel uses the ODBC connection to Firebird fine, Access can't create the linked table through the Create Linked Table dialogue.  I get the following error message:

ODBC--call failed

[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 30, column 13
when (#-104)

I take this to mean that whatever query Access is using to attempt to create the linked table is failing to be interpreted correctly by the Firebird database, but I don't get to see any of the code because Access is handling it.  I seem to recall reading that Access tend to fall back to SQL query structure when it can't figure out how to talk to certain databases, but I can't find the source.

I did see a post by Max Vernon with regards to linked tables in SQL server 2008.  He suggested using Microsoft OLE DB Provider for ODBC Drivers, but I have been unable to locate the driver.

I guess my question is this: Is it possible to manually configure linked tables in Access, do I need to get the OLE DB interface working (I have yet to figure out where it is to install it), or is there another method I should be exploring?

It is very easy to connect, just configure ODBC. However, when you install Firebird Server, you must select the option to copy files to the Windows directory.


INSERT to a table from a database to other (same SQL Server) using Dynamic SQL I need to copy a record from a Database DBa, Table Tbl1 to Database DBx, Table Tbl1 and get the Scope Identity inside the same SQL Server 2005 server

I have already checked this:
How to Dynamically change the database using TSQL

but my issue is more complicated.

I do want just to execute something in the DBx being inside DBa.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.

The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?

Example code:

CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
    AS

    DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)

    SELECT @group_name = group_name ,
    @arrival = arrival,
    @departure = departure,
    @contact_id = contact_id
    FROM grp
    WHERE group_id = @group_id

    DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
            @sql  nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?

        EXEC @exec @sql;
        go

Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(

Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:

CREATE PROCEDURE dbo.pr_consolidation_copy_group (
    @group_id numeric(10,0),
    @database_to varchar(100)
)
AS
SET NOCOUNT ON;

DECLARE @group_name char(100)
       ,@arrival datetime
       ,@departure datetime
       ,@contact_id char(82);

 SELECT @group_name = group_name ,
        @arrival = arrival,
        @departure = departure,
        @contact_id = contact_id
   FROM grp
  WHERE group_id = @group_id;

DECLARE @sql_cmd NVARCHAR(MAX);
    SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
                 + 'INSERT grp(group_name, arrival, departure, contact_id)'
                 + 'SELECT @group_name, @arrival, @departure, @contact_id;'
                 + 'SET @id = SCOPE_IDENTITY()';

DECLARE @params NVARCHAR(MAX);
    SET @params = '@group_name CHAR(100),'
                + '@arrival DATETIME,'
                + '@departure DATETIME,'
                + '@contact_id CHAR(82),'
                + '@id INT OUTPUT';

DECLARE @id INT;

   EXEC sp_executeSql @stmt = @sql_cmd
                    , @params = @params
                    , @group_name = @group_name
                    , @arrival = @arrival
                    , @departure = @departure
                    , @contact_id = @contact_id
                    , @id = @id OUTPUT;

-- @id is now available to use
END

You can find more information about outputting a variable in the documentation for sp_executeSql.


Why can't I take RDBMS Database Design practices, and use for NoSQL MongoDB Database Designs? Let's say I have a  BankAccount table, and a BankAccountHistoryTransactions table.

When it comes to RDBMS database schema design, most Database designers would recommend something like the following:

BankAccount Table

int: BankAccountNumber Primary key

double: CashBalance

......

..

Also, in an RDBMS Database Design, the BankAccountHistoryTransactions Table would be like:

BankAccountHistoryTransactions Table

int: BankAccountHistoryTransactionsId Primary key

int: FK_BankAccountNumber Foreign key

DateTime2: DateOfTransaction

.................

.........

In NoSQL MongoDB database schema, it would b more like a BankAccount collection containing an embedded BankAccountHistoryTransactions collection:

db.BankAccount.find().pretty()
{
    "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
    "BankAccountNumber" : "7575785885859",
    "CashBalance" : "890399",
....................................,
...............................,
.......................,
    "BankAccountHistoryTransactions" : {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountHistoryTransactionsId": 1,
        "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

}
}

My problem with the NoSQL MongoDB Database Schema design approach is that a Bank Account could have a tonne of BankAccountHistoryTransactions entries (possible going into hundred thousands of BankAccountHistoryTransactions entries for a Bank Account).

Therefore, wouldn't it be better if we used a pseudo-foreign key relationship like in the following:

    db.BankAccount.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountNumber" : "7575785885859",
        "CashBalance" : "890399",
    ....................................,
    ...............................,
    .......................,
}

And a different separate collection for BankAccountHistoryTransactions

db.BankAccountHistoryTransactions.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "FK_BankAccountNumber" : "7575785885859",
"BankAccountHistoryTransactionsId": 1,
            "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

    }

I've heard that NoSQL MongoDB database designers Discourage the use of pseudo-foreign key relationships like the one above.  However, wouldn't it be much more organized, and modular to have a like the "pseudo-foreign key relationship" design?  ( Correct me if I'm wrong but Performance might be a problem, but it's certainly more organized and modular )

If you want to use a relational structure, and you're thinking about doing it like this with a NoSQL system, use an RDBMS, that's what they're designed to do.

Using the right tool for the job will always result in better performance, and no-one has ever been fired for using an RDBMS where one fits instead of using a NoSQL solution where it doesn't make sense, like in a banking situation where you need 100% guaranteed consistency, not "eventual consistency".

Using relational algebra in a non-relational database will just make you replicate relational functionality in code, and I can guarantee you're not better at that than, say, SQL Server or Oracle.


SQL Server sharing drive location DBA newbie here. Did some searching online but cannot find a definite answer on this.
Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications that go under a Program Files folder?
I assume it is not but maybe this depends on the type of storage that is used? We use SAN storage.
I'll rephrase....If the drive that contains .MDF files is shared with other non-SQL Server applications, could this potentially affect SQL Server's read/write performance depending on the storage that is used?
The answer isn't obvious to someone that is new to SQL Server, let alone hardware storage. If everything is located on the same SAN, does it really matter if they are on the same drive? I'm researching and can't find the answer and the only SQL Server hardware book out in the market is from 2011.

"could this potentially affect SQL Server's read/write performance?"

yes.  Here are some things to consider:
1 - The type of file(s) matters in context of performance.  Files which are often read/written to will of course share the resources with the SQL Server files. Files which aren't being accessed when SQL Server files are being accessed won't noticeably affect performance.
2 - The type of storage matters.  Shared IO has more severe performance consequence on spinning disk storage than SSD.
The idea is similar to the "Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files." as stated in Microsoft docs.  Read placing any two files with simultaneous IO demand on the same device can cause contention for that device, resulting in poor performance.
Will it cause poor performance? Far too many variables to take into consideration such as storage specs & config, workload patterns of both SQL Server and w/e is sharing its resources, etc. to have concrete answer.