Disk Latency vs Throughput I've searched the web for a little while and haven't found an answer I'm confortable with. I am testing a SQL Server on a VM that was presented new disc.

If disk latency is reduced, shouldn't throughput increase?  Is there a direct correlation between disk latency and throughtput?  Seems like if I have faster disk I should see an increase in throughput.

It might help to define terms up-front.

The term latency is used in (at least) two ways regarding I/O. It can mean:

The average time it takes to service an I/O at any given time. That's how Paul Randal is using it in "How to examine IO subsystem latencies from within SQL Server" and it can be measured using (for example) Performance Monitor with counters such as "Avg. Disk sec/Read". I'm going to call this the 'measured' latency, and it is related (inversely) to the queue length at a given time, but is a much more helpful measure because it is easy to relate to actual performance from a user perspective.
The average time a single I/O takes in isolation. That's how it is typically used on spec sheets for HDDs and SSDs like this one from Seagate. I'll call this the 'inherent' latency (it's also sometimes called 'access time').

Throughput can also mean different things in different contexts:

The maximum rate of sequential data transfer, typically measured in MB/sec
The maximum rate of I/O operations typically measured in IOPS
The current rate of data transfer such as that measured using Performance Monitor with counters such as "Disk Bytes/sec"

  Seems like if I have faster disk I should see an increase in throughput.

Because of the way you phrase this, I think you are specifically talking about the quoted MB/sec and 'inherent' latency for disk drives. If that's the case then the answer is "no, there is no direct correlation between disk latency and throughput". Latency for a HDD is affected by the seek time and the spin speed, but it only applies to the first read: subsequent sequential reads do not have the same latency. The mechanics are different for SSDs, but they can still be tuned by the manufacturer for high 'throughput' or high IOPS with issues like block size being important.


Variable inside another variable in SQLCMD mode Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue:

:setvar db_suffix "some_suffix"
:setvar some_db "some_db_$(db_suffix)"
print 'some_db: $(some_db)'

The output of that is:

some_db: some_db_$(db_suffix)

However, what I expected was:

some_db: some_db_some_suffix

Is there a way to have variable interpolation like this?

(Note that T-SQL functions such as CONCAT() won't work since I'll be using the variable as a database name).

Is there a way to have variable interpolation like this?

Kinda sorta, in a way. Just not directly.

You need to keep in mind a few things:

The value that you set a variable to via :setvar is a simple, literal value. You can see this by running just the following two lines:

:setvar var1 $(var2)
PRINT '$(var1)';

which returns:

$(var2)

If SQLCMD / SQLCMD-mode attempted to parse the value portion of :setvar in any way, then it would error on $(var2) not being defined.
Variable substitution is allowed in some other SQLCMD commands.

This means that you can, for example, execute a shell command while passing a SQLCMD variable to that command line:

:setvar db_suffix some_suffix
!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt

The two lines above will create / overwrite a file, C:\TEMP\setvar.txt, with the text of ":setvar other_db [MyDB_$(db_suffix)]" where $(db_suffix) is replaced with its value of "some_suffix".
SQLCMD commands in a file / script that is imported via the :r command are processed in a second-pass, allowing for variables set in those external files to be reflected in the main script:

:setvar db_suffix some_suffix
!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin: $(other_db)';

Returns:

somethin_somethin: [MyDB_some_suffix]

SQLCMD commands are interpreted per-each batch !! So if you want to change the concatenated value, then you need to separate each creating and reading of the temp file, else the value you get will be the last one to be set since all of the !! and :r commands will be processed before the variable values are substituted in, and then the batch can be submitted to SQL Server to process the T-SQL. For example:

:setvar db_suffix some_suffixes

!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin: $(other_db)';

--GO

!! echo :setvar other_db [NotMyDB_$(db_suffix)22] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin2: $(other_db)'

Will return:

somethin_somethin: [NotMyDB_some_suffixes22]
somethin_somethin2: [NotMyDB_some_suffixes22]

BUT, uncomment the --GO and you will get the following:

somethin_somethin: [MyDB_some_suffixes]
somethin_somethin2: [NotMyDB_some_suffixes22]


SQL "select" query for getting 0 instead of null? In the database I have some value as null . So while fetching these null value from database it should be treated as 0 not as null.
Note:- I don't want to be it done by code but from SQL query.

You can use

SELECT IFNULL(ColumnName,0) FROM TableName

or

SELECT COALESCE(ColumnName,0) FROM TableName


Faster query with pattern-matching on multiple text fields I have one Postgres table with more than 20M tuples:

first_name | last_name | email
-------------------------------------------
bat        | man       | batman@wayne.com
arya       | vidal     | foo@email.com
max        | joe       | bar@email.com

To filter the records I'm using:

SELECT *
  FROM people
WHERE (first_name || '' || last_name) ILIKE '%bat%man%' OR
    first_name ILIKE '%bat%man%'  OR
    last_name ILIKE '%bat%man%'   OR
    email ILIKE '%bat%man%'
    LIMIT 25 OFFSET 0

Even with indexes the search is taking almost one minute to return results.
There are indexes for (first_name || '' || last_name), first_name, last_name and email.

What can I do to improve the performance of this query?

For your kind of pattern matching you best use a trigram index. Read this first:

How is LIKE implemented?

I assume there's a typo in your expression (first_name || '' || last_name), which makes no sense with an empty string, and you really want (first_name || ' ' || last_name) - with a space character.

Assuming that either column can be NULL, you would need NULL-safe concatenation, the simple solution is concat_ws():

Combine two columns and add into one new column

But that function is not IMMUTABLE (explanation in the linked answer), so you cannot use it directly in an index expression. You could use an IMMUTABLE function wrapper:

CREATE OR REPLACE FUNCTION f_immutable_concat_ws(s text, t1 text, t2 text)
  RETURNS text AS
$func$
SELECT concat_ws(s, t1, t2)
$func$ LANGUAGE sql IMMUTABLE;

The wrapper can be IMMUTABLE because it only takes text parameters.
Either way, this is more verbose but has less internal overhead and is considerably faster:

CREATE OR REPLACE FUNCTION f_immutable_concat_ws1(s text, t1 text, t2 text)
  RETURNS text AS
$func$
SELECT CASE
         WHEN t1 IS NULL THEN t2
         WHEN t2 IS NULL THEN t1
         ELSE t1 || s || t2
       END
$func$ LANGUAGE sql IMMUTABLE;

Or, with hard-coded space character:

CREATE OR REPLACE FUNCTION f_concat_space(t1 text, t2 text)
  RETURNS text AS
$func$
SELECT CASE
         WHEN t1 IS NULL THEN t2
         WHEN t2 IS NULL THEN t1
         ELSE t1 || ' ' || t2
       END
$func$ LANGUAGE sql IMMUTABLE;

Basing the index on this function, I suggest:

CREATE INDEX people_special_gin_trgm_idx ON people
USING gin (f_concat_space(first_name, last_name) gin_trgm_ops, email gin_trgm_ops);

I added email as second index column for several considerations.

Creating the index will take a while for 20M rows, best not during top load, or maybe use CREATE INDEX CONCURRENTLY .... A GIN index is considerably bigger than a plain btree index and also more expensive to maintain. Be sure to run the latest version of Postgres, there have been major improvements for GIN indexes in recent versions.

Then your slightly adapted and simplified query should be fast and correct:

SELECT *
FROM   people
WHERE  f_concat_space(first_name, last_name) ILIKE '%bat%man%' OR
       email ILIKE '%bat%man%'
LIMIT  25;

You only need the one index for this query.

Basics for pattern matching:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
How to create an index to speed up an aggregate LIKE query on an expression?


Configure Always On to use secondary site as DR I have two SQL Server 2017 Standard Edition clusters, each in a different datacenter, only a pair of servers each.

I was able to configure and test the Windows Cluster, Quorum for it, AG, including failing over from primary to secondary, and created one listener for each.

How do you think I can replicate to second DC?

I can't upgrade to SQL Server Enterprise to use Distributed AG.

Standard Edition availability groups are limited to one replica, which is clearly stated in  the documentation at Basic Always On availability groups for a single database.  So if you need to maintain a another copy, you'll need to use a different option.

Some possibilities are:

Log shipping
Replicate the entire server at the virtual server's host layer with something like Zerto
Replicate the storage at the storage layer
Replicate the database files with a third party product that performs host-level file replication (e.g., Carbonite Double-Take).


Why do I keep getting ORA-01033: ORACLE initialization or shutdown in progress when my database is open? (12c) I'm running Oracle on a Windows server (maybe that is why it is so buggy?). It was working fine for a bit, but lately I've been having an issue.  I keep getting the ORA-01033: ORACLE initialization or shutdown in progress error.

If go into the server and run SQLPlus and check the v$instance table it says my instance is open.  I'm able to switch to my pluggable database.  v$database shows  that my database is read/write.

I have a linked server set up from my MS SQL Server and it will work for awhile and then return ORA-01033.  So, maybe it's just SQL Server I say and try SQL Developer.  I get the same error!  Just for kicks I create a new connection on SQL Developer, and it works.  For a little while.  Then I get this:

I only have ~30 GB of freespace on the drive where Oracle is loaded.  Could that be causing the issue?  If I reboot the server it works.  For a little while.  Basically, it seems to work until I start hammering on it and pulling a lot of data out.

I can't tell you why this happens, but I get such errors regularly with the pluggable database. What I do as a quick fix is I connect using sqlplus "/ as sysdba", then I run the following code for that specific database:

alter pluggable database orclpdb open;

Or the following for all databases:

alter pluggable database all open;

And I stop getting the errors for a while... until the next time it happens. :)
At least I have a quick procedure as a fix.


