Find out what encryptor name my database is using

Now I don't know if a symmetric key or a certificate is used to encrypt database. The encryptor_name for one encrypted with certificate is I find in sys.certificates of master database. It is name of the row display in sys.certificates

If you look in sys.database_encryption_keys it'll tell you what database is encrypted using TDE (filter out TempDB [dbid 2]) along with the type of encryptor (cert/asymmetric key), and the thumbprint of that encryptor which can be used to find the encryptor in either the certificates or asymmetric keys catalog.

  What about the ASSYMETRIC KEY.Can you give give me examples to encrypt database with it, then the script to display information of the encryptor_name that it use.

To use an asymmetric key as an encryptor you'll need a hardware security module (HSM) or a software emulation of an HSM such as Azure Key Vault (AKV) which has a complete walk through. Other than the specific Azure part of AKV, the same steps would be taken for an on-prem HSM.


Different plans or buffers for different connections?

When you say "same params and all" then you may find different credentials were used or the same compiled plan wasn't re-used based on timing.

I suspect here that the first web call and the SSMS call used different plans. The DBCCs then cleared the plan, so the second web call worked as expected

There are criteria that determine plan re-use such as using schema on all object references (eg dbo.table not table) which will be affected by different credentials.

However, the main culprit is usually parameter sniffing. See

Does restarting SQL Server speed it up?
Suddenly Slow Execution Plan for Stored Proc
Code creates different plan when ran ad-hoc vs. in a stored procedure

Failing that, some SET options may be different too:

Why would SET ARITHABORT ON dramatically speed up a query?

Note: these follow through to good background reading...


Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table uses a transactional engine, like InnoDB):

START TRANSACTION ;

    DELETE FROM
        payment_transactions_daily_facts
    WHERE
        date BETWEEN '2018-11-11' AND '2018-11-14' ;

    INSERT INTO
        payment_transactions_daily_facts
        (count, volume, date, ...)
    SELECT
         ...  ;

COMMIT ;


A bit of testing shows that the data is being inserted correctly, but the shell is trimming the field when it displays it.  You can test this as follows:

mongos> db.test.insert({"name":"    adam    "})
mongos> db.test.find({"name":"    adam    "})
{ "_id" : ObjectId("50814e5c2a17d922cd25974f"), "name" : " adam " }
mongos> db.test.find({"name":" adam "})
mongos>

That is, the data is found when you query with multiple spaces but not found when you re-use what the shell displays. So this is just a display issue with the shell, the DB is behaving as it should behind the scenes.  Also tested this with a driver and it behaved correctly.

Even though this means that the data is the way you want, it is still technically a bug, because it could be confusing.  So I filed a bug here:

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

Finally, to make this print out correctly, try this (assuming the same data I used in my test):

mongos> var fix = db.test.find({"name":"    adam    "}).next()
mongos> printjsononeline(fix)
{  "_id" : ObjectId("50814e5c2a17d922cd25974f"),  "name" : "    adam    " }


Choosing a PK: VARCHAR(2) or SMALLINT(2)?

In most cases I agree with @Aaron's approach, but this just happens to be one of the few instances of there being a truly natural key to use: ISO codes (ISO 3166 specifically). For those who are unfamiliar with ISO, they are, in their own words (from the main http://www.iso.org/ page):

  We're ISO, the International Organization for Standardization. We develop and publish International Standards.

ISO 3166-1 describes the country codes. While there are a few choices of codes to use (2 character, 3 character, and numeric), the 2 character codes are the recommended choice and the most widely used (including for most country-based top-level domain names).

ISO 3166-2 describes each country's subdivisions (e.g. states). This standard is divided into country-based sections (e.g. ISO 3166-2:BR for Brazil) and the codes are 1, 2, or 3 alphanumeric characters.

So, you could do something like:

CREATE TABLE dbo.Country
(
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      PRIMARY KEY,
  CountryName VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.CountrySubdvision
(
  CountrySubdvisionCode VARCHAR(3) NOT NULL
                                   COLLATE Latin1_General_100_BIN2,
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      CONSTRAINT [FK_CountrySubdvision_Country]
                                 FOREIGN KEY
                                 REFERENCES dbo.Country(CountryCode)
                                 ON UPDATE CASCADE,
  CountrySubdvisionName VARCHAR(50) NOT NULL,
  --  LocalizedSubdvisionName NVARCHAR(50) NOT NULL, -- ??
  CONSTRAINT [PK_CountrySubdvision]
             PRIMARY KEY (CountryCode, CountrySubdvisionCode)
);

The idea here is that you would place the CountryCode and CountrySubdvisionCode fields in any tables that needed the "state" value, and FK back to dbo.CountrySubdvision on (CountryCode, CountrySubdvisionCode).

While this method, using 3 - 5 bytes depending on the Country / Subdvision, is not as compact as using a 2-byte SMALLINT, it does have the advantage of placing human-readable / meaningful values in those related tables. This could easily reduce some number of JOINs (when you only need the 2-character code anyway) and can (at least slightly) reduce time spent debugging certain problems.

Please note that I specified a _BIN2 (i.e. binary) collation for both of the "Code" fields to assist performance. Even with an extra couple of bytes, that should be nearly as fast as comparing two SMALLINT values. The only downside there is that you need to be consistent in adding codes in all upper-case and people need to remember that they need to use all upper-case when filtering on these codes.

Please also note that I added the ON UPDATE CASCADE clause to the FK on dbo.CountrySubdvision to handle the off-chance that a CountryCode is changed by ISO (extremely unlikely). Along those same lines, the FKs created on tables where the CountryCode and CountrySubdvisionCode fields have been placed will also need to be set to ON UPDATE CASCADE. This will propagate changes made to CountryCode in dbo.Country to the tables that have both of the fields. This also will propagate any changes made to the CountrySubdvisionCode to those related tables. This is also highly unlikely to happen (especially for where most of our systems would be dealing with) though more likely than any changes being made to the CountryCode.

UPDATE

  Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea.

Just to have this stated, if using this method (i.e. ISO codes) to denote "States" / "Provinces" and possibly Countries, then you do technically have the ability to start out handling only "States" for a single country. In this configuration you would simply remove the dbo.Country table and the CountryCode field (and associated FK) from dbo.CountrySubdvision. Then you would only have CountrySubdvisionCode to place in any related tables.

Now, if you later find that you need to expand the system to handle other countries, you can, at that time, do the following steps (none of which change any existing CountrySubdvisionCode values in any of the related tables):

Create the dbo.Country table
Populate the dbo.Country table
Drop any FKs from related tables that reference dbo.CountrySubdvision
Drop the PK on dbo.CountrySubdvision
Add the CountryCode field to dbo.CountrySubdvision, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
Recreate the PK on dbo.CountrySubdvision to be on (CountryCode, CountrySubdvisionCode)
Create the FK on dbo.CountrySubdvision for the CountryCode field to reference the dbo.Country table
Add the CountryCode field to all of the related tables that already have the CountrySubdvisionCode field, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
Recreate the FKs on those related tables for both fields to reference the dbo.CountrySubdvision table
Remove the Default Constraints on the CountryCode fields from dbo.CountrySubdvision and the related tables


There are lots of ways to run a scheduled job. One of them is using pgAgent.
For windows you can store your password in %APPDATA%\postgresql\pgpass.conf file. Here you can find details. You can also add trust to specific ip and user from server side by changing pg_hba.conf file


There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like:

create table t
( ts timestamp not null
, EngOilP_sd smallint not null
, CompOilLVL_sd smallint not null
) engine = innodb;

insert into t (ts, EngOilP_sd, CompOilLVL_sd)
values
 ('2015-06-24 20:28:07',0 ,0 )
,('2015-06-24 20:30:20',1 ,0 )
,('2015-06-24 20:36:47',1 ,0 )
,('2015-06-24 20:41:11',1 ,0 )
,('2015-06-24 20:43:29',1 ,0 )
,('2015-06-24 20:45:42',0 ,0 )
,('2015-06-24 20:47:51',0 ,0 )
,('2015-06-24 20:49:59',0 ,1 )
,('2015-06-24 20:52:01',0 ,1 )
,('2015-06-24 20:54:17',0 ,0 );

Since we are going to reference the "pivot" several times we create a view for it:

create view t_pivot as
    select 'EngOilP_sd' as sd, ts, EngOilP_sd as val from t
    union all
    select 'CompOilLVL_sd', ts, CompOilLVL_sd from t;

The start time (using @Pauls comment as the definition) is the earliest time where each attribute is 1:

select sd, min(case val when 1 then ts end) as start
from t_pivot
group by sd

The stop time is the earliest time after that where each attribute is 0:

select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
from (
    select sd, min(case val when 1 then ts end) as start
    from t_pivot
    group by sd
) as x
join t_pivot as y
    on x.sd = y.sd
   and y.ts > x.start
group by x.sd, x.start

Adding another level of nesting makes it convenient to determine the duration:

select sd, timediff(stop, start) as duration, start, stop
from (
    select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
    from (
        select sd, min(case val when 1 then ts end) as start
        from t_pivot
        group by sd
    ) as x
    join t_pivot as y
        on x.sd = y.sd
       and y.ts > x.start
    group by x.sd, x.start
) as z
order by sd desc;

+---------------+----------+---------------------+---------------------+
| sd            | duration | start               | stop                |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd    | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| CompOilLVL_sd | 00:04:18 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
+---------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)


