PLSQL search procedure to return best and near matches based on multiple criteria i have a requirement to write a stored procedure that takes 6 potential parameters.
I need to do a search on a table that returns rows that match as closely as possible to the search parameters entered.

For example.

Table quotes
(status {incomplete, pending, ordered},
date_created date,
type {autoGenerated,manual,split}
company {PipedPiper, Hooli,Oracle...}
...
)

Here is a use case to illustrate what i need to achieve.
Lets say a user enters the following search criteria on the front end:
they want to get a list of quotes that met the following criteria, or the next best match:

quote status = pending
company = PipedPiper
type=manual
date = Not selected

So, once the stored proc is called it should get the list of quotes that meet that criteria.
If, no quotes are in pending then any Piped Piper manual quotes should be returned.
Perhaps there are no pending quote for PipedPiper.
At that point it should return any manual quotes in the db.

The SP needs to return a cursor and also a flag to confirm what criteria entered is met by the row. please note, it should not return anything against the date flag as the user did not select any date entries for this particular search.
For example

if anyone can guide me in how to code this, provide pseudo code, or any guidance is much appreciated.
I am poor at SQL so I am struggling to design an SQL statement that can achieve the above.

thanks in advance,
Adrian.

if I correctly understood, that criteria:

1) status = pending

2) company = PipedPiper

3) type=manual

4) date = Not selected

means (1 or 2 or 3 or 4) = true

if so, there I can see two way.
firstly you can use dynamic sql, secondly you can use case keyword. I think you know how to use dynamic sql, I will show example for using case keyword.

select * from your_table t

   where case
         when param_status != 'Not selected' and param_status = t.status then 1
         when param_status = 'Not selected' then 1 else 0 end = 1

         and case
         when param_company != 'Not selected' and param_company = t.company then 1
         when param_company = 'Not selected' then 1 else 0 end = 1

       ...

    -- Checking filter parameters, if non of selected then returning empty --
     and  case when param_status = 'Not selected' and param_company = 'Not selected' ...
         then 0 else 1 end = 1

I hope this will help you.


LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR We have a process that generates an inventory report.  On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one store out of many (potentially thousands, typically dozens).  Each worker thread calls a web service that executes a stored procedure.

The database process for processing each chunk gathers a bunch of data into a #Temporary table.  At the end of each processing chunk, the data is written to a permanent table in tempdb.  Finally, at the end of the process, one thread on the client side requests all the data from the permanent tempdb table.

The more users that run this report, the slower it gets.  I analyzed the activity in the database.  At one point, I saw 35 separate requests all blocked at one point in the process.  All these SPIDs had on the order of 50 ms waits of type LATCH_EX on resource METADATA_SEQUENCE_GENERATOR (00000010E13CA1A8).  One SPID has this resource, and all the others are blocking.  I did not find anything about this wait resource on a web search.

The table in tempdb that we are using does have an IDENTITY(1,1) column.  Are these SPIDs waiting for the IDENTITY column?  What methods could we use to reduce or eliminate the blocking?

The server is part of a cluster.  The server is running 64-bit SQL Server 2012 Standard Edition SP1 on 64-bit Windows 2008 R2 Enterprise.  The server has 64 GB RAM and 48 processors, but the database can only use 16 because it is the standard edition.

(Note that I'm not thrilled by the design of using a permanent table in tempdb to hold all this data.  Changing that would be an interesting technical and political challenge, but I'm open to suggestions.)

UPDATE 4/23/2013

We've opened a support case with Microsoft.  I'll keep this question updated as we learn more.

UPDATE 5/10/2013

The SQL Server support engineer agreed that the waits were caused by the IDENTITY column.  Removing the IDENTITY eliminated the waits.  We could not duplicate the issue on SQL 2008 R2; it occurred only on SQL 2012.

(Updated February 2019)

This is an old post, that said I've finally managed to convince Microsoft that the very fact this happens is indeed a defect.

Update: MS Confirmed the defect and assigned it a bug # of 12628722.

I had seen this post this past November 2018 when we to began to suffer much the same after we'd upgraded from Sql Server 2005 to Sql Server 2017. A 3.3 million row table that used to take 10 seconds to bulk insert suddenly started taking 10 minutes on tables with Identity columns.

Turns out there are two issues behind this:

Microsoft changed the behavior in Sql Server 2014 to force Bulk Inserts to run parallelized - in prior versions Bulk Inserts were given a Serialized plan.
Once running in parallel on our 32 core box, the engine spent more time with the cores locking each other than actually doing the work.

Took me 4 weeks but just after the holidays I got a belated present from Santa - confirmation that the issue was indeed a defect.

There are a few possible workarounds that we found until this is fixed:

Use Option (MaxDop 1) in  the query to turn the bulk insert back into a serialized plan.
Mask the Identity column by casting it (e.g. Select Cast(MyIdentityColumn As Integer) As MyIdentityColumn)

this prevents the identity property from being copied when using SELECT...INTO

Remove the identity column as described above.
Change the database compatibility mode to Sql Server 2012 or lower to re-establish a serialized plan.

Update:
The fix MS will be implementing will be to return these sorts of Inserts back to using a Serialized plan. This is planned for Sql Server 2017 CU14 (no news on other versions of Sql Server - sorry!).
When implemented, Trace Flag 9492 will need to be turned on, either at the server level, or via DBCC TraceOn.


Does changing a parameter value in a stored procedure before the query affect the cardinality estimate? I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this:
-- Scrub params
SET @SearchText = NULLIF(@SearchText, '')
SET @ThreadId = NULLIF(@ThreadId, 0)
SET @Hashtag = NULLIF(@Hashtag, '')

But then from this article I read the following:

If the query predicate uses a local variable, consider rewriting the
query to use a parameter instead of a local variable. The value of a
local variable is not known when the Query Optimizer creates the query
execution plan. When a query uses a parameter, the Query Optimizer
uses the cardinality estimate for the first actual parameter value
that is passed to the stored procedure.

Does it count as using a local variable if the value originated from a parameter? I'm wondering if my parameter scrubbing could affect the creation of the query execution plan.

Does it count as using a local variable if the value originated from a parameter?

No, SQL Server will still see the reference as a parameter, which may be sniffed.

"Parameter sniffing" refers to a process whereby SQL Server's
execution environment "sniffs" the current parameter values during
compilation or recompilation, and passes it along to the query
optimizer so that they can be used to generate potentially faster
query execution plans. The word "current" refers to the parameter
values present in the statement call that caused a compilation or a
recompilation. Both in SQL Server 2000 and SQL Server 2005, parameter
values are sniffed during compilation or recompilation for the
following types of batches:

Stored procedures
Queries submitted via sp_executesql
Prepared queries

Generally, the plan for the whole procedure is compiled (and parameters sniffed) before execution begins, so your SET will not have executed when the sniffing occurs. An exception may occur if the statement is recompiled for any reason after the procedure starts executing. In that case, the recompilation process will see the new value you have set.
In most cases, each statement referencing a parameter will be optimized based on the sniffed value, not any changes you have made within the procedure.
Further reading: Parameter Sniffing, Embedding, and the RECOMPILE Options


Where is the source code of the Postgres txid_current function? Where is the source code of the Postgres txid_current function? (docs)
I want to read the function to better understand how it handles values bigger than (2^64)/2.

include/catalog/pg_proc.dat maps txid_current to src/backend/utils/adt/xid8funcs.c:pg_current_xact_id which leads the majority of the implementation in GetTopFullTransactionId and deeper it goes, but easy to follow.


Update Table with Network Client Connection Status, I need help with DB design. SQL 2008 Ex R2 I have C# client application that updates a Server Database, SQL 2008 Express R2. The client application send a ping to a group of devices once every 30 seconds and displays their status then updates Database. (updates only on successful response).
There is also a web page (from the same server) that can display the current status of each device, getting data from the Database.
If a device goes offline the client application tries 4 times to get a response and if it fails 4 times in a row it marks the device offline saying "Offline since DateTime".

I have 2 tables.
1. A Devices table with Name and IP address plus Index/ID.
2. A Connections table with FK: DeviceID and DateTime and Index.

The Index on the Connections table is going to be a massive value in a very short space of time. I don't need all that Data. All I need is the last known Online dateTime for each Offline device.

If you can image my Primary Customer, the owner of these devices will open the Web Page on his IPhone and see: Device 1 to 6 is Online, Device 7 is Offline since 7AM Yesterday etc.

I have 2 possible solutions:
A. Hold the last connection value in the application and only update the database if the device goes Offline. (This is error prone and could loose information).
B. Create a new Table at Midnight and have Multiple Connections tables separated by Day. (Is this possible? Is it a good idea?)

On the Devices table, add two fields: LastStatus and LastDateTimeChecked.  Update it whenever you insert records into the Connections table.  The Connections is your history, but you shouldn't be hitting it for live reporting queries.