Oracle 9i import error IMP-00017: following statement failed with ORACLE error 3113 I try to import a schema in my database 9i of copy which is a clone of the original from where I made the export 9i also and when trying to import the schema after many hours gives the following error:

  IMP-00017: following statement failed with ORACLE error 3113:
   "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C20537'; SREC.MAXVA"
   "L := 'C23524'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STA"
   "TS.NUMARRAY(454,455,....

  IMP-00003: ORACLE error 3113 encountered
  ORA-03113: end-of-file on communication channel
  IMP-00000: Import terminated unsuccessfully

The exp command is:

exp system/nnnnn CONSISTENT=y OWNER=myschema FILE=/path/backup_schema.dmp log=/path/backup_schema.log BUFFER=30720

The imp command is:

imp system/nnnn FROMUSER=myschema TOUSER=myschema FILE=/ path/backup_schema.dmp LOG=/path/backup_schema.log BUFFER = 536870912

I have searched and can not find a solution to this problem.

ORA-03113 is a generic error encountered when your database server process was terminated with a critical error. You can find more details in the database alert log. I guess you will see an ORA-07445 error there.

As we do not know these details, all I see is that your error is related to importing statistics. I would try to run this import without importing statistics by specifying STATISTICS=none.

STATISTICS

  NONE

  Do not import or recalculate the database optimizer statistics.

Then after the import you can gather statistics as needed.


Does latin1 have performance benefits over utf8? I have a table in utf8 with > 80M records and one of the columns (char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) can contain just latin symbols ([a-zA-Z0-9]). Does it have the sense to convert this column into latin1?

MySQL doc says:

  To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible character length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.

I made a test - created 2 tables with the same 50M records:

CREATE TABLE `t_utf8` (
  `c_1` char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `index_t_utf8_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

CREATE TABLE `t_lat` (
  `c_1` char(6) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  UNIQUE KEY `index_t_lat_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

but MySQL says that they have almost the same size:

           Name: t_lat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 57557700
 Avg_row_length: 30
    Data_length: 1741668352
Max_data_length: 0
   Index_length: 0
      Data_free: 2097152
 Auto_increment: NULL
      Collation: utf8_general_ci
 Create_options: row_format=COMPACT

           Name: t_utf8
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 57554528
 Avg_row_length: 31
    Data_length: 1810874368
Max_data_length: 0
   Index_length: 0
      Data_free: 3145728
 Auto_increment: NULL
      Collation: utf8_general_ci
 Create_options: row_format=COMPACT

Why does it so?

MySQL 5.7
InnoDB

P.S: I made the same test with MyISAM and got expected benefit: table with latin1 - 383Mb, utf8 - 1Gb. But why it does not work for InnoDB?

Short answer:  Don't bother.

Long answer:

https://bugs.mysql.com/bug.php?id=84440 -- you may or may not save space.
InnoDB is inherently bigger than MyISAM.
Comparing characters in utf8 is slightly slower than in latin1.  But you will probably not notice.
The tiny difference between 1741668352 abd 1810874368 is probably due to the random nature of how you build one table from the other.  Or the phase of the moon, not something significant.
The 30 vs 31 comes from how InnoDB estimates things.

Do not use CHAR except for truly fixed-length strings.  Almost always they are ascii, such as country_code, postal_code, UUID, hex, md5, etc.

Get in the habit of explicit saying ascii or utf8mb4 when you create the column/table unless you have an unusual case where you need something else.


What if the lowest granularity level can legitimately be null? I have a normalized database that I'm going to be using as a source for a dimensional data store; one of the facts I'm collecting is Orders, the normalized schema looks roughly like this:

However when a new order is created in the source system, it doesn't have sizing information and so, there is no record for it in the OrderDetailUnits table.

I'm planning to make an Orders source table that flattens these tables into a single, denormalized table, but then I won't have a lowest-level record for quite a lot of the latest order data... which is expected as far as business is concerned.

How should I structure my Orders facts? Making separate "AllOrders" and "SizedOrders" fact tables / cubes would be annoying for the business users.

I agree.  Your business users would not like "AllOrders" and "SizedOrders" fact tables.

I suggest creating a synthetic row in OrderDetailUnits for each OrderDetail where you have entries which are legitimately null.  I recommend populating *NotApplicable* or something like that for each dimension which is in the OrderDetailUnits.

While not exactly parallel with what you're doing, I suggest reading up on Late Arrriving Dimensions to get some ideas.


Permission denied on function after schema copy I had one schema 'public' and simple function in the schema named 'isvalidoption'.

What was done:

backup schema using pg_dump;
rename schema to 'development';
create new schema 'public';
restore it from backup using pg_restore.

The problem:

when I select from any table omitting schema prefix like 'SELECT *
FROM mytable;' it's OK
when I run function with schema prefix like 'SELECT public.isvalidoption();' or 'SELECT development.isvalidoption()' it's OK;
when I run function omitting schema prefix like 'SELECT isvalidoption();' I get ERROR: permission denied for function isvalidoption

User has USAGE on both schemas and EXECUTE on function in both schemas also he is owner of the function in both schemas.

PostgreSQL version is 9.6.1.

'SELECT current_user;' shows correct user.

'SHOW search_path;' shows '"$user", public'.

Any ideas?

After hours of reading of documentation I found that PosgreSQL first searches pg_catalog schema even before all schemas listed in search_path.

So checked pg_catalog catalog and I don't know why but my function was defined there and user postgres was the owner of the function.

I just deleted my function from pg_catalog and it's OK and works perfect as expected.

I still don't know how the function got there in pg_catalog.


Force update statements to take page locks in the same order? I have two update statements on the same table that take page locks in different orders. This leads to deadlocks.

Process A owns an update lock on page 1, and requests an update lock on
page 2.
Process B owns an update lock on page 2, and wants an update
lock on page 1.

How can I force both statements to take page locks in the same order?

How can I force both statements to take page locks in the same order?

You can't.  Instead force them both to take a table lock with a TABLOCKX hint, or use an Application Lock to serialize the sessions.


Modifying a part (one procedure) in oracle database package Is it possible to modify an individual procedure inside an Oracle DB Package? instead of recreating (modification is not done using a SQL editor, so cannot modify a place and easily recompile the package) the package again every time we need to do a minor change?

Thanks.

Don't. Keep all your package sources in a source control system. That way you can go back to a previous version if you need to.


Alternatives to running query for rarely changed data everytime on large table I have an internal web application running and everytime a user goes to the "search" view it queries three different tables in the db to generate values for three dropdowns in the view.

It's basically running a

SELECT DISTINCT (PortName)
FROM Ports
ORDER BY PortName ASC

But the table contains ~10'000'000 rows and is under quite heavy load which means that from time to time the loading time for the page (due to loading the dropdowns with data) can be upwards of 10-15 seconds.

So, is there a better way to do this, for example running some script at certain intervals and creating a table/view/whatever at a different location so as to offload querying the big table just to have 80 rows returned from the 10'000'000 in the main table?

I am assuming from the DISTINCT that PortNames are duplicated in your table and that there are not 10 million different portnames being returned.

The minimal effort solution is to just place an index on that column:

CREATE INDEX IX_Ports_PortName ON Ports(PortName);

Of course there is still some DB load with this and storage overhead, so you may want a more sophisticated solution such as Caching, which Aaron Bertrand covers quite well in his answer.

You could also employ more Normalization: If portnames are duplicated and knowing them distinctly is important, then you could make a [PortNames] table, and use a PortNameID in the [Ports] table.  That way you could just scan the [PortNames] table which would presumably be much smaller and faster. Of course that may have additional costs and considerations of its own.


how Postgres works internally I would like to learn how postgres works i.e how queries are handled internally,the read and write performance ? I don know where to start,help me with refrences

Bruce Momjian has an update of his old but useful presentation here:

http://momjian.us/main/writings/pgsql/internalpics.pdf

and there's Tom Lane's classic "tour of PostgreSQL internals" as mentioned by Neil, and the back-end flowchart that illustrates the high level overview of the execution path.

Then take a look at the further resources here:

http://wiki.postgresql.org/wiki/Developer_and_Contributor_Resources
http://www.postgresql.org/developer/

After reading the useful links from Neil and those mentioned above, your next port of call is:

https://github.com/postgres/postgres

i.e. the best reference is the source code. The PostgreSQL sources have some pretty detailed comments on data structures and functions, but you'll still land up wanting to attach a debugger and step through the code to see what it's doing sometimes. Use the tools provided, like the pprint function, to examine the state of the server.


Comparing two time fields in database with 2 given time fields I am trying to compare 2 given time fields with two time fields, StartDateTime and EndDateTime respectively.

From my Resources, I have 5 items of different resource types and I have to do a check whether my StartDateTime and EndDateTime does not fall between the values in my database – If so, the Resource Item is available to be used.

From the sample data above, I wanted to do a query to select ALL resources of the selected resource type, and not show the resource items that are being used:

SELECT a.ResourceID
FROM Resources AS a
LEFT OUTER JOIN Bookings AS b
ON a.ResourceID = b.ResourceID
AND b.Status IS NULL
WHERE a.ResourceTypeID = (SELECT c.ResourceTypeID FROM ResourceType AS c INNER JOIN Resources AS d ON c.ResourceTypeID = d.ResourceTypeID WHERE c.ResourceType = 'Coffee Machines' LIMIT 1)
AND NOT a.ResourceID IN (SELECT ResourceID FROM Bookings WHERE DATE(StartDateTime) = '2017-07-15' AND Status IS NULL AND ((TIME('11:00:00') >= TIME(StartDateTime) OR TIME('11:00:00') <= TIME(EndDateTime)) AND (TIME('12:00:00') >= TIME(StartDateTime) OR TIME('12:00:00') <= TIME(EndDateTime))))

The subquery in line 7 is to query for resourceIDs that are being used, and with the AND NOT Clause, the query will only show the ones that are available. But the subquery logic is wrong... I am getting this result:

My given time fields do not fall between the time fields in the database, yet CM-01 and CM-03 is not shown. Can anyone please help me solve this logic?

Actually, I think you're wrong - there are conflicts for CM-01 and CM-03, based on the query and the data shown.

Your data shows bookings for:

CM-01 on 2017-07-15 starting at 10:30 and ending at 11:00
CM-03 on 2017-07-15 starting at 10:35 and ending at 11:05

(No AM/PM shown, so assuming all times are military (00:00 to 23:59).

You're checking if resources are free between 11:00 and 12:00 on 2017-07-15.

So, CM-03 should trivially be unavailable, as there's a five minute period where the current booking overlaps with the desired (11 - 12) new booking.

Based on the intent of the rules you've laid out, I'd say CM-01 should also be unavailable. Your comparing the starts and ends of the two times inclusively. That would imply that, if something's booked for 10:30 to 11:00, and is wanted for 11:00 to 12:00, then it shouldn't be available, because the end of the existing booking is the same as the start of the new booking. If you want to allow back-to-back booking, you need to tweak the logic.

However, if we look at the query, I do think there is a problem. Let's say there was a booking for CM-02 on 2017-07-15 from 9:00 to 10:00 (with a NULL Status). That should clearly not prevent a new booking from 11:00 to 12:00 from being added.

However, when we look at your query to find IDs that should be ignored (reformatted for readability):

SELECT ResourceID
  FROM Bookings
 WHERE DATE(StartDateTime) = '2017-07-15'
   AND Status IS NULL
   AND (    (   TIME('11:00:00') >= TIME(StartDateTime)
             OR TIME('11:00:00') <= TIME(EndDateTime)
            )
        AND (   TIME('12:00:00') >= TIME(StartDateTime)
             OR TIME('12:00:00') <= TIME(EndDateTime)
            )
       )

and consider our 9-10 booking for CM-02, here's what happens:

DATE(StartDateTime) = '2017-07-15' - This is true.
Status IS NULL - So is this.
Checking the start time for the desired (11-12) booking against the existing (9-10) booking:

TIME('11:00:00') >= TIME(StartDateTime) - "11:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

Checking the end time for the desired (11-12) booking against the existing (9-10) booking:

TIME('12:00:00') >= TIME(StartDateTime) - "12:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

So, all is true - and we apparently have a conflict (except we shouldn't).

Feel free to confirm the above by adding such a record to your test data.

I've worked through this before, and there are two things you need to check:

Does time period A start before time period B ends?
Does time period A end after time period B starts?

If both are true, you have an issue.

Note - this does assume your start and end times are in the correct order. If either time period's start time comes before its end time, this won't work.

So, rewrite the query as:

SELECT ResourceID
  FROM Bookings
 WHERE Status IS NULL
   AND CAST('2017-07-15 11:00:00' as datetime) <= EndDateTime
   AND CAST('2017-07-15 12:00:00' as datetime) >= StartDateTime

Let's compare that to the booking I created, and to the bookings you had for the desired 11-12 booking:

CM-02, 2017-07-15 9:00 - 10:00

11:00 (desired) <= 10:00 (existing) = FALSE
12:00 (desired) >= 9:00 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.

CM-03, 2017-07-15 10:35 - 11:05

11:00 (desired) <= 11:05 (existing) = TRUE
12:00 (desired) >= 10:35 (existing) = TRUE
Result - TRUE, so not available for desired booking.

CM-01, 2017-07-15 10:30 - 11:00

11:00 (desired) <= 11:00 (existing) = TRUE
12:00 (desired) >= 10:30 (existing) = TRUE
Result - TRUE, so not available for desired booking.

If you want to allow back-to-back bookings (like for CM-01, 10:30 - 11:00 and 11:00 - 12:00), then change the two comparisons to be < and >, not <= and >=

With that change:

CM-01, 2017-07-15 10:30 - 11:00

11:00 (desired) < 11:00 (existing) = FALSE
12:00 (desired) > 10:30 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.

NOTE: Code is untested.


How is it possible for Hash Index not to be faster than Btree for equality lookups? For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better"  than btree indexes, at least up to version 8.3. From the docs:

Version 7.2:

  Note: Because of the limited utility of hash indexes, a B-tree index should generally be preferred over a hash index. We do not have sufficient evidence that hash indexes are actually faster than B-trees even for = comparisons. Moreover, hash indexes require coarser locks; see Section 9.7.

Version 7.3 (and up to 8.2):

  Note: Testing has shown PostgreSQL's hash indexes to be similar or slower than B-tree indexes, and the index size and build time for hash indexes is much worse. Hash indexes also suffer poor performance under high concurrency. For these reasons, hash index use is discouraged.

Version 8.3:

  Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.

In this version 8.0 thread, they claim that had never found a case where hash indexes were actually faster than btree.

Even in version 9.2, the performance gain for anything other than writing the actual index was almost nothing according to this blog post (14 March 2016):
Hash Indexes on Postgres by André Barbosa.

My question is how is that possible?

By definition, Hash indexes are a O(1) operation, where a btree is an O(log n) operation. So how is it possible for a O(1) lookup to be slower than (or even similar to) finding the correct branch, and then finding the correct record?

I want to know what about indexing theory could EVER make that a possibility!

Disk based Btree indexes truly are O(log N), but that is pretty much irrelevant for disk arrays that fit in this solar system.  Due to caching, they are mostly O(1) with a very large constant plus O((log N)-1) with a small constant.  Formally, that is the same thing as O(log N), because constants don't matter in big O notation.  But they do matter in reality.

Much of the slow down in hash index lookups came from the need to protect against corruption or deadlocks caused by hash-table resizing concurrent with the lookups.  Until recent versions (every version you mention is comically out of date), this need led to even higher constants and to rather poor concurrency.  Vastly more man hours went into the optimization of BTree concurrency than hash concurrency.


Help writing a query that select the min column in a group by I have two simple table

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  nickname varchar(35) NOT NULL,
  PRIMARY KEY (id),
);

CREATE TABLE user_service (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  service_type VARCHAR(10) NOT NULL,
  price numeric(15,2) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_service (user_id, service_type),
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

I'm trying to retrieve in ONE query a list of user that provides certain services.
The query should return the user and the lowest price of its services

Here is the query I wrote, as you can see i have wrote a sub select and I was wondering if this is a good way of doing things? Or is there a more efficient way?

SELECT
    u.id,
    u.nickname,
    (select MIN(price) from user_service where user_id=u.id order by price desc) as minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
    WHERE us.service_type IN ('TYPE1','TYP2')
   group by u.id;

You can try this:

SELECT
    u.id,
    u.nickname,
    t.minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
      INNER JOIN (
    SELECT user_id, MIN(price) minPrice
        FROM user_service
        GROUP BY user_id
    ) t ON t.user_id = u.id AND us.price = t.minPrice
    WHERE us.service_type IN ('TYPE1','TYPE2') and u.id=1
   group by u.id;


Optimize a query reducing logical reads I have a SQL Server 2016 query that returns 127K rows. You can find the query and query plan here. Let me know if you also need tables structure.

I need to join with a table that has only 20 rows, which acts as a replacement for one of the products. In other words, I query products from a main table but, under certain conditions, some of them can be replaced by others.

Problem is that, for that simple table, I have 254K logical reads. I've tried LEFT JOIN and OUTER APPLY.

Any suggestion about how to replace this to avoid that amount of logical reads? Just to mention, only 1 product has a replacement.

Taking a closer look at the execution plan XML, notice these problematic statistics:

<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="1328" WaitCount="403"/>

<QueryTimeStats CpuTime="353" ElapsedTime="1853"/>

The query spent 1.3 seconds waiting on the results to be consumed by the application.  The query only ran for 1.8 seconds total.  So the main problem here is that the application is consuming these 127k results row-by-row.  The query itself runs fairly quickly.

Forrest McDaniel has a good blog post that demonstrates this problem: Two Easy ASYNC_NETWORK_IO Demos

The remainder of the answer addresses the "logical reads" portion of your question.

The reason for all of those logical reads on the OUTER APPLY'd table (DBVAREKT) is here:

The "Index Seek" there is executed once for each row on the upper input to the NESTED LOOPS join.  So there are 127,329 seeks into that index (ID1), even though in the end only 302 matching rows are returned.

The optimizer wouldn't normally choose to do that many seeks into the index, but it only thought there would be 82 rows on the upper input.  Doing 82 seeks is definitely more reasonable.

The general approach to solving this problem would be to avoid doing a NESTED LOOPS join on that particular table, since that is the source of the problem.  To that end, you could use join hints, but it's a little hard for me to tell where the hints should be applied.

Randi mentioned a possible rewrite of the query that would place some of the data into a temp table, essentially breaking the query up into smaller chunks that the SQL Server optimizer can do a better job with.  You could break this up at the OUTER APPLY as follows:

SELECT
       1756,
       L.MADTYPE,
       DBM.VNR1
INTO #results
FROM dbo.STDORDRE S
INNER JOIN dbo.STDORD STO ON sto.DATO = s.DATO
                           AND sto.KUNDE = s.KUNDE
LEFT JOIN dbo.STDORDML L ON L.ONR = s.ONR
CROSS APPLY (SELECT dbo.MCS_ClarionDateToSQL(D.DATO) SQL_DATO,
                    DATEPART(dw, dbo.MCS_ClarionDateToSQL(D.DATO)) DP,  D.VNR1, D.DATO, D.LINE, D.KATALOGNR, D.VFAKTOR, D.MADTYPE FROM dbo.DBMENU D WHERE D.SNR = s.VARENR AND D.LINE = L.MENULINE) DBM
INNER JOIN dbo.DBKUNDE kun ON kun.NR = s.KUNDE
INNER JOIN dbo.DBKUNGRP dbk ON dbk.NR = kun.GRP
INNER JOIN dbo.DBVARE varm ON varm.NR = s.VARENR
INNER JOIN dbo.DBVARE var ON var.NR = DBM.VNR1
LEFT OUTER JOIN dbo.MENORDRE MEN ON MEN.KUNDE = s.KUNDE
                                AND MEN.DATO  = DBM.DATO
                                AND MEN.LINIE = DBM.LINE
                                AND MEN.NR    = s.MNR
WHERE 1 = 1
  AND ( kun.UDSKREVET = 0
                     OR ( kun.UDSKREVET = 1
                          AND kun.UDSDATO >= 79627 ))
                   AND varm.TYPE = 9
                   AND varm.KPFIX = 0
                   AND s.ML = 1
                   AND DBM.DATO BETWEEN 79627 AND 79777
                   AND sto.TYPE = 1;

SELECT
       1756,
       L.MADTYPE,
       DBM.VNR1
FROM #results RES
OUTER APPLY (SELECT MTY.PREC MTY_PREC,
                    MTY.NR   MTY_NR ,
                    VAR1.PREC VAR1_PREC,
                    var1.VAR_PKG_ID VAR1_VAR_PKG_ID,
                    VAR1.NR VAR1_NR ,
                    VAR1.TYPE VAR1_TYPE,
                    VAR1.GRP VAR1_GRP
             FROM dbo.DBVAREKT VKT
              LEFT JOIN dbo.DBVARE VAR1 ON VAR1.PREC = VKT.TO_VARE_PREC
                                               AND (ISNULL(VKT.MADTYPE,0) <> 0  )
              LEFT JOIN dbo.DBMTYPE MTY ON MTY.PREC = VKT.TO_MADTYPE_PREC
            WHERE 1 = 1
              AND VKT.MADTYPE = RES.MADTYPE
              AND VKT.VARENR  = RES.VNR1
                      ) OA;


Transaction log full due to log_backup I am testing how certain operations are logged under different recovery models.Below are steps i have did till now
1.create database in full recovery model
2.take backup
3.create a table and insert 10 million records
4.Take log backup,check VLF count and see log space free percentage
5.Now do an index rebuild and see the records generated using fn_dblog function
6.Now i swicthed to bulk recovery model
7.Took  a backup
8.Taken a log backup
9.did an index rebuild
Strangely index rebuild is failing with below error.
The statement has been terminated.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'bulklogging' is full due to 'LOG_BACKUP'.
thats not true ,actually
1.log space autogrowth is not restrcited

2.Space on where logfile stored

Can some one help me understand why i am getting above error ,eventhough space is there and autogrowth is not restrcited
adding image of index size..
Issue got resolved,but not sure how this change made difference.Any pointers would be much appreciated..
Comment is saying two long  --so posting here...
i scripted out index again ,after changing recovery model and then it worked.Moreover scripted index before and after exactly remain same,only session got changed.But i am not sure how this got worked .
Before :
USE [bulklogging]
GO
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After:
USE [bulklogging]
GO
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Update to close this question:
I am using Fn_dblog to check log records,This seems to have a hidden Bug as described here,which may be affecting my log growth..

Edit 8/15/13: Beware – Jonathan just found out from a customer system that uses this extensively that every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It’s a bug that the SQL team is going to fix now we’ve alerted them to it. Use with caution.
Edit 5/15/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be backported any earlier.

This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,

  the SQL Server Database Engine will raise a 9002 error.

To solve this issue, you have to do the following:

Take a Full database backup.
Shrink the log file to reduce the physical file size.
Create a LOG_BACKUP.
Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

  Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.

  For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Hope this helps you


PostgreSQL: pg_upgrade failing because hstore incompatible I'm trying to migrate from PostgreSQL 9.4.5_2 -> 9.5.3

I'm trying to use pg_upgrade (from 9.5.3 distribution) and getting the following error:

pg_dump: [archiver (db)] query failed: ERROR:  incompatible library "/usr/local/lib/postgresql/hstore.so": version mismatch

Anyone know how to fix this? must I pg_dumpall and then reload? That has worked in the past. Trying to use pg_upgrade to save some steps.

So I am having the same problem now that 9.6.1 is out. Here is the only thing I have found so far on-line but haven't tried it out for myself yet. I'll update this answer once I've been able to successful solve the problem.

EDIT: I followed these steps and everything works great. Hopefully when I upgrade to 9.7 it won't be so hard.

Currently on 9.5.4_1

Upgrading to 9.6.1

The answer below is take from this comment with the answer provided by Felix Bünemann. I have modified the steps for my version numbers (see above) and expanded some of the steps with explanations.

  1) While still on [9.5] make sure your hstore databases no longer use the deprecated => operator:

$ for db in `echo 'SELECT datname FROM pg_database;' |/usr/local/Cellar/postgresql/9.5.4_1/bin/psql -t`;do echo $db; echo 'ALTER EXTENSION "hstore" UPDATE;' | /usr/local/Cellar/postgresql/9.5.4_1/bin/psql $db;done

Running this in the terminal basically loops through each of your databases and tries to update the hstore extension for each. See here for more details. There are a few

  2) stop [9.5] server, brew upgrade postgresql and initdb the new data directory as in the guide

These steps are described in the main article and in my case were already done by this point.

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ brew update && brew upgrade postgresql
$ initdb /usr/local/var/postgres9.6 -E utf8

The next 3 steps are to create 3 shell scripts to replace (or wrap) 3 of the programs used in the upgrade process which will be explained later.

  3) Rename [9.6] pg_ctl to pg_ctl.bak and create a shell wrapper as pg_ctl:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.6.1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl.bak "$@"

  4) Rename [9.6] pg_dump to pg_dump.bak and create a shell wrapper pg_dump:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.5.4_1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dump.bak "$@"

  5) Rename [9.6] pg_dumpall to pg_dumpall.bak and create a shell wrapper pg_dumpall:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.5.4_1/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall.bak "$@"

  6) Make the wrappers executable with chmod +x

