When is it better to create STATISTICS instead of creating an Index? I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to find any guidance or "best practices" information regarding when to create them: what situations benefit more from a manually created STATISTICS object than from an Index. I have seen manually created filtered statistics helping queries on partitioned tables (because the statistics created for the indexes cover the whole table and are not per partition--brillaint!), but surely there must be other scenarios that would benefit from a statistics object while not needing the detail of an index, nor worth the cost of maintaining the index or increasing the chances of blocking / dead-locks.

@JonathanFite, in a comment, mentioned a distinction between indexes and statistics:

  Indexes will help SQL find the data faster by creating lookups that are sorted differently than the table itself. Statistics help SQL determine how much memory/effort is going to be required to satisfy the query.

That is great info, mostly because it helps me clarify my question:

How does knowing this (or any other technical info on the whats and hows related to the behaviors and nature of STATISTICS) help determine when to choose CREATE STATISTICS over CREATE INDEX, especially when creating an Index will create the related STATISTICS object? What scenario would be better served by having only the STATISTICS info and not having the Index?

It would be super-duper helpful, if possible, to have a working example of a scenario where the STATISTICS object is a better fit than an INDEX.

Since I am a visual learner / thinker, I thought it might help to see the differences between STATISTICS and INDEXes, side-by-side, as a possible means of helping determine when STATISTICS are the better choice.

Thingy           PROs                             CONs
-------          ----------                       -------------------
INDEX            * Can help sorts.                * Takes up space.
                 * Contains data (can             * Needs to be maintained (extra I/O).
                   "cover" a query).              * More chances for blocking / dead-locks.

STATISTICS       * Takes up very little space.    * Cannot help sorts.
                 * Lighter maintenance / won't    * Cannot "cover" queries.
                   slow down DML operations.
                 * Does not increase chances
                   of blocking / dead-locks.

The following are some resources that I found while looking for this, one that even asks this same question, but it was not answered:

SQL Server Index vs Statistic

SQL Server Statistics Questions We Were Too Shy to Ask

Statistics. Are multicolumn histograms possible?

** To be clear, I do not have an answer for this and am actually looking to get feedback from hopefully a few people to provide what appears to be oddly missing information out here in the interwebs.

You question revolves around - When is it a good thing to just create statistics vs create index (which create stats).

From my sql server internals notes (SQLSkills class- IE1 and IE2) and SQL Server internals book, below is my limited understanding :

SQL Server statistics are nothing but system objects that contain vital information about the index key values and regular column values.

SQL Server uses a cost based model to choose a "good enough" execution plan as fast as possible. Cardanility estimation (estimating no. of rows to be processed on each step of the
query execution) is the most important factor in query optimization which inturn affects the join strategy, memory grant requirement, worker thread selection
as well as choice of indexes when accessing data.

SQL Server wont use nonclustered indexes when it estimates that a large no. of KEY or RID loopup operations will be required, so it maintains statistics on indexes (and on columns) which
will help in such estimations.

There are 2 important things about stats :

Histogram stores info about data distribution for the leftmost statistics (index) column ONLY. It also stores info about multi column density of the key values. So essentially,
histogram stores data distribution for the leftmost statistics column only.
SQL Server will retain at most 200 steps in histogram irrespective of table size. The intervals covered by each histogram step increase as the table grows which leads to "less accurate" stats for large tables.

Remember that index selectivity is a metric which is inversely proportional to density i.e the more unique values a column has, the higher its selectivity.

When particular queries do not run very often, you can select to create column-level statistics rather than an index. Column-level statistics help
Query Optimizer find better execution plans, even though those execution plans are suboptimal due to the index
scans involved. At the same time, statistics do not add an overhead during data modification operations, and they
help avoid index maintenance. This approach works only for rarely executed queries.

Refer :

Column Statistics Give the Optimizer an Edge by Kalen Delaney
Statistics, Damned Lies, and Statistics – What is Statman? by Conor Cunningham

Note: Someone like Paul White or Aaron Bertrand can chime in to provide more color to your good question.


updatable entities in the design of databases I have a question on Entity Relationship design.

It is a shop where products are kept in stock first and then go to the store. When the store has less than a certain amount of product, products that are in stock go to the store. How would you design these entities?

I thought two entities products_store and products_stock but I don't know if there is another, more practical way.

Thanks.

You can have two entities products_store and products_stock as depicted in your question.

Other Options

  Incase you just need a track of which product is moved

I think of having single Products table with a column name it as In_stock (can be a bit field) where you change it to false, when the product is moved to store.

In_stock value options

1- InStock

0- InStore

  Incase you need a track of DateTime when the product is moved to store.

I think of having single Products table with one column called Move_DateTime (default value NULL) to update the column when the product is moved to store from stock.

Move_DateTime value options

NULL - InStock

DateTimevalue -InStore

In one possible scenario you can go for choosing both the columns to be added in the table.


SQL Server 2012 - Full Text Search [Break word - Underscore] Let's say I want to search for a list containing 'PS3':

SELECT * FROM [TABLE] WHERE CONTAINS(Title, 'PS3')

When the Title is proceeded by an underscore it will not return it.

Oblivion-PS3 -> Good
Oblivion PS3 -> Good
Oblivion_PS3 -> Not returning

I suppose the underscore is not a break word.

How can I fix it so that the underscore can be used as a break word?

I found it! :)

I changed the Language for Word Breaker in the Full-Text Catalog to Traditional Chinese.

I used the sys.dm_fts_parser in order to find what language could use the underscore as a break word.

1033 - English

1028 - Traditional Chinese

select * from sys.dm_fts_parser('Oblivion_PS3', 1028, 5, 0)

return Oblivion AND PS3

select * from sys.dm_fts_parser('Oblivion_PS3', 1033, 5, 0)

return Oblivion_PS3


When hash indexes should be used in Oracle? Hash indexes are usefull when you have a large table with URLs and you need to query by them. So, a solution would be to have an additional column "url_crc32" that will be filled with hashed URL value via trigger on inserts. An index on url_crc32 would be definitely faster than index on URL column that is a type of text.

It's not very common case to query data by URL. The more frequent case would be to query text data by fragment and hash indexes are useless in such case. So, I'm curious do you use such hash indexes, and if so then when do you use? IMHO Oracle does not have native hash indexes, so that must be done manually.

You'd use a hash value as an identifier for rows of data whenever that's "practical", i.e. you actually need a simple identifier for all your rows, there are no candidate (natural) key fields that are practical (too wide for example), and an ordinary (sequential for instance) generated identifier doesn't cut it (for instance you need that row identifier to be "global" - if the same row was created in two distinct databases, they should have the same identifier).

(One non-trivial example of such a thing would be Git. Each object stored in a git repository is uniquely identified by a SHA-1 hash, which is pretty handy to refer unambiguously to a given commit.)

If you need to use something like that, indeed you'll need to create it yourself with Oracle (adding that hash as a column to the table and indexing it, or with a function-based index).

You could go even further by creating your very own index type with application domain indexes if a plain hash isn't good enough – full-text indexing is, I believe, implemented this way.

There is something built-in to Oracle that is hash-based and doesn't actually need an index to get fast row retrieval: hash clusters. You can theoretically retrieve the target row with as little as a single-block I/O (which normal index+table lookups can't match, and even IOTs can't match unless the table is really small). Do read the When to Use Hash Clusters docs though, they are quite peculiar, and you need a good key (one or more columns) in the first place to use them.


pg_upgrade doesn't find config file I'm trying to upgrade PostgreSQL on Ubuntu Server from 9.6 to 10. This is the first time I'm doing this. The server doesn't contain any useful data yet but I want to make sure I can do this properly next time.

I followed the instructions in the manual. Here's what I did:

Installed the new packages for version 10
Stopped both versions on the server
Ran the pg_upgrade script:

sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin -d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/

This is the console output:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

This is the contents of pg_upgrade_server.log:

command: "/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: could not access the server configuration file "/var/lib/postgresql/9.6/main/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I'm totally confused now. What should I do?

You know what they say about the word assume

I assumed the config dir and data dir was the same.

Answer 1:

Change your command to this and it will work.

sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
-b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin \
-d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/
-o 'config_file=/etc/postgresql/9.6/main/postgresql.conf' \
-O 'config_file=/etc/postgresql/10/main/postgresql.conf'

we can provide the special config file location as an option

Also, I believe you have identified a default install issue or the documentation needs to be updated

The default install should create the config files in the datadir

Answer 2:

Since this is play, try this too:

sudo -u postgres pg_dropcluster 10 main
sudo -u postgres pg_upgradecluster 9.6 main

Keeping this, since it might help someone else.

The empty databases seem to have not been created yet. Or are in a different directory. That's why postgresql.conf is not found in the error message.

ls -l /var/lib/postgresql/9.6/main/postgresql.conf /var/lib/postgresql/10/main/postgresql.conf

should show two files.

The equivalent initdb command for both versions should be run

pg_ctl -D /var/lib/postgresql/9.6/main initdb

pg_ctl -D /var/lib/postgresql/10/main initdb


Database never maintained Just moved from wrong forum i paste here my question:

I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor application is compatible only max to SQL Server 2005. I found that the database never got a maintenance plan: it's about 14 years old, always up and, fortunately, always backed up (daily full backup, 4 hour differential and 10 minutes transaction log backup).