MySQL: Does Amazon's RDS Multi-AZ feature use binary logs?

The Multi-AZ feature does not use MySQL's native replication features. Quoting their documentation, "Multi-AZ deployments for Oracle, PostgreSQL, and MySQL DB instances use Amazon technology, while SQL Server DB instances use SQL Server Mirroring." It seems likely that they're doing replication at the storage level and not depending on binary logs. However, disabling binary logs (if RDS will even let you do that) would probably break point-in-time recovery.

Multi-AZ should not be confused with Read Replicas. As @GeekRide's answer shows, Read Replicas do use MySQL's native replication via binlogs.


Parallel pg_dump against a replica is not supported cleanly until v10.

You can either drop the -j, run the dump against the master rather than the replica, or you can use --no-synchronized-snapshots which will make your backup be potentially inconsistent.

Pausing the recovery might cure the consistency problem with use of --no-synchronized-snapshots, but the pause must be run on the replica, not on the master, which is what you seem to doing.


Version 10 is out of support and 11.2.0.4 is out as well except for those who paid for extended.  Upgrading to 12 is the best choice if you want a database that is supported with security patch sets and bug fixes.
Many other factors need to be considered as well and some are outlined in this answer here:

downtime allowed:  an hour, a day, a weekend?
are you changing character sets (if you are will need to use the oracle tool to check for possible issues)
are you adding or removing licensed features?  Example: Enterprise to Standard means partitioning is not allowed
databases with a large code base can have migration issues where things don't work the same way. QA and testing need to be done.
changing operating systems?  Directory paths need adjusting
what other systems interact with the database?  They all need to have configuration changes too
what resources do you have to test your migration?  Believe me you will want to do it more than once.  VMWare snapshots allow a go-back facility that is handy.

Migration paths

Easiest way is to create an installation script that can be used on the new production and development instances and install the latest version of Oracle.
Create table spaces to match your existing ones or take this opportunity to improve the organization of them.
use the data pump to export the contents from existing and import into the new
check that grants, directories, jobs and database links are copied over
test and test again

Given the specifics you have added to your answer the choice of the upgrade assistant or a clean install is still an "It depends" issue.  The upgrade assistant makes things easier but does things like locking the user accounts that can catch one by surprise.  If you want total control over the upgrade do a clean install and import.  If you are not changing table spaces then try the assistant.

The important thing is to test and test again.


Get All data from one table according to another, using Temporal Tables

Is this what you are looking for?
SELECT ss.validfrom AS 'status validFrom', ss.ID, ss.PrimaryStatusID, ca.[students validFrom], ca.ID, ca.IsFoster
FROM StatusForStudents ss
CROSS APPLY
(SELECT TOP(1) s.validfrom AS 'students validFrom', s.ID,s.IsFoster
FROM Students s
WHERE s.validfrom<=ss.validfrom
ORDER BY s.validfrom DESC
) AS ca


You can't use the V_SPECIAL_SAMPLES with an existing index on the sample_date column because of the TO_CHAR in the view.  Your options are

1) Don't use the view and go directly against the sample table.

2) Create a function based index on sample_date that does both the TO_CHAR and the TO_DATE

Something like

CREATE INDEX fbi_sample_date
    ON sample( to_date( to_char( sample_date,
                                 'mm/dd/yyyy HH24:MI AM' ),
                        'mm/dd/yyyy HH24:MI AM' ));

3) Modify the v_special_samples view to have both the sample_date as a VARCHAR2 and to have the raw DATE value and filter on the raw DATE value.


Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

No. There's no direct support in SQL Server for the sort of single-operator row-rejection you seem to have in mind.

Speaking more generally, the filtered index is not directly helpful for the given query, and isn't a valid index filter definition anyway. You could create an indexed view containing those predicates instead, but it still wouldn't be helpful as way to locate rows for the target query. You could rewrite the query to use the indexed view to exclude rows fetched with a separate full scan, but it's tough to see how that would be a good idea in general.

The closest you could get would probably be a scan of the indexed view populating a bitmap filter, with that filter applied as part of a full scan of the target table. It might be challenging to get this query plan shape reliably.

You could also use an indexed persisted computed column using a CASE expression, but again this would require rewriting the original query, and would require storage for every row in the table, plus the index.

From the information given, it seems the best you can do is to define the index as:

CREATE NONCLUSTERED INDEX IX_dbo_My_Table__Float_Filtered
ON dbo.My_Table (my_Float_column)
WHERE
    my_Float_column <> 0
    AND my_Float_column IS NOT NULL;


How to turn off all RAISE NOTICE on production server?

You could wrap them in an IF clause that tests a custom configuration variable. This will work but it's a bit verbose and each statement in PL/PgSQL has a cost.

I suspect you'll be better off lowering the log level in the RAISE messages so you log on a level like DEBUG that isn't captured by log_min_messages by default. That way you can just turn it on by changing client_min_messages or log_min_messages.

See the expanded form of the RAISE statement, eg:

RAISE DEBUG USING MESSAGE = 'TheMessage';

See the documentation for the RAISE statement.

Alternately, you can leave your messages at RAISE NOTICE and increase the log level, but that may hide other information you'll want to see in the logs. Better to log with a lower priority initially.

So long as the notices aren't doing lots of expensive string formatting or being called huge numbers of times I suspect you won't have too much trouble with their performance impact while logging to file/client is suppressed by log levels.


Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit?

My problem is that my server is going over the set 12GB memory and if things like CLR/Full Text/Server exe files taking additional memory.

What Denny Cherry wrote is true for SQL Server version from 2005 to 2008 r2. From SQL Server 2012 onward there was considerable change in SQL Server memory allocation. Memory for SQLCLR is allocated from max server memory setting in SQL Server 2012 not outside of it. BOL says that from SQL Server 2012 onward

  Max server memory controls SQL Server memory allocation, including
  the buffer pool, compile memory, all caches, qe memory grants, lock
  manager memory, and CLR memory (basically any “clerk” as found in
  dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked
  server providers other than SQL Server, or any memory allocated by a
  “non SQL Server” DLL is not controlled by max server memory.

Prior to SQL 2012, the buffer pool both “managed” memory and was a consumer of memory for database pages. It’s management of memory meant it allocated 8Kb pages of memory for other consumers like plan cache. Roll forward to 2012+, the buffer pool is a pure consumer of memory from SQLOS which manages all of the memory.

  Please tell me how to configure SQL Server, so it does not go over the set max limit.

This is not possible. In SQL Server 2012 if you are not using third party linked servers providers or if any DLL which is not Microsoft provided is not loaded in SQL Server address space there is less chance that SQL Server can consume memory outside max server memory setting. But the fact is you cannot control SQL Server not to utilize memory outside buffer pool. That is how it is designed.


Question on conditional join or other way to create view based on two tables in Postgresql

SELECT a.id, a.market_id
FROM all_products a
INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
WHERE EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )

UNION ALL

SELECT a.id, a.market_id
FROM all_products a
WHERE NOT EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )


Meaning of SSMS database icon preceded by scroll with red x

This is documented in BOL under "Administering Servers by Using Policy-Based Management"

  For failed policies, Object Explorer indicates a critical health
  warning as a red icon next to the target and the nodes that are higher
  in the Object Explorer tree.

I've not used policy based management myself so am not entirely clear what this means. Through the UI I couldn't see any way of denoting a policy condition as having any particular severity.

I've made this answer community wiki in case someone more familiar with the subject area wants to expand upon it.


How can I exclude range overlaps if one range may be empty?

Use inclusive ranges

Adding an argument like '[]' to int4range tells it to use inclusive ranges, meaning that, eg, (5,5) is not empty because it contains 5. Eg:

SELECT int4range(3, 6) @> int4range(5, 5, '[]');   -- t
SELECT int4range(3, 6) @> int4range(50, 50, '[]'); -- f

Therefore, this exclusion works as I expected:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (int4range("min", "max", '[]') WITH &&);


query to concatenate columns if record duplicate

Use partition by on the name column, and count the rows based on the order of id-s. Where the row number of the actual row is greater than 1, concatenate id and name, else just use the name.