Does the keyword NEW have the per-connection basis aspect to it? I am watching a mysql table with an after insert trigger and i have started to receive many inserts.I am using NEW.message to get the last inserted message.

Since i am receiving many inserts from many clients,i am thinking there could be a time that two records are inserted concurrently.I know that the OLD and NEW keywords enable you me access columns in the rows affected by a trigger,but does NEW have per-connection uniqueness just like LAST_INSERT_ID()?.

A trigger is fired by a SQL statement that makes changes to data in the table for which it is defined. It is important to note that triggers are executed for each row of data affected, not for each statement executed. Therefore the scope of new is actually more limited than session variables (which are connection-specific). The new and old aliases exist only within the local scope of the trigger, so concurrency should not be an issue as long as it is handled correctly in any stored programs which cause a trigger to fire.


Creating a database for students the institute I am working at wants to create a database for students. I have to find the best and easiest solution for this application and I need your opinions.

We would like to create a website/web application where students can enter their data into a form, without having access to the database.
Once the data has been entered, the students shouldn't be allowed to change the data entered.

The institutes employees should then be able to access the database and enter some more data, like thesis deadline.

It is fairly important to us to be able to export a student's data into a .pdf form
.

We would also like this data to be automatically deleted after a defined number of years(5 or 10 years), maybe after automatically sending a notification to the datamanager.

Because of hacking worries, the institute wants this website to be only accessed locally. What this means is that in order for a student to be able to enter his data, he will have to come and use the institute's computers.

We've been thinking about using MS Access but I guess a CMS would be the solution in this case. Now I only need your opinions on which CMS-platform to use for this kind of application. The main focus should be the ease of use, both for me creating it and the employees using it. I also need to be able to find documentation (books, tutorials, how-to's) for it.

For learning, teaching, and assessing I'd recommend an open source system called moodle  (https://moodle.com/moodle-lms/).  It can probably do all you ask for, and much more eg online assessments with time locks (or protected by passwords).  It is also good for keeping students' work, for sending messages to students (or giving feedback on documents or archives sumitted by students), for grouping students into classes - which then have different access permissions for teaching materials etc etc.

There is a lot of documentation available for it ( see https://docs.moodle.org/33/en/Main_page ).  I recommend that you have a look at this.  You can configure a server (or VM) so that it is only accessible on your institute's LAN.

I'd recommend against developing your own application.  Rather spend the time/money/energy to set this up the way it is beneficial for you.  There's - of course - much more to be said or written about this, but: have a look, try it out, maybe you will find it useful.  I am not associated with the company or the project.  However, having used other VLEs (virtual learning environments) in the past, I would preferably use moodle.


SQL to find nearest location in one table from locations in another I have a table like this, that contains a few million locations (POINT):

CREATE TABLE NewLocations(
    [NewName] [varchar](20),
    [NewLocation] [geometry]
)

I have another table like this, that contains a few hundred existing locations (POINT):

CREATE TABLE SourceData(
    [OriginalName] [varchar](20),
    [originalLocation] [geometry]
)

What I want to do is construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres, in other words I'm trying to find a NewName either in the same place or near enough the same place as each originalname in SourceData.

e.g.

Name    NewName
loc1    loc56
loc2    NULL

I'm not bothered if there are multiple rows, I can remove them later, and there need not be a match.

With a single created point I can call STDistance and find a match, but I don't quite know how to construct the SQL for this to work with the table. I guess I could also turn it into a function, but I figured there might be a way to do it wholly in sql.

thanks for any help.

construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres

Something like:

select *
from NewLocations n
cross join SourceData s
where originalLocation.STDistance(NewLocation) < 5


What permissions are needed to see view definitions via a query? I have full admin rights on our database and hence can query and see view definitions. I want however query the views with a read-only user in a JDBC Jenkins job magic. The problem: Unlike my admin-user, the read-only user does not see the code/definitions of a view.

This query give me all the view definitions and the meta data I need for all views when I act as Admin:

SELECT name AS VIEW_NAME,
definition,
create_date,
modify_date
FROM [my_database].[sys].[all_views]
JOIN [my_database].[sys].[sql_modules]
ON [my_database].[sys].[all_views].object_id = [my_database].[sys].[sql_modules].object_id

As a result when executing the query as admin I get entries like:

name        | definition        | create_date | modify_date
sample_view | SELECT * FROM bla | 01.01.2017 | 02.01.2017

However, not so much when I do it with my read-only user, I get

 name        | definition        | create_date | modify_date
 sample_view | null              | 01.01.2017  | 02.01.2017

Here you can see my permission configuration for the read only user. Although I have granted him the necessary permission, the view definitions are not visible to the user in a result set.

It is furthermore really strange that after allowing the user to do SELECT and VIEW definition statements and saving the config, a second entry for SELECT and VIEW DEFINITION was added to the config table.

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view.

Permissions that you added will provide you information about all objects in sys schema and information_schema.

I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema.

Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]


Stop logging INSERT INTO statements in PostgreSQL (but keep SELECT) Using logging_collector = on and log_statement = 'all' (from postgresql.conf), currently my log files are correctly populated with every single query that has been executed.

This works great. Except... I don't want to log the INSERT INTO queries, because in this case, such queries are very big, and very frequent. They tend to 1. pollute all the logs and 2. take an enormous amount of space on the disk.

Among the different options for log_statement, which are none, ddl, mod and all, I can't see anything that would log all statement except data-modifying ones such as INSERT INTO.

More concretely, here are some of the things I need to log, or not to log:

statement                                          desired action
--------------------------------------------------------------------
INSERT INTO ...                                    DO NOT LOG
UPDATE ...                                         DO NOT LOG
SELECT                                             LOG
BEGIN; declare cursor with hold for SELECT ...     LOG
EXPLAIN SELECT                                     whatever
CREATE TABLE                                       whatever (preferably don't log)

Is there a way to achieve something that resembles this?

If there is no way to do it through settings, I'll have to resort to some ugly workaround such as running a cron job every 10 minutes which goes into each log file and deletes everything that resembles an INSERT INTO statement... Would be nice if PostgreSQL could avoid me that.

There is no built-in way to log DML statements selectively.

The pgAudit extension does probably what you want, through its pgaudit.log configuration setting:

  Specifies which classes of statements will be logged by session audit
  logging. Possible values are:

READ: SELECT and COPY when the source is a relation or a query.

WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

FUNCTION: Function calls and DO blocks.

ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

DDL: All DDL that is not included in the ROLE class.

MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.

ALL: Include all of the above.


How can i branch based on a columns value in oracle? I am trying to have a dynamic inner join..on in an Oracle database.

For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for example), and a cursor which returns the resulting dataTabe).

There is a slight problem here. The userId which is passed belongs to two different groups - buyers and sellers.  So, whenever I want to run my stored procedure and get the needed record I need to know if it's a buyer or a seller id, then have the correct condition applied in my where clause.

To cut it short, this is my half-complete query:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
BEGIN

  OPEN a_CURSOR FOR
      SELECT TBLDEALS.*, TableName.* FROM TBLDEALS
      inner JOIN Tablename on
      Tablename.ID = TBLDEALS.id
      --Now here I need a branch, something like this:
      --if (buyerOrSeller == 1 )//then this is a buyer
          and personID = TBLDEALS.Buyer_ID
      --else if (buyerOrSeller = 2 ) /then it is a seller and we need to return his record
          and PersonID = tblDeals.Seller_ID

END;
/

For this I used case like this :

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
BEGIN

  OPEN a_CURSOR FOR
      SELECT TBLDEALS.*,TableName.* FROM TBLDEALS
      inner JOIN Tablename on
      Tablename.ID = TBLDEALS.id and
      case when BuyerOrSeller = 1 then TBLDEALS.Buyer_ID = PersonID
        when BuyerOrSeller = 2 then TBLDEALS.Buyer_ID = PersonID;
END;
/

But it seems to be wrong!( I get a

  Error(14,56): PL/SQL: ORA-00905: missing keyword

near equality sign in TBLDEALS.Buyer_ID = PersonID

And I am stuck on how to specify my where condition according to the buyerOrSeller variable.

As per the comment, perhaps something like this:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
  v_stmt_str      VARCHAR2(200);
BEGIN
  if BuyerOrSeller = 1 then
    v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Buyer_ID = :PersonID)';
  else
    v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Seller_ID = :PersonID)';
  end if;
  OPEN a_CURSOR FOR v_stmt_str using PersonID;
END;
/


Backup failure in SQL Server 2005 I'm being unable to perform a backup in SQL Server 2005 installed on Windows 7 local machine. I was initially able to do so but as of now, every time I try I get the following message:

  Backup failed for Server 'EBENEZERAKAGLO\SQLEXPRESS'.
  (Microsoft.SqlServer.Smo)

  System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program
  Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak'.
  Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