At first glance DB is split in 4 .mdf files (10Gb, 30Gb, 2Gb, 2Gb) and one .ldf file of 35Gb. .ldf is quite big because transactions log are max 100 mb. For what office folks say during those years there were 2 - 3 big deletion, but they don't know anything else. Server is a bit old (IBM x346, 8Gb Ram and only 70gb of free hd space), running W2003 R2 32bit with /pae and SQL Server 2005 with AWE.

At first glance index fragmentation is HUGE.

What are the best practices to start 'revive' :-) this situation ?

It is certainly possible to use a Maintenance Plan to handle the fragmentation issues.  However, many people use the free scripts at: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I am part of the 'many people'.  These scripts by default implement the suggested standards for reducing the fragmentation of indexes.  So, if you implement the script with one of the many sample settings further down the page, it should bring your indexes into better order.

Your database is not particularly huge, so scheduling an online index rebuild on a relatively quiet time, perhaps Saturday night if that is a low use time, and let it run.

If you link to the home page (http://ola.hallengren.com/) you will also find scripts for database backups and integrity checking.  It seems that you have a good backup schedule overall, though I do not know your retention period, so you do not need to change unless you personal see the need.

Second, regarding the large LOG file, you can look into the DBCC SHRINKFILE() command to reset the size of the log file. On the other hand why bother unless you need the space.

DBCC SHRINKFILE ('LogFileName', );

If it does not shrink the first time, run CHECKPOINT, backup the log again, and try to shrink the file again. Once the high order portion of the log is empty (through the log backups) the file will be able to shrink.


Oracle auditing on specific user activity I want to be able audit user 'SCOTT' on 'SELECT, INSERT, UPDATE, DELETE' dml operations across all objects in the database SCOTT has access to.

I'm familiar with dbms_fga but I can't achieve what I'd like to using the add_policy arguments (it wants to be more fine-grained than I need it to be).

I've also tried switching on the audit_trail parameter:

alter system set audit_trail=os scope=spfile;
bounce
alter system set audit_file_dest='/u01/app/oracle/auditing';
audit session by SCOTT;

but all this seems to do is create files which log login/logoff activity (and doesn't seem to be restricted to SCOTT either).

Any ideas if it's possible to audit a specific user on all activity that takes place in their session?

Thanks to Andrew Brennan, I created a trigger that enables tracing when the user logs on:

CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'SCOTT'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 1''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


Where is "UNDO" in SQL Server? Seems like a simple question but I've found conflicting information out there. Can anyone enlighten me (ideally with a rock-solid source)? Is it:

Part of the transaction log, per database?
Part of tempdb?
Somewhere else...

My gut says the transaction log file.

Part of the transaction log, per database?

Yes that is correct. Undo happens when database goes through crash recovery or recovery. There are 3 phases of recovery

Analysis
Redo
Undo

The undo phase is where transactions that are not committed are rolled back so that they don't end up being in database when it comes online. Yes this information about transaction which is not committed is present in transaction log. To understand more about this please read Logging and recovery in SQL Server

Tempdb is always in simple recovery model but "Minimal Logging" is followed in tempdb. This means ONLY information to rollback transaction is there no information to rollforward the transaction is present. Crash recovery does not happens in Tempdb.

Further Reading.

Understanding How Restore and Recovery of Backups Work in SQL Server


tempdb log file usage huge by sleeping status sessions but not relased the space We have a alert threshold of tempdb usage on drive level at 90% and getting lot of alerts. But the first problematic point is the tempdb usage occupied whole drive of 500GB among Log file only occupied 95% and not able to increase the log file for other works on tempdb. While we checked the open transactions we seen sleeping sessions are there 2days and 1day before time stamps and not released the space to other transactions. I think it's worst behavior the sleeping sessions blocked the space and released to the others. We need the script for Is really those sessions are occupieing the LOG file space and not released them. (I came to know jdbc drivers sessions suddenly drops and so that the sleeping sessions are not released the space to other transactions). Can you provide Is there any script to identify the same.

Use this script to determine tempDB usage per sessions :

--Lists the TempDB usage per each active session.
--It helps identifying the sessions that use the tempdb heavily with internal objects.

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC


Insert statement fails silently when a FK constaint can't be statically evaluated I ran into an interesting issue which I'm not entirely sure how to address.

My tables:

CREATE TABLE entity (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  properties jsonb
);

CREATE TABLE hierarchy (
  parent_id int REFERENCES entity(id) NOT NULL,
  child_id int REFERENCES entity(id) NOT NULL,
  PRIMARY KEY(parent_id, child_id)
);

And some dummy data:

INSERT INTO entity (id, properties)
VALUES (1234, '{}'::jsonb);

The issue occurs when inserting data into hierarchy which violates this FK constraint. The following doesn't work, as expected, and returns a helpful error message:

WITH entity_result AS ( SELECT 5678 AS id )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- [23503] ERROR: insert or update on table "hierarchy" violates foreign key constraint "hierarchy_child_id_fkey" Detail: Key (child_id)=(5678) is not present in table "entity".

However, when doing another simiiar query where the ID's value cannot be statically assessed, instead of reporting the error it just fails silently. For example, the following query:

WITH entity_result AS (
  SELECT id FROM entity
  WHERE properties->>'member_id' = '92385' )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- ...silence

Simply fails, and without any indication as to why.

Is there any way to get some information about why a query like the above fails? It's baffling me!

My bet is that:

SELECT id FROM entity
WHERE properties->>'member_id' = '92385'

returns NO ROWS (NO ERROR) and INSERT command is never executed.

I've reproduced it in this way:

create table entity
(
  id int primary key,
  foo int
);

create table hierarchy
(
  id serial primary key,
  parent_id int,
  child_id int,
  foreign key (parent_id) references entity (id),
  foreign key (child_id) references entity (id)
);

insert into entity
select x, x * 2
from   generate_series(1, 100) x;

WITH entity_result AS (
  SELECT id FROM entity
  WHERE id = 10000 )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 100, id FROM entity_result;

✓

SELECT * FROM hierarchy;

id | parent_id | child_id
-: | --------: | -------:

INSERT INTO hierarchy VALUES (100, NULL);

1 rows affected

SELECT * FROM hierarchy;

 id | parent_id | child_id
--: | --------: | -------:
100 |      null |     null

db<>fiddle here


SQL 2019 Linux, slow at GUI fast at sqlcmd/Query Analyzer Server information
OS Redhat 7
Memory 100GB
CPU usage 1%
User db size 2 gb each on avg
total 7 user databases
SSMS installed on a jump server
Azure Data studio is also installed
App Gui on application server
Latency between jump server to db server <1ms
Latency between application server and db server  <1ms
all are in same network, only switch is between them
all are physical machines
Issue

SSMS - When I connect to SSMS, the databases in object explorer takes around 30 seconds to populate. Opening database properties takes upto 50 seconds to open
Query analyser/sqlcmd from SSMS runs "select name from sys.sysdatabases" instantly without delay

Azure data studio - Populates Databases list fairly quickly but takes more than a minute to show database properties.

App Gui - also have the same behaviour as SSMS.

From every location, querying seems fast enough but GUI is found to be slow.
Different scenario
I have other SQL Server on windows OS, the SSMS, Azure data studio behaviour is usual where they populate information on GUI instantly.
We also have few database on Azure SQL Database and Azure VM, they also connect instantly and populate information instantly at SSMS GUI
What we want to achieve?
We need our App gui, SSMS to perform as fast as SQL Server on Windows OS at gui level.
So what could be the issue and how I can fix this?
Please help!
Edit -
I missed the most important part. The said instance is Express edition. So the super impressive hardware they put into it was not being fully utilised. Plus the default auto close feature.

Auto close was found to be the culprit and it was decided to turn off this option for all databases. Express edition have this feature on by default.


Collect all Similar Persons to One Group I have a person with several Id's.
Some of them in Column Id1 and Some of them in Id2.
I want to collect all the same persons Id's to one group.

If id1=10, is in the same row with id2=20. so it's mean that person with id1=10 he is the same person like id2=20.

The Input and Output example:

Input

Id1     Id2
---     ---
10      20
10      30
30      30
10      40

50      70
60      50
70      70

Output

NewId   OldId
-----   -----
1       10
1       20
1       30
1       40

2       50
2       60
2       70

It will be better to make such calculations in iterative manner.

The "issue" of such solution is that, in case of large source table, the output table will contains all the rows. But, actually, this is the benefit also: in case of large source table, such code will work much more faster, that recursions.

if object_id('tempdb..#src') is not null drop table #src;
create table #src (id1 int not null, id2 int)

if object_id('tempdb..#rez') is not null drop table #rez;
create table #rez (id int not null, chainid int not null);

declare @chainId int;

insert #src
values (10, 20), (10, 30), (30, 30), (10, 40), (50, 70), (60, 50), (70, 70)
--values (4457745,255714),(4457745,2540222),(2540222,4457745),(255714,4457745)

set @chainId = 1

while 1 = 1
begin
    insert #rez(id, chainid)
    select top 1 id1, @chainId
    from #src
    where
        Id1 NOT IN (select Id from #rez)
        and id2 NOT IN (select Id from #rez)

    if @@rowcount = 0 break

    while 1 = 1
    begin
        insert #rez(id, chainid)

        select id1, @chainid
        from #src
        where
            id2 in (select id from #rez where chainid = @chainId)
            and id1 not in (select id from #rez where chainid = @chainId)

        union

        select id2, @chainId
        from #src
        where
            id1 in (select id from #rez where chainid = @chainId)
            and id2 not in (select id from #rez where chainid = @chainId)

        if @@rowcount = 0 break
    end

    select @chainId = @chainId + 1
end

select [NewId] = chainid, OldID = id
from #rez
order by 1, 2;

In some details, the "logic" of the script is the following:

set new ID (chainid) initial value (ex. 1)

get the first entry which was not found yet (external cycle)

find and store all related to it (in both columns) (internal cycle)

increment the new ID
find next entry which is not in the result table
continue till have data to work

return sorted result


How to select rows which are mapped to a junction table in a many to many relationship? I have three tables:

locations (has zipcode, location name);
professionals (has name);
location_professional.

I want to select only those rows from locations table where professional_id is 4. What's the best way to do this?

Assuming that locations and professionals also have their PK columns as location_id and professional_id, respectively, and location_professional uses those columns to link the two entities:

SELECT loc.*
    FROM location_professional AS lp
    JOIN locations AS loc  ON lp.location_id = loc.location_id
    WHERE lp.professional_id = 4;

In your simple example, there is no need to also JOIN professionals AS p ON ....  In a typical case, that would also be needed.


Application service down due to SQL Server error I'm using SQL Server 2012 where in I found an error in my SQL Server error logs which says:

  Logon,Unknown,The login packet used to open the connection is
  structurally invalid; the connection has been closed. Please contact
  the vendor of the client library.

Because of this, on my application services went down.

What is the error pointing to?

Before generating this message, the server would have previously received other structurally incorrect packets that corrupted components within the application stack.  This is typically how denial of service or penetration attacks are formed.  If you have this system on the Internet, I'd get it off the Internet, or use filtering to restrict access to specific IP's.  If it's not on the Internet, I'd check the users for software that has not been tested and qualified by an entity such a vendor or open source community.


Logging/profiling an unpredictably slow stored procedure I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only the OCI execute call. The second is inside the PL/SQL procedure itself, timing the inside of the procedure's main block.

Normally, if the procedure's timer takes, say, 5 seconds to run, the client's timer is only a few milliseconds greater. This is completely reasonable. The problem is that sometimes the client's timer will be much greater than the procedure's, even double.

I've eliminated any issue on the client or in the transport layer. I enabled client and server logging in sqlnet.ora at support level. On the client, the gap between request and response packets is essentially equal to the client code's timer. On the server, a similar gap is seen between the incoming request and the outgoing response.

The question remains, if the overall call takes 10 seconds, and inside the procedure takes only 5 seconds, where did the other 5 seconds go?

I don't expect the exact answer to the above question, but I would like to hear ideas on how to go about finding the answer. What logs can I turn on to give me insight into what Oracle is doing both after the request arrives but before the actual call to the procedure, and after the procedure until the response is sent back to the client?

Edit:
Perhaps there's something wrong with my timing methodology? I'm using dbms_utility.get_cpu_time() (which provides time in hundredths of a second, hence the division by 100.)

PROCEDURE foo (elapsed_time OUT NUMBER) IS
  start_time NUMBER;
BEGIN
  start_time := dbms_utility.get_cpu_time();
  -- ... PL/SQL stuffs
  elapsed_time := (dbms_utility.get_cpu_time() - start_time) / 100;
END;

You can set tracing at the ADMIN level. Your timing is suspiciously similar to an issue I dealt with in April/May this year. The admin level trace file showed a 5 second gap when reading from a socket. (10:44:05 -> 10:44:11) NTTRD is the read from tcp socket call. I'd definitely work this issue with Oracle support, but if you see gaps like the one below then the pause is somewhere in your network stack or hardware (i.e outside Oracle). In our case Oracle support pointed us towards network switch logs etc, but the culprit turned out to be an issue in the Microsoft Windows TCP stack down to the new Receive Window Auto-Tuning feature (see http://support.microsoft.com/kb/983528 for example if on Server 2008). Obviously it would be pure fluke if your issue was the same as ours, but if you can see the gaps in the admin level trace file it's a fair bet that it will turn out to be a network issue.

[08-APR-2011 10:44:05:938] nsprecv: reading from transport...
[08-APR-2011 10:44:05:938] nttrd: entry
[08-APR-2011 10:44:11:095] nttrd: socket 2156 had bytes read=60
[08-APR-2011 10:44:11:095] nttrd: exit
[08-APR-2011 10:44:11:095] nsprecv: 60 bytes from transport
[08-APR-2011 10:44:11:095] nsprecv: tlen=60, plen=60, type=6


Difference between now() and current_timestamp In PostgreSQL, I use the now() and current_timestamp function and I see no difference:

# SELECT now(), current_timestamp;
              now               |              now
--------------------------------+--------------------------------
 04/20/2014 19:44:27.215557 EDT | 04/20/2014 19:44:27.215557 EDT
(1 row)

Am I missing something?

There is no difference. Three quotes from the manual:
1)

These SQL-standard functions all return values based on the start time
of the current transaction:
...
CURRENT_TIMESTAMP
...

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
named to clearly reflect what it returns.

now() is a traditional PostgreSQL equivalent to transaction_timestamp().

Bold emphasis mine. CURRENT_TIMESTAMP, transaction_timestamp() and now() do exactly the same. CURRENT_TIMESTAMP is a syntactical oddity for a function, having no trailing pair of parentheses. That's according to the SQL standard.
If you don't declare a column alias for a function call in an SQL statement, the alias defaults to the name of the function. Internally, the standard-SQL CURRENT_TIMESTAMP is implemented with now(). Up to Postgres 9.6 that shows in the resulting column name, which was "now", but changed to "current_timestamp" in Postgres 10.
transaction_timestamp() does the same, but this one is a proper Postgres function, so the default alias has always been "transaction_timestamp".
Do not confuse either of these functions with the special input constant 'now'. That's just one of several notational shorthands for specific date/time/timestamp values, quoting the manual:

...  that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.

It may add to the confusion that (up to at least Postgres 12) any number of leading and trailing spaces and brackets ({[( )]}) are trimmed from those special input values. So 'now()'::timestamptz - or just 'now()' where no explicit type cast is required - is also valid and happens to evaluate to the same timestamp as the function now() in most contexts. But those are constants and typically not what you want as column default for instance.
db<>fiddle here
Old sqlfiddle
Notable alternatives are statement_timestamp() and clock_timestamp(). The manual:

statement_timestamp() returns the start time of the current
statement (more specifically, the time of receipt of the latest
command message from the client). [...]
clock_timestamp() returns the actual current time, and therefore its
value changes even within a single SQL command.

Note: statement_timestamp() is STABLE as the above (always returns the same value within the same SQL command). But clock_timestamp() necessarily is only VOLATILE. The difference may be significant.


Need a help in Query to check whether Task is completed by other user or not before showing on dashboard I have 3 tables in database Task, User & assignedTo.

Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to which user.

i need to query on 'assignedTo' table to show a list of Task assigned to users on their Dashboard. Problem is a single Task is assigned to multiple user and it may possible task may be completed by one of them. So i need to show task which are in Open Status and not completed by anyone in assigned list.
Below is structure of 'assignedTo' Table:

--------------------------------
| id | taskId | userId | status|
--------------------------------
| 1  |  1     | 1      | open  |
| 2  |  1     | 2      | open  |
| 3  |  1     | 3      | open  |
| 4  |  1     | 4      | open  |
| 5  |  2     | 1      | open  |
| 6  |  2     | 2      | close |
| 7  |  3     | 1      | open  |
| 8  |  3     | 2      | open  |
--------------------------------

So as per above data, Task 1 is shown on user 1,2,3 & 4 Dashboard as it is not closed by any of them.
Task 2 is shown on no one's Dash as it is closed by User 2.
Similarly, Task 3 is shown on user 1 & 2 Dash as it is open for both of them.

i tried to join same table to it's own but failed to execute.

SELECT * FROM assignedTo  a1
JOIN assignedTo a2 ON a2.taskId = a1.taskId AND a2.userId != 1 AND a2.status = 'open'
WHERE a1.userId = 1 AND a1.status = 'open'

Please help with Query if possible.

SELECT  A1.*,
        CASE WHEN A2.taskId IS NULL THEN 'Task Completed'
        ELSE 'open' END AS CurrentStatus
FROM AssignedTo A1
LEFT JOIN (
    SELECT taskId
    FROM AssignedTo
    WHERE status IN ('open', 'close')
    GROUP BY taskId
    HAVING COUNT(DISTINCT status) = 1
    ) A2 ON A2.taskId = A1.taskId
ORDER BY A1.taskId;

Fiddler: http://rextester.com/CAHOPS39734

Help from this post

The above query will return one extra column as CurrentStatus. If the taskId has any close status, for those tasks the CurrentStatus will be Task Completed and for remaining tasks it will show as open only.

So the output will be:

id  taskId  userId  status  CurrentStatus
1   1       1       open    open
4   1       4       open    open
2   1       2       open    open
3   1       3       open    open
5   2       1       open    Task Completed
6   2       2       close   Task Completed
7   3       1       open    open
8   3       2       open    open


Nonclustered Index still doing index scan I have this simple query.

SELECT a.RecordID SubmissionID,
  a.[DateSubmitted],
  a.[FinalAction],
  a.[FinalActionReason],
  a.[FinalActionDate],
  b.GradeSubmissionListID,
  b.[Action],
  b.ActionReason,
  b.ActionDate
FROM [dbo].[GradeSubmissionList] a
  LEFT JOIN dbo.GradeSubmissionApproveRejectLog b
  ON a.RecordID = b.GradeSubmissionListID
WHERE a.EdpCode = '1314ACT1258'
ORDER BY a.DateSubmitted

This produces the following execution plan,

I want to get rid all Clustered Index Scan. When I hover the first clustered index scan, it generated this image,

It looks like it's using the Primary Key index,

ALTER TABLE [dbo].[GradeSubmissionList]
ADD CONSTRAINT [GradeSubmissionList_pk_RecordID] PRIMARY KEY CLUSTERED
([RecordID] ASC) ON [PRIMARY]

So I created one based on this article: Why use the INCLUDE clause when creating an index?

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(RecordID, FinalApprovedRejectedBy, EdpCode , DateSubmitted)
INCLUDE ( FinalAction, FinalActionDate, FinalActionReason );

I am expecting that the sql statement will now be converted into Index Seek, but shows out it's still scanning the entire row.

What are the thing the I need to do in order to make this work? If there are things that you want to know about my situation please do ask.

Try to create this index:

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );

Index Seek  will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article SQL Server Index Design Guide


error when starting up the mongodb cluster I have a cluser of mongodb of 3 nodes:
After increasing the max-openfile-count of the linux system user,
 I tried the following cluster startup commands after a rebooting the linux system without shutdowning mongodb cluster normally.

# log in each node:
1, /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod -f /data/mongodbtest/config/mongo.conf
2, /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard1/mongo.conf --nojournal
3,-- /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard2/mongo.conf --nojournal
4,-- /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard3/mongo.conf --nojournal

when I run the step 2, the process stucked with following message:

I tried to delete the mongod.lock file in " /data/mongodbtest/shard1/data " and retry the process.
But the problem still exists.I don't know how can i fix this.

Without knowing what is content of /data/mongodbtest/shard1/mongo.conf, it's look like you don't have --fork (in config file processManagement.fork:true) set up. Parameter moves the process to background.

BTW, what is reason to --maxConns parameter?


Trying to avoid bad MySQL database design - can't figure out how to avoid too many columns I am pretty much a beginner having only created a few small php web applications relying on a MySQL database. I am now working on a somewhat larger project. I am in the planning stages of setting up the database and am having difficulty wrapping my mind around one issue.

The database will be used for Lego. The main tables will be parts and sets. Under the sets table I will require a listing of the part numbers that are in each set as well as a listing for the quantity of those parts in the set. My limited knowledge of how to set this up would see me setting up something like...   part_1, part_2, part_3... etc & qty_1, qty_2, qty_3... etc. The problem with this is that there will be some sets that have hundreds of parts which makes the method I can think of unfeasibly messy. Also I would like to have the functionality for a user to be able to query the database and recover all sets that have a particular part. With the setup I have in mind I can't see this as possible as it would potentially require a search through 100+ columns.

I am sure there is a simple solution that my noobness cannot see and one which I cannot find online via the keyword searches I have been making (I am 4 hours into that search and have learned a lot but not what I wanted...). Any advice would be very greatly appreciated.

Apologies for not giving you code. The website will be done in PHP with a MySQL database. At the moment I am at the stage of drawings in a notebook and do not have anything coded down.

Anytime you'd start getting a col1, col2, col3 in a database design, you "typically" want to consider those columns as rows in another table.  (typically, there are always exceptions - doubtful this is the case ;) )

So you have a LEGO_SETs and LEGO_PARTs table already ?

Sounds like you have something like this:

table: LEGO_SET
SET_ID
name
... etc ..
PART_1
QTY_1
PART_2
QTY_2
... etc.

table: LEGO_PART
PART_ID

You have here a many to many relationship. (ie A Lego_Set has many Lego_parts. A Lego_Part may belong to many Lego_sets).

You should have an interim table to help resolve this:
Remove the PART_1, QTY_1, etc. from LEGO_SET table and do this:

table: LEGO_PART_SET
SET_ID
PART_ID
QTY

that's it.

So if you have SET_ID 123, having Lego_parts 333 and 444.
You would have 2 rows in this table:

SET_ID  PART_ID  QTY
123     333       12
123     444       4

Hope that helps.

[edit]
Edited based on comments: Don't use SET table, avoid reserved words.
[/edit]


MySQL specific database configuration file In MySQL's configuration file I've globally disabled autocommit as so.

[mysqld]
autocommit=0

I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the user or the database itself (doesn't matter). Thinking it would look something like this.

[mysqld]
autocommit=0
execute_sql="Custom SQL to set autocommit for a database"

Let's say

you are using MySQL in Linux
datadir is /var/lib/mysql

You could use the init-file option

You could just create an init file called /var/lib/mysql/init-file.sql

Put the customer SQL in /var/lib/mysql/init-file.sql

You could then try one of two things

Try #1

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file.sql

then run

service mysql restart

Try #2

Directly run this

service mysql restart --init-file=/var/lib/mysql/init-file.sql

Give it a Try !!!

CAVEAT

That was a global setting of init code. If your session has to enable autocommit, then just run

SET autocommit=0

in the session or put SET AUTOCOMMIT=0 in the init-file option file. This may still not produce the effect you want since everything goes back to default settings upon connection termination.

UPDATE 2013-02-28 15:40 EST

If you are looking for a startup option for my.cnf to affect the client, try this:

[client]
autocommit=0

or

[mysql]
autocommit=0

Perhaps try adding another my.cnf in the Linux home directory with these options

For more information, see the MySQL Options File Documentation


cannot find old connection string connecting to old database and not new one that has been defined Cannot find old connection string connecting to old database and not new one that has been defined. We have changed web servers on Azure and cannot find the reference to the old database because our IIS sites are still hitting the old database as well as the newly defined one.

I am hoping this is not edge case and someone has had this problem. There are a lot of variables to this problem and know anyone reading this will have questions. Our new sites are working on new SQL but when we shut down the old one our sites break and error out. I am thinking something to do with session state and checked all the conn strings in IIS and found no reference whatsoever to the old SQL Server.

To add on to what Max is saying, you could use the following to get you the sql info and use the port information and sql text to figure out which service is doing what and then use those additional clues to help you isolate where it's coming from.

SELECT
   des.host_name
    , des.program_name
    , des.login_name
    , dec.client_net_address
    , dec.client_tcp_port
    , db_name(a.dbid) as db_name
    , object_name(a.objectid) as object_name
    , a.text as sql_text
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) a
WHERE des.is_user_process = 1;


LOAD DATA keeps CPU usage high after it finishes I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this:

mysql.RegisterLocalFile(fileName)
_, err = dbConnection.Exec(loadDataCommand)

This works as expected after the load has finished I run some SELECT queries (I'm reusing dbConnection for those SELECT queries and all other queries) on newly loaded data and this is where the problem occurs. The above mentioned SELECT queries are unusually slow (due to high CPU usage).

While running LOAD DATA database CPU usage spikes which is expected but after the LOAD DATA finishes CPU usage should drop but it doesn't unless I close and reopen dbConnection CPU usage stays high.

This is how I'm "reseting" the database connection:

_ = dbConnection.Close()
dbConnection, err = sql.Open(databaseDriver, databaseUser+":"+databasePassword+"@tcp("+databaseAddress+":3306)/"+databaseName+"?multiStatements=true")

This is the database configuration.

Why is this happening? The above code is running on a development database so there are no other connections to it.

As expected.

The updating of secondary indexes (INDEXes other than PRIMARY KEY) happens later.  Such changes are stored in the Change Buffer; they are actually written to disk later in a more efficient way than trying to do them as the rows are being inserted.  You will see both CPU and I/O activity.

The net effect should be that your LOADs and SELECTs will complete faster (elapsed time) than without the Change Buffer.


Large Table Times out - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding We have a customer who is experiencing Time-outs in our .NET application which uses SQL Server as the backend.
The table in question is the OrderLines table in a SQL Server Database that has 4155151 rows
Lately, the customer has reported, that intermittently they would get these Time-Outs during the process of updating this table.
The code doesn't have a specific Time-out set in the Connection String to the Database but on the SQL Server, the Time-out is set to 10 minutes.
The error reported is as follows:

Execution Timeout Expired.  The timeout period elapsed prior to
completion of the operation or the server is not responding.
at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
 at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)    at
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)    at
System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

We have checked the Indexes for this table and it seems ok (see image attached).
The customers IT guys have checked processes/tasks running on the server throughout the day, and did mention they recently updated to .NET 4.6.1
. Also, they mentioned they have plenty of RAM allocated and disk latency is fine and CPU is fine as well.
Any ideas on how to trouble shoot this issue further?

The code doesn't have a specific Time-out set in the Connection String
  to the Database but on the SQL Server, the Time-out is set to 10
  minutes.

You are most probably reaching a client timeout which by default is 30 seconds for C# SQL Server client (ADO.NET?). Consider increasing this timeout until you find a decent solution that does not force the client into waiting so much time.

  We have checked the Indexes for this table and it seems ok (see image
  attached).

Typically indexes are not OK by default, but based on actual SELECT queries that hit the table. In your case you seem to issue an UPDATE and the first thing that comes into my mind is that you have a lot of indexes (5) on that table and UPDATE might need to also update most of them. Just from the name, idx_ordernumber and idx_ordernumber_Status seem to be very similar and they might be merged into a single index.

The following is mostly an approach made by a .NET programmer and it is not optimal from a DBA's perspective, but it might provide useful insight before diving into more DBA savvy stuff. Try this on a development / test database that has little activity, but a data volume similar to that from production:

Run SQL Server Profiler (yes, it is deprecated, but the tool is still very useful for quick checks)
Create a new trace and double check that you are filtering by your database, remove audit events and include events like *:Starting + *:Completed
Run your UPDATE scenario. You should see some queries and I expect that your problematic query to be more expensive (perhaps a rather large duration). Also, this step might reveal another issue: lots of queries issued by that "update" bundled into a single transaction that might take a very long time (in spite of each being very quick). This later is a client side issue, not a DB one.
You might run the query again in SQL Server Management Studio by also including Actual Execution Plan to see what is causing the rather long execution

If you still do not figure out what is the cause, edit your question and provide what you have found out: the query, the execution plan, duration etc.


Grouping categorical values in a sequence I have a table with categorical values in a sequence, as such:

CREATE TABLE cat (name, v1, v2)
AS VALUES
    ('John', 1::int, 3::int),
    ('John', 3, 4),
    ('John', 4, 9),
    ('Mike', 9, 11),
    ('Mike', 11, 17),
    ('John', 17, 24),
    ('John', 24, 25),
    ('Dave', 25, 29);

And I want to group them by the name column, getting the minimum value_1 and maximum value_2, however group them as the names appear in unbroken sequence, as such:

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 9       |
| Mike      | 9       | 17      |
| John      | 17      | 25      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

However, doing normal GROUP BY selection returns all the entries for a particular name together, regardless of whether they appear together or not:

SELECT name, MIN(value_1), MAX(value_2)
FROM table
GROUP BY name;

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 25      |
| Mike      | 9       | 17      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

Now, I could probably do this with plpgsql, using traditional programming logic with variables, loops and such, but I was wondering if there is a more SQL-flavoured approach to this.

Software: PostgreSQL 9.6.3

Using value_1 to order the row, you can make groups every time name changes.

Then use this groups to get max and min values.

select name, min(v1) v1, max(v2) v2
from (select name, v1, v2,
             sum(rst) over (order by v1) grp
      from (select name, v1, v2,
                   case when coalesce(lag(name) over (order by v1), '') <> name then 1 end rst
            from cat
            ) y
      ) z
group by name, grp
order by v1;

name | v1 | v2
:--- | -: | -:
John |  1 |  9
Mike |  9 | 17
John | 17 | 25
Dave | 25 | 29

dbfiddle here


Giving Special Permissions to a Stored Procedure in SQL Server I have a restricted database (that contains confidential data) called database B with only a couple of users with access to it. However, I need some select data from a couple of tables within B to be available to the users who request it. The users requesting the data are not added as database users to database B as they are not allowed to have access. These users, however, have access to database A (they are added as DB users of DB A). Is there a way to give a stored procedure access to the confidential database (B) so that any user running the SP could get the data they are requesting? If so, how? I’ve studied that this is possible, but without mention of how.

NOTE: it is NOT an option to move the needed data from Database B to A through IS package (given real-time access is needed). I’ve thought of using views too (but again, how? If you don’t have access to the base tables, you won’t have access to the data through the view either).

This is fairly easy to accomplish (including handling multiple databases and Dynamic SQL) without any Impersonation (IMPERSONATE permission), cross-database ownership chaining (Server / Instance or Database setting), or TRUSTWORTHY (Database setting). What you need to do, generally speaking, is:

Create a Certificate in Database A
Sign Stored Procedure in Database A using ADD SIGNATURE
Create the same Certificate in Database B
Create a User in Database B based on that Certificate
Assign the new User the permissions needed to access the desired tables

Example:

CLEANUP

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

SETUP

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';

USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.RunReport
AS
SET NOCOUNT ON;

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
GO

GRANT EXECUTE ON dbo.RunReport TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[RunReport]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
                 AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

CREATE TABLE dbo.[RestrictedTable]
(
  [ID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_RestrictedTable] PRIMARY KEY,
  [Other] VARCHAR(50)
);

GRANT SELECT ON [dbo].[RestrictedTable] TO [PermissionsUser];

INSERT INTO dbo.[RestrictedTable] ([Other]) VALUES ('Ta da!');
GO

TEST

-- Quick test to show that [PermissionsUser] cannot be Impersonated:
USE [DatabaseB];
EXECUTE AS USER = 'PermissionsUser';
/*
Msg 15517, Level 16, State 1, Line 123
Cannot execute as the database principal because the principal "PermissionsUser" does not
exist, this type of principal cannot be impersonated, or you do not have permission.
*/

-- Main test:
USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
/*
Msg 916, Level 14, State 1, Line 144
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB"
under the current security context.
*/

EXEC [dbo].[RunReport]; -- SUCCESS!!!
-- 1    Ta da!

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

For more info on Impersonation vs Module Signing you can review this other answer of mine (and the answer linked in it):

SQL Server Impersonation is just NOT working


What is the recommended memory_target and memory_max_target for ORACLE 12 DB running on a dedicated server with 32GB RAM? What is the recommended memory_target and memory_max_target for ORACLE DB running on a dedicated linux server with 32GB RAM?

The server is dedicated to the Oracle 12 database.

Is there any recommendations in such case?

Forget memory_target and memory_max_target. AMM has always been a feature that worked on paper, but failed in practice. Oracle finally admitted it semi-officially as of the release of 12.2:

When I have no information about earlier memory usage, I just go with a 1:4 ratio for PGA:SGA target.

sga_target=20G
pga_aggregate_target=5G

Which implicitly sets:

sga_max_size=20G
pga_aggregate_limit=10G

Then later you can tune if needed, after observing the workload and memory usage.


How to join global temp tables in SQL Server? I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:

SELECT *
INTO ##A

These four tables all have a column with same name 'key' and I want to perform join using this column. I am  using this logic:

SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key
LEFT JOIN ##C as c ON a.key = c.key
LEFT JOIN ##D as d on a.key = d.key

When I execute this code I get the following error:

  Cannot resolve the collation conflict between "Latin1_General_100_BIN"
  and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I have been stuck here and cannot seem to resolve it.

Any help/advice/suggestions much appreciated.

SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key collate database_default
LEFT JOIN ##C as c ON a.key = c.key collate database_default
LEFT JOIN ##D as d on a.key = d.key collate database_default

Should eliminate the problem

But the underlying problem is that your (source) tables have different collations. This should be avoided.

This is a good source for some more information:
Questions about sql server collations


MySQL version appears to be inconsistent? I have recently upgraded mysqld (percona server) from 5.5.60-38.12 to 5.5.62-38.14. This is on Debian, and the upgrade was done like this:

# apt-get update
# apt-get upgrade

I am surprised to find that the version is reported differently internally from what I get on the command line:

db3 root = mysql -V
mysql  Ver 14.14 Distrib 5.5.62-38.14, for debian-linux-gnu (x86_64) using readline 5.1
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.60-38.12-log |
+------------------+
1 row in set (0.00 sec)

Is this normal? Or do I need to do something to fix this issue?

Edit: I've allso checked the installed packages:

db3 root = dpkg -l | grep percona
ii  libperconaserverclient18        5.5.62-rel38.14-1.stretch         amd64        Percona Server database client library
ii  percona-server-client-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database client binaries
ii  percona-server-common-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database common files
ii  percona-server-server-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database server binaries
ii  percona-toolkit                 3.1.0-2.stretch                   amd64        Advanced MySQL and system command-line tools
ii  percona-xtrabackup              2.3.10-1.stretch                  amd64        Open source backup tool for InnoDB and XtraDB

Answering my own question, in case other might benefit.

The explanation, it turns out, was that although the upgrade succeeded, the restart of the daemon had failed silently. It took a kill -9 and systemctl start mysql to sort that out.


Multi-tenant database constraints I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other.

My question is, is there any sort of way that I can use database constraint to make sure the tenant_ids are consistent with their parents.

For example, imagine a table blog_posts that has many comments. I would like to ensure that for any comment, the tenant_id is the same as the tenant_id for the given blog_post_id.

Can this be done in a simple and maintainable way?

This is what I ended up going with:

create table blog_posts (
    id uuid not null,
    tenant_id uuid not null,
    title text not null,
    content text not null,
    primary key (id)
);

create unique index blog_posts_tenant_id_id_idx on blog_posts (tenant_id, id);

create table comments (
    id uuid not null,
    tenant_id uuid not null,
    blog_post_id uuid not null references blog_posts (id),
    content text not null,
    primary key (id)
);

alter table comments add foreign key (tenant_id, blog_post_id)
    references blog_posts (tenant_id, id)
    on update cascade;


How I can copy from local file to remote DB in PostgreSQL? I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?

I am using the following command

\COPY test(user_id, product_id, value)
      FROM '/Users/testuser/test.tsv' WITH DELIMITER '\t' CSV HEADER;

but this searches the file on the remote DB while I need to do that on my local PC.

There are two possibilities here.

If you are using psql, then \copy is the correct way to do things as you mention.
If you are using a client program you have authored, then you need to

COPY FROM STDIN.....

And then feed the file in (see the documentation on the COPY command.

The COPY command is a slightly strange beast.  It is the one command PostgreSQL currently supports from Quel and so it is both non-standard, and based on something of a different paradigm than the rest of the commands.  For this reason it's important to read the docs relatively carefully on this command.


Im trying to group two queries by year so that it will show the amount of tours done(by count of tour id) in that year SELECT [school tours done showing year].[CountOfTour ID],
       [Visitor Tours Done Showing Year].[CountOfTour ID],
       Year([Date]) AS [Year]
FROM   [school tours done showing year], [Visitor Tours Done Showing Year];

Assuming [Date] contains "year" values (i.e. 2018, 2017, 2016, ..)

;With SchoolTours AS
(
SELECT [Date] AS YEAR, COUNT(ID) AS [SchoolCountOfTour ID]
FROM [school tours done showing year]
GROUP BY [Date]
)
, VisitorTours AS
(
SELECT [Date] AS YEAR, COUNT(ID) AS [VisitorCountOfTour ID]
FROM [Visitor Tours Done Showing Year]
GROUP BY [Date]
)
SELECT t1.Year
, t1.[SchoolCountOfTour ID]
, t2.[VisitorCountOfTour ID]
FROM SchoolTours t1
LEFT JOIN VisitorTours t2 ON t2.Year = t1.Year

Plenty of other ways to do it but I'm partial to CTEs

B


How can I wipe all databases and users from a SQL Server database? Okay, here is the story.

I have a Windows 7 Ultimate box with a SQL Server 2008 R2 Standard installation that I inherited from a person who left the company long before I started at my company. No documentation about the users has been left.

I don't need any of users other than having the ability to set a sa password and don't need any of the databases currently on the box.

The only reason I can't remove SQL Server from the box is because I don't have the original media in this office and would have trouble getting the media from the other office. I need the standard install because one of the databases I want to load is 70GB in size (they stored images in tables).

Is there a way to just start fresh?

Assuming you can get access to the box (if you need to recover access, see the link Kin already pointed out), you need to:

Drop all user databases. Please run the PRINT first and make sure you're not dropping anything you may regret. You may want to take backups of all databases first just in case.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  DROP DATABASE ' + QUOTENAME(name)
  + N';'
FROM sys.databases
WHERE name NOT IN (N'master',N'tempdb',N'model',N'msdb');

PRINT @sql;
-- EXEC master.sys.sp_executesql @sql;

Drop all non-system logins. Again, please run the PRINT command first, and look the list over very carefully. I have a filter there to prevent dropping yourself if you are logged in via Windows Authentication, but you may want to add additional specific filters in the event you are logged in as sa or similar and don't want to drop your Windows login.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql+= N'
  DROP LOGIN ' + QUOTENAME(name) + ';'
FROM sys.server_principals
WHERE name <> N'sa'
AND name NOT LIKE N'##%'
AND name NOT LIKE N'NT [AS]%'
AND [type] IN ('R', 'S', 'U', 'G')
AND principal_id > 256
AND name <> SUSER_SNAME(); -- don't drop yourself!

PRINT @sql;
-- EXEC master.sys.sp_executesql @sql;

Correct the default database for any logins you keep. You may find you can lock yourself out if your default database no longer exists. So, set your login to default to master or tempdb:

ALTER LOGIN [DOMAIN\Login] WITH DEFAULT_DATABASE = tempdb;

Correct any jobs/maintenance plans that reference old databases. You'll need to look through the list manually and identify anything that touches these databases, otherwise they'll start failing. You can get a starting list with this, but this won't necessarily be a complete list - it won't tell you if the job uses ad hoc SQL that references a database with three-part naming or calls a stored procedure that does so. Thankfully you won't have to worry about synonyms (unless some evil person put synonyms in system databases).

SELECT j.name
FROM msdb.dbo.sysjobs AS j
WHERE EXISTS
(
  SELECT 1
    FROM msdb.dbo.sysjobsteps AS js
    WHERE js.job_id = j.job_id
    AND js.database_name NOT IN (N'msdb',N'master',N'tempdb',N'model')
);

In fact, you may want to run this step first, so that you can clean up all the jobs before they start failing. I'd also validate that all the jobs that remain still have valid owners. You'll have to correct any of these jobs after you delete any logins:

SELECT j.name FROM msdb.dbo.sysjobs AS j
WHERE NOT EXISTS
(
  SELECT 1
    FROM sys.server_principals AS p
    WHERE p.sid = j.owner_sid
);


SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory Server:

HP ProLiant DL380 G5
2 proc -2x QuadCore Intel Xeon E5450, 3000 MHz
Memory- 12288 Мб (DDR2-667 Fully Buffered ECC DDR2 SDRAM)

Settings:

/3GB in the boot.ini.
/PAE in the boot.ini.
Enabled AWE in SQL Server.
Enabled Lock Pages in Memory Option for users SYSTEM and Local Service.
Set Min/Max Memory to : 6 Gb/8 Gb

SQL Server: Memory Manager_Total Server Memory (KB) = 100%

I don't know how to make SQL capture free memory allocated him.

AWE memory can only be used for data cache. Things like procedure cache, workers stacks, execution memory grants, SQLCRL and so on can only use the process 'real' memory space (ie. the 3Gb). So even if you did set up everything correctly, it does not mean that 1) SQL Server will actually use all the 6Gb and 2) you won't run into out-of-memory issues despite plenty of AWE memory being available.

32bit is dead. Just move to a 64 bit environment and call it a day.


Why is it not recommended to use RAID 5 for a log file? While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID 5 disk arrays incur twice the number of disk I/Os compared to RAID 1 or RAID 10.

I know that RAID 5 differentiate from other RAIDs with its parity feature. It means that if some of the drives fails, then it is possible to recover lost data from the other drives. I want to understand why it is not recommended to use RAID 5 for a transaction log file. Explanation in the book was not enough for me to get it. Maybe someone could explain it to me or provide a good article.

Transaction log writes, when they occur, are synchronous operations, that is, the activity that has caused a log write must wait until log I/O completes before continuing to do whatever it is doing. As a result log writes are very sensitive to the write throughput of the underlying storage.

As you have mentioned, every write to a RAID-5 device has an overhead1 of calculating and writing a parity block in addition to the data block(s). However small, this extra work RAID-5 performs on each write operation is the reason behind the recommendation to not use RAID-5 for log storage.

1 - More details in this Q&A


Count and Group data based on consecutive occurrence Tricky for me to define the problem, maybe you understand it through my dummy data.

I have this data:

PK, TaskPK
1, 1
2, 1
3, 2
4, 2
5, 5
6, 1
7, 1
8, 2
9, 2
10, 5
11, 5

Now I have to count TaskPK so, I make this query

Select PK, TaskPK, Count(*)
From tbl
Group by TaskPK

It brought this result

TaskPK, Count(*)
1, 4
2, 4
5, 3

But I want slight different result

Like this

TaskPK, Count(*)
1, 2
2, 2
5, 1
1, 2
2, 2
5, 2

The above result based on consecutive data occurrence, as TaskPK start with 1 (it group together), then it change it 2 (it group together), then 5 (it group together) taskPK. But as TaskPK again shifts to  1, then it should group seperatly not link with previous occurrence of 1, this task seperately count.

Is this possible?

Problems of this kind are a variation of what is normally referred to as Gaps and Islands. You can see a good overview at The SQL of Gaps and Islands in Sequences

Step by step approach

This can be done with several intermediate steps. First, let's make a (derived) table, that we will store as t1, finding out at which points you start a new series of TaskPK values.

CREATE TEMPORARY TABLE t1 AS
SELECT
    PK, TaskPK,
    case when
        (SELECT TaskPK
         FROM tbl t_prv
         WHERE t_prv.PK < tbl.PK
         ORDER BY PK DESC
         LIMIT 1
        ) <=> TaskPK
    then 0
    else 1
    end AS start_of_series
FROM
    tbl ;

t1 contains:

PK | TaskPK | start_of_series
-: | -----: | --------------:
 1 |      1 |               1
 2 |      1 |               0
 3 |      2 |               1
 4 |      2 |               0
 5 |      5 |               1
 6 |      1 |               1
 7 |      1 |               0
 8 |      2 |               1
 9 |      2 |               0
10 |      5 |               1
11 |      5 |               0

NOTE: the <=> operator in MySQL is equivalent to NOT DISTINCT FROM in standard SQL. It allows for equality comparison with NULL values as well as NON-NULL ones.

Now that we know where every series starts, we can give each PK a series_number by adding together all the current and previous values of the column start_of_series (that's a cumulative sum). Without window functions, this must be done with an (awful, inefficient) subquery.

We store the result in a table t2:

CREATE TEMPORARY TABLE t2 AS
SELECT
    PK, TaskPK,
    (SELECT
        sum(start_of_series)
    FROM
        t1
    WHERE
        t1.PK <= tbl.PK
    ) AS series_number
FROM
    tbl
;

The content of t2 is:

PK | TaskPK | series_number
-: | -----: | ------------:
 1 |      1 |             1
 2 |      1 |             1
 3 |      2 |             2
 4 |      2 |             2
 5 |      5 |             3
 6 |      1 |             4
 7 |      1 |             4
 8 |      2 |             5
 9 |      2 |             5
10 |      5 |             6
11 |      5 |             6

At this point, what you want to do is just GROUP BY series_number, and take the value of TaskPK (just min(TaskPK) will do this job), together with a count(*):

SELECT
    min(TaskPK) AS TaskPK, count(*) AS count
FROM
    t2
GROUP BY
    series_number
ORDER BY
    series_number

This is the result you actually wanted:

TaskPK | count
-----: | ----:
     1 |     2
     2 |     2
     5 |     1
     1 |     2
     2 |     2
     5 |     2

Putting everything together

By substituting t2 and t1 by their definitions, you can put everything together in a single query:

SELECT
    min(TaskPK) AS TaskPK, count(*) AS count
FROM
    (SELECT
        PK, TaskPK,
        (SELECT
            sum(start_of_series)
        FROM
            (SELECT
                PK, TaskPK,
                case when
                    (SELECT TaskPK FROM tbl t_prv WHERE t_prv.PK < tbl.PK ORDER BY PK DESC LIMIT 1) <=> TaskPK
                then 0
                else 1
                end AS start_of_series
            FROM
                tbl
            ) AS t1
        WHERE
            t1.PK <= tbl.PK
        ) AS series_number
    FROM
        tbl
    ) AS t2
GROUP BY
    series_number
ORDER BY
    series_number
;

TaskPK | count
-----: | ----:
     1 |     2
     2 |     2
     5 |     1
     1 |     2
     2 |     2
     5 |     2

This query is quite complicated (there are five SELECT within it), and can probably be still simplified some.

You can find the whole code and data at dbfiddle here

This can be very hihgly simplified if you can use MariaDB's WINDOW functions, or MySQL 8.0 Window functions (not yet released for production as of now).


SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa.

The security team decided to change that owner to a SQL login called SQLUSERID. It is DB owner for the databases that maintenance job runs and no server level access.

I was asked to create a SQL Agent proxy SQLJobs_Proxy and created a credential DBAAdmin_Creditional using Domain\sql_service_account which is a sysadmin and a memeber of local admin group. I created this proxy under Operating System(cmdExec) and SQLUSERID as the principal. In the job property I Type is selected Operating System(cmdExec and Run As  SQLjobProxy.

When I execute the job I get the following error:

  Executed as user: Domain\sql_service_account.
  Warning: cannot write logfile:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\Indexrebuild_83722.txt.
  Writing to log files is only allowed to jobs that are owned by sysadmin.
  Please consider writing log to table.  The step failed.

Domain\SQL_Service_Account is sysadmin and local admin so it has full access to the folders and should be able to write.

What can I do to run the jobs successfully with this SQL login that is not a domain user?

I would work on educating your security team about jobs...they are server/instance level objects, not database level objects, so db_owner seems severely limited.

Ola recommends sysadmin for the job owners:

  Which permissions are needed for the SQL Server Maintenance Solution to work?

    If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

    If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

    If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

    DatabaseBackup: sysadmin

    DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

    IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

Regarding the comment about the msdb filling up if you run against a table, when you install his jobs using his big script MaintenanceSolution.sql he installs a CommandLog Cleanup job that will clear out the tables used to log his job steps.


Join table exactly once Rephrased, see original question below.

I have the following tables

Table A (UserId, Age)
Table B (UserId, Age)

For each row in table A I want exactly one row from table B that satisfies the following critera

Do not exist in Table A.
Has the same age as the record in table A.
Has not been assigned to any other row in table A (i.e. the column UserIdFromTableB in the result set should be unique)

Assumptions

There will be enough rows (20x) in table B to find a corresponding match by age.
If this condition fails, NULL should be returned.

Original question

I have a table A (UserId, Age). For each record in table A, I would like to select exactly one records from table B (UserId, Age).

B.UserId must be distinct from A.UserId, but A.Age must be equal to B.Age.

Each record from table B should be joined at most once - each record in table A should be assigned a (random) record from table B, but any given row in table B should only be assigned to a single row in table A (or should be left out of the results altogether).

Is there a set-based solution for this? It will of course be possible to do it in a procedure, but as the number of rows in table A can be quite large.

Edit: Table A and table B are different tables and do not have the same number of rows. If there are multiple rows in B each satisfying the join criteria (Age), a random row should be chosen.

Looks like we're not getting any more information, so I'll go ahead and post a partial answer that might or might not be helpful to you. For future questions, including information about data types, indexes, how many rows the tables have ("quite large" means different things to different people), and your expected output can be very helpful for those of us trying to answer your question.

This answer contains a "set-based solution" that assumes that you always have at least twice as many rows in Table B compared to Table A for every age. This is well within your 20X estimate, but I do not meet the requirement of returning NULL if there aren't enough rows. With that said, that case will be obvious because the query will return NULLs for the UserId from Table B.

For the data set, I assumed that Table A had 1 million rows spread evenly over 100 different ages and Table B had 20 million rows spread evenly over 100 different ages. There are no duplicate UserIds in either table for the same age, but rows can share an UserIds for different ages in the same table. Neither table has any indexes. Code to create this data set:

DROP TABLE IF EXISTS dbo.TableA;
CREATE TABLE dbo.TableA (UserId BIGINT, Age TINYINT);

INSERT INTO dbo.TableA WITH (TABLOCK)
SELECT 1 + (t.RN % 10000)
, (t.RN - 1) / 10000
FROM
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.TableB;
CREATE TABLE dbo.TableB (UserId BIGINT, Age TINYINT);

INSERT INTO dbo.TableB WITH (TABLOCK)
SELECT 1 + (t.RN % 200000)
, (t.RN - 1) / 200000
FROM
(
    SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t
OPTION (MAXDOP 1);

The algorithm for matching can be expressed in the following way:

Process rows for a single age at a time. Suppose that TableA has m rows for that age and TableB has n rows for that age. As stated before, n >= 2 * m.
Assign random positive integers to the rows from TableA from 1 to m.
Assign random positive integers to the rows from TableB from 1 to n/2. Each number is repeated twice, so the set looks like 1, 1, 2, 2, ... n /2, n/2.
Join the two intermediate result sets on matching age, matching random numbers, and not matching UserIds.
Each UserId from TableA will match to either 1 or 2 UserIds from TableB. This is because the ids are unique within each table and age. The result set will have at most 2 * m rows.
Reduce the UserIds from TableB that match to 2 UserIds from TableB to a single row. Keep an arbitrary UserId from TableB.

My query to implement that is a bit of a mess:

SELECT
  t.Age
, t.UserId UserIdFromTableA
, CAST(RIGHT(magic_column, 20) AS BIGINT) UserIdFromTableB
FROM
(
    SELECT
      ta.Age
    , ta.UserId
    , MAX(
          RIGHT(REPLICATE('0', 10) + CAST(tb.RN AS VARCHAR(10)), 10)
        + RIGHT(REPLICATE('0', 20) + CAST(tb.UserId AS VARCHAR(20)), 20)
    ) magic_column
    FROM
    (
        SELECT
          UserId
        , Age
        , ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID()) RN
        FROM dbo.TableA
    ) ta
    LEFT OUTER JOIN
    (
        SELECT
          UserId
        , Age
        , FLOOR(0.5 + 0.5 * ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID())) RN
        FROM dbo.TableB
    ) tb ON ta.Age = tb.Age AND ta.RN = tb.RN AND ta.UserId <> tb.UserId
    GROUP BY ta.Age
    , ta.UserId
) t
OPTION (MAXDOP 1, HASH JOIN);

There are a few important tricks that I did in that query for performance reasons. The string casting in the aggregate is so that I can take an arbitrary row to resolve ties with a single GROUP BY. Normally I would just take the minimum or maximum UserId but you said that you wanted random results and that would slightly bias the UserIds chosen from TableB.

I mapped rows from TableB in the way I did so that I could have an extra equality condition in the join: ta.RN = tb.RN. Doing the mapping in a simpler way could lead to a join condition like this: tb.RN IN (2 * ta.RN, 2 * ta.RN - 1) which cannot be efficiently checked in a hash or merge join.

The query hint at the bottom is to help the query optimizer get a more efficient plan for my machine. All of the ROW_NUMBER() stuff makes it difficult for the query optimizer to come up with good cardinality estimates and to cost the plan correctly. For example, it has no idea that we've arranged the random sequences so that at most two rows match for every row in TableA. You may not need that query hint or a different query hint may be better for your server and data.

On my machine, the query returns 1000000 rows as expected in about 40 seconds. The query plan has a horrifically high estimated cost but that doesn't reflect reality. Here's what the plan looks like:

Here's a sample of the query's results:

Again, I need to stress that this query will not return accurate results unless you have at least twice as many rows in Table B compared to Table A for every different age.


Is array serialization always bad idea? I'm making form-generating app in php. I have Widget table with type_id (html input) field, and I would like to add options field, like width/height, step, range, etc. Different widgets do have different options though.

I can think of two options:

Use separate meta table for every input type (20+)
Use serialized array as a meta

Option 1 seem more "legal", but option 2 would be easier to implement and maintain(?).

Would performance be considerably better with option 1?

EDIT: This widget table will be accessed only through widget_id and used to render html. Changes are possible, but they shouldn't happen that often.

If you actually plan to query these attributes separately (such as "Find all form elements with height = 20 and colour = #FF3377") then it is probably better to store them in some kind of queryable attributes table. If you are only plan to use a set of attributes as a whole, and never query inside it, never update/insert/delete individual attributes for specific form elements, then serializing the array and storing it as a BLOB (or something) might not be so bad, as it would keep your design simpler.


Making dynamic queries for linked server in SQL Server I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows.

USE [MYDB]
GO

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX);

SET @company = 'mycompany.com';
SET @query = N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12,
   ''SELECT id from company where name = @comp'')';
EXECUTE sp_executesql @company_query, N'@comp nvarchar(50), @csid
nvarchar(MAX) OUTPUT', @comp = @company,@csid = @id OUTPUT

In the above script, I want to pass the value for @comp dynamically. For that I tried setting input and output variable while executing SQL with sp_executesql.

I am getting the following error

  Syntax error in SQL statement. Syntax error line 1 at or after token .[10179].

  Msg 7321, Level 16, State 2, Line 4
  An error occurred while preparing the query "SELECT id from company where name = @comp" for execution against OLE DB provider "MSDASQL" for linked server "LINKSERVER12".

The error is happening at the dynamic query

N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12,
   ''SELECT id from company where name = @comp'')'

I tried replacing @comp in the SQL query with ''@comp'', ''''@comp'''' with no luck. Any help is greatly appreciated.

You can do it this way:

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX) = N'SELECT @id = id from company where name = @comp';

exec [LINKSERVER12].[MYDB].sys.sp_executesql @query, N'@comp nvarchar(50), @id nvarchar(MAX) OUTPUT', @comp = @company,@id = @id OUTPUT;

select @id;


Mysql 8.0 and read-only not working as expected I have a new Master/Slave config running. Everything is happily replication. I have added a secondary user whose role will be to manage the databases and tables.

Currently the following privileges are set:

         Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: N
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
       account_locked: N
      Create_role_priv: N
        Drop_role_priv: N

Privileges have been flushed and the slave server was started read-only.

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

However I can still perform write operations on the slave with the user.

mysql> create database foo;
Query OK, 1 row affected (0.01 sec)

mysql> use foo;

Database changed
mysql> create table `foobar` ( `x` int );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into foobar values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into foobar values (2);
Query OK, 1 row affected (0.05 sec)

mysql> select * from foobar;
+------+
| x    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> drop database foo;
Query OK, 1 row affected (0.06 sec)

It's my understanding that without the Repl and Super priv's I should not be able to do the above, or am I doing something else wrong ?

Essentially I want the scripts and daemons using this login to never break stuff if I flip the master and slave around.

Thanks.

@Rossi, you don't have to update your "CONNECTION_ADMIN" privilege to make your slave read only for SUPER users like you.

you can set super_read_only (available from MySQL 5.6.21) and your server will not allow any updates even for the users that have SUPER privilege.

super_read_only Documentation


Suggestion for Large SQL Server Database Design We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very few UPDATEs or DELETEs on the data. Its a data archival system where we insert historic records on a daily basis. We will generate different sort of reports on this historic record on user request so we've some concerns and require technical input and advice.

What is the best way to manage this kind of archival tables and database?

Here is my opinion:

If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though.
Partition the table based on a broad category like year.
Put these partitions on different filegroups.


SQL Server on real server For a project, I am using Microsoft SQL Server Management Studio to run my database. I connect to this through localhost.
I have written an application using Qt which use this database, but now I am distribute the application. How do I change my database on localhost so that I can connect to it from other computers and have multiple connection to the database at the same time.

The first thing you want to check is if Remote access is enabled on your SQL Server instance.
If not please configure by following the steps here.


Value of prepared statement is listed as error in query string I have made a Wordpress plugin that writes some data to a proprietary table.

The query as shown in the log is:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)