with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select
  id,
  case
    when row_number() over (partition by name order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;

        ID NAME
---------- ----------------------------------------------
         1 john
         2 david
         3 zed
         4 4 john
         5 5 david
         6 6 john
         7 7 david
         8 8 john
         9 9 john

9 rows selected.

To treat john, JOHN, joHN, etc. equally, simply change the partition by column, e.g upper(name).

select
  id,
  case
    when row_number() over (partition by upper(name) order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;


MySql | How to compute every row in same table

So you want to get the total sum of grades, lec and lab and divide that by the total sum of lec and lab?

SELECT (SUM(Grade) + SUM(Lec) + SUM(Lab)) /  (SUM(Lec) + SUM(Lab))
FROM grades;

Result:

1.6667

Db<>fiddle


Best fill factor for GUID clustering key

There really is no "ideal" Fill Factor since there are too many other factors to consider.

What is the actual page size of the table?
How many variable length columns are in the table? If not "none, then: How likely / often are the variable length fields updated to a length greater than their initial value?

If you have a table with a row size of 3000 bytes, you would only ever be able to fit 2 of those rows on a data page in the first place. Setting the Fill Factor to 50 will initially build the pages out to have only 1 row per data page, but that only leaves room for one row to be added. In a high-insert environment, how long does that really last prior to a page-split occurring on that page? Probably not long, even with "random" placements due to using a GUID.

The only "ideal" here is to rebuild the tables using an INT or BIGINT key fields using an IDENTITY or SEQUENCE. If you need the GUID values for referencing externally because they are known by the app and/or customers, then there should be a single instance of them in one table, and that field should have a Non-Clustered index on it (this is known as an Alternate Key).

It might be a bunch of work, not only in terms of scripting, but possibly also of convincing the "business", and possibly some app code changes, but given that the Clustered Index key are copied into all Non-Clustered Indexes, PLUS PKs are used to Foreign Key to other tables, so now there are FK columns in tables with GUID, the entire system is slower due to needing to manage 16 byte values to find things instead of 4 or 8 byte values (INT or BIGINT respectively). Meaning, even if it is difficult politically, the end result will be a faster system, and people rarely complain about that.

But if you have no choice but to keep the GUID PKs, then you have no choice but to suffer the consequences of said situation (unfortunately).

Oh, and regarding using NEWSEQUENTIALID() those help because they are not as fragmented, but not fully sequential either since the initial value gets reset whenever the SQL Server NT service is restarted, so the new value could be "below" the existing set.

AND, even if you rebuilt all indexes every night, you would still be left with overly large Key values (again, copied into the NonClustered indexes) that take up 2x - 4x more space than the INT / BIGINT options, PER EACH TABLE, PER EACH ROW, PER EACH INDEX. The net-effect is that it takes longer to read everything from disk, longer to write things to disk, takes up more memory for all operations (since everything goes through the Buffer Pool), backups take longer, restores take longer, you can keep fewer backups (per the same storage), etc, etc. Meaning: this is not merely an issue of idealism like so many debates about which method or thingy is better; these are very real consequences if you have data volumes of anything but rather small databases.


Trouble with Ola Hallengren's @DatabasesInParallel parameter

After reading Ola's last comment I had a light bulb moment and figured what I was doing wrong and got it working.

Since I was just testing these commands out I never actually put them into an Agent job - I figured they would work the same way by just manually executing them at the same time. I just had all 4 of the job commands pasted in a Query window and would just execute that. I thought this would work but it appears that it doesn't even though they all showed the same RequestStartTime in the QueueDatabase table.

Once I created the jobs and set a single schedule I could see the RequestStartTime varies by milliseconds but the DatabaseStartTime is the same for all DBs.

The screenshot below shows the QueueDatabase table from my test today (pink) which was successful when I created an Agent job. The test on 11/8 (blue) was when I manually executed the commands in a Query window.

I asked Ola in an email if this functionality makes sense that it doesn't work when executing manually in a query window and below is his response:

  When you execute all the commands in one query window, you can never
  get parallel executions (as T-SQL does not have support for that).

  If you would like to test it in SSMS, then you need to us 4 different
  query windows, and then quickly start the execution of all of them.


How can I force a bushy tree join in Oracle SQL?


Creating a relationship between one field in a table with two fields in another

The only reason I see this occurring is if you wrote a query that INNER JOINs twice

EXAMPLE

SELECT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate1.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
INNER JOIN tbl_Estimate AS tbl_Estimate1
ON tblState.ID = tbl_Estimate1.JobSiteStateID;

Because you are running INNER JOIN twice, the graphical representation has to match. This would indicate the possibility of checking different columns with each INNER JOIN

Let's say you are checking for Jersey City but didn't specify the state.

SELECT DISTINCT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate1.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
AND tbl_Estimate.JobSiteCity = "Jersey City"
INNER JOIN tbl_Estimate AS tbl_Estimate1
ON tblState.ID = tbl_Estimate1.JobSiteStateID;

This should return JobSites in all ZipCodes from Jersey City, NJ and Jersey City, WI

If you collapse the query to

SELECT DISTINCT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
AND tbl_Estimate.JobSiteCity = "Jersey City"

you should get the same result, but the picture should only have tbl_Estimate once


Since we're using streaming replication, writes to the master will no longer go through when the standby fails.

Not necessarily the case.  This is only the case when you are setting up synchronous streaming replication.  Normally the master will return immediately after it writes its own log files to disk and not wait for the slave.  It is then replicated out when the slave requests.

See the synchronous_commit parameter.

I don't see why you need to futz with repmgr for this.


How much I/O can a normal disk and a SAN disk can handle?

You have to read the manual to find out or you can do a stress test using SQLIO or SQLIOSim. Brent has a good article on : SQLIO Tutorial: How to Test Disk Performance

To see if underlying storage is a problem, you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are and you can ask your SAN admin to move them away.

 ---Ref: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO


mysqldump is a client application. It connects to the MySQL server using "normal" credentials and queries for data.

It has no immediate access to the data other than what it can ask of the server. The mysqldump binary doesn't have to run on the same host as the MySQL server. Hence it is obvious it cannot access files directly.

It is instructive to turn the general log on for the duration of the dump to understand how it works.

For example, you might see in the general log queries such as:

                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t1`
                   29 Query     COMMIT
                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t2`
                   29 Query     COMMIT
                   29 Query     SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'
                   29 Query     COMMIT

As you can see, mysqldump interrogates the schema via IFNORMATION_SCHEMA. It later follows to get all data via standard SELECT:

                6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

So, mysqldump's power is by correctly iterating all objects and querying for data, then producing the SQL statements to generate those.

Since mysqldump accesses as any normal client (seen by the above SELECT), it does not care about the storage engine, other than caring about what to export as table's engine. It is up to the MySQL server to access the engine (via storage engine API) and fetch the data.


Duplicate results from query trying to find cross-indexed multiple tables

Never mind. I re-wrote it and this seems to work;

SET search_path="SCHEMA_NAME";
SELECT e1.label, c1.label, h1.contentsid, h1.group
FROM hostdata as h1
INNER JOIN (
    SELECT contentsid, group
    FROM hostdata
        GROUP BY contentsid, group
    HAVING COUNT(id) > 1
    ) dup
    ON h1.group = dup.group AND h1.contentsid = dup.contentsid
INNER JOIN class as c1
  on h1.classid = c1.id
INNER JOIN element as e1
  on h1.elementid = e1.id
ORDER BY t1.group, t1.contentsid;


How to log data changes in Access at the table level?

Unfortunately, Microsoft Access doesn't have triggers, or anything even resembling triggers.  Off the top of my head, you have a couple of unappealing choices:

Create DateModified and UserModified columns, and update your code to include these values.  The downside is that you'll only know the last person who updated a record, and won't have a history to look at..
Keep your Access front end, and port your tables over to a SQL Server Express back end.  This way, you'll be able to use triggers (as well as back-up jobs and other nice things).


In general, FCI is more beneficial compared to AG because of various reasons already mentioned above. Of course, 2-node FCI is completely great but should not be built on top of storage spaces direct since S2D is known to be problematic in less than 4 nodes deployments (source).

For Azure, I would rather stick to a VSAN that is designed to work in 2 or 3 nodes scenarios like (example).

Here is a bit more information on FCI vs AG that you might find useful too


Do I need to reindex mysql table after bulk delete?

Do you need to optimize the tables when using InnoDB? Yes and no, depending on your workload and if you are encountering performance problems or not.

A shameless copy-paste from the MySQL documentation :

  For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which
  rebuilds the table to update index statistics and free unused space in
  the clustered index. This is displayed in the output of OPTIMIZE TABLE
  when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

  This operation does not use fast index creation. Secondary indexes are
  not created as efficiently because keys are inserted in the order they
  appeared in the primary key. See Section 14.14.6, “Limitations of Fast
  Index Creation”.

  InnoDB stores data using a page-allocation method and does not suffer
  from fragmentation in the same way that legacy storage engines (such
  as MyISAM) will. When considering whether or not to run optimize,
  consider the workload of transactions that your server will process:

  Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.
  Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.
  Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is
  available. See Section 14.10.5, “How Compression Works for InnoDB
  Tables” and Section 14.12.1, “Overview of InnoDB Row Storage”.
  High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC
  mechanism. See Section 14.5.12, “InnoDB Multi-Versioning”.


Rank function creating duplicate partitions? (two rank 1's for the same key)

Found the problem, there were ties in the rank ordering, hence why multiple rows are returned with the same rank id.

The solution will be for me to find an ordering key that has unique combinations of partition and order key.


Try File - Preferences - Dashboard within PGAdmin. See if toggling those options will bring it back.
If not, then providing you do not have any customised layout, try File - Reset Layout.


Oldest unsent transaction alert for Availability Group

Very interesting question ..
Based on my understanding, I will try to answer this.
Other experts can correct me wherever I am wrong.

AlwaysON is different from Database Mirroring especially when sending the log blocks to the secondary replica/s.
The difference is how the log block gets send to the mirrored instance (for mirroring) or replica (for AlwaysON).
For Database Mirroring

it flushes the log block to the disk and once completed locally, it sends the log block to the secondary server.

For AlwaysON

it flushes the log blocks in parallel to the secondary using a secondary log scanner on primary.
The beauty is that the secondary could have hardened log blocks even before the primary I/O completes which increases performance and narrows the NOT IN SYNC window.
Now the interesting part is that to monitor above scenario, there are progress messages. The secondary sends messages to the primary indicating the hardened LSN level. The primary uses that info to help determine synchronization state.

So, there is no alert mechanism like Mirroring to actually be able to know the oldest unsent transaction. The only way to know is using DMVs. and possibly Extended Events.
As a side note, you can use Policy Based Management to monitor your AlwaysON health.
Below is the query that I am using to monitor my AlwaysON environment :
/************************************************************************************************
Author      :   Kin Shah
Purpose     :   Find "How far is secondary behind primary"

                Written for DBA.STACKEXCHANGE.COM
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Disclaimer  :   Any scripts found on internet you must irst
                    - understand what it is doing
                    - then test it if it suits your requirements
                I am not responsible for any data loss or any blue screen that you might get.
                     ^^^
                *** USE THIS AS PER YOUR OWN RISK ****
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*************************************************************************************************/

SELECT AGS.NAME AS AGGroupName
    ,AR.replica_server_name AS InstanceName
    ,HARS.role_desc
    ,Db_name(DRS.database_id) AS DBName
    ,DRS.database_id
    ,is_ag_replica_local = CASE
        WHEN DRS.is_local = 1
            THEN N'LOCAL'
        ELSE 'REMOTE'
        END
    ,AR.availability_mode_desc AS SyncMode
    ,DRS.synchronization_state_desc AS SyncState
    ,DRS.last_hardened_lsn
    ,DRS.end_of_log_lsn
    ,DRS.last_redone_lsn
    ,DRS.last_hardened_time
    ,DRS.last_redone_time
    ,DRS.log_send_queue_size
    ,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'
    /*
    if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server
    then there is NO LATENCY
    */
    ,DATEDIFF(SECOND, DRS.last_redone_time, DRS.last_hardened_time)
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
    AND AR.replica_id = HARS.replica_id
ORDER BY Db_name(DRS.database_id)
    ,is_ag_replica_local

References :

How It Works: Always On–When Is My Secondary Failover Ready?
AlwaysOn Availability Groups Dynamic Management Views and Functions


Rebuild or Reorganize indexes in Sql Server 2016

You can consider to change the fillfactor of some of your indexes:

see Specify Fill Factor for an Index

The fact is the solution you use is already optimized, it does not consider small indexes(having only few pages) and does not rebuild all of the indexes but only those that need to. Maybe you just use 100% FF everywhere and your keys are smth like guids?

I think this article can help youin it: How to choose a good index fill factor


SQL Server 2019 performance worse than 2012... am I missing something?

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level.

Change the Database Compatibility Level and use the Query Store
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher database compatibility level with:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;


Performance differences between RID Lookup vs Key Lookup?

Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?

However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.

I blogged about this recently, and I'm reproducing the content here to avoid a  comment answer.

CREATE TABLE el_heapo
(
    id INT IDENTITY,
    date_fudge DATE,
    stuffing VARCHAR(3000)
);

INSERT dbo.el_heapo WITH (TABLOCKX)
      ( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)

CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);

We can look at the table with sp_BlitzIndex

EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
                              @SchemaName = 'dbo',
                              @TableName = 'el_heapo';

This query will produce bookmark lookups.

SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);

Now we can cause some forwarded records to occur:

UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)