Well, suppose you have a command to execute your backup like this

BACKUP DATABASE [naass]
TO  DISK = N'C:\program files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak''
WITH NOFORMAT, INIT,
NAME = N'naas-Complete Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

this will fail if the process that executes this command doesn't have the permissions to write in the mentioned folder. And Windows 7 (for very good security reasons) keeps the C:\program files folders and its subfolders write locked also to an administrator account.

You could mess with the default permissions and give to your folder above write permissions for your account but of course this is not the recommended way.
Much better, simply change the script to work on a different directory on your disks.

BACKUP DATABASE [naass]
TO  DISK = N'D:\BackupSQL\naass.bak'
WITH NOFORMAT, INIT,
NAME = N'naas-Complete Database Backup'
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Of course this new directory should have the correct permissions.


Can a trigger procedure in PostgreSQL learn its own name? A trigger procedure in PostgreSQL can learn the name of the trigger that fired it from the special variable TG_NAME. But can it also learn its own name?

I would like to keep additional configuration information that applies per trigger procedure in a new table and need the trigger procedures's name (preferrably not retyped manually) to select values from that table.

You can query pg_trigger catalog table and gather this information:

SELECT
    tg.tgfoid::regproc::text
INTO
    v_function_name
FROM
    pg_trigger tg
WHERE
    tg.tgrelid = TG_RELID
    AND tg.tgname = TG_NAME;

v_function_name is a variable you must declare before (as text or varchar).

Casting to regproc and then to text will make it double quote the name if not simple (just like quote_ident), if you want to avoid it, you can simple join pg_proc and get proname (in this case v_function_name can be of type name):

SELECT
    pr.proname
INTO
    v_function_name
FROM
    pg_trigger tg
    INNER JOIN pg_proc pr ON pr.oid = tg.tgfoid
WHERE
    tg.tgrelid = TG_RELID
    AND tg.tgname = TG_NAME;


Decrypting Previously Encrypted Data Returns NULL I'm having trouble decrypting data that has previously been encrypted and stored in a table.

After opening my symmetric key and encrypting the data using EncyptByKey(), I get NULL values when attempting to decrypt using DecryptByKeyAutoCert().

Below is what I use for my setup and attempt at decryption:

Create Keys and Cert

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'abcd'
   WITH SUBJECT = 'Database encryption key',
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;

Encrypt Data

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'abcd'

(retrieve and store selected data with)
EncryptByKey(Key_GUID('key_dbKeys'), @clean_data);

CLOSE SYMMETRIC KEY key_dbKeys;

Decrypt Data

SELECT DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'abcd', table.data) FROM table

As a result of this, I get a column of nulls.

If possible, please assist in helping me determine as to why I am retrieving NULL values instead of the original data or if there is a better way to carry out this task.

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it.

When data is encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically locate the correct symmetric key.

Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?

select
    name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
    name ='key_dbKeys'

select distinct table.data from table
where
    table.data is not null

After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.

declare  @clean_data varchar(100)

set  @clean_data='Hello World'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'P@ssword!'
   WITH SUBJECT = 'Database encryption key',
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'

insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))

CLOSE SYMMETRIC KEY key_dbKeys;

SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData


Get rows with most recent date for each different item Let's say  this is the sample date coming from a join of 2 tables. Database is Postgres 9.6

id  product_id  invoice_id  amount       date
1    PROD1       INV01       2          01-01-2018
2    PROD2       INV02       3          01-01-2018
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

I want to know if it's possible in a optimized way to:

Get all the PRODx with their respective INVx which have the latest date, but per product_id. Please note that records unused from a day may be reported to a new one.
This means:

id  product_id  invoice_id  amount       date
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

Get daily summed amounts for each PRODx but fill the gaps with the previous ones if day does not exist.

This means:

 product_id    amount       date
   PROD1         2          01-01-2018
   PROD2         3          01-01-2018
   PROD1         2          02-01-2018
   PROD2         3          02-01-2018
   PROD1         2          03-01-2018
   PROD2         3          03-01-2018
   PROD1         2          04-01-2018
   PROD2         3          04-01-2018
   PROD1         3          05-01-2018
   PROD2         3          05-01-2018
   PROD1         3          06-01-2018
   PROD2         3          06-01-2018
   PROD1         3          07-01-2018
   PROD2         3          07-01-2018
   PROD1         3          08-01-2018
   PROD2         7          08-01-2018

A few thoughts:

For first question I could obtain the max(date) for each PRODx and the pick for each PRODx the rows that have the date=with max(date) but I was wondering if there's faster way to obtain this given a large number of recors in the database
For the second question, I could generate a series of dates for the interval needed and then use WITH rows As and do the query grouping by product_id and sum by amount and then select for each date the previous values from rows with a limit 1 but that does not sound that optimized either.

Looking forward for any input. Thank you.

Later edit:
Trying to give DISTINCT ON () a try.

If I have distinct on(product_id, invoice_id) then I don't get only the most recent ones for the most recent date. If there were invoice_ids in the the past, beside the latest date, then they will be returned
If I have distinct on (product_id) then it returns from the most recent date, but as normal, only the last rows even if in the last day I have two positions for PROD1.

Basically I need something like 'I need for the most recent date, all the product_ids and their invoice_ids while keeping in mind that a product_id can have multiple invoice_ids'

Later edit 2:

Running a query like for first question seems to be reasonably fast:

select product_id, invoice_id, amount
from mytable inner join myOtherTable on...
             inner join (select max(date) as last_date, product_id
                         from mytable
                         group by product_id) sub on mytable.date =
                         sub.last_date

Skinning Q#1 independently and slightly differently than @ypercube

with cte as (select row_number() over (partition by product_id,
                                       invoice_id
                                 order by dt desc) as rn,
                    product_id,
                    invoice_id,
                    amount,dt
               from product )
select product_id, invoice_id,amount,dt
  from cte
 where rn=1
 order by product_id,invoice_id;

 product_id | invoice_id | amount |     dt
------------+------------+--------+------------
 PROD1      | INV01      |      2 | 2018-01-05
 PROD1      | INV03      |      1 | 2018-01-05
 PROD2      | INV02      |      3 | 2018-01-08
 PROD2      | INV04      |      4 | 2018-01-08
(4 rows)

For Q#2, you are on the right track, but the SQL will have a cross join(gasp!)

I think a function with a loop/cursor would be more optimized (i'll try that in my next free block of time)

--the cte will give us the real values
with cte as (select product_id,
                    sum(amount) as amount,
                    dt
               from product
              group by product_id,dt)
select p.product_id,
       (select cte.amount --choose the amount
          from cte
         where cte.product_id = p.product_id
           and cte.dt <= d.gdt -- for same day or earlier
         order by cte.dt desc
         limit 1) as finamt,
       d.gdt
from (select generate_series( (select min(dt)
                                 from product), --where clause if some products
                                                --don't have an amount
                              (select max(dt)
                                 from product),
                              '1 day'
                            )::date as gdt)  d
cross join --assuming each listed product has an amount on the min date
     (select distinct product_id
        from product) p
left join --since we need to fill the gaps
     cte on ( d.gdt = cte.dt
             and p.product_id = cte.product_id)
order by d.gdt, p.product_id
;


What is the HY number (HY000) in MySQL Error messages? When I get an error some of them have an HY code,

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

What is HY000 in the above context. See this question for another example of HY000 in an error message

MySQL reports with ODBC error numbers which include a SQLSTATE. HY000 is a MySQL ODBC extension for there being no ODBC equivalent error number from the docs on Server Error Codes and Messages

  A five-character SQLSTATE value ('42S02'). The values are taken from ANSI SQL and ODBC and are more standardized. Not all MySQL error numbers have corresponding SQLSTATE values. In these cases, 'HY000' (general error) is used.

You can find the HY000 Error Codes in error_code.h, and the full list of authoritative ODBC error codes from the Microsoft ODBC Appendix

The 2 "No such file or directory" is operating system specific.


Resetting a SQL Server 2012 sequence I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm unfamiliar with how to program this). The problem I'm seeing with this specific table is that when I start another population test, the SEQUENCE does not reset back to the first number I want (which is 1).

When I wish to re-run a new test I delete the table in question then run the following:

DROP SEQUENCE foo.fee;
GO

DROP SCHEMA foo;
GO

When I want to re-run the test I run the following SCHEMA & SEQUENCE commands, which are fired in the order below:

CREATE SCHEMA foo;
GO

CREATE SEQUENCE foo.fee
START WITH 1
INCREMENT BY 1
NO CYCLE
NO CACHE;

GO

I then create the table:

CREATE TABLE foo.sample_table_with_data
(order_number bigint PRIMARY KEY NOT NULL,
sample_column_one nvarchar(max) NULL,
sample_column_two nvarchar(max) NULL,
sample_column_three nvarchar(max) NULL)

GO