If I take this line and enter it manually via adminer in the database, replacing the first %s with 'test' and the second %s with 1:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES ('test', 1)

The this is stored in the database without a problem. There is also another table that is written to from the plugin which works fine.

The php that is responsible for storing the data is as follows:

function insert_artice_read($uuid, $article_id) {
error_log('$uuid:'.$uuid, 0);
error_log('$article_id:'.$article_id, 0);

    $query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)";
error_log('$query', 0);
error_log($query, 0);
    $query = $this->wpdb->prepare($uuid, $article_id);
    return $this->wpdb->query($query);
  }

The parameter $this->wpdb is defined as

$this->wpdb = $wpdb;

The logging that is shown is this:

dcr-wordpress | [Tue Jun 25 10:45:03.363771 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $uuid:ABCDEF01-2345-6789-ABCD-9876543210AA
dcr-wordpress | [Tue Jun 25 10:45:03.363776 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $article_id:1
dcr-wordpress | [Tue Jun 25 10:45:03.363781 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $query
dcr-wordpress | [Tue Jun 25 10:45:03.363785 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)
dcr-wordpress | [Tue Jun 25 10:45:03.364844 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ABCDEF01-2345-6789-ABCD-9876543210AA' at line 1 bij query ABCDEF01-2345-6789-ABCD-9876543210AA gemaakt door require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, Prop\\Inc\\Core\\RestController->article_retrieved, Prop\\Inc\\Common\\Repository->insert_artice_read
dcr-wordpress | 192.168.224.1 - - [25/Jun/2019:10:45:03 +0000] "POST /wp/wp-json/prop/v1/berichten-devices HTTP/1.1" 200 802 "-" "Mozilla/5.0 (iPhone; CPU iPhone OS 12_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148"

From this logging it is obvious that there is something wrong with the sql syntax, but this line seems weird to me:

for the right syntax to use near 'ABCDEF01-2345-6789-ABCD-9876543210AA'

This is data from the prepared statement parameters, I would expect to see  something like

for the right syntax to use near %s

I tried to use %i instead of %s for the second parameter, just be sure, but there was no difference.

This is the table definition:

CREATE TABLE IF NOT EXISTS berichten_devices (
  device_UUID     VARCHAR(255) NOT NULL,
  article_id      int(10)      NOT NULL,
  date            timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),

  KEY `dev_uuid` (device_UUID)

My question is basically: "What am I doing wrong?"

You likely will need to enclose the parameters in quotation marks if they are strings. Try this:

$query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (\'%s\', \'%s\')";


ORA-24155 - Why am I getting this error when creating a chain rule When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense.

I stop and drop all jobs/chains and then attempt to create one.  Are chain rule names shared with all chains?  Should I use a dynamic name instead of "StartChain".  Only guessing.  Any idea?

-- Stop Job
BEGIN
  sys.dbms_scheduler.stop_job(job_name => l_job_name);
EXCEPTION
  when job_not_running then null; -- dont care, continue
  when job_doesnt_exist then null; -- dont care, continue
END;

-- Drop Job
BEGIN
  sys.dbms_scheduler.drop_job(job_name => l_job_name);
EXCEPTION when job_doesnt_exist then null; -- dont care, continue
END;

-- Drop Chain
BEGIN
  sys.dbms_scheduler.drop_chain(chain_name => l_chain_name);
EXCEPTION when chain_doesnt_exist then null; -- dont care, continue
END;

-- Create chain
sys.dbms_scheduler.create_chain(chain_name          => l_chain_name,
                                rule_set_name       => NULL,
                                comments            => '');

sys.dbms_scheduler.define_chain_rule(chain_name => l_chain_name,
                                     rule_name  => '"StartChain"',
                                     condition  => 'True',
                                     action     => 'START "Step 1 - Build Bio"',
                                     comments   => '');

I never did find a "proper" way to resolve this, but I was able to fix by:

select * from sys.scheduler$_job for update -- manually remove a running job
-- Delete the job from this table

The job kicked off and then the machine was rebooted in the middle of the job.

Later, the job was deleted, but apparently the old ruleset still existed somewhere in the database even though there wasn't a record of the ruleset existing (EG: data was still there, but the meta saying it was there did not exist).  When I deleted the job object from the appropriate table, it gave me a different ruleset when I passed it "null" to the ruleset parameter.  I guess it was somehow pulling the old rule set and the name was being detected as a duplicate.


How to restore a database then upgrade it in a single SQL script? I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a backup. For a new installation we restore the backup then apply our own patches to it. For an upgrade we just apply our own patches to the existing database.

I can get it to restore, and I can get it to patch, but I can't get it to do both from one script because it doesn't like to USE the database if it didn't exist when the script starts.

This is the outline of my script:

USE master

IF NOT EXISTS (SELECT NULL FROM sys.databases WHERE name = 'MyDatabase')
BEGIN
    RESTORE DATABASE MyDatabase ...
END
GO

USE MyDatabase -- The database exists by this point

-- Apply patches ...

The line USE MyDatabase throws an error:

Msg 911, Level 16, State 1, Line 2
Database 'MyDatabase' does not exist. Make sure that the name is entered correctly.

I guess it's parsing the whole script before it executes anything because by the time it gets to the USE the database either already existed or was restored, either way it now does exist.

Is there a way to RESTORE a database then USE it in the same script, or do I just have to separate the restore from the update?

You could use dynamic SQL

    Set @SQL =
   'USE MyDatabase -- The database exists by this point

    -- Apply patches ...'

    EXEC SP_ExecuteSql @SQL

You may need to do a wait to allow the database to restore before executing SQL on it.

WAITFOR DELAY '00:00:02';