BlitzIndex will show them to us:

And if we re-run the lookup query:

Profiler will show a difference as well:


Linking job steps from a single run together in sysjobhistory

Note that sysjobhistory has an ID column (instance_id). at least one entry should hit the table for each step of a job that was completed, followed by an entry with step_id = 0, recording the outcome of the job. Each step also records the time (run_date and run_time) the step was started, which will be equal to or greater than the time the job was started. So, the step_id = 0 row for a given run has a higher instance_id than the related steps, but a lower (or equal) run time.

So, try doing an initial pull of the data from the rows where step_id = 0 into a temp table (or equivalent). Then, all rows from sysjobhistory with the same job_id, a lower instance_id, and a higher or equal start time (from run_date and run_time) should belong to the job run you're looking for.

I used something much like this for a failed job report I used to run, at  a former employer.

Here's a stripped down, modified version of that code. It ran for me in a quick test on a SQL Server 2016 box, just now. However, I don't have any jobs that run frequently enough that multiple runs would have the same run times.

USE msdb;

DECLARE @start_date varchar(8) = '20171001';

IF(OBJECT_ID('tempdb..#failhist') IS NOT NULL) DROP TABLE #failhist;

CREATE TABLE #failhist
(
    [job_name] [sysname] NOT NULL,
    [run_datetime] [datetime] NULL,
    [run_ended] [datetime] NULL,
    [instance_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [run_date] [int] NOT NULL,
    [run_time] [int] NOT NULL
);

INSERT INTO #failhist
select *
  FROM
(SELECT j.name as job_name
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
        as run_datetime
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 + (run_duration * 9 + run_duration % 10000 * 6 + run_duration % 100 * 10) / 216e4
        as run_ended
       ,h.instance_id
       ,h.job_id
       ,h.run_date
       ,h.run_time
   from msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
  WHERE h.step_id = 0
    and h.run_date >= @start_date
) x
;

SELECT
       t.job_name AS [Job/Step]
      ,' ' + CONVERT(varchar(19), t.run_datetime, 121) + ' to ' + CONVERT(varchar(19), t.run_ended, 121) AS Run_Msg
      ,0 as HdrDetail
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,-1 as step_id
      ,jh.instance_id as actual_instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 WHERE jh.step_id = 0
UNION ALL
SELECT
       '        '
      +CASE
         WHEN jh.step_id = 0
           THEN 'Job Summary'
         WHEN jh.run_status BETWEEN 0 AND 1  -- summary
           THEN RIGHT(CAST(100 + jh.step_id as varchar),2) + ' - ' + jh.step_name
         ELSE   '  ' + RIGHT(CAST(100 + jh.step_id as varchar),2) + ' (add''l info)'
       END
      ,'        ' + CAST(jh.message as varchar(max))
      ,1
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,jh.step_id
      ,jh.instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 ORDER BY run_datetime, instance_id, step_id

If you actually have a job that runs more that once a second, you might have to use a windowing function, to ensure that you don't pick up job steps from an earlier run of the job with the same run_time value.

Caveat: If the limit on the number of rows each job can have in sysjobhistory is hit, you may get odd/incomplete results. Also, I occasionally saw jobs that failed without generating a job outcome (usually a temporary failure to authenticate the Windows user running the job).


Oracle not using index when > used in where clause

Oracle should be able to use the statistics to determine the high and low values for any column if you are keeping them up to date:

drop table foo;
create table foo(id date primary key);
insert into foo select sysdate-level from dual connect by level<31;
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE                                    HIGH_VALUE
-------------------------------------------- ------------------------------------------

*/
execute dbms_stats.gather_table_stats('JDOUGLAS', 'FOO');
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE                                    HIGH_VALUE
-------------------------------------------- ------------------------------------------
786F0C0A0E0837                               787001080E0837
*/


What does HSJOIN mean in an Explain Plan?


I'd suggest that instead of using the inner cursor, if you want to avoid its creation and release for every step of the outer cursor, to use a temp table to fetch data and truncate it for each step.

That way you'll create it just once, and the truncation operation is not logged, so it will not cause any problems.

PS: in this situation I'm always in favor of temp tables with an identity column that will be looped for each record (or batches :-).
But I agree with the comments, better tell us the problem and maybe we'll find another solution.


How to keep an Oracle 11g instance lean?

The best check for memory allocations is the buffer advisories. These estimate the impact on caching effectiveness of modifying the cache sizes, so you know whether you have the sizes of the PGA and SGA correct, and whether reallocating memory from one to another would be beneficial.


Update field with the longest varchar field

Using the MAX function you could write the following to obtain the entry with the longest varchar
SELECT
  MAX(LEN(options))
FROM
  ForrasReportsRicsiTeszt;

This can then be combined with an UPDATE statement to give
UPDATE ForrasReportsRicsiTeszt
SET
   options = REPLICATE(options, 4)
WHERE
   LEN(options) = (  SELECT
                     MAX(LEN(options))
                   FROM
                    ForrasReportsRicsiTeszt
               );

Alternatively you could use a Common Table Expression (CTE).
;WITH MyTestCTE ( LengthLogngestOption)
AS (
    SELECT
      MAX(LEN(options))
    FROM
      ForrasReportsRicsiTeszt
   )
UPDATE FRRT
   SET FRRT.Options = REPLICATE(options,4)
FROM
   ForrasReportsRicsiTeszt FRRT
JOIN
   MyTestCTE MTCTE
ON
LEN(FRRT.OPTIONS) = MTCTE.LengthLogngestOption;


UPDATE all values of a column with values of a related table

Run the following select in order to find cases where you have more then one record for the key you are using. Once you find the records with duplicate values you can figure out how to deal with them and it could be removing them from the other table or updating the select to exclude them.

SELECT GH220_INVENTAR_NR,
       COUNT(0)
FROM GH220_AUSSTATTUNG
GROUP BY GH220_INVENTAR_NR
HAVING COUNT(0) > 1


Changing db ownership using a user which can only see the sa

May not be pretty, but you could use a trigger on database creation that executes as a privileged user of your choice:

CREATE TRIGGER [database_setup]

    ON ALL SERVER
    WITH EXECUTE AS 'privuser'
    FOR CREATE_DATABASE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @db nvarchar(100)
    DECLARE @sql nvarchar(MAX)
    DECLARE @data XML
    SET @data = EVENTDATA()

    SELECT @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)');
    SELECT @sql = ('ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO sa;');
    sp_executesql @sql;
    END