$ chmod +x pg_ctl pg_dump pg_dumpall

As stated in a later comment by Felix:

  Essentially what the shell wrappers do is swap around between the [9.6.1] and [9.5.4] lib directories at the right points in the migration process. Because pg_dump needs the [9.5] versions and pg_ctl starts the server which needs the [9.6] ones.

Again remember I am swapping out my versions for everything which you will need to do as well.

  7) Start pg_upgrade as in the guide (this also works with the -k switch, if your low on space, like me)

pg_upgrade -d /usr/local/var/postgres -D /usr/local/var/postgres9.6 -b /usr/local/Cellar/postgresql/9.5.4_1/bin/ -B /usr/local/Cellar/postgresql/9.6.1/bin/ -v

  8) Remove the shell wrappers and rename the .bak files to their original name

  9) Swap your data directories as in the guide

$ mv /usr/local/var/postgres /usr/local/var/postgres9.5
$ mv /usr/local/var/postgres9.6 /usr/local/var/postgres

  10) Start the [9.6] server and analyze it using the generated analyze_all.sh script

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  # or, if you're running a current version of Homebrew
$ brew services start postgresql

  11) Enjoy postgres 9.6 and grab yourself a coffee for successfully working around the buggy pg_upgrade

  Note: If you’re using the pg gem for Rails, you should recompile:

$ gem uninstall pg
$ gem install pg


How to propely perform a recovery? So, let me give you some context.

I started to work for this company just a few weeks ago, and I'm a jr sysadmin, not a database administrator. Actually, my boss started the same time. The previous boss did not knew what he was doing, so the company hired us to fix a lot of things.

We have a partner that developed the main system we use here and the database(postgres) backup routine was automated by then (old boss had no clue how to do it). My boss asked this partner if the backup/recovery routine was working, but the response was not confident, so he asked me if I could performe a test.

I've done backup/recoveries in databases before, so no problem, but when I  got the backup file, to my surprise, it was not a pg_dump file, but a bunch of folders named like 'base', 'pg_clog', 'pg_logical', etc. So, their backup was just a shell script that copy all database folders. That base folder alone is more than 32Gb.

My question is: is this a standard or acceptable way of doing a database backup? I ask because I was wondering if, in case a fatal hardware failure happens in the server, how would I recover this database in another server from this folders?

It could be a "cold backup" where someone shuts down the database and copies the PGDATA directory files.  If that is the case, it should be a valid backup.  You can test it by copying the entire directory to some test machine with the same architecture as the database server, taking that test machine off the network (in case it tries to reach out and touch someone when you start it up), and then start it up.  You can also run the program pg_controldata against this directory.  If the database was shutdown at the time the backup was taken, you will get a line like this:

Database cluster state:               shut down

This suggest it was a cold backup and should be valid, but it is not conclusive proof.

It could be a correctly taken hot-backup.  If this is the case, the directory should have a file named backup_label.  But again, this is not conclusive that the backup is complete and was correctly taken.  You need to test the backup.  The hot backup could be entirely self-contained, all the needed WAL being located in pg_xlog.  Or, it could be usable only in conjunction with an external WAL archive.

Finally, it could be that someone ran cp -r or similar against the database when it was in service without going through the proper steps, and created a useless backup.

You should be able to look at the backup script to at least see what it is attempting to do, that should be easier than doing a from-scratch forensic inspection of the back up directory.


how to drop job with lowercase name ORACLE (=> ORA-27475 ) How can I drop a job with lowercase name in Oracle?

It gives me an error ORA-27475 "JOB_NAME" must be a job

BEGIN
 DBMS_SCHEDULER.stop_job(job_name => 'MY_JOB_name');
END;
/

The job exists for sure .. do you know how to circumvent this issue?

Thx

When you need to work with case-sensitive identifiers, you need to put them between double quotation marks, otherwise the database handles them as case-insensitive and automatically converts to uppercase.

begin
  dbms_scheduler.create_job
  (
   job_name             => '"job1"',
   job_type             => 'plsql_block',
   job_action           => 'begin null; end;'
  );
end;
/

select job_name from dba_scheduler_jobs where job_name = 'job1';

JOB_NAME
------------------------------
job1