Once that is completed I run the following insert command 50,000 times:

INSERT INTO [foo].[sample_table_with_data]
(
    [order_number],
    [sample_column_one],
    [sample_column_two],
    [sample_column_three]
)
VALUES
(
    NEXT VALUE FOR foo.fee,
    'Blah',
    'Blah Blah',
    'Blah Blah Blah'
)

Now there is absolutely no problem with the data entering into the table.  The challenge I'm encountering is that when I delete the table, drop the schema and sequence then re-create the table, sequence, and schema the SEQUENCE picks up from the last number in the previous database incarnation and not reset back to one.

For example, if the last number in the sequence is say 634,534 the next sequence number in the new table is 634,535.

After deleting the table and dropping the schema and sequence I run the following to verify removal of the sequence and schema:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
GO

SELECT * FROM sys.sequences
GO

I'm stumped as to why this is happening.  Is there another command that I'm missing here that would help me localize what exactly is going on here?

I should note that this table belongs to a database with 7 other tables all running the SEQUENCE command correctly.

This is a SQL 2012 SP1 Enterprise Edition installation.

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

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


Backup-SqlDatabase: Cannot bind parameter 'InputObject' Error Being Thrown I have a PowerShell script that is used in a fairly complex automated process. One thing it does is create a database backup, and for that we are using the Backup-SqlDatabase cmdlet. I am also using the -InputObject parameter for the Backup-SqlDatabase cmdlet so I can control the timeout of the backup operation. I had to do this because many of the backup and restore operations we are performing run longer than 10 minutes and were timing out. Here is the relevant snippet of code:

### Backup database. Changed to using SQL Connection object so I can control the timeout.
$SQLServerConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer
$SQLServerConn.ConnectionContext.StatementTimeout = $StatementTimeout

Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFile $DatabaseBackup

and here is the error that is being thrown:

Backup-SqlDatabase : Cannot bind parameter 'InputObject'. Cannot convert the "[server\instance]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
At C:\Users\Homer\Documents\testrestore.ps1:44 char:33
+ Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFi ...
+                                 ~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [Backup-SqlDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

(The square brackets are added in the error message. "server\instance" is just a string.)

The server where we are running this process used to only have SQL 2012 client tools installed on it, and it was working fine. This error started being thrown when we installed a SQL 2014 instance on the same server.

Based on the error I believe it is a compatibility issue between the SMO connection object and the Backup-SqlDatabase cmdlet InputObject parameter, but I am having a hard time trying to figure out exactly why. I've only found 1 other reference to this problem, but the thread kind of dead-ended with no real root cause.

Any thoughts or things to try to help me isolate it?

Please check
out my blog for a detailed explanation of the problem.
I was having the same issue, blogged after I couldn't find a lot of information online.

The solution comes down to importing only the required module, in stead of the modules for several versions of SQL Server.

First check what modules are being loaded:

# Get loaded assemblies
([appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"}).Location

Next make sure your session is closed if you already loaded the modules, then use this modified script to load only the modules you need:

$TempArray = @()
$TempArray = $env:PSModulePath -split ';'
# 110 for SQL 2012, 120 for SQL 2014, 130 for SQL 2016
$env:PSModulePath = ($TempArray -notmatch '110') -join ';'

Now, load SQLPS module and run commands.


MySQL Replication, Can i add a trigger to the SLAVE db? Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave.

"Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave."

yes, you can

A lot of warehousing architectures are based on the slave only.

The title of your question is wrong. Change replica DB with SLAVE ONLY


How to increase the performance of this 3 minute reporting query We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ActionDate on when it took place, the status Status and the order type OrderType of what type of transaction has been performed.

To generate a report where for every user the amount of transactions it did for every order type is displayed we have the following query as part of a stored procedure:

SELECT mt.Owner AS User,

(SELECT COUNT(ID)
FROM `MyTable` mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = ca.Owner
AND mt.OrderType = 1
AND mt.Status <> 3
AND mt.Status <> 2  ) AS FIRST,

(SELECT COUNT(*)
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = mt.Owner
AND mt.OrderType = 2
AND mt.Status <> 3
AND mt.Status <> 2 ) AS SECOND,

(... Do this for six order types where mt.OrderType and the AS name changes ...)

(SELECT COUNT(*)
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner =  mt.Owner
AND mt.Status<> 3
AND mt.Status<> 2 )
AS TOTAL

FROM MyTable mt
WHERE mt.Owner IS NOT NULL
GROUP BY mt.Owner

Some relevant information on indexes:

There are three indexes on MyTable. One on ActionDate, Status, OrderType, one on Id, and one on Owner.

These are created as follows:

CREATE NONCLUSTERED INDEX AnExampleOfAnIndex ON dbo.MyTable (ActionDate ASC,
                                                             Status ASC, OrderType ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]

The query takes three minutes. Does someone see how it can be improved? Thanks.

Instead of 7 Subqueries, you can just use one query. This should be a large performance gain:

SELECT owner [User],
       SUM(CASE WHEN OrderType = 1 THEN 1 ELSE 0 END) [First],
       SUM(CASE WHEN OrderType = 2 THEN 1 ELSE 0 END) [Second],
...
       COUNT('x') [Total]
FROM MyTable mt
WHERE status NOT IN (2,3)
  AND ActionDate >= CAST(GETDATE() AS DATE)
  AND ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
GROUP BY owner

You'll also want to make sure that DATEDIFF(day, ActionDate, GETDATE()) = 0 is doing what you want. What you're actually getting is anything that happened on the day that the date is run- the period between the start of the current day and the current time. If you're looking for anything else, say, transactions that took place YESTERDAY, i.e. before midnight this morning and after midnight the previous morning, you'd want to use different logic.

In order to get all users and the related records for them, whether or not there are any related records in the timeframe, you'd have to use a subquery failing another source for the users:

   SELECT  user_lst.owner [User],
           SUM(CASE WHEN mt.OrderType = 1 THEN 1 ELSE 0 END) [First],
           SUM(CASE WHEN mt.OrderType = 2 THEN 1 ELSE 0 END) [Second],
    ...
           COUNT(mt.owner) [Total]
    FROM (SELECT DISTINCT owner FROM MyTable) user_lst
    LEFT JOIN MyTable mt
      ON mt.owner = user_lst.owner
      AND mt.status NOT IN (2,3)
      AND mt.ActionDate >= CAST(GETDATE() AS DATE)
      AND mt.ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
    GROUP BY user_lst.owner

Also note that we had to change the aggregate logic for [Total], as otherwise COUNT('x') would return 1 as [Total] for users with no activity in the timeframe.


mysql master slave replication scenario We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take log position from the master or from the backup server where i took the dump from.

Secondly the new server we have created is 5.6 and old server is 5.5, would there be any replication issues as well ?

If you want to setup slave from another slave steps would be as follows

Flush tables with read lock to lock the slave so it should not get updates
show slave status\G and note master co-ordinates
Take backup now using mysqldump
unlock tables to unlock the slave
transfer that backup and restore it to new one
Hope you have issues grant replication slave on master so need to use change master on the new slave and give co-ordinates noted in step-2

For details regarding master-slave version difference, shouldn't be a big issue but a good idea is to go through MySQL documentation.

Hope it helps


Is it possible to build an UNDO framework for postgres? I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the statement from the log table and execute it.

Example:

You issue a

INSERT INTO sales (id, client_id, product, value)
VALUES (4621, 231, 'Hamburguer', 500)

so the log-trigger would do

INSERT INTO log (undo) VALUES ('DELETE FROM sales WHERE id = 4621')

When you issue UNDO() the function would issue something like EVAL(SELECT undo FROM log ORDER BY id DESC LIMIT 1).

Someone experienced, please tell me if this is possible.

In theory you can, but in practice you'll find that undoing individual changes is kind of hard to get right because of foreign keys, etc.

Consider:

CREATE TABLE a (
    id integer primary key
);

CREATE TABLE b (
    id integer primary key,
    a_id integer not null references a(id) on delete cascade
);

INSERT INTO a(id) VALUES (1),(2),(3);
INSERT INTO b(id, a_id) VALUES (10,1), (20,2), (30,3);

Now, say you do a:

DELETE FROM a WHERE id = 2;

This will cause an automatic delete of (20,2) from b. When you want to undo this change, you need to know that the delete from a also caused a delete in b.

Similarly, say you:

DELETE FROM b WHERE id = 30;

then separately and later, someone else does a:

DELETE FROM a WHERE id = 3;

If you want to undo the first delete it'll fail unless you undo the second delete first.

Basically, you can only reliably undo an operation if you undo all subsequent operations in the order that they occurred first. Even then, it's hard to get right.

That's before you even get to thinking about schema changes. If you add a new NOT NULL column to a table, then you want to undo a DELETE that happened before the column was added, what do you do? Give the new row the default value in the column? What if there's no DEFAULT set?

I'd record an audit history but wouldn't generally attempt to support undo.

You might want to investigate PostgreSQL's point-in-time recovery features for a way to do database-wide rollbacks or restores to points in the past.


SQL Server Table keeps big after schema change I have a table in SQL Server 2012 with 460.000 rows.

This Table has the following Schema:

We had an issue with our application so all 500.000 rows were created with Comments filled up with spaces.

This led to a table-size of approximately 3.7GB:

After this I did the following to fix the ending spaces, but the database stays at 3.7GB.

update voucher set comments = ltrim(rtrim(comments))

A quick calculation showed me, that one completely filled row has approximately 280 bytes of raw data and one empty row (all varchars empty) has 32 Bytes.

This means my table should be between 32MB-122MB for 460.000 rows.

Why is the table 3.7GB? How can I reclaim the space?

Your rows fit in page, even with 100 spaces in Comments. Updating the Comments to ltrim(rtrim)) will make absolutely no difference in space used, since the rows will be left in place in their respective pages (ie. absolutely no page gets freed, you just get more space available on each page). To reclaim the lost space you should rebuild the table:

ALTER TABLE ... REBUILD;

Not that 3.7Gb is big by any stretch...


How is LIKE implemented? Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?

The naive approach would be to inspect each record, executing a regular expression or partial string match on the field of interest, but I have a feeling (hope) that these systems do something smarter.

In addition to what Justin Cave wrote, since PostgreSQL 9.1 you can speed up any search with LIKE (~~) or ILIKE (~~*), and basic regular expression matches, too (~). Use the operator classes provided by the module pg_trgm with a GIN or GiST index to speed up LIKE expressions that are not left-anchored. To install the extension, run once per database:

CREATE EXTENSION pg_trgm;

Create an index of the form

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

Or:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

Creating and maintaining a GIN or GiST index carries a cost, but if your table is not heavily written, this is a great feature for you.

Depesz has written an excellent article in his blog about the new feature.

GIN or GiST?

These two quotes from the manual should provide some guidance

  The choice between GiST and GIN indexing depends on the relative
  performance characteristics of GiST and GIN, which are discussed
  elsewhere. As a rule of thumb, a GIN index is faster to search than a
  GiST index, but slower to build or update; so GIN is better suited for
  static data and GiST for often-updated data.

But for "nearest neighbour" type of queries with the using the distance operator <->:

  This can be implemented quite efficiently by GiST indexes, but not by
  GIN indexes.


Need suggestions for best way to archive MySQL (InnoDB) tables Problem:
I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active rows for the 'wall' table small while I want to diminish the size of the chapter table. I made the mistake of not compressing text data to begin with and I'd like to start compressing data in the archives.

For the 'wall' table, I'm thinking that everything older than a certain wall id will be transferred and compressed to a 'wall_archive'. Anyone wanting to view older posts will just be given a "view archive" link where older post queries use the archive table. Then I run a cron job to do this every now and then and the last wall id archived will be stored somewhere for reference. Am I going the right direction here?

I'm not so sure how to keep the 'chapter' table manageable. Perhaps it's less archiving and more needing to partition the table (or both). But what's the best way to do this? I was thinking of splitting 'story' IDs into odds and evens and dividing the chapter into two tables but I'll run into the same problem again down the road. Or I can archive stories modified before a certain date. Or before a certain story ID. Any suggestions for a scalable solution?

Lastly, how should I go about compressing text data? Should I use PHP's gzcompress function at level 9 to store text data into a BLOB column then gzuncompress the data on retrieval? Or should I use MySql's COMPRESS/UNCOMPRESS functions? I'm leaning towards using PHP in case I separate the web server(s) from the DB server where I can have PHP do the compression processes instead of the more valuable DB server but I'd like to know what best practices are.

Considerations:
I'll still need to be able to access old 'chapter' data easily. 'wall' data can be put into slower storage if needed but it isn't necessary at the moment.

Environment:
6 Core AMD Opteron, 16 GB RAM, 256 GB SSD for MySql,
Percona Server 5.5, Apache, CentOS 6, PHP 5.3, innodb_file_per_table is enabled, database and webserver is running on same machine, total database size is 30 GB, all tables are InnoDB

Schemas:

CREATE TABLE `wall` (
  `id_wall` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_author` int(10) unsigned NOT NULL
     COMMENT 'wall''s owner',
  `id_user` int(10) unsigned NOT NULL DEFAULT '0'
     COMMENT 'user that wrote the comment',
  `comment` text NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id_wall`),
  KEY `id_user` (`id_user`),
  KEY `id_author` (`id_author`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8
  COMMENT='User profile wall'
  AUTO_INCREMENT=9710655 ;

CREATE TABLE IF NOT EXISTS `chapter` (
  `id_story` int(11) unsigned NOT NULL,
  `id_chapter` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `main_image` varchar(2047) DEFAULT NULL,
  `body` mediumtext,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `is_not_shown` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_story`,`id_chapter`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8
  ROW_FORMAT=DYNAMIC
  COMMENT='Story content by chapter';

ALTER TABLE `chapter`
  ADD CONSTRAINT `chapter_ibfk_1`
    FOREIGN KEY (`id_story`)
    REFERENCES `story` (`id_story`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Performance

I would develop analytics that tell me how frequently older data is accessed and by what date intervals. If very few people look at anything older than 1 month, your options are numerous. If it's all over the board, that will be a different strategy.

Let's pretend that content older than 1 month is only accessed occasionally, say < 2% of the requests. Two options available to you include: partitioning and horizontal sharding, both by date. With partitioning, you simply partition by RANGE using the 'created' field in your table. With horizontal sharding you do the same thing, except using a cron job or event to create a new periodic version of your table, then migrate rows to the periodic table. With horizontal sharding, you could use UNION statements to span multiple tables.

Partitioning will have little or no effect on your data model. Sharding will require data model intelligence.


Product of an entire Collumn MySQL Does MySQL have any functions like SUM but then do the MULTIPLICATION of a collumn?

Example:
I have an table like this, and I want to multiply the entire collumn

+--------------+
| combinations |
+--------------+
|            3 |
|            5 |
|            4 |
|            6 |
|            1 |
|            3 |
|            4 |
+--------------+

expected result:

+----------------------------+
| product_total_combinations |
+----------------------------+
|                       4320 |
+----------------------------+

I cannnot find anywhere how to achieve this. Any help is appreciated.

As explained by How do I create a user-defined aggregate function? there is no such aggregate function (also see here).

However you can emulate your product by:

log(product(x) ) = sum(log(x))

SELECT EXP(SUM(LOG(a))) AS product
FROM t ;

Beware, there could be rounding errors. If this is all integers, do ROUND(...) around the EXP.


What's difference between MySql IN and ANY operator? Data

Dump

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `points` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);

Following works fine

SELECT * FROM sql_store.customers
where customer_id in(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id = any(
    select 1
);

but following not

SELECT * FROM sql_store.customers
where customer_id = any(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id in(
    select 1,2
);

Is ANY made to be used with subqueries which returns table and IN which returns list?

What's difference between MySql IN and ANY operator?

There is 2 differences between them.

IN is an operator whereis ANY is not, it is a keyword and may be treated as a comparison operator modifier (the same for ALL and SOME).

See Operators and Operator Precedence. IN is listed in both whereas ANY, ALL and SOME are not.

IN may process an expression list whereas ANY cannot.

For example, the code

WHERE t1.id IN ((SELECT id FROM t2), (SELECT id FROM t3))

cannot be converted into = ANY form without subqueries code rewrite.

In all other respects they are complete synonyms.


Entity-Relationship Conceptual Data Model So I am busy learning databases for an rpg I have been thinking of to learn more specialized techniques with game development itself. Now I am trying to draw a conceptual data model to get things solid and clear for me to work with, but I can't quite understand the relationships between two entities.
Take for example this image:

How does this work, I understand the rectangles are entities and therefore become tables in a SQL database but how can those relationships have attributes? Do they become their own tables or is the fk in Account a multivalue with all Character Names?

I can't quite grasp it yet I understand how databases work on a basic level. It just needs to click so any help or explanation will be appreciated.

Green rectangles are tables, red eclipses are attributes, blue triangles are tables that connect tables and a relationship descriptors so you could understand their relationship. Do not name triangle like tables as referred on the diagram, such as 'contains' , give them meaningful names.

Purple eclipses are additional attributes of tables that connect other tables

Numbers and letters next to triangles are telling you what kind of relationship it is

Whether is :

One to many - example Table Account has 1 and table Character has N which means one account can have many characters , but character can have only 1 account

Many to many - example Character can "Ran into" many creeps and many creeps can ran into many characters

What does it means?

It means that depending on a relationship type, you will create foreign keys and constrains accordingly. If you are still in doubt what the foreign keys are, i suggest you to read some books/articles before you actually start designing database. Databases are base for any project, and it is not something to be taken lightly


Prevent SysAdmin from doing a Database Restore I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database.  I restored databases to a test machine often for developers to debug/test and while I am always really careful, after about ten years, today I wasn't.

My thought was to put in a DENY on the restore permission and if I ever need to really restore, remove the DENY, but I can't find anything like this.

Does anyone know how to do this or maybe have a better idea?

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything.

My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect item).

My next idea is to either have an alert on the restore event or have some job periodically looking at the msdb.dbo.restorehistory table so you can at least catch the restores quickly and work to fix them. Not ideal, I know.


MySQL 5.6: Slave_IO thread stops working while replicating Standard replication breaks for no apparent reason.

MariaDB [(none)]> SELECT @@version, @@version_comment;
+----------------+-------------------+
| @@version      | @@version_comment |
+----------------+-------------------+
| 5.5.52-MariaDB | MariaDB Server    |
+----------------+-------------------+
1 row in set (0.00 sec)

slave is running fine.

# mysql -e "SHOW SLAVE STATUS\G" | grep "Slave"
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Id  | User        | Host                        | db       | Command | Time | State                                                                       | Info             | Progress |
+-----+-------------+-----------------------------+----------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|   1 | system user |                             | NULL     | Connect |  438 | Waiting for master to send event                                            | NULL             |    0.000 |
|   2 | system user |                             | NULL     | Connect |  120 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|   3 | u271869     | S0280I2.ba.ad.ssa.gov:59117 | mysql    | Sleep   |    2 |                                                                             | NULL             |    0.000 |
|   4 | nagiosxi    | localhost                   | nagiosxi | Sleep   |  176 |                                                                             | NULL             |    0.000 |

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: $IP
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 433
               Relay_Log_File: mysql-relay.000265
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 433
              Relay_Log_Space: 710
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1124732721
                  Master_UUID: 4412a455-e1d0-11e3-835a-5254007fe78d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl: /etc/mysql/ca-cert.pem
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
mysql replication
shareedit
edited Jul 18 '14 at 13:16

Everything looks fine, what is making you think replication breaks? Is there any active writes being made to the master? If you did show master status\G on the master, as well as show processlist; are you seeing any active queries or any queries that are locked?

If you posted some of the binlog or taken a look at it, it may help you see, if replication is being broken, what exact query is breaking it. You can access the binlog by doing
mysqlbinlog binlog.file

For more reference on binlog


SQL Server 2016 support for 32-bit systems I understood that SQL Server 2016 was not going to support installation onto 32-bit machines. The cumulative update released on 15 November 2016 includes an x86 installer (SQL2016RTMCU3\x86\SQLServer2016-KB3194717-x86.exe).

Cumulative Update Package 3 for SQL Server 2016 RTM - KB3205413

I don't have a 32-bit machine to test this out on but would like to know what the situation is with 32-bit support for SQL Server 2016.

These fixes aren't for the database engine.

  Why are there both x64 and x86 packages?
  As above, while there is no x86 engine, fixes can apply to a variety of shared components on the machine (including from the feature pack), and I suppose it's possible that those could be installed in isolation on an x86 machine. It is almost certainly the case that if you have installed SQL Server 2016, you should be applying the x64 CU, and only the x64 CU.

These fixes will continue coming out this way because the updates are cumulative - as soon as a fix is there that targets an x86 binary, that fix has to stay in all future CUs too.


postgres bloom index i read the doc about  postgres bloom, but can't reproduce the same results, please help me to understand what i missed.
my server is:

SHOW server_version;
        server_version
-------------------------------
 10.6 (Debian 10.6-1.pgdg90+1)

dev=# show random_page_cost;
 random_page_cost
------------------
 4

first create the table with the same command as in the docs:

dev=# CREATE TABLE tbloom AS
   SELECT
     (random() * 1000000)::int as i1,
     (random() * 1000000)::int as i2,
     (random() * 1000000)::int as i3,
     (random() * 1000000)::int as i4,
     (random() * 1000000)::int as i5,
     (random() * 1000000)::int as i6
   FROM
  generate_series(1,10000000);

next i create the btree index

dev=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

and get next plan:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=258.963..260.900 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=255.446..255.446 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.412 ms
 Execution time: 260.939 ms

Execution time: 260.939 ms

and now drop the btree index and create bloom:

dev=# DROP INDEX btreeidx;
DROP INDEX
dev=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

get new plan:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=260.278..261.989 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=256.224..256.224 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.165 ms
 Execution time: 262.053 ms

Execution time: 262.053 ms in the docs

  Bloom is better than btree

but not in my test. I tried several options with Length but not find good result.

When bloom was introduced in 9.6, parallel query had just been introduced and is was turned off by default.  Bloom appeared to be better than a non-parallel sequential scan in the example given.  But when you can do a parallel seq scan, it appears better than using a bloom index does.  It is not actually better as can be verified by turning off parallel query with set  max_parallel_workers_per_gather TO 0 and looking at the actual executions speeds, but the planner thinks the parallel seq scan will be better.  It looks like maybe the cost estimation part of bloom could use some work.

The example code was not updated for when parallel query was turned on by default, in v10, so it no longer works as advertised.

Note that your example never achieved any index usage at all, so you can't really draw any conclusions about which index is better for that scenario.


Best solution for search scenario I have the database structure:

|     Tag     |         | Article_Tag |         |    Article    |
---------------         ---------------         -----------------
id                      tag_id                  article_id
name                    article_id              title
                        type (numeric)

In a search field I'm entering various tag names and the search must return the Articles which contain those tag (or at least a part of them).

The type field it's a numeric value representing the weight of the tag in it's article.

The query that I'm using now for solving this problem is:

SELECT DISTINCT sum(article_tag.type), article.id, article.title FROM tag JOIN article_tag ON tag.id = article_tag.tag_id JOIN article ON article.id = article_tag.article_id WHERE [tag.name LIKE ? OR] x n GROUP BY article.id ORDER BY sum(article_tag.type) DESC LIMIT ?

The [tag.name LIKE ? OR] x n represents that tag.name LIKE ? OR will be repeated for the n tags entered in the search field. (For example the "sailing adventure ocean" string will be splitted and the three words will be compared with the tag.name).

The ordering by sum is required because we need some kind of "most relevant articles" feature.

The tag.name field is indexed and the DB Vendor is MySQL.

The Problem

My concern is linked to the scalability of this structure. I don't think that having a large number of rows in the Tag table and lots of OR clauses in that query will lead to a good response time. I ran some tests and the results were acceptable but I'm wondering if there is another solution for my problem. Maybe NoSql?

Thank you!

It depends on the number of articles you expect to have. If you have indices on the columns you are filtering, it will be pretty fast. Joining is very fast and will surely not be the problem.

But your WHERE clause is very intensive. Because you are basically scanning all rows in the table and therefore loading it all from the disk to compare. This cannot be avoided in your case, but it will be the crucial part. Nevertheless, if you aren't going past 100'000 articles I wouldn't be too concerned. Still - test it, if you want to be sure!

However, your use case seems to be indexing document (articles) and get the top x of them, based on a search query. A way to be faster and more efficient is, to create vectors from documents which contain for each word its occurrence. Your search query will be transformed into one too, and then it's all about comparing vectors. (simplified)

You don't have to implement that of course, there are libraries for document indexing and searching. Maybe you want to have a look at lucene (or maybe you can even use solr which should do everything for you). Just to give you some ideas, with those tools you can build your own little 'google'. Lucene would of course build its own (noSql) database / index. So you would maybe need to separate this functionality from the rest of your application (I don't know the context).


sqlite Json grouping json_each I want to generate  json grouping users by year.

I have got a "user" table in sqlite with the following columns:

date
name
surname
value

I would like to create a JSON with the following structure:

{
"App":[
    {
    "data": [
        {
            "name": "UserName1",
            "Surname": "Surname1",
            "v": 1231
        },
        {
            "name": "UserName2",
            "Surname": "Surname2",
            "v": 131
        },
        {
            "name": "UserName3",
            "Surname": "Surname3",
            "v": 121
        }
    ],
    "year": 2018
    },
    {
    "data": [
        {
            "name": "UserName4",
            "Surname": "Surname4",
            "v": 1231
        },
        {
            "name": "UserName5",
            "Surname": "Surname5",
            "v": 131
        }
    ],
    "year": 2019
    }

How to group all the data based on the year?

The JSON1 extension makes it pretty easy.

Given the following:

CREATE TABLE user(name text, surname text, date text, value integer);
INSERT INTO user VALUES('UserName1','Surname1','2018-04-01',1231);
INSERT INTO user VALUES('UserName2','Surname2','2018-03-01',131);
INSERT INTO user VALUES('UserName3','Surname3','2018-02-01',121);
INSERT INTO user VALUES('UserName4','Surname4','2019-02-01',1231);
INSERT INTO user VALUES('UserName5','Surname5','2019-03-01',131);
CREATE INDEX user_year_idx ON user(strftime('%Y', date));

this query:

WITH json_by_year AS
 (SELECT json_object('year', cast(strftime('%Y', date) AS INTEGER)
                   , 'data', json_group_array(json_object('name', name
                                                        , 'surname', surname
                                                        , 'value', value))) AS year_obj
  FROM user
  GROUP BY strftime('%Y', date)
  ORDER BY strftime('%Y', date))
SELECT json_object('App', json_group_array(year_obj))
FROM json_by_year;

Produces this (After being run through a JSON pretty-printer):

{
  "App": [
    {
      "year": 2018,
      "data": [
        {
          "name": "UserName1",
          "surname": "Surname1",
          "value": 1231
        },
        {
          "name": "UserName2",
          "surname": "Surname2",
          "value": 131
        },
        {
          "name": "UserName3",
          "surname": "Surname3",
          "value": 121
        }
      ]
    },
    {
      "year": 2019,
      "data": [
        {
          "name": "UserName4",
          "surname": "Surname4",
          "value": 1231
        },
        {
          "name": "UserName5",
          "surname": "Surname5",
          "value": 131
        }
      ]
    }
  ]
}

Basically, it uses a CTE to produce rows of users grouped by year, and then the outer query wraps those in your final data format. The index on the expression speeds up the grouping; handy if you're going to be doing this a lot, but not really needed if it's a once in a while thing.


SQL Server placement of master database files vs resource database files In a post on SqlServerCentral.com, it is mentioned by several people that the files for the Resource database (mssqlsystemresource.mdf and ldf) should be placed in the same folder as the master database file. This was for SQL Server 2005. Is this still a recommendation when it comes to SQL Server 2017? I tried looking in BOL, but could not find any mention of this there.

Totally agree with what George has mentioned, just trying to shed some more light to make it clear.

For SQL Server 2005

You were allowed to move resource database. If you read SQL Server 2005 Master Database Document it says

  The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location.

But this caused problem during service pack upgrade when users started moving it along with master database. The issue came while applying SP/CU which eventually failed because resource database was moved.

For SQL Server 2008 and above.

This was then fixed by MS and they changed the whole thing such that "user is not allowed to change resource database location" and this is why it was moved to
<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binnfolder along with other binaries, people normally do not touch contents of BINN folder. It's specifically in BINN folder because it does not contains any USER data only information related to SQL Server.

Yes there was some discrepancy in BOL documents which was later fixed. I wrote an article on this please read Should We Move Resource Database

Moral: Do not move resource database


How to configure Oracle traffic director for full site failover to have high availability? I have two server systems, each server having both Apache Tomcat server (deployed web app) and Oracle 11g database server. The database on the two servers is synchronised by Oracle Data Guard for high availibility. Tomcat web server is running individually on each server system with no load balancing. One server acts as primary server and other server acts as redundant server.
Usually for any failure on primary server, I have to manually change the IP/Domain name to access the other server.
I have came across Oracle Traffic Director which may solve my isssue to switch any client request to redundant server in case of failure of primary server. Since I am new to Oracle Traffic Director, can anyone guide me to configure OTD for full site failover?

For a true HA architecture, HTTP traffic must be balanced with a front-end load balancer on a separate host. See here for a list of open source balancers (any of which would be simpler and cheaper to implement than OTD):

https://geekflare.com/open-source-load-balancer/

Oracle DB connections should be configured for high availability failover as described here (use the architecture that best fits your technology stack):

https://www.oracle.com/technetwork/database/availability/client-failover-2280805.pdf

An example of how to implement tomcat's native clustering to compliment the load balancing can be found here on my blog site:

https://pmdba.files.wordpress.com/2019/09/ha-oracle-ords-apex-with-http-header-authentication.pdf


Materialized view log not updating Under what conditions would a materialized view log not update?

I must be missing something. I've got a 10gR2 database. I can create the following MV logs:

CREATE MATERIALIZED VIEW LOG ON linked_t1
 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON t1
 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES;

Then if I do an insert:

INSERT INTO linked_t1 (id, link_id, link_analysis_id) VALUES ('11111111','22222222','0000000001');

I see my insert in the MV Log:

SQL> select * from MLOG$_LINKED_T1 t;

LINK_ID  M_ROW$$            SEQUENCE$$ SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
-------- ------------------ ---------- ----------- --------- --------- ---------------
11111111 AAHvaaAAHAABvDsACR         11 1/1/4000    I         N         FE

Not really sure what to make of the 1/1/4000 snaptime.

As soon as I create a materialized view:

CREATE MATERIALIZED VIEW mv$sub2 (c1, c2, m1, m2, m3, m4, m5)
USING INDEX TABLESPACE idx
 REFRESH FAST WITH ROWID ON COMMIT DISABLE QUERY REWRITE AS
SELECT la.rowid c1, ar.rowid c2, ar.analysis_id m1, ar.id m2, ar.valid m3, la.analysis_id m4, la.id m5
  FROM linked_t1 la, t1 ar
 WHERE ar.analysis_id = la.analysis_id;

The materialized view log on LINKED_T1 is emptied, and will no longer track any DML! Why is that? I have not made any changes to T1 for this test.

I should mention that this MV was suggested by DBMS_ADVISOR.TUNE_MVIEW.

And the answer is, operator ineptitude :-)

Apparently, when the change is applied to the MV, the log entry gets purged. The update shows up in the MV. The reason I was looking in the log at all is that this MV is used to update another MV, and the change was not showing up there. That view has it's own issues which I will have to work out.  Thanks for looking!


Optimize a postgresql database view query Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum):

Client has_many Deals (0 to many)
Deal has_many DealStages (1 to many)
DealStage belongs_to Stage

where Stage has an attribute position:integer
and DealStage has an attribute current:boolean and only 1 DealStage can be marked as current = true

I am trying to get each Client's highest stage by position, with the ultimate goal of filtering a set of Clients by Stage ID. The current stage of each deal is defined by whichever DealStage is marked as current. This is what I have done to achieve this result:

  CREATE VIEW client_max_stages AS
    WITH client_max_stage_positions AS
    (
      SELECT DISTINCT clients.id AS client_id, max(stages.position) AS position
      FROM clients
      INNER JOIN deals ON deals.client_id = clients.id
      INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
      INNER JOIN stages ON stages.id = deal_stages.stage_id
      WHERE deal_stages.current = true AND deals.is_active = true
      GROUP BY clients.id
    )
    SELECT DISTINCT client_max_stage_positions.client_id, stages.id AS stage_id
    FROM client_max_stage_positions
    INNER JOIN deals ON deals.client_id = client_max_stage_positions.client_id
    INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
    INNER JOIN stages ON stages.id = deal_stages.stage_id
    WHERE stages.position = client_max_stage_positions.position;

I strongly believe this can be optimized in some way, but this gets me all the clients grouped by maximum stage, then each client with the ID of it's highest stage.

It seems you need in something similar to:

WITH cte AS ( SELECT *,
                     RANK() OVER (PARTITION BY clients.id
                                  ORDER BY stages.position DESC) rnk
              FROM clients
              INNER JOIN deals ON deals.client_id = clients.id
              INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
              INNER JOIN stages ON stages.id = deal_stages.stage_id
              WHERE deal_stages.current = true AND deals.is_active = true )
SELECT *
FROM cte
WHERE rnk=1

PS. Posted as an answer because it's too long for a comment.

PPS. I'm not sure because WHERE exists in CTE and not exists in main query.


Running out of space, add new drive (add file to filegroup) I've been looking into a bunch of options on how to tackle issue of running out of space

The MDF file is currently on D drive (log file on different drive), I have an new empty E drive that I would like to start using

Based on what i've been told/ read I need to add a file on the E drive, and then run an Alter database command to add this file to the existing file group?
Then sql server will automatically see there is a lot of free space on the E drive and start saving data there?

My questions are

1) is the above correct?

2) what are the scripts to run to add the new file to the filegroup

3) do i need to tell sql server to stop saving data to the old D drive (as its almost full)

is the above correct?

Sort of.  Adding file and Alter database goes together.  Based on what you said I am assuming you only have one filegroup at this time which is called primary.

what are the scripts to run to add the new file to the filegroup

Sample tsql you need to run (copied from books online)

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

I highly encourage you to read this:

ALTER DATABASE (Transact-SQL) File and Filegroup Options
Database Files and Filegroups

do i need to tell sql server to stop saving data to the old D drive (as its almost full)

You do NOT need to tell SQL Server to stop saving data to the old D drive. Because of something called Proportional fill algorithm.

Ref: File and Filegroup Fill Strategy

  Filegroups use a proportional fill strategy across all the files
  within each filegroup. As data is written to the filegroup, the SQL
  Server Database Engine writes an amount proportional to the free space
  in the file to each file within the filegroup, instead of writing all
  the data to the first file until full. It then writes to the next
  file. For example, if file f1 has 100 MB free and file f2 has 200 MB
  free, one extent is allocated from file f1, two extents from file f2,
  and so on. In this way, both files become full at about the same time,
  and simple striping is achieved.

Read more about this here.


100% I/O utilization when mysqlcheck is running The following mysqlcheck commands (run daily) are causing 100% Disk I/O utilization for a long period of time (1-2 hours):

mysqlcheck --check --auto-repair --all-databases;
mysqlcheck --optimize --all-databases

Which mysql server configuration settings can be used in order to prevent/limit high disk I/O?

posted the entire mysql configuration here: https://pastebin.com/huvkfkUF .
Thanks for any help!

"Universally acceptable time interval ..." -- "never".  Seriously, change your app tables from MyISAM to InnoDB, and you won't need to ever run mysqlcheck again.

Also, set innodb_buffer_pool_size to about 70% of RAM, not the paultry 128MB.

How much RAM do you have?


Can I retrieve all database objects owned by a particular database? Is there a way to search all database objects by a particular database name or its id. Is that possible? I would like to do this so that I can find out how many stored procedures, functions, views, etc on each DB. By googling, I found some posts about searching objects by user but not database.

Update:
To make my question little bit more clear, I already have this code to get total count of each object type for a specific DB.

SELECT
    SUM(CASE WHEN CHARINDEX('FUNCTION', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
    SUM(CASE WHEN CHARINDEX('STORED_PROCEDURE', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
    SUM(CASE WHEN CHARINDEX('TRIGGER', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
    SUM(CASE WHEN CHARINDEX('VIEW', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id

But I was wondering if I can iterate through sys.databases and return result like below:

SQL Server doesn't aggregate this type of information for you in any way, so you're going to use some kind of loop to get this information in a single resultset. One way is to concatenate a set of UNION ALL queries, but I think an explicit cursor populating a #temp table one row at a time is an easier way to digest what is happening, especially as the source query gets more complex:

First, create a #temp table:

CREATE TABLE #objs
(
  db        sysname PRIMARY KEY,
  functions int,
  procs     int,
  triggers  int,
  views     int
);

Then:

DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(),
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id;';

DECLARE @n sysname, @e nvarchar(512);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT QUOTENAME(name)
  FROM sys.databases
  WHERE database_id > 4
  AND state = 0 -- online
  ORDER BY name;

OPEN c;
FETCH NEXT FROM c INTO @n;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @e = @n + N'.sys.sp_executesql ';
  INSERT #objs EXEC @e @sql;
  FETCH NEXT FROM c INTO @n;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #objs;

Note that for any database that doesn't have any of these objects you will get a row of NULL values instead of 0s. If you want to change that, just fix your pattern from:

SUM(CASE WHEN ... THEN 1 ELSE 0 END)

To:

COUNT(CASE WHEN ... THEN 1 END)

Clean up:

DROP TABLE #objs;

You can also take a look at sp_foreachdb.

For completeness, here is an example using dynamic SQL:

DECLARE @query nvarchar(max) = N'
 UNION ALL SELECT N''$dbs$'',
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM $dbv$.sys.sql_modules m INNER JOIN $dbv$.sys.objects o ON m.object_id = o.object_id';

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += REPLACE(REPLACE(@query,
    N'$dbs$', REPLACE(name,'''','''''')),
    N'$dbv$',QUOTENAME(name))
  FROM sys.databases
  WHERE database_id > 4
  AND state = 0 -- online
  ORDER BY name;

SET @sql = STUFF(@sql, 1, 12, N'') + N';';

EXEC sys.sp_executesql @sql;


Explaining how to design a database to model the stocks of a firm to my Grand'ma My teacher always asked us to create simple but precise databases. Being able to explain it simply but not without leaving the theory "to your wise but aged loved Grand'ma"...
I have to create a database to model the stocks of a firm which commercialize constructing industry products. A same product (Produit) can be bought (Achat) from suppliers (Fournisseur(s)). Client(s) place orders (Commande(s)) which will be used on different construction sites (Chantier(s)). An order is composed (Composition) by severals products. For each of them a discount can be done as a percentage of the selling price. Every order can be delivered in several times. The total quantity of a product can be allocated in several delivery notes (Bon de commande).
This is described in UML in the following pics:

Therefore we should have:

Fournisseur
Achat
Produit
Composition
Commande
Client
Chantier
Livraison
ContenuBL
BonDeLivraison

The scheme modeling the database is the following one

with the following Latex Code

! $Achat(\underline{#IdFourniseur},#IdProduit,PrixVente,Date)$
! $Fournisseur(\underline{IdFourniseur}, NomFournisseur, NumérotSIRET,NuméroTel)$
! $Produit(\underline{IDProduit},PrixVente,QuantiteEnStock)$
! $BonDeLivraison(\underline{NuméroBon},AdresseLivraison)$
! $Commande(\underline{IDNuméroCommande},Date)$
! $Client(\underline{IDClient},AdresseClient,NomContact,NuméroSIRET)$
! $Livraison(\underline{#RefChantier},\underline{#IDProduit},\underline{#NumeroBon},Date)$
! $ContenuBL(\underline{#IDProduit},#NuméroBon,Quantité)$
! $Chantier(\underline{#RefChantier},ResponsableChantier,AdresseChantier)$

Yet, I'm not sure about it, especially because the associations between "classes", are not always shown in the scheme.

I'm not sure about Livraison for instance, which links ContenuBl and
Chantier but therfore Produit and BonDeLivrasion to Chantier
I'm not sure about Client, which as an Association Qualifier and how
to lin it between two classes which are not association classes

Furthermore, I created these relations by reflex. How to be able to explain to my loved Grand'ma why I always use Foreigner Kyes to association classes and not For simple classes ?

What leads to establish the relations I did, if they are right and optimal?

You might find this page interesting. Your schema seems to be reasonable - the only way you'll know is by writing your application and seeing if it works :-).

What you initially call a "bon de commande", you later refer to as a "bon de livraison" (which makes more sense in French). You also fail to provide a definition of ContenuBL (La composistion d'un bon de livraison je présume?).

Most of the people on this forum won't be able to speak/read French and will find it difficult to answer the question. If you like, I can translate it into English for you?


SELECT INTO creates a new table? I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three columns when I use the following code. What am I doing wrong?

declare
    @TempTable table (idx smallint Primary Key IDENTITY(1,1), OneId int, OtherId int)

declare
    @OneId int,
    @OtherId int,
    @date datetime = dbo.GetFloorDate(getdate()),
    @i int = 1

insert @TempTable select Id, OtherId from One where @date = (select dbo.GetSomeDate (Id))

select MessageId = 9999, OneId, OtherId into MessageQueue from @TempTable

select * from MessageQueue

Yes, SELECT..INTO creates a new table

You'd need this to add rows to an existing table

INSERT MessageQueue (MessageQueue, OneId, OtherId)
SELECT MessageId = 9999, OneId, OtherId
   from @TempTable


MySQL slow query with IN() clause on indexed column I have a table with this structure:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `playhead` int(10) unsigned NOT NULL,
  `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci NOT NULL,
  `recorded_on` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_5387574AD17F50A634B91FA9` (`uuid`,`playhead`),
  KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=16944996 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I'm running queries with a long list of type in a IN() clause, like:

SELECT COUNT(id) FROM events WHERE type IN (<long_list_of_types)

where "long list" means around 80 type which are strings like these:

Namespace.Domain.User.Event.SomeEvent1Happened
Namespace.Domain.User.Event.SomeEvent2Happened
Namespace.Domain.User.Event.SomeEvent3Happened
Namespace.Domain.User.Event.SomeEvent4Happened
Namespace.Domain.User.Event.SomeEvent5Happened
Namespace.Domain.User.Event.SomeEvent6Happened
Namespace.Domain.User.Event.SomeEvent7Happened
Namespace.Domain.User.Event.SomeEvent8Happened
Namespace.Domain.User.Event.SomeEvent9Happened
Namespace.Domain.User.Event.SomeEvent10Happened
Namespace.Domain.User.Event.SomeEvent11Happened
...

Result of the query:

| COUNT(id) |
+-----------+
|   8941096 |
+-----------+
1 row in set (27.77 sec)

This is the output of EXAPLAIN on that query:

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | events | range | type          | type | 768     | NULL | 38400 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+

The table contains roughly 16 million events, and the query takes just short of 30 seconds. Is there a way to improve execution time on this kind of query?

MySQL version 5.6.44

You may try a temporary table and join against that:

create temporary table t1
( type varchar(255) not null
);

insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent1Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent2Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent3Happened');
...

create index x1 on t1 (type);

SELECT COUNT(id)
FROM events e
JOIN t1
    ON e.type = t1.type;