GO

ENABLE TRIGGER [database_setup] ON ALL SERVER
GO


Not only client connections? Is "MongoDB Wire Protocol" also used for replication?

From a protocol perspective, there is no difference. A really basic way to think of it is this (assuming you start a secondary that is reasonably up to date and is not performing an initial sync):

Replication is a client connection from a secondary to the primary
That is done using TCP/IP and with the MongoDB wire protocol being used over the top (unless you are using SSL, which would add that to the mix)
The secondary creates a tailable cursor and begins streaming the operations from the Oplog
The secondary applies these idempotent operations to its data in order to reach the same state as the primary

Now, in reality things are more complicated than that but that gives you a good starting point. You still have to add in multi-threaded replication, replication chaining so that you can replicate from another secondary rather than the primary etc. These are advanced topics which I have given multi-hour whiteboard sessions on (when I worked in MongoDB) and beyond the scope of this answer, not to mention the fact that I am likely out of date with my information at this point.

If you would like to go deeper into how replication works, I recommend the internals posts from Kristina Chodorow (though they are a little dated these days) as well as the various talks the MongoDB engineers have given on the topic (here is one example).


Is it possible to exclude a database from WAL and basebackup?

No, there is no way to do that.
I would recommend that you create a second PostgreSQL cluster and put the database there. That does not incur a big overhead as far as system resources are concerned, and it will solve your problem.


how to update the value of a period contained inside a bigger range

You cannot simply use the difference operator -, because it does not allow multiple resulting rows:

  ERROR:  result of range difference would not be contiguous

So you need to create three rows and use the intersection operator *:

1st range unbounded up to lower bound
2nd range as given
3rd range upper bound to unbounded

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 14:30)'::tsrange, 15)
SELECT t.range * tsrange(NULL, lower(c.r), '(]') - c.r AS range, t.val FROM tbl t, cte c
UNION ALL
SELECT t.range * c.r                                           , c.val FROM tbl t, cte c
UNION ALL
SELECT t.range * tsrange(upper(c.r), NULL, '[)') - c.r         , t.val FROM tbl t, cte c;

Returns:

range                                           val
---------------------------------------------------
["2010-01-01 14:00:00","2010-01-01 14:10:00")   20
["2010-01-01 14:10:00","2010-01-01 14:30:00")   15
["2010-01-01 14:30:00","2010-01-01 15:00:00"]   20

This applies the operation to all rows in table tbl.
And it works for all ranges (overlapping completely, in parts or not at all).

Note that my 2nd row matches the upper bound of the input value (unlike your example, which is probably unintended).

All bounds are preserved. Input values trump existing values. This is achieved with (example 1st part):

tsrange(NULL, lower(c.r), '(]') - c.r AS range

The lower bound is unbounded and automatically excluded (()
Initially use including upper bound (]); intersect that with the input range, to get the complementary bound to the lower bound of the input range.

To eliminate rows with an empty time range:

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 15:30)'::tsrange, 15)
SELECT * FROM (
<same as above>
   ) sub
WHERE range <> 'empty';

SQL Fiddle.


SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

This worked for me --> creating an SSRS 2008 project and add the Report Models in there. Then deploy the report models to the SSRS 2016 server.


Limit connection to specific database with AlwaysOn Primary

There's not a straightforward, built-in way of accomplishing what you want to do beyond what you suggest at the end of your question:

besides the obvious use two logins with different permissions

I've heard of folks doing something like this:

create a SQL Server Agent job that runs once every X seconds (might be 1, 5, 15, etc depending on your requirements)
in that job, check to see if we're running on the primary replica for this database (sys.fn_hadr_is_primary_replica OR the AG DMVs)
if it is the primary, query sys.dm_exec_sessions to see if that login / user is connected ( in the login_name)
if they are connected, check to see if they are using the off-limits database (in the database_id column, or pull the query text and check that)
if they are using the off-limits database, use the kill command on the session_id

It's not perfect (because the job is not continuous, and because it's hard to check if the off-limits is being used conclusively), but it's better than nothing (if you're stuck with the single login approach).


sort a varchar field numerically in mysql

Two possibilities:
Either pad your values with zeros from the left and sort based on that:

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 3, '0');
-- choose an appropriate number instead of 3

or cast your values to a number (similar to Aaron W's solution, apart from here I cast explicitly, and that is a clearer solution):

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);

Be careful if you have non-number strings in your field:

SELECT cast('something' as decimal);
0
SELECT 'something' + 0;
0


Query Optimization required - Subquery using Inner Join

You could rewrite with a LEFT JOIN:

SELECT
  IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName
...
-- ### unchanged up to here ##--
, G.TermNo
, G.TermNo * G.MaxMonthlyAmount AS TotalAmount
-- ### end ### --

FROM
    [tbl_InternalTransactions] IT
    INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
    INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
    INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
    INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
    AND IA.[iInternalAccountID] = @InternalAccountID

--  ### the two subqueries converted to a LEFT JOIN ###
    LEFT JOIN
      ( SELECT CPH.iContractID
             , CPH.dtTransactionDate
             , MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iInternalAccountID = @InternalAccountID
        GROUP BY CPH.iContractID
               , CPH.dtTransactionDate
      ) G
      ON  G.iContractID = C.iContractID
      AND G.dtTransactionDate = IT.dtTransactionDate
-- ### end of changes ###

WHERE
    IT.[dtTransactionDate] >= @FromDate
    AND IT.[dtTransactionDate] <= @ToDate
...

or using OUTER APPLY:

--  ### the two subqueries converted to an OUTER APPLY ###
    OUTER APPLY
      ( SELECT MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iContractID = C.iContractID
          AND CPH.iInternalAccountID = @InternalAccountID
          AND CPH.dtTransactionDate = IT.dtTransactionDate
      ) G
-- ### end of changes ###

These are just rewritings along the lines you tried. Not at all sure if they improves efficiency. They will probably do but the main bottleneck may be elsewhere (indexes).


Calculating a cumulative sum with an alias in postgresql

SELECT user_id,
       payments.due,
       payments.amount,
       COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due) available,
       LEAST(payments.amount, COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) used,
       GREATEST(0, COALESCE(credit.amount, 0) - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) remaining
FROM ( SELECT user_id FROM credit
       UNION
       SELECT user_id FROM payments ) userlist
LEFT JOIN credit USING (user_id)
LEFT JOIN payments USING (user_id)
ORDER BY user_id, due

https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=7e5814dbe02ced69684d331b277dd205
PS. payments (user_id, due) must be defined as unique - if not then the output is indefinite (or you must use another ordering in the window definition, for example, by payments.id additionally).


Error During IMPDP in oracle database 12c

The error message is self-explanatory:

ORA-01918: user 'XXXXXX' does not exist

Look at the log output for your expdp execution. If it does not contain the following:

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

... then the users & grants were not exported in the first place, and so were not created when importing.

FULL=Y does not solve the problem when the users were not exported in the first  place.

Check that the exporting user, hmis has the CREATE USER grant, then re-export the data.


Updating SQL Server 2016SP2 to latest CU in Cluster...what happens?

Install the CU on a passive node, failover, install on another passive node, repeat until all nodes are patched.
When you failover to the passive node, the databases will be upgraded. If you failback, the databases will be downgraded back to the old CU.  (This is true for CU's, not for SP's or Major Version upgrades).


About MongoDB 3.6 shards, from the official release notes:

  Starting in 3.6, shards must be replica sets.

When you add shards, should be something like:

sh.addShard( "rs1/mongodb3.example.net:27017,mongodb4.example.net:27017,mongodb5.example.net:27017" )

With a status like:

--- Sharding Status ---
sharding version: {
   "_id" : 1,
   "minCompatibleVersion" : 5,
   "currentVersion" : 6,
   "clusterId" : ObjectId("5be0a488039b1964a7208c60")
}
shards:
   {  "_id" : "rs0",  "host" : "rs0/mongodb0.example.net:27017,mongodb1.example.net:27017,mongodb2.example.net:27017",  "state" : 1 }
   {  "_id" : "rs1",  "host" : "rs1/mongodb3.example.net:27017,mongodb4.example.net:27017,mongodb5.example.net:27017",  "state" : 1 }
active mongoses:
   "3.6.8" : 1


How to create function(table_name) that returns a set of the table

You cannot use a parameter name (_t) as return type.
Use polymorphic types instead:

CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
       SELECT DISTINCT ON (gid) *
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
     )
   USING _d;
END
$func$  LANGUAGE plpgsql;

And use RETURN QUERY EXECUTE to actually return the results.
Call (important!):

SELECT * FROM vsr_versioning_at_time(NULL::mytable, '2014-04-18 12:00');