This fails:

begin
  dbms_scheduler.drop_job('job1');
end;
/

Error report -
ORA-27475: "BP.JOB1" must be a job
...

But this works:

begin
  dbms_scheduler.drop_job('"job1"');
end;
/

PL/SQL procedure successfully completed.


Recovering data from SQLite .db File Data To give a bit of background, I was looking for the occurrence of specific text from within multiple SQLite DB files.  I initially tried a simple grep of the text on the multiple files and grep found matches in one of the .db files.

# grep "mytext" *.db
Binary file sqlite.db matches

So I opened the sqlite.db file using a hex (note HEX) editor and searched for the same text.  It was found in multiple spots and with the HEX editor, and as I can see text strings, I was able to actually see the text for which I was looking.

So I then opened the .db file using SQLite.  The file had about three dozen tables.  So I first tried (from within SQLite):

sqlite> .output sqlite.dbdump.sql
sqlite> .dump

...then...

# grep "mytext" sqlite.dbdump.sql

...no results were found...weird.

I went a step further and tried:

# echo ".tables" | sqlite3 sqlite.db | while read tablez
# do
#  echo "select * from $tablez" | sqlite3 sqlite.db
# done | grep -i "mytext"

...no results were found...weird again.

Can anyone shed any light on where this matching text may be within the SQLite DB file?  Are their hidden tables that my .dump and table selects may not be searching?

Since posting this, I believe those are deleted records. I now need to find the best way to attempt to recover instead of vacuuming.

Community wiki answer:

Is it possible that the text appears in deleted rows whose disk space has not yet been "recycled" or "garbage collected".  If you vacuum the database and check again with grep, the text should be gone.

  I now need to find the best way to attempt to recover instead of vacuuming.

To recover the data, instead of vacuuming, make a copy of the database as it is now, look for a hex editor, and see what you can salvage by hand.

See also Undelete accidentally deleted records in Sqlite3 on Stack Overflow.


Monitoring Maintenance Tasks in AlwaysOn Clusters I have an environment with multiple AGs on a single server and they encounter a split situation in which some end up on one node and some end up on the other node (in a failover event, perhaps). When maintenance tasks (the basic gamut of reindexing, update, etc.) execute on these nodes, they will only effectively perform operations on databases which are read_write on the primary side of their respective AGs.  This causes the job to fail since the databases are read_only on the secondaries.  Therefore, in a split AG situation, both nodes will always show failed maintenance in the job history.

Needless to say, it is difficult to monitor these clusters.  I have proposed two possible solutions to management.  We either script the maintenance tasks using DBCC commands for the individual databases based on their updateability properties, or we simply limit ourselves to one AG per server.  They don't like either solution.  Does anyone know of a way I can keep the built-in SQL Server maintenance tasks and have them target only the primary databases?

SQL Server Maintenance Plans do not have in-built functionality to detect the AG replica state for a particular DB and act accordingly. To do this, you must implement custom functionality.

Depending on how you're implementing your maintenance tasks, there are a few options for this.

Option #1:

If you're using SQL Maintenance Plans, then create one set of maintenance plans per availability group and target only the DBs in that group. Remove the schedules for these plans and then create a SQL Agent job that runs on the schedule you want. Have that Agent job check sys.dm_hadr_availability_replica_states to confirm the AG in question is in the primary state and if so, execute the appropriate Agent jobs to run the maintenance plan tasks.

Option #2:

If you're using SQL Agent jobs with T-SQL scripts to perform your maintenance tasks, add in some logic to your scripts to check sys.dm_hadr_database_replica_states prior to executing to validate that the database in question is indeed the primary.

Option #3:

Redesign your maintenance processes to utilise Ola Hallengren's excellent maintenance solution. This has the logic for handling Availability Groups already built in, so this saves you time in designing, testing and deploying a solution yourself. You simply deploy an identical set of Agent jobs to run the stored procedures on every replica. They will all execute, but only the ones you want to perform any work will do so.

Notes:

Some tasks in Ola's solution run on all replicas all the time, such as Integrity Checks and server-level maintenance (log cycling etc). These should be run on all replicas (yes, even integrity checks), so you shouldn't be concerned about this.
Ola's solution checks the preferred backup replica using sys.fn_hadr_backup_is_preferred_replica before backing up, so make sure your backup preferences are correctly set on your AGs.
Maintenance Plans, in general, are not recommended by most DBAs because of the inflexibility they often impose. T-SQL or PowerShell based solutions are usually preferred as they're much more robust.
Options #2 and #3 are essentially the same, but option #3 means someone else has done the hard work for you. Additionally, the widespread use of this solution means there is lots of help and advice in the SQL community if you run into issues.

Any of these options should be deployed in a fashion where the same jobs are deployed on all replicas and run at the same time. The only differences being that some tasks are only performed on the primary replica, while others just run the job and exit quietly performing no work because of the replica state.

Your management team may not like the idea of refactoring the current maintenance processes, but the reality is they have to if they want to automate this to handle failover, otherwise, the maintenance tasks have to be managed manually post-failover.


Why SQL Server doesn't support foreign key constraints on views? I know that SQL Server doesn't support using a view as the reference for a foreign key constraint, is there a reason (i.e. something to do with the relational model) this is the case?  It seems like it would be useful....

There's no fundamental problem with doing this, since tables and views are both relation variables in the relational model.  Foreign key constraints are just a particular subclass of general constraints, which standard SQL implements using CREATE ASSERTION so there's no particular SQL language difficulty either.

According to this Connect item (no longer available, and not archived) it's simply a matter of prioritization.  If you would find this feature useful, vote for the item and particularly add details of your use case in the comments.


SELECT max value for each group of values order by grouped by MYSQL 8.x I need to get the max age of a group of records, even is the age is duplicate ORDER BY the age DESC
Person | Group | Age
---
Bob  | 1     | 32
Jill | 1     | 34
Shawn| 1     | 42
Jake | 2     | 29
Paul | 2     | 36
Laura| 2     | 39
Lalo |3      |50
Julia|3      |50
Travis|3     |18

Wanted results:
Julia | 3     | 50
Shawn | 1     | 42
Laura | 2     | 39

I tried this solutions by when applying to my real table i brings duplicated age records and i cant get to Order by 'column' DESC
I also tried by doing GROUP BY Group and ORDER BY Age DESC at the end of the query, this brings the values grouped correctly but the Age values are not right

On MySQL 8.0 one solution is using ROW_NUMBER function in this way:
Notice I've used Grp instead of Group, because it's a reserved word.
SELECT
  Person,
  Grp,
  Age
FROM
  (SELECT
    Person, Grp, Age,
    ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Age DESC) Rn
  FROM
    Ppl) a
WHERE
  Rn = 1
ORDER BY
  Age DESC;

Person | Grp | Age
:----- | --: | --:
Lalo   |   3 |  50
Shawn  |   1 |  42
Laura  |   2 |  39

There is no way to get Julia instead of Lalo, unless you add another column to the ORDER BY clause.
db<>fiddle here


Inserting data into multiple tables For my system, students are required to create a user account by completing a registration form (personal, contact and course details; also username and password).

I need to collect registration data through a single form and insert the collected data into two related tables on my database (all student details in student table and login details in users table).

How best can I code this in php?

Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated
Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute
Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ...
If you feel intimidated by SQL, an ORM can help you build your queries, but they are not without issues
Students' id should have a foreign key referencing user_id, assuming you want a 1:1 relationship. To use foreign keys on MySQL you will want to use InnoDB stored engine.
You cannot insert to two tables with a single statement, so you should use transactions. Start a transaction before inserting to the first table, then insert to the second one, then COMMIT the changes. That way, either the two inserts succeed or both fail. Transactions also require InnoDB.
student_id/user_id are probably going to be unique arbitrary keys. You can use the auto_increment property for that column and do:

START TRANSACTION;
INSERT INTO student (last_name, fist_name, ...) VALUES ('Crespo', 'Jaime', ...);
INSERT INTO users VALUES (last_insert_id(), 'jynus', 'hashedpassword', 'student');
COMMIT;


Does SA account need to be called "SA" or can I rename it safely? We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change it - my question isn't about why we should change it, but about working around it.

I'm considering renaming the SA account to something else (like "SysAccount" or something like that, and giving it a new password) and then creating a new account called "SA" with the old password and granting it rights appropriate for the application (obviously not membership in the sysadmin role). Are there any pitfalls to doing this? Are there known problems SQL Server will have if the account called SA isn't actually a sysadmin?

I assume that since I'm just renaming the account, I'm safe - it will still have a uid of 0x01, and I've tested this and it's physically possible and appears to work properly in testing, but I want to make sure I'm not overlooking anything that will clearly break as a result. If it breaks things,  I can always delete the new SA and rename the old one back to undo the damage, but hoping to avoid trying and failing.

I'm using SQL Server 2012, though I suspect the same answer will apply to any modern version. I've seen the bug where the upgrade from 2005 to 2008 can break if you've done this, but I imagine that's long since resolved.

It can be renamed. It's often considered a Best Practice for security, but there can be SQL Agent fallout if you don't bounce the Agent service or change job owners, and I can find reports of it borking Server 2008 upgrades, too.

That's enough to make me not want to bother renaming it.  I say assigning a complex password and disabling the login is enough, and it's generally a better option since it actually closes the vulnerability instead of merely obfuscating it. Obfuscation gains you very little security. In the future, if you truly need an SQL authorization sysadmin, create a new account for it. That's not particularly difficult. You have the same attack surface as renaming without having to remember that SID 0x01 isn't sa anymore (even though it still is sa).  And, of course, since the SID never really changes, it's not difficult to find which account is SID 0x01 (or a member of the sysadmin role, for that matter). Of course, if possible, don't even use mixed mode.  Best practice is to use Windows authentication only mode, AFAIK.


How to make inter-database queries with dynamic SQL Server database name? We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary server restore backups daily from the primary server, but only the development instance could have data modifications.

The two databases are from different applications, but one database has triggers that alter data on the other database. Triggers update the second database hardcoding its name, like UPDATE database2..thetable
One database can fetch data from another via SQL queries from one application or Stored Procedures, hardcoded as SELECT FROM database2..thetable

We are considering joining the two SQL Server instances on secondary server (reporting and development) to simplify administration and memory managing, but I couldn't find a solution to make triggers and stored procedures calls another database dynamically and automatically with another name using the same suffix. Ex:

Instance: RS

DB1 (calls DB2 with DB2..table)
DB2

Instance: DEV

DB1 (calls DB2 with DB2..table)
DB2

Into:

Instance: default

DB1_RS (calls DB2_RS with DB2_RS..table)
DB2_RS
DB1_DEV (calls DB2_DEV with DB2_DEV..table)
DB2_DEV

What are the possible solutions to this? I can think about some, but they are too convoluted:

Create dynamically T-SQL to be executed via EXEC(). This makes thing more complex because the triggers have lots of code for UPDATES and INSERTS and DELETES.
Create separate triggers and stored procedures for each group of database names (one for DB1, other for DB1_DEV, other for DB1_RS). This approach seems to not scale...
Make the database name dynamic on the query: INSERT INTO @db..table. Unfortunately SQL Server doesn't support this... :-(

Is there a simpler and effective solution to this?

Thanks!

Not sure I fully understand all of the variables but I suspect you would be able to use synonyms that are different between the two environments which would allow the code to be the same. It is a layer of abstraction that requires documentation and/or an extra layer of the onion to peel, but can definitely simplify the code you write. Here is the CREATE SYNONYM topic... I have used them extensively so please let me know if you have further questions about them.


What can cause low cost and high runtime in EXPLAIN ANALYZE? I have a database that pretty consistently runs queries in a magnitude of cost/10 ms.  There are a couple queries where EXPLAIN ANALYZE reports a cost of 2000 (which I'd expect to be somewhere in the ballpark of 200ms) but runs take multiple minutes.

My first thought is that some other activity is bogging down postgres causing this (either other processes on the machine or concurrent database activity). Is there anything else I should be looking into?  Am I mistaken to expect similar cost:time ratios for different queries?

My first thought is that some other activity is bogging down postgres causing this

My first thought is the data is better or worse cached than the assumptions which are implicitly baked into the random_page_cost / seq_page_cost ratio(and into some various other things).  My second thought is that the expected number of rows is very wrong (compared to the actual number of rows reported by EXPLAIN ANALYZE) and that that leads to wrong costs.  Sometimes those thought orders are reversed.

But I also always keep a terminal open running 'top', so if I am running a query in real time (as opposed to finding it in the slow query log) I can immediately rule out the idea that other work is slowing things down.

  Am I mistaken to expect similar cost:time ratios for different queries?

While that is clearly the goal (what is the point of assigning costs to alternatives and choosing the lowest cost plan if has no better than random chance of actually being faster than a higher cost plan?), planning is very difficult and you shouldn't expect the correlation to be all that tight.

If you are one of the "hackers" who writes the code behind PostgreSQL and you want to find weaknesses in the planner for future improvement, then it might make sense to develop sense of the typical ratio between "arbitrary cost units" and ms of execution time.  But you have to carefully control for how hot the cache is for this to have even a snowball's chance of working.


Couchbase - get all changed today I have a couchbase bucket, in it I have an Integer data with a key.

I am incrementing it, using the API "increment" so I cannot put a JSON there with the date of the last updated.

The question is, how can I select ALL element that where changed today/yesterday?

You have a few options depending on what services you use in Couchbase.

The easiest way that I have used (since v4.6) is query via Couchbase's SQL-like N1QL language and the meta().cas value.

For example, CAS value converted to a timestamp and truncated down to seconds works well, though it's largely undocumented and historically not recommended for this use.  Now it represents revision time (more or less, beyond scope here).

A query for documents before today would look something like this (sorry it's not elegant, probably a better way):

SELECT meta().id,
((NOW_MILLIS())/1000 - meta().cas/1000000000 )/86400 as days_before_today
FROM bucket1
WHERE meta().cas/1000000000  <= (NOW_MILLIS())/1000-86400

Note: 86400 seconds in a day, sub in your own timestamp for WHERE clause or change to reflect the range you are interested in.

Obviously if you were able to add a revision timestamp value to the document you'd have more full control over the value.

You could probably also use TTL value settings on the doc though sounds like you do not specifically want to expire documents.


SQL Server 2017: OR and LIKE Interaction I am using SQL Server 2017 (i.e. v14).
I could use one of the following equivalent criteria in a query:
x IN ('104','504','554')
(x='104' OR x='504' OR x='554')
(x='104' OR x Like '5[05]4')

If I use either of the first two, the query takes around ten times longer to execute than the third one.  Originally, different query plans were produced.  However, as I stripped the query down, I managed to create simple queries which produced exactly the same plan for all three.  However, only the query using the third statement ran quickly and those using the other two ran slowly.
Since the query plans are identical, it leads me to believe that there is something special about a formulation with an "= OR Like" structure.
I have also tried these similar but non-equivalent alternatives:
x Like '[15][05]4'
(x='104' OR x='504')

They took a long time to run, and their query plans were slightly different.
I would like to understand what is going on so that I can adapt my query writing appropriately!  (At least, until I have a new version of SQL server to play with and retest this peculiarity.)
Edit: query plan attached.

Even though the asker didn't provide actual execution plans for comparison, I found the question interesting and dug up a little. I am pretty confident that the difference is due to implicit conversion introduced through the LIKE keyword. I made small script showing the effects of it:
CREATE TABLE T1 (A INT NOT NULL)

;WITH CTE AS ( SELECT X.A FROM (VALUES (101), (102), (103), (104), (105), (155), (107), (108)) X(A))
INSERT INTO T1
SELECT CTE1.A FROM CTE AS CTE1
CROSS JOIN CTE AS CTE2
CROSS JOIN CTE AS CTE3
CROSS JOIN CTE AS CTE4
CROSS JOIN CTE AS CTE5
CROSS JOIN CTE AS CTE6

INSERT INTO T1
VALUES (505), (555)

SELECT COUNT(1) FROM T1 WHERE A IN ('101', '605', '655')

SELECT COUNT(1) FROM T1 WHERE A = '101' OR A LIKE  '6[05]5'

If you check the execution plans of the last two selects you see it clearly:

The second has wrong estimates due to implicit conversion. In this case I scewed the data on purpose, so it got overestimated.
In this simplistic query it doesn't matter, but the real query probably has overall too low estimates, which leads to tempdb spills during hash joins. Changing IN to LIKE inflates the expected number of rows (like in my example) and increases memory grant. This in turn reduces/prevents tempdb spilling, which speeds up the query.


Size Requirement of SQL Database I have been asked this question in an interview that how much would be the DB size for a standalone SQL server if 650MB of data is processed every month. I couldn't give any answer to this question in my interview and later on didn't find any answer either. Any help would be highly appreciated.

This is an open-ended interview question that doesn't have a precise answer. The interviewer might expect you to ask additional questions for clarity.
Actual space requirements will depend on what is meant by "processed" (inserted?), data retention (are data ever deleted?), column data types, indexes, storage overhead, use of compression features (including columnstore), plus additional free space for routine maintenance.


SQL Server Unique Constraint on two columns with an exception Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns.  However, if DocumentID = 'NA', I would like to ignore the rules of the constraint.
Some suppliers in our system do not provide an invoice id, for example.  Therefore, I leave the field NULL.  I would like to remove all nulls for the field 'DocumentID' to avoid accounting for the NULLS in my client code.
I am new to SQL Server, but I could figure out how to do this using a trigger.  The reason I'm asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.
Thanks!
Thanks Tibor.  I was unable to enter a comment correctly, I suppose.  This is what I ended up with using your method.
use CMRBE
CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_DocumentID
ON Expense(SupplierID, DocumentID)
WHERE DocumentID <> 'NA';

You can create a unique index with a where clause to filter out those values for which you want to avoid duplicates.


Spatial Data Cluster in sql-server ? ( like ST_ClusterWithin in Postgis ) Postgres 9.5 providing ST_ClusterWithin for Cluster the spatial data in DB level.

Is there any available solution for data clustering in MS SQL (NOT SERVER CLUSTER)

OR

Is there Any Function ( like ST_ClusterWithin ) in MS SQL Server

------------NO POSTGRES/ POSTGIS SOLUTION -------------

------------NO OL3 SOLUTION -------------

Having looked a the description of ST_ClusterWithin, I believe the geography::Point and STDistance methods are what you're looking for. (See Serge's answer to https://stackoverflow.com/questions/42004474/merge-stored-procedure-and-ctefunction-together-to-get-results-from-reference for an example.)


Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files As the title suggests I'm moving around a lot of data. This is in an environment with a 24 core CPU and 2TB of M.2 storage.

My first question is: given I have 16GB of ram, does that mean it's impossible for me to load a 30GB file into SQL (as the entire thing must be stored in ram?)

The second question: This is a fresh table I'm loading into, so I have the luxury of dictating which engine I can use. I've read that InnoDB is better for writing (in which I'm doing alot of). However, MySQL manual says that the CONCURRENCY clause will work on MyISAM. So which engine should I use?

Any other helpful advice is appreciated.

My mysqld conf:

innodb_buffer_pool_size = 12G
innodb_log_file_size    = 4G
innodb_write_io_threads = 18

There are no restrictions on importing/loading a 30GB file to MySQL (Unless your proc/OS are 32 bit which I doubt). The only additional variable I'll suggest to increase is max_allowed_packet=1G... Later you can return it back to something about 128M (after import)

About the second question: As DBA you will select the storage engine depending on the use of the data after imported, not depending exclusively on the import step. So following standards and current support on transactional procedures, backups, reliability and so on, I suggest you to use Innodb, but again this will depend on the future use of your data.

Since 30 GB actually is not that big, concurrency won't make a huge difference here when importing data.


How to check existence in Oracle without scanning the entire table? Normally, to check existence in Oracle I will do:
SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'

However, if the foo table contains multiple rows where bar='baz', this query needlessly scans through the entire table in order to report the total count.
How do I quickly check if a column in a table contains at least one row with a specified value, and have the query short-circuit, such that as soon as it detects that the value exists, the query will stop and return, as opposed to scanning the entire table?
Ideally I would want this query to return a scalar value, regardless of how many rows in the table contain the value.

The following solution appears to short-circuit (compared to SELECT COUNT(1), this is much faster for a large table), however it will either return 1 if the value exists, or return no rows at all if the value does not exist. It would be better if a row was always returned.
SELECT 1
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)

Just combine the two:
SELECT count(*)
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)


SQLAnywhere: Cursor getting closed after executing a DDL statement Having this code here:

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

I've recognized, that the cursor is getting closed after the execute(@query) statement. Based on what I've found so far it's because of implicit commit done for DDL statements.
As I need to create number of procedures via a script, is there any work around for this issue?

When opening a cursor, there is the WITH HOLD preventing the issue. So

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor WITH HOLD
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

Is working well for my case. As an alternative close_on_endtrans option could also be switched either for session or public.


Backup weekly current production db to Historical db server I have a production sql db of 600gb and a historical server where i move the full db every month.
Now we want to have only the last 3 months information on the production server, and move every week data from the production DB to historical server, to then delete each register which have more than 90 days old.

I couldnt find the best way to do that.
Should i save the date of each register i put on the database to know when it was stored?
Does sql server have any function to know when a register was stored?

Both database are stored on AWS, so it´s easy to move data between them.
Thanks a lot!

You can look into replication as well (not sure if it works on AWS though)

[Prod SQL] --> [Archive SQL] constantly

periodically delete on Prod: anything > 90 days
periodically delete on Archive: anything > 365 days maybe?

If not replication, does "linked server" work on AWS?

Not sure what you mean by "register"


Quoting columns with spaces in PostgreSQL? I migrated a database from SQL Server to PostgreSQL.

Most column names contain double words, for example:

SELECT [Column Name] FROM table;

...which does not work in PostgreSQL.

What is the correct syntax for PostgreSQL?

In most RDBMSs, double-quotes are what are used to specify an exact spelling of something..  (single quotes being string delimiters).

SELECT
  tab."This IS My Column EXACTLY" AS col
FROM "My TabLE Name Contains Spaces Too!" tab
WHERE tab."ANOTHER UGLY COLUMN name" = 'MyFilterString';

Notice that capital/lowercase also matters when using double-quotes.  (Postgres lower-cases everything when double-quotes are not used ... Oracle upper-cases everthing, etc..)

SELECT COLUMN1 FROM TABLE

in postgres, is different from

SELECT "COLUMN1" FROM TABLE

where as in oracle, is different from

SELECT "column1" FROM TABLE


Spool Unicode data in sqlplus to ISO-client without character set conversion I'm currently facing the following problem:

We have an Oracle 11g database with character set AL32UTF8, and an sqlplus-client with NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1.
Stored in the database are various non-ISO8859-1 characters.

Now, when the client connects to the database and tries to spool these special characters, a character set conversion takes place according to the client's NLS_LANG setting and the special characters are lost.

Unfortunately, changing the client-NLS_LANG isn't easily possible (legacy application - it's complicated...).