A lot more details in this related answer on SO (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries


PostgreSQL bug(?) - logging with a minimum log level

the logger logs every statement

Then that behavior comes from log_statement:

  log_statement (enum)

  Controls which SQL statements are logged. Valid values are none (off),   ddl, mod, and all (all statements). ddl logs all data
  definition statements,


Yes.

If you want to the save a lot of time and trouble, forget RMAN for this specific task. Oracle XE 11g is limited to 11 GB data. Data Pump can easily handle that.

Data Pump Export

Data Pump Import


Disable a bad SQL Server Query Plan vs. Force a good plan

The current ways (I'm oversimplifying) to affect plan shape are hinting, which usually removes options from the optimizer, and forcing a particular plan, whether with USE PLAN, plan guide, or Query Store.

Adding hints can prevent the plan you don't want, but will likely prevent other options as well. Forcing a plan has the same issue of course - it prevents ALL other options.

Your best practical option here is probably to force a plan that works well, then continue to monitor it.

If you really want to keep both options available, then you may want to apply hints with a plan guide.


I know this was asked a while ago, but maybe someone else will stumble across this answer and not spend the multiple hours that I've been bashing my head against this.

Turns out that you need to use the SQL_WVARCHAR type for Variables.

The following is my evidence, showing the SQL_WVARCHAR works.

OLEDB command from SQL Profiler:

exec sp_executesql N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)','TestMe','01','2015-03-24'

ODBC command from SQL Profiler using SQL_VARCHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T','0','2'
select @p1

ODBC command from SQL Profiler using SQL_CHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 char(20),@P2 char(20),@P3 char(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T                   ','0                   ','2                   '
select @p1

ODBC command from SQL Profiler using SQL_WVARCHAR

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(20),@P2 varchar(20),@P3 nvarchar(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'TestMe','01',N'2015-03-24'
select @p1


How to create test environment for lost transactions

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about.

Then look into your DB log for a sequence like this:

  lsn1 BEGIN XACT xid1
  lsn2 some update
  lsn3 COMMIT xid1
  lsn4 BEGIN XACT xid2
  lsn5 some update
  lsn6 COMMIT xid2

This sequence shows two operations that occurred in the DB in two distinct transactions. You want to show what happens if xid1 commits, but xid2 rolls back. Since catching the app right in between those two updates is difficult, you can simply restore the DB with STOP AT (see Recover to a Log Sequence Number (SQL Server)) and stop the recovery after lsn3 but before lsn6 (that is, xid 1 commits but xid2 will be forced to rollback). Now you have your DB in a logical inconsistent state (the first operation succeeded, the second failed).


Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server?

The error message is harmless, it only means that you imported the dump as a non-superuser and the extensions already existed.

There are potential problems with using a non-superuser (ownership usually cannot be restored), but since you didn't get any such error messages, you are fine.

The only important consideration is that you need to use pg_dump from version 10 to dump the 9.5 database, not the 9.5 pg_dump. While v10 knows how to dump a 9.5 database so that it can be loaded into v10 properly, 9.5 lacks that knowledge.


There is a simple and a complicated way.

The complicated way is to join with pg_roles and get the user name from there.

But since PostgreSQL hackers don't want to type more than necessary, they came up with something else:

Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that is for example used in the proowner column of pg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just the oid, but the type output function, which is used for display of the type, renders it as the object's name.

Put that together with PostgreSQL's type cast operator ::, and you can do:

SELECT proname,
       proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would prefer oid::regprocedure over proname.


Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed?

Another way to avoid temp tables would be this:

;WITH tmpTest AS
(
    SELECT  Num as [UserId]
            , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
            , COUNT(*) OVER() AS Quant
    FROM dbo.StringToNumSet('2,3,1', ',')
)
SELECT *
FROM tmpTest as T1
INNER JOIN
    (
        SELECT Top 10 Id
            , Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
        FROM TableA WITH (NOLOCK)
    ) as T2 ON T1.RowNo = (T2.RowNo % Quant) + 1;


Use GROUP BY and return more than 2 columns

You can use ROW_NUMBER:

with cte as
(
    select  *,
            RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
    from un_hi
    where ag_id = 'bfd'
    and cdts > '201708'
    and unit_status != 'uc'
)
select *
from cte
where RN = 1;


This is a clear case to use cte and window functions, please read about them a little. I assume you do not have too much data, so this code is efficient

;with cte
    as (select Index, cardCode, updateDate, updateTS, unitValue,
        ROW_NUMBER() OVER(PARTITION BY cardCode ORDER BY updateDate DESC, updateTS DESC) as rn
    from YourTable)
    select Index, cardCode, updateDate, updateTS, unitValue
    from cte
    where rn = 1


How to select from 3 tables while using count on 1, and sum on another?

I assume you have multiple entries in shows, views table for each user. When joining all three together, you get more rows than you expect: if for some user you have x rows in table shows and y rows in table views, then the result set contains x*y rows for that user, so you sum each entry in shows y times, and each entry in views table gets counted x times.

You need to aggregate each table separately, and then join the results:

select t1.sum1, t2.cnt, u.user_name
from users u
left join (select show_user, sum(show_count) sum1 from shows group by show_user) t1 on u.user = t1.show_user
left join (select view_user, count(view_views) cnt from views group by view_user) t2 on u.user = t2.view_user

SQLFiddle


Mongodb: Does not seem to respect capped size limit

OK, we solved this.

Issue is that mongo auto creates the collection when incoming data comes in.

So the problem was:

Drop collection
// transactions are coming in
Create collection as capped

But #3 comes too late, as the collection is already present, as not capped, because of #2.

Solution:

stop mongo
bring up mango on non standard port // to prevent auto creation of collection
create collection as capped
stop mongo, bring it back up on regular port

Now I have a capped collection!


how to check list of users present in all postgres database

Users are global to the Postgres instance (aka "cluster"). So there is no such thing as "their respective database name". \du will always show you the same list of users regardless of the database you are connected to.

If you want to get a list of databases a user is allowed to connect to, you can do that with this query:

select u.usename,
       (select string_agg(d.datname, ',' order by d.datname)
        from pg_database d
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u
order by u.usename

To turn that query "around" and list the users that are allowed to connect to each database, you can use this:

select d.datname,
       (select string_agg(u.usename, ',' order by u.usename)
        from pg_user u
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_users
from pg_database d
order by d.datname;


Counting postgres open connections without SQL query or connecting to postgres itself

A safe and reliable way is to use netstat.

I would start with netstat --numeric-ports | grep 5432 as this will show you active connections.  The problem of course is that there may be some false positives.  The following should work for most cases though:

netstat --numeric-ports | awk '/(localhost:5432|PGSQL.5432)/ {print $0}' | wc -l

Inspecting the process table (with ps) won't necessarily give you the number of open connections: there is no reason you might not start more worker processes than you need at startup.


How to model very flexible referencing relationships between many tables?

This is opinion-based. I wouldn’t try to suggest there’s a best-practice here.

If you have separate tables for actors, directors, movies, and have a generic EntityID column with separate EntityType column, you’ll always need to use dynamic SQL to query it, because until you know the type, you won’t know which table to join to.

Another option is to have a generic table of entities which contains the type. Your relationships table is then just two columns.

Then you have sparse columns in your entities table for all your properties, as directors have different attributes from movies.

And you index it well, so that you’re not trawling through all the actors when you want a list of movies. Filtered indexes can be particularly handy here, so that you have a dedicated index for movies which includes the movie-related columns.

Views can make querying easier too, but for traversing relationships you can use the generic table.


You must move the test on beenverified  from the WHERE clause to LEFT ... ON ...:

SELECT sa.empname
    , coalesce(Da.itemssold, 0)
FROM #ShowAll sa
LEFT JOIN #Data da
    ON sa.empid = da.empid
    AND da.beenverified = 'Yes';

With a LEFT JOIN, when there is no match on empid between the two tables, this query (minus the WHERE clause) returns NULL for beenverified (and other columns from #Data):

empname itemssold   beenverified
JJ      14          Yes
AA      12          No
BB      13          No
CC      NULL        NULL
DD      NULL        NULL
RR      NULL        NULL

If beenverified is tested in the WHERE clause, it does not return rows where the condition is not met:

beenverified IS NULL
beenverified <> 'Yes' (i.e. = 'No')

Only row JJ is then valid. With the test in the WHERE clause, this query behaves like an INNER JOIN ....

Coalesce is then added in order to replace NULL by 0.


The first query has a group by q.id. While MySQL allows you to use select * in a group by query, it is not the best practice, unless you are 100% sure that you want the undesired side effects. If you wan to not have ill-determined results, you should only include in the select list columns are are functionally dependent on the group by columns/expressions.

In your case, that would be any column from questions, users or trainers because you group by the primary key of questions and the joins to the other 2 tables are based on foreign key relationship from questions. But you should not put any column from the question_filters because that join is on a reverse foreign key relationship.

If/when you move to version 5.7, you would get an error with the 1st query otherwise.

Also, the left join to question_filters is actually treated as inner join, due to the where condition (and qf.filter_id in (3,4)). You could also change that to be explicit. The query becomes:

select q.*, u.*, t.*
from questions as q
    join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2 ;

Now for error you got with the count query: this is caused by the multiple columns with identical alias (id) in the subquery's select list. You can easily solve that by using select 1 or select q.* (or just select null).

What's more, the joins to  users and trainers are redundant for that query (because they are left joins) and so can be removed. The query becomes:

select count(*)
from
   (select 1 from questions as q
        join question_filters as qf on q.id = qf.question_id
    where q.deleted_at is null
        and qf.filter_id in (3,4)
        and q.status = 'published'
        and q.published_at < now()
        group by q.id
    having count(distinct qf.filter_id) = 2
   ) as first_query ;


Why optimizer introduces spool here, what is the reason to do it? There is nothing complex beyond the spool.

The thing beyond the spool is not a simple table reference, which could simply be duplicated when the left join / anti semi join alternative is generated.

It may look a little like a table (Constant Scan) but to the optimizer* it is a UNION ALL of the separate rows in the VALUES clause.

The additional complexity is enough for the optimizer to choose to spool and replay the source rows, and not replace the spool with a simple "table get" later on. For example, the initial transformation from full join looks like this:

Notice the extra spools introduced by the general transform. Spools above a simple table get are cleaned up later by the rule SpoolGetToGet.

If the optimizer had a corresponding SpoolConstGetToConstGet rule, it could work as you wish, in principle.

  How to get rid of it in this case, what are the possible ways?

Use a real table (temporary or variable), or write the transformation from full join manually, for example:

WITH
    p([Id], [Code]) AS
    (
        SELECT @id1, 'A'
        UNION ALL
        SELECT @id2, 'B'
    ),
    FullJoin AS
    (
        SELECT
            p.Code,
            d.[Status]
        FROM p
        LEFT JOIN #data d
            ON d.[Id] = p.[Id]
        UNION ALL
        SELECT
            NULL,
            D.[Status]
        FROM #data AS D
        WHERE NOT EXISTS
        (
            SELECT *
            FROM p
            WHERE p.Id = D.Id
        )
    )
SELECT
    COALESCE(FullJoin.Code, 'X') AS Code,
    COALESCE(FullJoin.Status, 0) AS [Status]
FROM FullJoin;

Plan for manual rewrite:

This has an estimated cost of 0.0067201 units, compared with 0.0203412 units for the original.

* It can be observed as a LogOp_UnionAll in the Converted Tree (TF 8605). In the Input Tree (TF 8606) it is a LogOp_ConstTableGet. The Converted Tree shows the tree of optimizer expression elements after parsing, normalization, algebrization, binding, and some other preparatory work. The Input Tree shows the elements after conversion to Negation Normal Form (NNF convert), runtime constant collapsing, and a few other bits and bobs. NNF convert includes logic to collapse logical unions and common table gets, among other things.


It's a problem in public websites because you can't email your shiny new CA key to everybody on the Internet.  So instead by convention browsers ship with a small list of CAs (Verisign and friends) and we all buy certificates from them.

In database replication, you can visit both servers and install your new CA key.  As long as you trust you, and you don't want strangers to authenticate to your database, you're probably in the clear.


What about redundancy if distributor goes down?

There is no support for Distribution failover in MSSQL replication.

One solution to this limitation is to use Windows clustering on the Distribution database. See more about clustering here: http://sqlmag.com/sql-server-2008/planning-and-implementing-sql-server-cluster http://sqlmag.com/sql-server-2008/set-sql-server-2008-cluster http://sqlmag.com/sql-server-2008/upgrading-sql-server-2005-cluster-sql-server-2008


Importing a SQL Dump File through XAMPP's Command Line

Depending on the tool and parameters used to create the .sql dump file of multiple databases, the file will normally have

CREATE DATABASE DBn...;

and

USE DBn;

statements that will allow your import to proceed without hiccups. For example, both the mysqldump command mysqldump command and phpMyAdmin's Export function for multiple database insert them.

Assuming you have exported with a sensible tool like those above, then you can import the database with a command line like this:

mysql -u username -p < dumpfile.sql

Your mysql username account needs to have appropriate privileges to, for example, create databases.

You can even run this command in a more familiar way by naming the startingDB database to use before running the commands in dumpfile.sql:

mysql -u username -p startingDB < dumpfile.sql

As the dumpfile will have a CREATE DATABASE and USE before any CREATE TABLE or INSERT statements, everything will work fine.


When creating views, why do users need direct object permissions if they already have the same permissions via a role?

From the manual:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510

  The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

(Emphasis by me)

So I guess there is no way around this problem.


What is the difference between public_dependency and dba_dependencies?

Check the source of both views:

select dbms_metadata.get_ddl('VIEW', 'PUBLIC_DEPENDENCY', 'SYS') from dual;
select dbms_metadata.get_ddl('VIEW', 'DBA_DEPENDENCIES', 'SYS') from dual;

PUBLIC_DEPENDENCY queries directly from the dictionary table dependency$ and nothing else.
DBA_DEPENDENCIES also queries dependency$, but joins some other tables and views as well, for example disk_and_fixed_objects. This view does not contain all the objects from dependency$, so this join eliminates some rows from dependency$ related to the missing objects. For example on my database:

select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#);

    P_OBJ#
----------
4294951597
4294952371
4294952370
4294952376
4294952374
4294952368
4294952372
4294952375
4294951440
4294952364
4294952365
4294952366
4294952373

The source of disk_and_fixed_objects is:

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DISK_AND_FIXED_OBJECTS" ("OBJ#", "OWNER#", "NAME", "TYPE#", "REMOTEOWNER", "LINKNAME", "EDITIONNAME") AS
  select co.obj#, co.owner#, co.name, co.type#, co.remoteowner, co.linkname,
       co.defining_edition
from sys."_CURRENT_EDITION_OBJ" co
union all
select kobjn_kqfp, 0, name_kqfp,
decode(type_kqfp, 1, 9, 2, 11, 3, 10, 0), NULL, NULL, NULL
from sys.x$kqfp
union all
select kqftaobj, 0, kqftanam, 2, NULL, NULL, NULL
from sys.x$kqfta
union all
select kqfviobj, 0, kqfvinam, 4, null, null, null
from sys.x$kqfvi

But the above 13 objects are not accounted for in the above underscore view or X$ tables, they are in another X$ table (X$KQFDT):

select * from x$kqfdt where kqfdtobj in ( select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));

ADDR                   INDX    INST_ID     CON_ID   KQFDTOBJ KQFDTNAM                       KQFDTEQU
---------------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------
000000000DF41A00          4          1          0 4294951440 X$KCVFHALL                     X$KCVFH
000000000DF41C80         20          1          0 4294951597 X$JOXFS                        X$JOXFT
000000000DF41D20         24          1          0 4294952364 X$JOXOBJ                       X$JOXFT
000000000DF41D48         25          1          0 4294952365 X$JOXSCD                       X$JOXFT
000000000DF41D70         26          1          0 4294952366 X$JOXRSV                       X$JOXFT
000000000DF41DC0         28          1          0 4294952368 X$JOXDRC                       X$JOXFT
000000000DF41E10         30          1          0 4294952370 X$JOXMOB                       X$JOXFM
000000000DF41E38         31          1          0 4294952371 X$JOXMIF                       X$JOXFM
000000000DF41E60         32          1          0 4294952372 X$JOXMIC                       X$JOXFM
000000000DF41E88         33          1          0 4294952373 X$JOXMFD                       X$JOXFM
000000000DF41EB0         34          1          0 4294952374 X$JOXMMD                       X$JOXFM
000000000DF41ED8         35          1          0 4294952375 X$JOXMAG                       X$JOXFM
000000000DF41F00         36          1          0 4294952376 X$JOXMEX                       X$JOXFM

13 rows selected.

Here you can see the name of these objects in the KQFDTNAM column. Another way to find them is querying the V$FIXED_TABLE view:

select * from v$fixed_table where object_id in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));

NAME                            OBJECT_ID TYPE   TABLE_NUM     CON_ID
------------------------------ ---------- ----- ---------- ----------
X$KCVFHALL                     4294951440 TABLE      65537          0
X$JOXFS                        4294951597 TABLE      65537          0
X$JOXOBJ                       4294952364 TABLE      65537          0
X$JOXSCD                       4294952365 TABLE      65537          0
X$JOXRSV                       4294952366 TABLE      65537          0
X$JOXDRC                       4294952368 TABLE      65537          0
X$JOXMOB                       4294952370 TABLE      65537          0
X$JOXMIF                       4294952371 TABLE      65537          0
X$JOXMIC                       4294952372 TABLE      65537          0
X$JOXMFD                       4294952373 TABLE      65537          0
X$JOXMMD                       4294952374 TABLE      65537          0
X$JOXMAG                       4294952375 TABLE      65537          0
X$JOXMEX                       4294952376 TABLE      65537          0

To sum up: DBA_DEPENDENCIES does not contain some dependencies related to some fixed objects. You can also check which objects are related to these "missing" dependencies, e.g.:

select o.owner, o.object_name, o.object_type from dependency$ d join dba_objects o on (d.d_obj# = o.object_id) where p_obj# in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#))

OWNER OBJECT_NAME                      OBJECT_TYPE
----- -------------------------------- -----------------------
SYS   USER_SOURCE_AE                   VIEW
SYS   ALL_SOURCE_AE                    VIEW
SYS   DBA_SOURCE_AE                    VIEW
SYS   INT$DBA_SOURCE                   VIEW
SYS   KU$_JAVA_SOURCE_VIEW             VIEW
SYS   DBMS_PITR                        PACKAGE BODY
SYS   USER_JAVA_CLASSES                VIEW
SYS   ALL_JAVA_CLASSES                 VIEW
SYS   DBA_JAVA_CLASSES                 VIEW
SYS   USER_JAVA_LAYOUTS                VIEW
SYS   ALL_JAVA_LAYOUTS                 VIEW
SYS   DBA_JAVA_LAYOUTS                 VIEW
SYS   USER_JAVA_IMPLEMENTS             VIEW
SYS   ALL_JAVA_IMPLEMENTS              VIEW
SYS   DBA_JAVA_IMPLEMENTS              VIEW
SYS   USER_JAVA_INNERS                 VIEW
SYS   ALL_JAVA_INNERS                  VIEW
SYS   DBA_JAVA_INNERS                  VIEW
SYS   USER_JAVA_FIELDS                 VIEW
SYS   ALL_JAVA_FIELDS                  VIEW
SYS   DBA_JAVA_FIELDS                  VIEW
SYS   USER_JAVA_METHODS                VIEW
SYS   ALL_JAVA_METHODS                 VIEW
SYS   DBA_JAVA_METHODS                 VIEW
SYS   USER_JAVA_ARGUMENTS              VIEW
SYS   ALL_JAVA_ARGUMENTS               VIEW
SYS   DBA_JAVA_ARGUMENTS               VIEW
SYS   USER_JAVA_THROWS                 VIEW
SYS   USER_JAVA_THROWS                 VIEW
SYS   ALL_JAVA_THROWS                  VIEW
SYS   ALL_JAVA_THROWS                  VIEW
SYS   DBA_JAVA_THROWS                  VIEW
SYS   DBA_JAVA_THROWS                  VIEW
SYS   USER_JAVA_DERIVATIONS            VIEW
SYS   ALL_JAVA_DERIVATIONS             VIEW
SYS   DBA_JAVA_DERIVATIONS             VIEW
SYS   USER_JAVA_RESOLVERS              VIEW
SYS   ALL_JAVA_RESOLVERS               VIEW
SYS   DBA_JAVA_RESOLVERS               VIEW
SYS   USER_JAVA_NCOMPS                 VIEW
SYS   ALL_JAVA_NCOMPS                  VIEW
SYS   DBA_JAVA_NCOMPS                  VIEW


Unable to save and error on DBCC CHECKDB

is it a physical or a virtual server? Please make a chkdsk on your filesystem where the database are stored.

Please also try a BACKUP DATABASE with CONTINUE_AFTER_ERROR:

BACKUP DATABASE AdventureWorks2012
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH CHECKSUM, CONTINUE_AFTER_ERROR;
GO

More information about this here:

https://docs.microsoft.com/de-de/sql/relational-databases/backup-restore/specify-if-backup-or-restore-continues-or-stops-after-error

Regards,
Björn


What are the parameters to install oracle 11g r2 command line?


Grouping data into 5 minute intervals within a time range


How to display rows containing special characters in a certain column

Try This:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z0-9]%'

What this does is search for

%.....% : a string
[.....] : that contains any of the mentioned characters
^       : well actually none of the mentioned characters
a-Z     : from lower-case a through to upper-case Z
0-9     : or any digit

Which results in a search for any character other than a-z, A-Z or 0-9.
Strings that match this would be:

test'user
something?else
I#can#fly

Reference: LIKE (Transact-SQL) (Microsoft | SQL Docs)

After re-reading your question, I would use just:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z]%'

...because you want to retrieve rows with numbers too.


SQL Server - Remove all logins that they are asossiated with orphan users

First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.

If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.

To find these logins with no database mapping please look here!

However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).


Apply Definition of MVD (multivalued dependency) to Example?

A FD is a MVD "in disguise". If an FD holds then a certain MVD also holds. With MVDs 1 is a special case of multi(ple). This can be seen from the rules of inference for MVDs at the definition link: RA → B implies A ↠ B.

Hint: t1 & t2 can be t3 & t4.

Hint: In the example there are only two tuples, so t1 & t2 must be those, and you know the MVD so you know A & B, and there are only two tuples, so if t3 & t4 exist they must be those. So fill in the equations and see whether given those two tuples (t1 & t2) there are also two tuples with the properties needed (t3 & t4).


Count of distinct values of a table using sql

This is know as conditional counting and can be achieved using a case expression:

select name,
       count(case when number = 8 then 1 end) as count_of_8,
       count(case when number = 41 then 1 end) as count_of_41
from the_table
group by name
order by name;

Aggregate functions (like count()) ignore NULL values. The case expression inside the count() returns null for those values that we don't want to count, hence only the number of rows containing that value are counted. case when x then y end is equivalent to case when x then y else null end


How can sp_blitz help investigate what caused TLog to fill up

No, sp_Blitz doesn't track that kind of thing. A better approach would be querying LOG_REUSE_WAIT_DESC.


SQL Server 2016 - Configuration Manager WMI Problem

Symptoms
On a 64-bit computer, you install an instance of the 32-bit (x86-based) version of Microsoft SQL Server. On the same computer, you install an instance of the 64-bit version of SQL Server 2008. If you then uninstall the 64-bit instance, you receive the following error message when you open SQL Server Configuration Manager:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

This problem also occurs if you uninstall the 32-bit instance, and you then open SQL Server Configuration Manager.
Cause
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.
Workaround
To work around this problem, open a command prompt, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"
Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2012   110
Microsoft SQL Server 2008 R2    100
Microsoft SQL Server 2008   100
Microsoft SQL Server 2005   90
After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.


Does SQL Server Only Perform Calculations In A SELECT List Once?

Most of the information you will need is going to be in the execution plan ( and the plan XML).

Take this query:

SELECT COUNT(val) As ColA,
       COUNT(val2) As ColB,
       COUNT(val) +  COUNT(val2) As ColC
FROM dbo.TableA;

The execution plan  (opened with sentryone plan explorer) shows what steps it went through:

With the stream aggregate aggregating the values for EXPR1005 & EXPR1006

If we want to know what these are, we could get the exact info on these expressions from the query plan XML:

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="COUNT([Database].[dbo].[TableA].[val])">
<Aggregate AggType="COUNT_BIG" Distinct="false">

With the first compute scalar computing ColA & ColB:

And the last compute scalar being a simple addition:

This is reading it as the data flows, in theory you should be reading it from left to right if going over the logical execution.

In that case, EXPR1004  is calling the other expressions, EXPR1002 & EXPR1003. In turn these are calling EXPR1005 & EXPR1006.

  Would CalculationA and CalculationB, each be calculated twice? Or
  would the optimizer be clever enough to calculate them once and use
  the result twice?

Previous tests show that in this case ColC is simplified as an addition of the calculations that are defined as ColA & ColB.

As a result, ColA & ColB are only calculated once.

Grouping by 200 distinct values

If we are grouping by 200 distinct values (val3) the same is shown:

SET STATISTICS IO, TIME ON;
SELECT SUM(val) As ColA,
       SUM(val2) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA
GROUP BY val3;

Aggregating down to these 200 distinct values in val3

performing the sums on val & val2 and then adding them together for ColC:

Even if we are grouping on all but one non-unique value, the same addition should be seen for the compute scalar.

Adding a function to  ColA & ColB

Even if we change the query to this:

SET STATISTICS IO, TIME ON;
SELECT ABS(SUM(val)) As ColA,
       ABS(SUM(val2)) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA

The aggregations are still not going to be calculated twice, we are simply adding the ABS() function to the resultset of the aggregation, wich is one row:

Ofcourse, running SUM(ABS(ColA) & SUM(ABS(ColB)) will make the optimizer unable to use the same expression for calculating ColC.

If you want to go deeper in when this happens I would nudge you towards Query Optimizer Deep Dive - Part 1 (until Part 4) by Paul White.

Anther way to dive deeper into query execution phases is by adding these hints:

OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

This will expose the input tree as created by the optimizer.

The adding of the two previous calculated values to get ColC is then translated to:

AncOp_PrjEl COL: Expr1004

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002

    ScaOp_Identifier COL: Expr1003

This information is already present in the Input Tree, even before the simplification phase has taken place, showing that the optimizer immediately knows that it does not have to perform the same calculation twice.


PostgreSQL how to get case insensitive schema name

Double quotes forces case sensitive names.
From this blog: Don’t use double quotes in PostgreSQL

Now, there is a way around this, namely by using double quotes.  Whereas single quotes in PostgreSQL are used to create a text string, double quotes are used to name an identifier without changing its case.
Let me say that again, because so many people get this wrong: Single quotes and double quotes in PostgreSQL have completely different jobs, and return completely different data types.  Single quotes return text strings.  Double quotes return (if you can really think of them as “returning” anything) identifiers, but with the case preserved.

In your question you should change:
GRANT SELECT ON ALL TABLES IN SCHEMA requetesql  TO ....;

by
GRANT SELECT ON ALL TABLES IN SCHEMA "requeteSQL"  TO ....;