Is there an easy, or standard way to spool this data without losing these special characters?

I've thought about encoding the data in base64, spooling it, and then decoding it client-side.

But is there a better way?

Without setting NLS_LANG it's going to be tough.

How good are you at simple coding or parsing/manipulating strings?

You can do:

select DUMP(field) from table;

Then write some sort of conversion code to parse the comma-delimited numbers from the dump into Unicode.


Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries? I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and found some ideas, but anytime I do it, I barely note any changes in the performance. So, does it affect in any way? How?

Thanks!

I'll outline SGA and PGA, and then will try to give you a couple of appropriate examples.

SGA consists of many structures, shared by many processes (both background and foreground). Just a few of them:

shared pool
buffer cache
log buffer

If you undersize, for example, buffer cache, the queries will perform slower. Buffer cache is the area of memory used to hold the blocks of data read from and written to data files. Whenever you query a table, data is read from the data files in the units called blocks. The requested data is not returned straight to the user process, instead it is copied to the buffer cache, and then is read from the buffers in the buffer cache if other queries request the same data. Undersized buffer cache leads to lots of I/O and thus adversely impacts performance.

PGA, on the other hand, is the area of memory private to one process. It cannot be shared shared by processes. It is used to maintain the process state. One of the parts of PGA is sort area. It is used to sort a set of rows. In the best case the sort is performed in memory, but if the PGA (sort area in particular) is undersized, the sorting involves the temporary tablespace segments residing on disk. This makes the queries perform slower because disk is one of the bottlenecks in the computer systems.

For more info read "Overview of the Program Global Area" and "Overview of the System Global Area" in Concepts.


Index SEEK is not used unless OPTION (RECOMPILE)? (Question moved from SO)

I have a table (dummy data) with clustered index contains 2 columns :

Now I run those two queries :

declare
@productid int =1 ,
@priceid  int = 1

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

The actual execution plan for both queries is :

As you can see , the first one is using SCAN while the second one is using SEEK.

However - adding OPTION (RECOMPILE) to the first query  , made the execution plan also to use SEEK :

Friends at DBA chat told me that:

  In your query, @productid=1, which means that (productID=@productID OR
  @productID IS NULL) can be simplified to (productID=@productID). The
  former requires a scan to work with any value of @productID, the
  latter could use a seek. So, when you use RECOMPILE, SQL Server will
  look at what value you actually have in @productID and make the best
  plan for it. With a non-null value in @productID, a seek is best. If
  the value of @productID is unknown, the plan has to suit any possible
  value in @productID, which would require a scan. Be warned: OPTION
  (RECOMPILE) will force a recompile of the plan every time you run it,
  which will add a few milliseconds to every execution. Though this is
  only a problem if the query runs very frequently.

Also :

  If @productID is null, to what value would you seek? Answer: there is
  nothing to seek to. All values qualify.

I understand that OPTION (RECOMPILE) forces SQL Server to see what actual values the parameters has , and see if it can SEEK with it.

But now I lose the benefit of ahead-compilation.

Question

IMHO - SCAN will only occur if a param is null .
That's fine - let SQL SERVER create an execution plan for SCAN.
BUT if SQL Server sees that I run this query many many times with values: 1,1 , then why doesn't it create ANOTHER execution plan and use SEEK for that ?

AFAIK - SQL creates execution plan for the most hit queries.

Why doesn't SQL SERVER save an execution plan for  :

@productid int =1 ,
@priceid  int = 1

( I run it many many times with those values)

Is it possible to force SQL to keep that execution plan(which uses SEEK) -  for future invocation ?

Full create table script + data

Summarizing some of the main points from our chat room discussion:

Generally speaking, SQL Server caches a single plan for each statement. That plan must be valid for all possible future parameter values.
It is not possible to cache a seek plan for your query, because that plan would not be valid if, for example, @productid is null.
In some future release, SQL Server might support a single plan that dynamically chooses between a scan and a seek, depending on runtime parameter values, but that is not something we have today.
General problem class
Your query is an example of a pattern variously referred to as a "catch all" or "dynamic search" query. There are various solutions, each with their own advantages and disadvantages. In modern versions of SQL Server (2008+), the main options are:

IF blocks
OPTION (RECOMPILE)
Dynamic SQL using sp_executesql

The most comprehensive work on the topic is probably by Erland Sommarskog, which is included in the references at the end of this answer. There is no getting away from the complexities involved, so it is necessary to invest some time in trying each option out to understand the trade-offs in each case.
IF blocks
To illustrate an IF block solution for the specific case in the question:
IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

This contains a separate statement for the four possible null-or-not-null cases for each of the two parameters (or local variables), so there are four plans.
There is a potential problem there with parameter sniffing, which might require an OPTIMIZE FOR hint on each query. Please see the references section to explore these types of subtleties.
Recompile
As noted above an in the question, you could also add an OPTION (RECOMPILE) hint to get a fresh plan (seek or scan) on each invocation. Given the relatively slow frequency of calls in your case (once every ten seconds on average, with a sub-millisecond compilation time) it seems likely this option will be suitable for you:
SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

It is also possible to combine features from the above options in creative ways, to make the most of the advantages of each method, while minimizing the downsides. There really is no shortcut to understanding this stuff in detail, then making an informed choice backed by realistic testing.
Further reading

Parameter Sniffing, Embedding, and the RECOMPILE Options
Dynamic Search Conditions in T-SQL by Erland Sommarskog
High Performance Procedures by Kimberly Tripp


SQL Server CLR method failing due to TLS 1.0 shutdown I have an old CLR assembly that was using .Net 2 and when our TLS 1.0 connections were shut down for security reasons, the calls I was making out of the assembly (rest posts to https end point) we being returned with

  The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Authentication failed because the remote party has closed the transport stream.

We figured we needed to upgrade the assembly to use .Net 4 (specifically 4.5.2) to get it to use TLS 1.1 which is still active.  I upgraded the project and assembly but am still getting that message.

Is there something specific I need to do in SQL Server or in OS to force the TLS 1.1 connection?

If you are still getting that error then it is possible that the requirement for the connection is actually TLS 1.2 and not 1.1, and 1.2 does not become the default until .NET Framework version 4.6. So, you can either try to compile with a target framework version of 4.6, or you can force the TLS version to use 1.2 (or support both 1.1 and 1.2) by setting the SecurityProtocol property of the ServicePointManager class. There are enum values for:

SecurityProtocolType.Tls12
SecurityProtocolType.Tls11

However, depending on which version of the .NET Framework you are using, those values might not yet have been added to the enum. But that won't matter since you can just specify the numerical value that the enum value translates to:

(SecurityProtocolType)3072 for TLS 1.2
(SecurityProtocolType)768 for TLS 1.1

It appears that there might also be a registry setting to force the TLS version if your code is not overriding via setting ServicePointManager.SecurityProtocol.

I think for my SQL# project I used a static class constructor for the main class that contains the SQLCLR methods that sets ServicePointManager.SecurityProtocol once upon being loaded. And the enum is a "HasFlags" enum, so you can specify multiple versions, such as:

ServicePointManager.SecurityProtocol =
    SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

or possibly:

ServicePointManager.SecurityProtocol =
    SecurityProtocolType.Tls11 | (SecurityProtocolType)3072;

Please also see:

TLS 1.2 and .NET Support: How to Avoid Connection Errors
Support for TLS System Default Versions included in the .NET Framework 3.5 on Windows Server 2012


Connection error while installing mongodb on fedora 28 I have been trying to install Mongodb but it keeps failing midway. When the download is complete, installation fails with a connection error:

MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:27017
2018-08-17T12:26:33.340+0200 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: Connection refused
2018-08-17T12:26:33.340+0200 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

What I've tried:
1. Changing the port:
mongo --port 4332
but it doesn't connect still:

MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:4332/
2018-08-17T12:32:55.743+0200 W NETWORK  [thread1] Failed to connect to 127.0.0.1:4332, in(checking socket for error after poll), reason: Connection refused
2018-08-17T12:32:55.743+0200 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:4332, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

Reinstalling the DB shows up as complete but I still can't start it. Check out the screenshot

Checking for any config files in /etc/mongo* or /var/lib/: there is none.

As it turns out, Fedora treats mongo and mongod as two separate packages. While on most distributions you can install both services in a single bundle, on Fedora you have to install them separately.
sudo dnf install mongodb mongodb-server
Then you can start the service with mongod.


join and repeat value from a column of another table I have two table A and B

Table A

C1
----
11
232
454
65
78
87
45
87
98
99

Table B

C2
----
c1
c2
c3
c4

How can I join these table to get output like

C1, C2
--------
11  ,c1
232 ,c2
454 ,c3
65  ,c4
78  ,c1
87  ,c2
45  ,c3
87  ,c4
98  ,c1
99  ,c2

Since the tables have no column in common, we'll have to use the rownum pseudocolumn which is asigned to every row in a resultset. Do not confuse with rowid which is hash-like and exists phisycally in the table. Since rowids go 1,2,3...n, they provide a way to join two unrelated tables.

select
    t1.c1,
    t2.c2
from
    (select rownum rn,c1 from a) t1 left join
    (select rownum rn,c2 from b) t2 on (t1.rn=t2.rn)

NOTE:

I assumed A had more rows than B so I made a left join. You can turn things around should B have more rows than A.


update query field order guaranteed Is an UPDATE query in MySQL guaranteed to execute the field updates in order that are presented?

e.g.

UPDATE `table` SET `first_to_update` = `second_field`,
`second_field` = `third_field`, `third_field` = 5

MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query.

  The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

  UPDATE t1 SET col1 = col1 + 1, col2 = col1;

  Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

  https://dev.mysql.com/doc/refman/5.6/en/update.html

Your query appears to be written in such a way that this behavior won't cause problems. As long as you don't reference the value of a column you've "already" changed (assuming left to right operation instead of atomic operation) it will do what you expect.


What is the optimal solution for converting a database schema? I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint names plus we're doing a bunch of cleanup and renaming of fields, tables etc.

I suppose a better way to frame the question is that given an existing database that needs to go through the following:

Create all brand new foreign key constraints with names that work within the framework
Tables will be renamed and/or massively restructured including renaming fields that are currently part of existing foreign key relationships

What is the best way to accomplish this?

My solutions thus far have been:

Create a conversion script (in php) that will run DROP FOREIGN KEY for all of the constraints in the database. This has resulted in weird errors occurring during the later steps of the process when trying to ALTER TABLES and add in the new constraints. Also, the constraints were removed but I get errors if I try to DROP the keys themselves.
Instead of using PHP and PDO, write the conversion script using straight SQL and use a CURSOR to DROP the constraints.
Instead of dropping the constraints themselves, convert the tables to MyIsam and DROP the keys then convert back to Innodb before continuing with the rest of the conversion process.
Create a brand new schema from scratch and then move the data from the old schema to the new.

I would like to know which of these approaches would be considered the most valid? Or if there is a better way of doing this?

UPDATE (this information was requested in the chat room so I thought I would add it here):

The current schema has 68 tables, the new schema will be closer to 55.
The largest table has just over 800,000 rows and in general our tables are in the tens of thousands.
The entire database is roughly 240 MB but most of that is in a single table that stores a large text field. We'll be compressing this during the conversion.

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

Renaming foreign key constraints to fit a new application framework.
Refactoring a significant proportion of existing tables.
Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).


When _id is present in the projection, is the index on _id used for the query? My query is

db.new.find({'a':{'$gt':200}, 'b':{'$lt': 30}},
            {'a':1, 'c':1}
).sort({'c':-1})

Indexes present are (a,b), (a,c), (c), (a,b,c), (_id)

When I check with explain('allPlansExecution'), I see that the index on _id is not used anywhere. However, since _id has not been suppressed in the projection, shouldn't it be used? Is there any possibility of it being used implicitly in such cases?

No, there is no need (in that query) to use _id index. There is no sort nor selection what need that index. Second, index (a,b) is not needed, it takes space and extra effort to keep updated. Index (a,b,c) answers all questions when keys a and b is queried.


Trying to install sql_server_2014_enterprise_edition_x64_dvd Windows 7(Ultimate) 64Bit OS Im receiving the following error after the Set-up checks Global rules

It shows the following error. any idea why this issue occurs :( ?by the way this machine was formatted yesterday and this is a fresh installation.!Your help is appreciated..

  TITLE: SQL Server Setup failure.

  SQL Server Setup has encountered the following error:

  No room is available to display rows..

  For help, click:
  http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0x52CFE887%25400xE9BC3D64

  ------------------------------ BUTTONS:

  OK

Extracted from the Log file

  Exception summary: The following is an exception stack listing the
  exceptions in outermost to innermost order Inner exceptions are being
  indented

  Exception type: System.InvalidOperationException
      Message:
          No room is available to display rows.
      HResult : 0x80131509
      Data:
        HelpLink.EvtType = 0x52CFE887@0xE9BC3D64
        DisableWatson = true
      Stack:
          at System.Windows.Forms.DataGridView.set_FirstDisplayedScrollingRowIndex(Int32
  value)
          at Microsoft.SqlServer.Configuration.InstallWizard.RunRuleProgressController.RulesEngineExecutionCompleted(Object
  sender, RunWorkerCompletedEventArgs e)

First of all I think you should be installing Developer Edition as Windows 7 is not a server-OS.  Make sure you have admin rights on your machine.  It may also be worth trying the different install media?

However it looks like there is an open connect item for this error and further discussion here

As a workaround, you could use some of your Azure credits to spin up one of the pre-prepared VMs which already have SQL Server 2014 installed, or try Express edition.


Looping through values stripping 1 character at a time I want to loop through values and strip 1 character at a time from the values and display the result.

So if I have a table with values:

ID
___
34679
13390
89906

I want the result to look like this

Id
----
4679
679
79
9
3390
390
90
0
9906
906
06
6

Please don't use loops for things like this (I'd also reserve recursive CTEs for scenarios where you have much less control over things, like hierarchies). Loops are bad in SQL; SQL is optimized to work in sets.

DECLARE @foo TABLE(ID INT);

INSERT @foo VALUES(34679),(13390),(89906);

;WITH x AS
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number)
  FROM master.dbo.spt_values ORDER BY Number
)
SELECT RIGHT(f.ID, x.n) FROM x
INNER JOIN @foo AS f
ON x.n < LEN(f.ID);

Results:

9
79
679
4679
0
90
390
3390
6
06
906
9906

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


Inheritence and Primary Key Constraints I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also happen to the supertable and vice versa.

Question 1: How is data physically stored if you have 2 tables and table 1 has no constraints but table 2 has an identity primary key column and also inherits columns from table 1.

Question 2:
Since one table inherits columns from the other and inserts in table 2 result also in population of table 1, is the data stored completely isolated and redundant in two different tuples on different pages or is are the tuples linked somehow? And if its separated, does that mean both tuples get locked in case of a writing operation?

Question 3:
How does an index affect table inheritance?

Question 4: What's an actual use for object oriented features in Postgres?

The tables used:

create table person (
    nachname varchar(30),
    age int,
    eigenschaft varchar(30)
    )

create table praktikant (
    praktikant_id int generated always as identity primary key,
    klassenstufe int) inherits(person);

the insert:

insert into praktikant(nachname, age, eigenschaft, klassenstufe)
values('Schmidt', 16, 'fleißig', 9)

The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but

SELECT ... FROM person;

is effectively the same as

SELECT ... FROM ONLY person
UNION ALL
SELECT ... FROM praktikant;

Look at the EXPLAIN output for both queries to confirm that.

Each of these tables has its own constraints and indexes, which don't affect the other table.

So the answers to your questions would be:

The tables are completely independent, except that

praktikant must have all columns of person
querying person will include rows from praktikant

That question is not quite clear, but I assume you can answer it yourself from the above. Locks are taken on the rows of each table independently.
Not at all. Also, a constraint on one table does not affect the other table. Specifically, you cannot guarantee uniqueness of entries across the two tables.
There are few real-world uses. Before the advent of declarative partitioning in v10, it was used to implement partitioning in PostgreSQL.