Select highest id of each type in mysql I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids. My Query is below.

Select
  backups.id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  backups.id

This outputs the following:

The latest backup for SDSBackup_OFFICE should have an ID of 118. I have read about using either subqueries or different types of joins but I wasn't able to get them to work. Any ideas?

I tried the following query as one of the answers below suggested without success. The max backup.id was indeed selected, but it was incorrectly matched up with other values in the database.

Select
  Max(backups.id) As Max_id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  Max_id

I did some research and found that the groupwise max function only applies to the given column that it is applied to. It is left to the server to determine how the other data should be assigned. I don't know if I fully understand this. I am getting my information from a question/answer on stackoverflow. https://stackoverflow.com/questions/7594465/group-wise-maximum-of-a-certain-column.

All the examples on that page are fairly simple queries, I don't know how to apply these examples to my query. Help would be much appreciated.

I think you're looking at something like this:

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  ( Select  backups.resource_id,
    Max(backups.id) As Max_id
      FROM backups  Group by backups.resource_id
      Order By backups.start_time Desc ) A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id

OR (order by outside, don't remember)

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  (  Select  backups.resource_id,
    Max(backups.id) As Max_id
  FROM backups  Group by backups.resource_id ) as A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Order By   A.start_time Desc


Updating a table with a single UPDATE statement vs several UPDATE statements CREATE TABLE `messages` (
  `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения объявления',
  `expire` date NOT NULL COMMENT 'Дата истечения объявления',
  `paid_top_cat` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху в разделе: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_top_main` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху на главной: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_links` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование ссылок: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_border` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование рамки: 0 - нет, 1 - запрошен, 2 - включен',
  `expire_top_cat` date DEFAULT NULL,
  `expire_top_main` date DEFAULT NULL,
  `expire_links` date DEFAULT NULL,
  `expire_border` date DEFAULT NULL,
  PRIMARY KEY (`mes_id`),
  # more fields
) ENGINE=MyISAM ;

I need to update some fields of a table dependently on some fields of the same table. Is the following in an UPDATE a good idea? paid_top_cat=IF(paid_top_cat=2 AND expire_to_cat<NOW(), 1, paid_top_cat), expire_top_cat=IF(expire_top_cat<NOW(), NULL, expire_top_cat) and so on for other expire_* and paid_* fields in a single statement.

What are arguments pro and counter to use this tricky UPDATE statements with many IF functions vs updating every column (or rather a pair of paid_* and expire_* columns) at once, so running UPDATE max 4 times?

MySQL.

When update executed once it's guaranteed that all rows that satisfy conditions will be updated (or none in case of error). 4 separate updates may update different rows (something may change between updates, and you're using MyIsam, so transactions won't help;  in addition, your update depends on non-deterministic NOW()  ).

Another point is that each UPDATE locks set of rows , so 4 updates will lock rows 4 times even if the same rows are affected(in case of MyISAM)


Attempting to identify transitive dependency Hello I am attempting to identify a transitive dependency in the following diagram. I think that possibly the determinate of "Subtotal" is "Quantity" and "Price Charged" but I'm not sure if "subtotal" is also dependant on the "product_code" which would make it a partial dependency right?

This is how I've worked out the 1NF.

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
INVOICE (INVOICE_NUMBER → INVOICE_DATE)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME, QUANTITY, PRICE_CHARGED)
TRANSITIVE DEPENDENCY
SUBTOTAL (QUANTITY, PRICE_CHARGED → SUBTOTAL)

I'm fairly confident about the other dependencies but am not sure the transitive one is correct?

edit: MODIFIED DETAIL

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME)
INVOICE (INVOICE_NUMBER, LINE_NUMBER → QUANTITY, PRICE_CHARGED, INVOICE_DATE, PRODUCT_CODE)
TRANSITIVE DEPENDENCY
-   ?

Is this looking better, is there no transitive dependency?

Can you get a different answer for subtotal for any specific pair of quantity and price_charged?  No, of course not.  Arithmetic is arithmetic.

Quantity and price charged depend on invoice_number and line_number, not on product_code at all.  In fact, product_code also depends on invoice_number and line_number.

EDIT:

Partial Dependencies are dependencies on part of a multi-part key.  Transitive Dependencies are things that depend on non-key attributes.  Step one is to look at your initial relation and determine what the key of the whole thing is, or if there are multiple candidate keys, what they are.

Here are the pieces that I'd guess make up your key, based on the limited sample data and some common sense:

Invoice Number
Line Number

Your table looks to me like an invoice detail table to me.  This means that if you know what invoice and which line on the invoice, you know which distinct record you're looking at.  If that's the case, what do each of the non-key columns depend on?  Let's look at each in turn:

CUTOMER_NUMBER: depends on just the INVOICE_NUMBER, therefore PARTIAL.
PRODUCT_CODE: depends on the whole key, therefore FULL
CUSTOMER_NAME: depends on CUSTOMER_NUMBER (non-key), therefore TRANSITIVE
INVOICE_DATE: depends on just the INVOICE_NUMBER, therefore PARTIAL.
COMMON_NAME: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.
SCIENTIFIC_NAME and CATEGORY (ditto)
QUANTITY: depends on the whole key, therefore FULL.
PRICE_CHARGED: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.

As noted previously, SUBTOTAL is calculated based on QUANTITY and PRICE_CHARGED so it depends on the full key as well.


Merge two databases in SQL Server I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different databases). Besides there are some SSIS packages that point to the different databases when they load data.
I think people who created the databases thought the a database is a folder and they made thier own system (e.g. "let us put these tables in a new database"). All objects are in the dbo schema and the names do not collude. I don't see any benefit of having multiple DBs. But many benefits of having one. E.g. I cannot create indexed views and objects from different databases are involved. I have to monitor the SPs from different databses, etc.
Is there any smart way of mergin the databases, or does everything have to be done manually? Is there a tool, e.g. on Redgate? So everything is on the same instance... Just some tips... Thanks.

I've always found SQL Examiner to be a great tool that's super easy to use when synchronizing one database from another.
Since you said the names don't collide between entities, then SQL Examiner will basically create all the entities (which you get to choose, e.g. you can exclude all procedures, or hand pick which views, etc) in the destination database from the source database.
It also has features such as showing you the scripts it generates before executing them, creating backups before making changes, and is just an overall good "comparison" tool for both schema and data differences between two databases.
They give you a 30 day full featured free trial, so you can test it out for a while first before deciding to buy.
Additionally you can check out ApexSQL's SQL Compare.


How to select row and column name where field is null? I wanted to show row and column where field is null.

i already see this example  here

but i don't understand xml code.

my code is like this

if exists (select * from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null )
    begin
        declare @error1 as varchar (50)
    select @error1=[id] from Header  where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null

        declare @error2 AS varchar(50)

    DECLARE ocur CURSOR FOR
    select [id] from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null

    OPEN ocur

    FETCH NEXT FROM ocur
    INTO @error1

    WHILE @@FETCH_STATUS = 0

    BEGIN
        --set @error2 = @error2 + @error1 + ', '
        set @error2 = 'error -' + @error1

        insert into yan values(@error2,@uploads, GETDATE() )

        FETCH NEXT FROM ocur INTO @error1
    END

        --insert into table_error values(@error2,@uploads, GETDATE() )
    CLOSE ocur
    DEALLOCATE ocur

    end
else
    begin
            insert code
            end

so i just need row code that show where column is null.

Thanks

there is no need for using slow cursors ...

Insert into table_error
select 'error - ' + Cast(ID as Varchar(20)), @uploads, GetDate()
from Header
where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null


How should I use DynamoDB table I have a list of items. Each item is attached to several urls and each url has a score number. So I have something like this :

How should I organise this in dynamoDB? How should I format my Json file to update my data?

Right now I'm using the first item column as a "partition key" and AttributeType as a number. I don't know how should I organise the rest. Maybe something like this : A { stuff : { URL : value, URL: value, URL: value} }. But with this structure I've hard time to update my table and I don't have experiences with this. I don't know the best practises, and I don't find any tutorial...

Thanks!

depending on how you want to fetch the data. If you want to get all by item, then item as a primary key is a great choice. If you want to filter by item and url then i would add primary key Item and sort key url. If you want to have url and search for the item by url, i would add url as primary key and url as sort key. If you want to be able to do both, i would add primary key for item, sort key for url and then add new GSI with primary key url and sort key item.

I learned a lot about this from this conference: https://www.youtube.com/watch?v=jzeKPKpucS0 recommended.


Is it possible to retrieve the specific databases selected in a maintenance plan step? I would like to use a tsql query to retrieve the databases involved in one of the steps in a database backup plan I've created. From a previous question, I understand that somehow, the data is probably stored in the msdb database, but running a trace didn't reveal the query that retrieved the "checked" status of each selected database.

Does anyone know how SQL Server determines which of the server's databases are included in a particular job step?

I don't believe there is a way.  If you create a Maintenance Plan, it is going to be an SSIS package.  Querying the msdb.dbo.sysjobsteps table, you can view the actual command, but for an SSIS package execution it won't be indicative to the particulare databases you are backing up:

select
    step_name,
    subsystem,
    command
from msdb..sysjobsteps

This will give you something similar to the following command:

/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\MaintenancePlan" /set "\Package\BackupDBs.Disable;false"


DROP USER takes too long when there are many users On a SQL Server 2014 instance with enough RAM and fast disks, there are over 160 users that have access to a database. For some reason unbeknownst to me, running the command DROP USER [username] in this database takes up to 5 seconds per user.
Remapping users to logins and restoring their permissions is very fast.
Within the context of refreshing DEV databases from production, I have to drop and recreate all database users.  So yes dropping the database users and recreating them is necessary.
How do I speed up the DROP USER command?
Remember, I have to run it over 160 times for the instance I'm writing about.
This is the SQL I'm using:
DECLARE drop_user_cur CURSOR FOR
SELECT name FROM #drop_users

OPEN drop_user_cur
FETCH NEXT FROM drop_user_cur INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'use [' + @db_name + '] DROP USER [' + @user + ']'
    BEGIN TRY
        print @sql
        EXECUTE(@sql)
    END TRY
    BEGIN CATCH
        print 'ERREUR : ' + @sql
    END CATCH
    FETCH NEXT FROM drop_user_cur INTO @user
END

CLOSE drop_user_cur
DEALLOCATE drop_user_cur

The problem is not coming from cursor; it's the actual DROP USER that's taking up to 5 seconds.
Using sp_whoisactive, the wait_type is NULL.

Don't pay attention to the duration, the DROP and CREATE USER were being run in a WHILE loop which is why it's saying more than a minute.
Profiler shows over 125,000 reads to execute DROP USER.
Service Broker is not enabled.

The resolution to this issue was enabling service broker on the database as so.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

After enabling service broker for the database, drop users were practically instantaneous.

Kin asked if the service broker was enabled in a previous comment which sent me searching in the right direction.


How to query Mirroring RTO & RPO from system objects Forward Clarification:  I'm using RPO to describe "Estimated Data Loss", and RTO to describe "Estimated Recovery Time" in this question. I realize they're not exact definitions, but are close enough for the sake of the question.

At the bottom of this article here, there's an excellent block of code used to calculate RPO for a given Always-On enabled instance:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b3177c3d-5450-4948-a234-34a8dd41bf37/estimateddataloss?forum=sqlreplication

--@EstimatedDataLoss (RPO)
WITH DR_CTE ( replica_server_name, database_name, last_commit_time)
AS
(
select ar.replica_server_name, database_name, rs.last_commit_time
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
where replica_server_name != @@servername
)
select ar.replica_server_name, dcs.database_name, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss, DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
inner join DR_CTE on DR_CTE.database_name = dcs.database_name
where ar.replica_server_name = @@servername
order by lag_in_seconds desc

And with a few of my own modifications, a similar query can be derived for RTO:

 --@EstimatedRecoveryTime (RTO)
select ar.replica_server_name, dcs.database_name, rs.redo_queue_size, rs.redo_rate, rs.redo_queue_size/rs.redo_rate as TRedo
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
where ar.replica_server_name != @@servername

These metrics will work fine for my Always-On environments, however I'm looking to apply the same for SQL Mirroring on some pre-2012 environments.

Are there similar system objects to calculate the same metrics for SQL Mirroring?

Note: My references to what counts as RTO and RPO come from this Mircosoft article:

https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx

sp_dbmmonitorresults will generate the data you're after. You could either periodically log it to a table (if you want to gather historical data), or run it ad-hoc, storing the data in a temp table to be able to query to derive your desired metrics.

You can also use Perfmon counters to track this if you want to integrate it with a monitoring tool that relies on Perfmon.

As an aside, I would avoid using RPO and RTO in this circumstance (despite what Microsoft says). Mirroring/AGs should be treated as high availability, not disaster recovery. A user dropping a table cannot be protected by an availability group or mirror - they will happily replay that mistake on your secondary nodes.

Using these terms with people not familiar with the technology could be misleading.


Better way to manage data integrity, without having too many composite FK's I have following tables:

class_table (class_id = Auto Incremented Primary Key (AIPK) of this table)
user_table (class_id, user_id (AIPK of this table))
course_table(class_id, course_id (AIPK of this table))

enrollment_table(class_id, user_id, course_id, enrollment_id (AIPK))

Now to maintain data integrity and make sure that both user_id and course_id belong to same class_id, I will have to create 2 composite Foreign Key's (FK) on enrollment_table e.g.

(class_id, user_id) to -> user_table
(class_id, course_id) to -> course_table

With this kind of schema design, if there are more tables involved then number of composite FK's are increasing. So I wanted to know if there is any better way of modeling this kind of schema and avoiding too many composite FK's.

Two composite FKs including the otherwise redundant column class_id are probably the most elegant and efficient solution to enforce that users can only enroll in courses of their class. I see no better alternative.


Performing Sum in Case Statement I am trying to get a sum inside a case statement, but I am getting the error

  Cannot perform an aggregate function on an expression containing an aggregate or subquery

This is my query syntax

Select case
          when sc.ars = 0 then 0
          else SUM(sc.ars / NULLIF(SUM(ic.lit),0))
       end
FROM salesconfig sc
Left Join income ic
On sc.empID = ic.empID

How do I need to change this query so it gives me the result and not an error?

Test

Select case when sc.ars = 0
            then 0
            else SUM(sc.ars / NULLIF(SUM(ic.lit) OVER (PARTITION BY NULL),0))
            end
FROM salesconfig sc
Left Join income ic
On sc.empID = ic.empID


Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014 We had a SQL Server 2008R2 Enterprise edition for our database to support a front-end application. We never had any timeout issues before. Recently the company decided to upgrade the database in to SQL Server 2014 Enterprise edition with 2 node always on cluster setup. The new server has better CPU, Memory than the old server.

After the upgrade I made all the necessary modifications, Checking Database consistency, run DBCC UPDATEUSAGE, update Statistics, index rebuilding, recompiling the stored procedures and so on. The database switching and migration all went well. However, our users start complaining about timeout issues.

I have been reviewing different articles,blog posts and made some modification like changing the connection string and add MultiSubnetFailover = 'True', which seems helped a lot and minimize the frequency of timeout but still the issue is there.  Does anyone know what can cause this issue and how to resolve it?  I would highly appreciate your suggestions and recommendation to resolve this problem.

The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.

I would perform all this testing on a test server and not in production.

https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx
https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/


SUM CASE WHEN CLAUSE I have the below Query and I want the Ageing profile to show as a new column
my ageing profile is 0-30,  31-60, 61-90, 91-120, 121-180, 181-365, 365+

I basically want the results to appear lie this with the Net due appearing in the appropriate ageing bracket.

BU   0-30   31-60   61-90   91-120   121-180   181-365   365+
--------------------------------------------------------------
A
B
C

Query:

select
 BU,
 Ageing,
 sum(NetDue) as Netdue
 from [dbo].[vw_FACT_CONSOL_CREDITORS]
 where date = @date
 Group by BU, Ageing
Order by BU ;

I am new to SQL and just trying to learn so apologise as this is basic stuff
Thanks

Assuming that the Ageing values match the names of the columns in your expected output, then this should work:

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    [dbo].[vw_FACT_CONSOL_CREDITORS]
    WHERE   Date = @date
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

If they are not, then you will need to add some logic to the interior select statement to convert Ageing from whatever it is into those string values (e.g. '0-30', '365+').

As an example to check this, I did the following:

CREATE TABLE x (
    BU INT,
    NetDue SMALLMONEY,
    Ageing VARCHAR(10),
    Date DATE
)

INSERT x
VALUES
  (1, 100, '31-60', '2017-04-01'),
  (2, 25.47, '31-60', '2017-04-01'),
  (1, 90.17, '31-60', '2017-05-01'),
  (1, 16.42, '181-365', '2017-04-01'),
  (2, 99.99, '365+', '2017-04-01')

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    x
    WHERE   Date = '2017-04-01'
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

which produced the output:

BU  0-30    31-60   61-90   91-120  121-180 181-365 365+
1   NULL    100.00  NULL    NULL    NULL    16.42   NULL
2   NULL    25.47   NULL    NULL    NULL    NULL    99.99

All the information (and more) for determining this logic can be found here.


Using ddl triggers with in-memory tables I've created server scope ddl trigger to audit user activities on the server.
Now if someone tries to create in-memory table the statement fails with the following error:

Error SQL72014: .Net SqlClient Data Provider: Msg 12332, Level 16, State 107,
Line 1 Database and server triggers on DDL statements DROP and CREATE are not
supported with memory optimized tables.

Can I create ddl trigger that will ignore actions on in-memory tables?

No, databases with In-Memory OLTP enabled do not support DDL triggers.

  Both database-level and server-level DDL triggers are not supported with In-Memory OLTP tables and natively compiled modules.

You cannot create DDL triggers in a database where In-Memory OLTP is enabled since the code path required to run the trigger would interfere with the In-Memory functionality.  The workaround is to put all your In-Memory OLTP tables in a single database without a DDL trigger, and the rest of your tables into a separate database without memory-optimized tables, and enable the DDL Trigger there.


Benefit to additional index with key column moved to includes Say I have an Index Foo with:

Keys [A, B, C] and Included columns (D, E)

Is there any performance reason to have an additional index Bar with:

Keys [A, B] and Included columns (C, D, E)

My assumption is that a non-needed key at the end would be treated the same as an included column.

IMO, first we have to understand

How optimizer work ?
Optimizer will choose choose which index and WHY ?

As we know Optimizer make Cost base query plan. if query plan is not Trivial or Simple then it must be Cost based Optimization.

Example,

CREATE TABLE Test (
   id int identity(1,1) primary key
    A VARCHAR(50)
    ,B VARCHAR(50)
    ,C VARCHAR(50)
    ,D VARCHAR(100)
    ,E VARCHAR(100)
    )

INSERT INTO Test
WITH (TABLOCK) (
        A
        ,B
        ,C
        ,D
        ,E
        )
SELECT CONCAT (
        REPLICATE('A', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('B', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('C', 30)
        ,(rn / 5)
        )
    ,REPLICATE('D', 100)
    ,REPLICATE('E', 100)
FROM (
    SELECT TOP (1000000) ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
    FROM sys.objects A
        ,sys.objects B
        ,sys.objects C
    ) t4

CREATE NONCLUSTERED INDEX nc1_ABC ON Test (
    A
    ,B
    ,C
    ) include (
    D
    ,E
    )

CREATE NONCLUSTERED INDEX nc2_AB ON Test (
    A
    ,B
    ) include (
    C
    ,D
    ,E
    )

In my example Index size of nc1_ABC will be more than nc2_AB. WHY ?

So if my query is,

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc1_ABC index.It will not use nc2_AB.

But if my query is

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        --and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc2_AB. WHY ?

Because optimizer will compare the price of choosing nc2_AB and nc1_ABC.Optmizer will see that it has to find all record based on filter A and B, that is can do with both index  but index size of nc2_AB is less than nc1_ABC.So it will be use.

  Is there any performance reason to have an additional index Bar with:

So it depend upon example and how frequently the query is use and how important it is to get those record quickly.

If all those are correct then additional index Bar is require,
else index Foo is good enough for both query.

It really depend upon example and situation.


Block insertions on a mysql table with specific value I've got a simple question:
I was wondering if it is possible to (temporary or permanent if possible) block the insertion of a record into a table with a specific value for a specific column. I'm using mysql with InnoDB

So I have this table Cart, which contains some columns and one of them is CartID (it is not the primary key thou). And I would like to block insertions in the table Cart with a specific CartID (let's say 100), while I'm doing work on another table in the DB.

The most powerful approach is to wrap all the basic operations like INSERT, DELETE, UPDATE and SELECT into the set of the stored routines. That acts like an API to the database and can be extended by any desired checkouts/conversions/calculations you ever want. If parameters passed to the stored routine meet some conditions you can INSERT them. Otherwise you reject those data without inserting.

Same effect can be achieved by trigger BEFORE INSERT that look like this:

BEGIN
  IF NEW.id IN (100, 200, 300, 456)
    THEN SET NEW.id = 1;
  END IF;
END

where id=1 is already existent record and id column have UNIQUE constraint. But triggers in general are unclear and non-transparent tool that can be very difficult to debug. Also autoincrements and triggers can interfere with unforeseen consequencies so it's better to avoid triggers as possible.


Consecutive dates count I have table data like below:

CLIAM_KEY   MBR_ID  SERV_FROM
clm1        mem1    01-JAN-18
clm2        mem1    02-JAN-18
clm3        mem1    03-JAN-18
clm3        mem1    04-JAN-18
clm4        mem1    07-JAN-18
clm5        mem1    08-JAN-18
clm5        mem1    09-JAN-18
clm5        mem1    16-JAN-18

My requirement is to get the output as shown below:

mbr_id    count(consecutive_dates)
mem1      4
mem1      3
mem1      1

Count of the dates must be consecutive and must start counting afresh when there is no consecutive date.

From the sample data, we have dates from 01-JAN-18 to 04-JAN-18 continuously. There is a gap after 4-JAN-2018 so it must stop counting after 4-JAN-2018 and start counting afresh from 07-JAN-2018. Since 7th, 8th, and 9th are consecutive, count must come as 3 and then 16th is alone date without consecutive dates so count of 16-JAN-2018 must be 1.

You made me type your data, so this is as much as you get:

create table data
(
  claim_key varchar2(10 char),
  mbr_id varchar2(10 char),
  serv_from date
);

insert into data values ('clm1', 'mem1', date'2018-01-01');
insert into data values ('clm2', 'mem1', date'2018-01-02');
insert into data values ('clm3', 'mem1', date'2018-01-03');
insert into data values ('clm3', 'mem1', date'2018-01-04');
insert into data values ('clm4', 'mem1', date'2018-01-07');
insert into data values ('clm5', 'mem1', date'2018-01-08');
insert into data values ('clm5', 'mem1', date'2018-01-09');
insert into data values ('clm5', 'mem1', date'2018-01-16');
commit;

select mbr_id, count(*)  from (
select mbr_id, sum(changed) over (partition by mbr_id order by serv_from) sum_changed from (
select mbr_id, serv_from,
  case when lag(serv_from) over (partition by mbr_id order by serv_from) - serv_from = -1
    then 0 else 1 end changed
from data
)) group by mbr_id, sum_changed;

MBR_ID       COUNT(*)
---------- ----------
mem1                4
mem1                3
mem1                1


SSAS Hierachies duplicates when browsing The dimensions in the cube are giving us duplicates, there is no error while processing the cube, but when the cube is browsed we could see duplicates.

There is a hierarchy in our dimension, the hierarchy is built like Country -- State -- Area.

In the DB, the dimension data looks something like below.

+---------+------------+-----------+
| Country |   State    |  Subarea  |
+---------+------------+-----------+
| India   | Karnataka  | Bangalore |
| India   | Telangana  | Hyderabad |
+---------+------------+-----------+

Now, when the cube is browsed with the attribute Country, we see 2 members under country as "India", when measure is selected against the attribute, the sales are shattered between these 2 values.

Can we do any work around to avoid this problem?

That behaviour is dependent on your KeyColumns setting.

Given a cube with these 2 named queries in the datasource view

Cities:

SELECT        1 AS id, 'India' AS country, 'Calcutta' AS city
UNION
SELECT        2 AS id, 'India' AS country, 'Bangalore' AS city

Sales:

SELECT        1 AS city, 5 AS salesamount
UNION
SELECT        2 AS city, 5 AS salesamount

A dimension based on cities, linked to the Sales table on the  sales.city->cities.id relation that looks like this, using the id column as KeyColumns:

The result is this:

However, if I change the KeyColumn property to country for that attribute like this:

The result is all sales are reported under one single country:

I agree the documentation is a bit terse:

  Contains the column or columns that represent the key for the
  attribute, which is the column in the underlying relational table in
  the data source view to which the attribute is bound. The value of
  this column for each member is displayed to users unless a value is
  specified for the NameColumn property.

Where you should interpret it as "there is one separate attribute member for each distinct value in the key column, even if the displayed value is the same"

Do note however that changing the key column might require you to change the NameColumn and the OrderBy properties.

Also, KeyColumns can be comprised of multiple columns acting as a unique key instead of a single key like in my example.

Sometimes when it's too risky for existing reports to change the structure already in place it might be a good option to add an extra attribute.


Why is mysql using the wrong index for order by query? Here is my table with ~10,000,000 rows data

CREATE TABLE `votes` (
  `subject_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `subject_id` int(11) NOT NULL,
  `voter_id` int(11) NOT NULL,
  `rate` int(11) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subject_name`,`subject_id`,`voter_id`),
  KEY `IDX_518B7ACFEBB4B8AD` (`voter_id`),
  KEY `subject_timestamp` (`subject_name`,`subject_id`,`updated_at`),
  KEY `voter_timestamp` (`voter_id`,`updated_at`),
  CONSTRAINT `FK_518B7ACFEBB4B8AD` FOREIGN KEY (`voter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here is the indexes cardinalities

So when I do this query:

SELECT SQL_NO_CACHE * FROM votes WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

I was expecting it uses index voter_timestamp
but mysql chooses to use this instead:

explain select SQL_NO_CACHE * from votes  where subject_name = 'medium' and voter_id = 1001 and rate = 1 order by updated_at desc limit 20 offset 100;`

type:
    index_merge
possible_keys:
    PRIMARY,IDX_518B7ACFEBB4B8AD,subject_timestamp,voter_timestamp
key:
    IDX_518B7ACFEBB4B8AD,PRIMARY
key_len:
    102,98
ref:
    NULL
rows:
    9255
filtered:
    10.00
Extra:
    Using intersect(IDX_518B7ACFEBB4B8AD,PRIMARY); Using where; Using filesort

And I got 200-400ms query time.

If I force it to use the right index like:

SELECT SQL_NO_CACHE * FROM votes USE INDEX (voter_timestamp) WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

Mysql can return the results in 1-2ms

and here is the explain:

type:
    ref
possible_keys:
    voter_timestamp
key:
    voter_timestamp
key_len:
    4
ref:
    const
rows:
    18714
filtered:
    1.00
Extra:
    Using where

So why didn't mysql choose the voter_timestamp index for my original query?

What I had tried is analyze table votes, optimize table votes, drop that index and add it again, but mysql still uses the wrong index. not quite understand what is the problem.

MySQL is using a relatively simple (simpler than other RDBMS) cost model for planning queries in which filtering your dataset has quite high priority. In your first query with the merge index it is estimated that scanning ~9000 rows is going to be necessary while the second with the index hint will require 18000. My bet would be that this weighs in the calculation enough to move the scale towards the merge. You can confirm this (or find other reasons) by turning optimizer_trace on, run your query and evaluate the results.

set global optimizer_trace='enabled=on';

-- run your query

SELECT SQL_NO_CACHE * FROM votes WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

select * from information_schema.`OPTIMIZER_TRACE`;

One remark on index_merge: in most cases you will find that it is quite expensive. Although very useful for OLAP type scenarios it might not be very well suited for OLTP because the operation can take significant time of your query and as you can see sometimes the suboptimal execution plan is actually faster.

Fortunately MySQL provides switches for optimizer so you can customize it as you wish.

For all the option you can run:

show global variables like 'optimizer_switch';

For changing one you don't have to copy paste the whole string. It works such as dict.update() in python.

 set global optimizer_switch='index_merge=off';

If possible I would also take a look at your table structure and improve. Having a ~100 byte primary key with many secondary keys is not really advised.

You have four secondary keys and some of them are superfluous for example (voter_id) index is a subset of (voter_id, updated_at)


Can I use the attach/detach database option to shut down Azure sql database until i need it I need to use Azure SQL Database once a week for a BI solution.  O load the data in on Monday, perform ETL, and then load the data into a PowerBI model.  After that I don't need the database anymore until next week.  In order to save costs can I just detach the database and attach next week?

No. Attach/detach are not supported on Azure SQL DB. A few workarounds today:

Change the pricing tier (performance level) to the lowest that can support your database (each tier has a certain size limit), you can bring it up to the require performance level when you need to use it
Export to Azure blob (very low cost storage) when not needed, import when needed later (ok for smaller databases). More information on this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export
Delete the database when not needed, restore later. Not recommended but you'll see this suggestion come up quite a bit. Be careful not to delete the server (costs you nothing if you don't have DBs in it) and depending on the pricing tier of the database, backup retention varies from 7 to 35 days. If it's past the period, you're pretty much out of luck. See difference between export/import and backup/restore here https://azure.microsoft.com/en-us/blog/azure-sql-database-built-in-backups-vs-importexport-2/


SQL Server 2016 - Parameter Sniffing Turned Off - Still seeing where a recompile improves performance We are on SQL Server 2016 and have turned Parameter Sniffing off, since at times it caused major performance issues. I am seeing that a few stored procedures run in 30 secs, When they are re-compiled they run in 2 secs. It seems to be an awful lot like the behavior we saw with Parameter Sniffing.
Any ideas why this is still happening?

bill

According to this link, turning the Parameter Sniffing option off in 2016 is equivalent to setting trace flag 4136.
You should read up on that trace flag.

Especially important information from that trace flag link:

  Enabling trace flag 4136...is equivalent to adding an OPTIMIZE FOR UNKNOWN hint

and

  Queries that meet any of the following conditions still undergo parameter sniffing even if the trace flag is set.

  The parameter is specified by using the "optimize for (@p = )" query hint.
  The query uses the "option(recompile)" query hint.
  The query is a part of a stored procedure that uses the "with recompile" option.

So by adding a recompile, you're telling the optimizer to build a plan based on whatever parameter is passed, but not to reuse the plan. With Parameter Sniffing off (and no recompile), a plan is built based off of statistics on the parameters and will be reused, even for values that would otherwise generate a different (perhaps better) plan.


Update query for Minimum absolute difference in Access [SQL] I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3.

Table_1 has the following fields:

Acct_Name
Amt

Table_2 has the following fields:

Acct_Name
Amt_1
Amt_2

Table_3 has the following fields:

Acct_Name
Final_Value

Taking the values in Table_1.Amt to be valid, I want to know which of the two values, Amt_1 or Amt_2, from Table_2 is a better match to Table_1.Amt. Amt_1 and Amt_2 are arrived at via 2 different summing algorithms (different pieces summed together to arrive at the total value). I want to use the closer match to fill Table_3.Final_Value.

For the sake of simplicity we'll say there's a 1-to-1 match for Table_1.Acct_Name to Table_2.Acct_Name.

Since it is known that for a large fraction of rows in Table_2 (Amt_1) - (Amt_2) = 0, I start with the following append query (Table_3 is empty):

INSERT INTO Table_3 ([Acct_Name],[Final_Value])
SELECT [Table_2].[Acct_Name], [Table_2].[Amt_2]
FROM Table_2
GROUP BY [Table_2].[Acct_Name], [Table_2].[Amt_2]
HAVING ((([Table_2].[Acct_Name])<>"0"));

At this point all Table_3.Final_value fields equal Table_2.Amt_2 with Table_2.Acct_Name equal to Table_3.Acct_Name. Now I just want to update any Table_3.Final_Value to Table_2.Amt_1 if the absolute value of the difference between Table_1.Amt and Table_2.Amt_1 is less than the absolute difference between Table_1.Amt and Table_2.Amt_2 where Table_1.Acct_Name equals Table_2.Acct_Name. So I run the following update query:

UPDATE Table_3,
Table_1 INNER JOIN Table_2 ON [Table_1].[Acct_Name] = [Table_2].[Acct_Name]
SET [Table_3].[Final_Value] = [Table_2].[Amt_1]
WHERE (((Abs([Table_1]![Amt]-[Table_2]![Amt_1]))<(Abs([Table_1]![Amt]-[Table_2]![Amt_2])))

Somehow when I run the above update query all rows in Table_3.Final_Value are equal. What!?

Any help would be appreciated.Thanks in advance.

The Update statement you posted doesn't include any join to table_3. This leads to a cross join and all the rows will be set to the same (rather random) value.

Here's the query that should perform the correct update:

UPDATE (Table_1 INNER JOIN Table_2 ON Table_1.Acct_Name = Table_2.Acct_Name)
INNER JOIN Table_3 ON Table_2.Acct_Name = Table_3.Acct_Name
SET Table_3.Final_Value = [Table_2].[Amt_2]
WHERE Abs([Table_1].[Amt]-[Table_2].[Amt_1])<Abs([Table_1].[Amt]-[Table_2].[Amt_2]);


Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent? I have a datafile with the following parameters:

When I execute DBCC SHRINKFILE (DBAtools_data,1), it shows the following resuls:

I am OK with that. My complaint is about DBCC SHRINKFILE (DBAtools_data,0) command. When I run it nothing changes. I thought that it will shrink the datafile to the last allocated extent, in order words the same result as above. Could somebody clarify it for me?

Without having access to the source code for SQL Server, we can only guess. So,
Edit: 0 means shrink to initial size as per page header. My speculation that it means dummy, below, is incorrect.
SET SPECULATION MODE ON
0 makes no sense. If you want to get rid of the file, you have the EMPTYFILE option. I.e., why would you want to have a file which is 0 MB in size.
I just did a test, and indeed 0 means it doesn't do anything. We could argue that an error messages would be better: "0 MB is not valid file size.", or something like that. But apparently MS decided to leave 0 as a valid valid for the command and implementing it as a dummy.
SET SPECULATION MODE OFF
I suggest you file a request with MS to either document that 0 means "dummy", or behavior change so we'll get an error message (less likely to happen).


Storage size for varchar length in Redshift So, a lot of databases will store the length prefix for a varchar field in 1 byte if the length is less than 255, and 2 bytes if it is more. For example, the MySQL docs say:

  In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

I'm wondering if the same thing is true for Redshift? I've scoured the docs, but can't find it for the life of me!

Incidentally, I ask the question because a coworker built our entire data warehouse using varchar(256) for most fields - all the restrictions of short field length, none of the benefits of 1-byte length storage (unless Redshift uses 2 bytes regardless).

According to this page Redshift varchars use "4 bytes + total bytes for characters...". The varchar stored size is likely a much smaller issue with Redshift due to columns being compressed.


Index on primary key not used in simple join I have the following table and index definitions:

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

Why are none of the indexes on munkalap_id used in the following query?

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

It's the same even if I add a filter:

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms

Many people have heard guidance that "sequential scans are bad" and seek to eliminate them from their plans, but it isn't so simple. If a query is going to cover every row in a table, a sequential scan is the fastest way to get those rows. This is why your original join query used seq scan, because all rows in both tables were required.

When planning a query, Postgres's planner estimates the costs of various operations (computation, sequential, and random IO) under different possible schemes, and picks the plan it estimates as having the lowest cost.  When doing IO from rotating storage (disks), random IO is usually substantially slower than sequential IO, the default pg configuration for random_page_cost and seq_page_cost estimate a 4:1 difference in cost.

These considerations come into play when considering a join or filter method which uses an index vs one which sequentially scans a table.  When using an index, the plan may find a row quickly via the index, then have to account for a random block read to resolve the row data. In the case of your second query which added a filtering predicate WHERE NOT lezarva, you can see how this effected the planning estimates in the EXPLAIN ANALYZE results. The planner estimates 1006 rows resulting from the join (which pretty closely matches the actual result set of 964). Given that the larger table munkalap_lepes contains about 38K rows, the planner sees that the join is going to have to access about 1006/38046 or 1/38 of the rows in the table.  It also knows the avg row width is 214 bytes and a block is 8K, so there's about 38 rows/block.

With these statistics, the planner considers it likely that the join will have to read all or most of the table's data blocks. Since the index lookups aren't free either, and the computation to scan a block evaluating a filter condition is very cheap relative to IO, the planner has chosen to sequentially scan the table and avoid index overhead and random reads as it calculates the seq scan will be faster.

In the real world, data is often available in memory via the OS page cache, and so not every block read requires IO.  It can be quite hard to predict how effective a cache will be for a given query, but the Pg planner does use some simple heuristics. The configuration value effective_cache_size informs the planners estimates of the likelyhood of incurring actual IO costs.  A larger value will cause it to estimate a lower cost to random IO and may thus bias it towards an index driven method over a sequential scan.


Does postgresql pl/lua language have luajit performance? I am trying to use PostgreSQL for a financial system and I need the best performance I can use, so I decided to use stored procedures for managing all the math and logic of the system. I have read that luajit have performance similar to C but without the "dangers" of using C.

Fortunately PostgreSQL has lua as one of its procedural languages but I don't know if this pl/lua is luajit or only lua (and therefore the performance associated to them).

So if PostgreSQL uses lua performance I would use pl/v8 instead but if pl/lua uses luajit I will stay with pl/lua.

According to Luis Carvalho, one of the developers of PL/Lua, PL/Lua can use LuaJIT.

However, you may find there are many more factors which affect the overall performance of the PL code you write, including the overhead of the language's bindings in PostgreSQL, data type conversions, familiarity of your developers with Lua vs. other languages and their ability to write performant code, and many more. There's some more discussion about performance of the PLs you may find interesting.

PL/Lua did do well in a benchmark done by Pavel Stehule.


Keep one row per ID while keeping previous order (from ORDER BY) I'm actually working on a big query and stuck at the very end.
I'm working on MySQL 5.7.

ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n
1 //   2   //    x        //    y      //  ... //   z
1 //   3   //    x        //    y      //  ... //   z
1 //   1   //    x        //    y      //  ... //   z
2 //   1   //    x        //    y      //  ... //   z
3 //   1   //    x        //    y      //  ... //   z

I would need for each ID the best (ID, SubID) pair, based on the existing order which is based on sorting on criteria 1 to n.

I had a look on several solutions but:

GROUP BY picks randomly any (ID, SubID) pair and doesn't take into account the existing order
GROUP BY + ORDER BY is not sufficient as I need to sort on complex criteria
I can't recover the subID info if I use DISTINCT

I have the feeling that the solution is fairly simple but I'm really stuck and I can't find similar problems/solutions on the Internet (maybe I just need some sleep ;) )

Just to make it clear: I need to keep 1 row per ID but in the same order as before, and to return at least ID // SubID (but it would be perfect if it could return all the criteria as well).

Thank you in advance for your help guys!

EDIT

Ok so to add a little bit of context here, my criteria are either boolean or float values that I computed in a subquery.

It looks like:

SELECT * FROM
(SELECT *,
CASE(formula) THEN 1 ELSE 0 as criteria_1,
CASE(formula) THEN 1 ELSE 0 as criteria_2,
ABS(formula), as criteria_3,
SQRT(formula) as criteria_4
FROM subquery
WHERE criteria_1 = 0
ORDER BY criteria_2 , criteria_3 DESC, criteria_4, value_z)
"Remove (ID,SubID) duplicates while keeping the previous order"

I want to replace the last sentence (the one between quotes) by something, but GROUP BY ID, SubID just randomly picks any (ID, SubID) value.

You possibly need something along the lines of groupwise max.


oracle blocking sessions I am trying to investigate some concurrency issues on Oracle 11 RAC.
So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking everyone else and what it is waiting for.  However, to take advantage of that view, I need to catch the problem as it is happening.  So I'm looking for a way to query oracle for historic data which would give me data similar to what "blocking sessions" screen has to offer in Enterprise Manager.

Assuming you are licensed to use the AWR, you could query either the gv$active_session_history view for more recent data or dba_hist_active_sess_history for older data.  gv$active_session_history will have a snapshot of what every active session was doing at the top of each second and will hold that data for a few hours.  dba_hist_active_sess_history will have the data from, essentially, every 10th snapshot in gv$active_session_history but it will be retained for much longer (whatever your AWR retention period is).

Both of these views will have a BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, and BLOCKING_INST_ID that will tell you which session was holding the lock that the blocked session was waiting on.  You can then look too see what queries the blocking session was running at around the same time-- the blocking session may well not be active in the particular snapshot you're looking at but it probably was in the snapshot a second or two earlier.


Postgres: insert new row in table if the table does not have records where column_name = 'abc' occurs N times? I would like to do this in one transaction
So let's take table with columns:
user -> [first_name, last_name]

The use case is that we don't want to insert a new record into a table, if the table already has records where user.first_name = 'ABC' occurs 5 times;
INSERT INTO user VALUES
('john', 'doe') ...

Apologies if this is simple, am a developer and SQL is not my strong point.

You can do something with triggers
Or you do somewhat complexer INSERT
Which allows only 5 John in your table

CREATE TABLE "user" ("first_name" varchar(16),"last_name" varchar(50) )

✓

insert into "user"
select 'john', 'doe'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe2'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe3'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe4'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe5'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe6'
where (select count(*) from "user" where "first_name" = 'john') < 5

✓

SELECT * FROM "user"

first_name | last_name
:--------- | :--------
john       | doe
john       | doe2
john       | doe3
john       | doe4
john       | doe5

db<>fiddle here


QUERY - pivot multiple columns, variable number of rows I have a table that looks like this:

RECIPE     VERSION_ID     INGREDIENT    PERCENTAGE
4000       100            Ing_1          23,0
4000       100            Ing_100         0,1
4000       200            Ing_1          20,0
4000       200            Ing_100         0,7
4000       300            Ing_1          22,3
4000       300            Ing_100         0,9
4001       900            Ing_1           8,3
4001       900            Ing_100        72,4
4001       901            Ing_1           9,3
4001       901            Ing_100        70,5
5012       871            Ing_1          45,1
5012       871            Ing_100         0,9
5012       877            Ing_1          47,2
5012       877            Ing_100         0,8
5012       879            Ing_1          46,6
5012       879            Ing_100         0,9
5012       880            Ing_1          43,6
5012       880            Ing_100         1,2

There are 100 ingredients per recipe/version. I'd like to display the data from this table like this:

RECIPE     INGREDIENT_Vxxx     PERCENTAGE_Vxxx     INGREDIENT_Vyyy     INGREDIENT_Vyyy (ETC)
4000       Ing_1               23,0                Ing_1               20,0
4000       Ing_100             0,1                 Ing_100              0,7

Because in different versions of recipes, ingredients could be removed or added, I'd like to display both ingredient and percentage per version per recipe. There's also the difficulty that different recipes have different number of versions.

I'm not even sure if this is possible at all or where to begin. Maybe with the PIVOT function?

Could anyone please point me in the right direction?

The problem here seems to me to be largely more of a scoping issue - you are likely having difficulty solving this problem on account of the requirements not being defined well enough.  With the description and sample data provided, there are at least three partial solutions, none of which may be applicable to your particular use cases.  With the test data set up as follows,

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'Recipe'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.Recipe;
    CREATE TABLE dbo.Recipe
    (
        Recipe          INTEGER NOT NULL,
        VersionID       INTEGER NOT NULL,
        Ingredient      VARCHAR( 8 ) NOT NULL,
        Percentage      DECIMAL( 5, 2 )
    );

    INSERT INTO dbo.Recipe ( Recipe, VersionID, Ingredient, Percentage )
                SELECT  4000, 100, 'Ing_1', 23.0
    UNION ALL   SELECT  4000, 100, 'Ing_100', 0.1
    UNION ALL   SELECT  4000, 200, 'Ing_1', 20.0
    UNION ALL   SELECT  4000, 200, 'Ing_100', 0.7
    UNION ALL   SELECT  4000, 300, 'Ing_1', 22.3
    UNION ALL   SELECT  4000, 300, 'Ing_100', 0.9
    UNION ALL   SELECT  4001, 900, 'Ing_1', 8.3
    UNION ALL   SELECT  4001, 900, 'Ing_100', 72.4
    UNION ALL   SELECT  4001, 901, 'Ing_1', 9.3
    UNION ALL   SELECT  4001, 901, 'Ing_100', 70.5
    UNION ALL   SELECT  5012, 871, 'Ing_1', 45.1
    UNION ALL   SELECT  5012, 871, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 877, 'Ing_1', 47.2
    UNION ALL   SELECT  5012, 877, 'Ing_100', 0.8
    UNION ALL   SELECT  5012, 879, 'Ing_1', 46.6
    UNION ALL   SELECT  5012, 879, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 880, 'Ing_1', 43.6
    UNION ALL   SELECT  5012, 880, 'Ing_100', 1.2;

    ALTER TABLE dbo.Recipe
    ADD CONSTRAINT PK__Recipe
        PRIMARY KEY CLUSTERED ( Recipe, VersionID, Ingredient );

    CREATE NONCLUSTERED INDEX IX__Recipe__Recipe__VersionID
        ON  dbo.Recipe ( Recipe, VersionID )
    INCLUDE ( Percentage );
END;
GO

we can use our new table to explore some possible solutions.  Expanding on the sample output, we add the next recipe into the result set to illustrate the difficulty with the question.

RECIPE --- INGREDIENT_V100 --- PERCENTAGE_V100 --- INGREDIENT_V200 --- INGREDIENT_V200
4000       Ing_1               23,0                Ing_1               20,0
4000       Ing_100              0,1                Ing_100              0,7
4001       Ing_1                8,3                Ing_1                9,3
4001       Ing_100             72,4                Ing_100             70,5

Columns %_V100 and %_V200 make perfect sense in the case of the 4000 recipe, but quickly lose their meaning as additional recipes are added.  The 4001 recipe would need new and separate columns to properly label the data by version, but since the version numbers differ for each and every recipe, that path leads us to a very sparse result set which would be downright annoying to use, or we must alias the columns, losing the version number data.

Solution 1:

Starting with what I feel is the absolute worst way of going about this, let's look at the sparse result set.  For the sample data, we would be attempting to generate a query which would look like something along the following lines:

SELECT  p.Recipe,
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300],
        [Ingredient_v871] = CASE WHEN p.[871] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v871] = p.[871],
        [Ingredient_v877] = CASE WHEN p.[877] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v877] = p.[877],
        [Ingredient_v879] = CASE WHEN p.[879] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v879] = p.[879],
        [Ingredient_v880] = CASE WHEN p.[880] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v880] = p.[880],
        [Ingredient_v900] = CASE WHEN p.[900] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v900] = p.[900],
        [Ingredient_v901] = CASE WHEN p.[901] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v901] = p.[901]
FROM (  SELECT  r.Recipe,
                r.VersionID,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300], [871], [877], [879], [880], [900], [901] ) ) p
ORDER BY p.Recipe;

On account of the variable number of versions, we can use some dynamic SQL to generate and execute the query.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                    + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                    + ' ELSE p.[Ingredient] END, '
                    + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe,
                        r.VersionID,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This result set plainly sucks.  Here's an SQL Fiddle which displays the results, take a peek and let's move on.

Solution 2:

With the sparse result set proving to be next to useless, we can accept losing the version numbers of the recipes and simply order them in ascending version number. For the purpose of the example we'll alias alphabetically, so that versions 100, 200 and 300 of recipe 4000 will receive A, B and C designations, whereas versions 900 and 901 will receive just A and B.  The query we would like to generate for this should look somewhat like the following:

SELECT  p.Recipe,
        [Ingredient_vA] = p.[Ingredient], [Percentage_vA] = ISNULL( p.[Percentage_vA], 0 ),
        [Ingredient_vB] = p.[Ingredient], [Percentage_vB] = ISNULL( p.[Percentage_vB], 0 ),
        [Ingredient_vC] = p.[Ingredient], [Percentage_vC] = ISNULL( p.[Percentage_vC], 0 ),
        [Ingredient_vD] = p.[Ingredient], [Percentage_vD] = ISNULL( p.[Percentage_vD], 0 )
FROM (  SELECT  Lvl = 'Percentage_v' + CHAR( 64 +
                    DENSE_RANK() OVER (
                        PARTITION BY r.Recipe
                        ORDER BY r.VersionID ) ),
                r.Recipe,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.Lvl IN ( [Percentage_vA], [Percentage_vB], [Percentage_vC], [Percentage_vD] ) ) p
ORDER BY p.Recipe;

Similarly to solution one, dynamic SQL can be leveraged to accomplish this.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Percentage_v' + CHAR( 64 + a.Lvl ) + '], '
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK()
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Col, LEN( b.Col ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CHAR( 64 + a.Lvl ) + '] = p.[Ingredient], '
                    + '[Percentage_v' + CHAR( 64 + a.Lvl ) + '] = ISNULL( p.[Percentage_v'
                    + CHAR( 64 + a.Lvl ) + '], 0 ),'
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK()
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Col );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  Lvl = ''Percentage_v'' + CHAR( 64 +
                            DENSE_RANK() OVER (
                                PARTITION BY r.Recipe
                                ORDER BY r.VersionID ) ),
                        r.Recipe,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.Lvl IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This ends up in a much prettier result set, as can be seen in this SQL Fiddle, despite the loss of the specific version numbers of each recipe.

Solution 3:

If the loss of the version numbers can not be tolerated, a hybrid approach can be implemented, though the results from each call would be limited to only a single recipe.  In effect, our goal SQL would be similar to the first solution, but with an expressly defined Recipe number.

SELECT  p.Recipe,
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300]
FROM (  SELECT  r.Recipe,
                r.VersionID,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r
        WHERE   r.Recipe = @Recipe ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300] ) ) p
ORDER BY p.Recipe;

Generation could be handled as follows:

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @Param          NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX ),
        @Recipe         INTEGER = 4000;

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                + ' ELSE p.[Ingredient] END, '
                + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @Param = N'@Recipe  INTEGER';

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe,
                        r.VersionID,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r
                WHERE   r.Recipe = @Recipe ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @Param, @Recipe = @Recipe;
GO

The results could then be accessed per recipe, as shown in this SQL Fiddle or this one.


Oracle generated column aliases different on two DB's We have a legacy application we cannot modify that connects to Oracle 11g and at some point runs a query and returns a result. The application however is using the "generated" column name from Oracle to read the result.

Consider the following query:

select nvl(1,0.0) from DUAL;

As this query does not specify an alias, the generated column name would be "nvl(1,0.0)"

However on another server the generated column name is "nvl(1,0)" (notice 0 and not 0.0) and the application fails.

Is there a configuration that can be changed for Oracle? I've searched for formatting and locale configurations and they are equal on both servers.

Any help would be appreciated

It turns out there's a parameter called cursor_sharing that was set to FORCE instead of EXACT

select nvl(1,0.0) from DUAL;

The query above returns the following depending on the value of the parameter:

FORCE=NVL(1,0)
EXACT=NVL(1,0.0)


Shrinking the SYSTEM tablespace in Oracle Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table.

We have truncated SYS.AUD$ but the datafile is still very big (~30G).

Resize doesn't work because the file contains used data beyond requested RESIZE value

What should I do here?

Here's our version information:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE  11.2.0.1.0  Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Query the DBA_EXTENTS view to see what objects are present towards the end of the data files. It is possible that they are not system objects, or are system objects which can be safely modified so that they are towards the beginning of the files.

However I suspect that you have a big problem here -- you may be looking at creating a new database and moving all your user objects to that.


Paging and grouping using CTE? Given the following code, from the answer to this question, how can I adapt it for paging?  I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a problem that was already over my head, I really have no idea where to start in merging the two solutions.

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
)
select *
from cte
where tags like '%test1%'

I was able to do this using multiple CTEs, similar to the following:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
),
results as (
    SELECT ROW_NUMBER() OVER (ORDER BY AppID) AS RowNumber, * FROM cte
        where tags like '%test1%'
)
select *
from results
WHERE (RowNumber >  @pageStart) AND (RowNumber <=  @pageStart + @pageLength)


Why does MySQL server 8+ create .ini config files in a MySQL server 5.5 folder? I have been struggling with MySQL timezone issues for a while I think I finally know why. In the past 2 days my timestamps were saved 2 hours behind my actual machine time (2+ UTC Jerusalem).

I tried running these commands:

SELECT @@global.time_zone;
SELECT @@session.time_zone;

And they both return SYSTEM, which means MySQL configures it's timezone based on a .cnf/.ini file. Problem is that I have no such file. I have went through all the "default locations" MySQL is supposed to read it's configuration from and there was not one file. My machine right now is 9:15 AM (UTC +2) but for some reason MySQL timestamps save at 7:15 AM.

In a fluke I caught this piece of condemning info:

Apparantly the server reads it's configuration from .ini inside a 5.5 version of MySQL. But it didn't exist! Only after I used the "Options file" inside workbench for the first time it created it. Here is where:

Mind you I never used 5.5. I am too young for that (though I have and old installation of WAMP from the days I screwed around with wordpress). I tried to diagnose the situation by trying to find my installed version of MySQL but I got this error:

mysql: [ERROR] Found option without preceding group in config file C:\Program Files\MySQL\MySQL Server 8.0\my.cnf at line 1.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

Any reason why 8 MySQL server wishes to read .ini files from a 5.5 folder that didn't even exist?

After alot of digging I found the answer.

Inside %AppData%..\MySQL I found "server_instances.xml". For some reason it listed MySQL server version as 5.5 even though workbench AND command line both showed it is 8.0, and 5.5 was never installed in my machine.

I searched for all mentions of 5.5 and swapped them for 8.0. Saved, rebooted machine (just to be sure) and opened workbench. Created a new config file via the Options File under the Administration in workbench. Everything works :)


SQL Server - Get the Date Ranges I have a table with start date and end date. For example,
PlaceID | StartDate  | EndDate
1       | 2020-01-01 | 2020-01-04
1       | 2020-01-10 | 2020-01-15
2       | 2020-01-03 | 2020-01-06
2       | 2020-01-20 | 2020-01-23

I need to write a script to get the date ranges that is not in the table for each PlaceID. (For now considering the month of January.)
For PlaceID 1 - I need the output date ranges as
2020-01-05 to 2020-01-09
2020-01-16 to 2020-01-31

For PlaceID 2 - I need the output date ranges as
2020-01-01 to 2020-01-02
2020-01-07 to 2020-01-19
2020-01-24 to 2020-01-31

What is the SQL logic behind this that I can use?

This is essentially an islands problem, except you are trying to find all the rows that don't belong to an island (these are gaps, but conceptually I find it easier to think about the islands). Basically, we want to find all of the possible days in your overall range, then identify the days that are "covered" within that range. The end result you want are all of the islands of days that are not covered.
My solution assumes you are on a currently supported version of SQL Server (2012+). Please always use a tag for the minimum version of SQL Server you need to support, since that can change the answer and prevent people from wasting time.
-- sample table + data:
DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200104'),(1,'20200110','20200115'),
(2,'20200103','20200106'),(2,'20200120','20200123');

-- input parameters (place you care about, start and end of date range):
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101',
        @EndDateOfInterest   date = '20200131';

;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d)
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT MIN(d), MAX(d) -- for each island, grab the start and end
  FROM islands
  GROUP BY island
  ORDER BY MIN(d);

Generating the contiguous range of dates is a topic of its own. I like the recursive CTE option because it's quick and easy for me to remember, and doesn't require any other objects to exist. But it has a quirk: If the range of days could be more than 100, you'll need to add OPTION (MAXRECURSION x) to the end of the statement (where x is the largest number of days you would need to support). If x > 32,767 (89 years or so), x will need to be 0.
If you don't like recursive CTEs, but you already have a calendar table (say, dbo.Calendar with a [date] column), the date_range CTE can simply be:
;WITH date_range(d) AS
(
  SELECT [date] FROM dbo.Calendar
  WHERE [date] >= @StartDateOfInterest
    AND [date] <= @EndDateOfInterest
),

If you don't like recursive CTEs, and don't have a calendar table, but you do have a numbers table (say, dbo.Numbers, with an int column named num), you can do this for the initial CTE:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, num-1, @StartDateOfInterest)
  FROM dbo.Numbers
  WHERE num >= 1 ORDER BY num
),

And if you don't like recursive CTEs and you don't have a calendar or numbers table, you can do this:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @StartDateOfInterest)
  FROM sys.all_columns
  ORDER BY [object_id]
),

If you don't like recursive CTEs, you don't have a calendar or numbers table, and the caller doesn't have access to catalog views like sys.all_columns, I give up.
More information here:

Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3
The SQL Server Numbers Table, Explained - Part 1
The SQL Server Numbers Table, Explained - Part 2
Creating a date dimension or calendar table in SQL Server


Amazon RDS replication with external slave I've recently configured a replication with an external MySQL master and RDS as slave but now I need to reverse that config and use RDS as master and the other instance as slave.
Actually I use a vpn and the other instance is outside AWS.

I changed the server host and password but I got this error:

  141103 11:35:44 [ERROR] Error reading packet from server: Binary log
  is not open ( server_errno=1236) 141103 11:35:44 [ERROR] Slave I/O:
  Got fatal error 1236 from master when reading data from binary log:
  'Binary log is not open', Error_code: 1236 141103 11:35:44 [Note]
  Slave I/O thread exiting, read up to log 'mysql-bin.000007', position
  284079763 141103 11:37:09 [Note] Error reading relay log event: slave
  SQL thread was killed

Perhaps I didn't get the right log filename and postion? but I can't find them in to RDS

Many thanks for your help

For RDS to have binlogs, there are a couple non-obvious tricks.  First, you need to set the "backup retention period" to at least 1 day.  This turns on binlogging.

Then, in order to prevent a ridiculous rollover rate in the logs:

call mysql.rds_set_configuration('binlog retention hours', 24);

Here's some documentation that Amazon provides for dumping the database and starting up the replication:  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html


Get the rows where id_user_rq exists in id_user_ap and viceversa I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_rq if not exists in id_user_ap as the title said. I need to get the rows where id_user_rq = 1 and id_user_ap = 2 and id_user_rq = 2 and id_user_ap = 1 meaning that I send a invitation and the other part accept me and inverse the relation, any help?

Please try this:

SELECT A.* FROM
(SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
LEFT JOIN
(SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
ON
(A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
WHERE B.id_user_ap IS NOT NULL;


Mysql Data truncation for Valid DECIMAL create table test(id INT, value DECIMAL(25,6));
insert into test values (16, 10e16);
insert into test values (17, 10e17);  -- Works till here
insert into test values (18, 10e18);  -- Fails here. Data truncation: Out of range value for column 'value'
insert into test values (19, 10e19);

How is this possible?


Why does the delete/update block each other in this case? The following drives me crazy.
I have:

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id=1;

id is the PK of the table.
These statements execute without blocking each other. Makes sense since they refer to different rows.
Next:

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id in (1,2);

These also execute without blocking.

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id in (1,2,3,4);

These block each other but not always! I can not understand this. These refer to different rows why do they block each other?

Answer based on comments by a-horse-with-no-name
You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.


In Change Data Capture (CDC), how to configure retention with success? We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar).

When we first configured it (with default options), it worked fine. But since the day we changed the retention period from default to something like 25 days, it stopped working correctly. It would run for many hours (15 to 20 hours). So, after a few days we disabled the job (not good, but we had other priorities to deal with than the data retention).

Now after 7 months, the disk is close to getting full. So we need to re-configure it and start purging data. A few days ago, just to test how it works, I ran a test run. I checked the timestamp of the oldest LSN and added a month to it and changed the retention period to something like 8 months. So when the clean up job runs, it should come back with success immediately, as there is no data older than 7 months. But it does not. Its still runs for many hours.

Now I am starting to wonder if the retention period (column) in msdb.dbo.cdc_jobs table works or not. Aaron Bertrand mentioned in his blog that he did not have perfect success with this setting. Maybe this setting is not used by the Clean up job.

Before I run some intensive tests on a Development machine, I wanted to check with you guys.

I answered this question in my blog at Sqltimes.wordpress.com
As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082;
UPDATE msdb.dbo.cdc_jobs SET retention = '666000' WHERE job_type = 'cleanup'

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this date. You can stop the CDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help,
_UB


Is there any way to trace the optimizer's work in Oracle? Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in Oracle through which I can see what are the alternative plans the optimizer has ever considered (and pruned because they are more expensive than the plan that the optimizer chooses to execute)?

Yes! This is called a 10053 trace in Oracle parlance.  It's more formally known as "Tracing of Optimizer Computations".

Check out MOS doc 225598.1, or Jonathon Lewis' blog post here: https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/

There's loads of hits for "10053 trace" so if you can't find what you need, it's probably out there in search-land.


Deny dbo schema permission I'm trying to deny all permissions on the dbo schema for a particular user, as I only want that user to access a specific schema. When I try the following (using sa):

DENY CONTROL, ALTER ON SCHEMA :: dbo TO AppUser

I get this message:

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
  information_schema, sys, or yourself.

Is it possible to deny all dbo schema permissions for a user?

EDIT: I've just had a thought - there are a few tables and views in the dbo schema that are owned by public (not my idea and I can't change them). Could this be why I am getting the message? If so, is there any way I can get around this? The purpose of denying access to dbo is to prevent unnecessary access to these tables.

EDIT: I think I got confused with the public role - the actual owner of the tables and views I mentioned is dbo, but the public role has SELECT, INSERT, UPDATE etc permissions. I've tried reproducing the message on a new database which has a table with public role permissions as in the other database, but this does not show the message - it is successful and the denied user can no longer see those tables. So to answer my question, yes it is possible, but I'm not sure why it isn't working for me.

Restarting Management Studio fixed it.

It looks like it was some sort of glitch - for some reason, it was acting like I was using AppUser to run the query. I double checked and I was definitely logged in using sa, so I'm not sure what happened.

I realised when I tried to change permissions for the new schema, which had previously completed successfully and was now giving the same message. I tried to remove the login and just try setting it up again, and it gave the error that the user was still logged in, despite me closing all instances of Management Studio aside from the one I was currently using with the sa account. I tried to kill the session using the advice from this post, but it gave an error about not having permission (at this point I double checked yet again that I was definitely using the sa account). In the end I closed the current instance too, logged back in as sa, and it has worked fine since - I can grant/deny on the new schema and dbo for AppUser.


Using Partial String match from one query results in where clause for another SQL Server 2012 I need results field of query1 to drive

I have one query that produces a table of several columns, namely "Time_stamp", "Value", and "Name" from two existing tables.

Select TOP 20
    MshiftV1.Field_ID
    ,MshiftV1.Timestamp
    ,LEFT(MshiftF1.Name,12) as Name
    ,MshiftV1.Value
    ,MShiftF1.ID
From
    [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Values_5Min_RealTime_V6] as MshiftV1
Inner Join
    [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Fields_5Min_RealTime_V6] as MshiftF1 on MshiftV1.Field_ID = MshiftF1.ID
Where
    MshiftV1.Quality = 1
    and MshiftV1.Value not like '%INF%'
    and MshiftV1.Value not like '%IND%'
    and MshiftF1.Name not like '%_Splice_%'
order by
    MshiftV1.Timestamp, ABS(convert (float, MshiftV1.Value)) desc

I have another, separate query for two different tables from the first, and it produces Fields like "Time_Stamp", "Correlation_Pair", and "Percent_Change".

select
    BSCv1.Timestamp "Field Timestamp"
    ,BSCV1.Value as Correlation_Pair
    ,BSCV2.Value "Percent Change"
    ,BSCF1.Name "Field Name"
    ,BSCF1.ID "DEV Name ID"
    ,BSCV1.Field_ID "Field ID"
    ,BSCF2.Name "Value Name"
    ,BSCV2.Field_ID "Value ID"
    ,BSCF2.ID "V ID"
    ,BSCv2.Timestamp "Value Timestamp"
From
    [BabyCare].[dbo].[BSC_v3_values] as BSCV1
inner join
    [BabyCare].[dbo].[BSC_v3_fields] as BSCF1 on BSCV1.Field_ID = BSCF1.ID
inner join
    [BabyCare].[dbo].[BSC_v3_values] as BSCV2 on BSCv1.Timestamp = BSCv2.timestamp
inner join
    [BabyCare].[dbo].[BSC_v3_fields] as BSCF2 on BSCV2.Field_ID = BSCF2.ID
                                              and BSCV1.Quality = 1
                                              and BSCF1.Name like '%Dev_N%'
                                              and BSCF2.Name like  '%Dev_V%'
                                              AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
                                              and BSCv1.Timestamp = BSCv2.Timestamp
where
    BSCV1.Quality = 1
    and BSCF1.Name like '%Dev_N%' and BSCF2.Name like  '%Dev_V%'
    AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
    and BSCv1.Timestamp = BSCv2.Timestamp
order by
    ABS(convert(float,BSCV2.Value)) desc, BSCv1.Timestamp Desc

Both queries work great, but I have a recent need to combine results so that output from first query drives the second query. Specifically, I need the second query to only return records where the first 12 characters in the "BSCV1.Value" field are equal to the first 12 characters of the output of the first queries field "MshiftF1.Name", AND where "Timestamps" are equal.

Any thoughts?

You could try using CTE. This will take your first query as base for the CTE and then use it to join on the second query. I have not tested this code but I think it may work:

WITH Answer (Field_ID, Timestamp, Name, Value, ID)
AS
(
SELECT TOP 20 MshiftV1.Field_ID
    , MshiftV1.Timestamp
    , LEFT(MshiftF1.Name,12) AS Name
    , MshiftV1.Value
    , MShiftF1.ID
FROM [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Values_5Min_RealTime_V6] AS MshiftV1
INNER JOIN [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Fields_5Min_RealTime_V6] AS MshiftF1
    ON MshiftV1.Field_ID = MshiftF1.ID
WHERE MshiftV1.Quality = 1
    AND MshiftV1.Value NOT LIKE '%INF%'
    AND MshiftV1.Value NOT LIKE '%IND%'
    AND MshiftF1.Name NOT LIKE '%_Splice_%'
    --ORDER BY MshiftV1.Timestamp, ABS(convert (float, MshiftV1.Value)) DESC --Order by not needed in CTE
)
SELECT BSCv1.Timestamp AS "Field Timestamp"
    , BSCV1.Value AS Correlation_Pair
    , BSCV2.Value AS "Percent Change"
    , BSCF1.Name AS "Field Name"
    , BSCF1.ID AS "DEV Name ID"
    , BSCV1.Field_ID AS "Field ID"
    , BSCF2.Name AS "Value Name"
    , BSCV2.Field_ID AS "Value ID"
    , BSCF2.ID AS "V ID"
    , BSCv2.Timestamp AS "Value Timestamp"
FROM [BabyCare].[dbo].[BSC_v3_values] AS BSCV1
INNER JOIN Answer
    ON Answer.Name = LEFT(BSCV1.Value, 12)-- This may be the link you are seeking
        AND DATEADD(MINUTE, -10, Answer.Timestamp) = BSCv1.Timestamp
INNER JOIN [BabyCare].[dbo].[BSC_v3_fields] AS BSCF1
    ON BSCV1.Field_ID = BSCF1.ID
INNER JOIN [BabyCare].[dbo].[BSC_v3_values] AS BSCV2
    ON BSCv1.Timestamp =BSCv2.timestamp
INNER JOIN [BabyCare].[dbo].[BSC_v3_fields] AS BSCF2
    ON BSCV2.Field_ID = BSCF2.ID
        AND BSCV1.Quality = 1
        AND BSCF1.Name LIKE '%Dev_N%'
        AND BSCF2.Name LIKE '%Dev_V%'
        AND RIGHT(SUBSTRING(BSCF1.Name, 5, 5), 3) = RIGHT(SUBSTRING(BSCF2.Name, 5, 5), 3)
        AND BSCv1.Timestamp = BSCv2.Timestamp
WHERE BSCV1.Quality = 1
    AND BSCF1.Name LIKE '%Dev_N%'
    AND BSCF2.Name LIKE '%Dev_V%'
    AND RIGHT(SUBSTRING(BSCF1.Name, 5, 5), 3) = RIGHT(SUBSTRING(BSCF2.Name, 5, 5), 3)
    AND BSCv1.Timestamp = BSCv2.Timestamp
ORDER BY ABS(convert(float,BSCV2.Value)) DESC, BSCv1.Timestamp DESC


MySQL - alter huge table without slowing down server I'm trying to alter a pretty large table (25GB including index) by adding a column & modifying an index, but in addition to taking a very long time (> 1 hour), which is ok, it affects the whole system, making queries on other tables more than 100 times slower, which is a huge issue and forced me to cancel the operation.

The server is pretty solid (196GB RAM with only 70GB used, SSD RAID disks with 300GB free space). Storage engine is MyISAM.

Is there any way to give the ALTER TABLE very low priority so that it doesn't dramatically affect the rest of the operations (I don't care if it takes twice the time)? Is it even normal that it does?

Upgrade to InnoDB.  Tips and caveats

Upgrade to at least 5.6

After those, you can do most ALTERs with almost no downtime.  See ALGORITHM=INPLACE.


PostgreSQL high disk usage I have a PostgreSQL database on a docker container with 50MB of data:
SELECT pg_size_pretty(pg_database_size('db_1'));
 pg_size_pretty
------------------
 50 MB

but the volume size is 8.5GB :
sudo du -hd1 db_volume
8.5G    db_volume

and my docker-compose is like this:
version: '3.8'

services:
  r3-db:
    image: postgres:12
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: PASSWORD
      POSTGRES_USER: USER
      POSTGRES_DB: db_1
    volumes:
      - ./db_volume/:/var/lib/postgresql/data:Z

I try using VACUUM(FULL, ANALYZE, VERBOSE) on any table but the file size of the database doesn't change!
The volume contains many files named core.NUMBER with size of 145MB,

How can I reduce disk usage of the database?

Those are core dumps which are generated when PostgreSQL crashes.  You can use them with gdb to perhaps figure out why it is crashing.  You can delete them.  You can save one to use it to figure out why PostgreSQL keeps crashing, as that should absolutely not be happening.  Also look in the PostgreSQL log file for info on why it is crashing.


Design: how to store lots of booleans? What is the most optimal way to store lots (up to 300) of properties about an object? All properties determinate presence of a factor. Like, "has dog", "has cat", "has garden", "has N".

Initially I was thinking about a table with 300 columns:

create table my_objects (
    has_dog enum('0','1') NOT NULL,
    has_cat enum('0','1') NOT NULL,
    has_garden enum('0','1') NOT NULL
);

Don't bother the relevance of the enum type, it's there only for extra self-explanatory reasons.

But having those has_ x 300 columns makes my guts feel nervous.

Any advice is much appreciated.

EDIT

Since many people started asking correct questions, here's the additional info:

The db will mostly exclusively handle SELECT queries. Selects consist of combination of has_{prop} = 0|1 AND/OR has_{prop} = 0|1. Like heavy filtering.
There will be Sphinx/Solr layer on top of db.
The amount of properties is constant. So if there's one row in table my_objects, it will have those 300 columns filled, either with default values, or defined.
my_objcets table will hold no more than 1 mil of objects.

"Just" 300? That might not really be a problem, actually. How many rows do you expect in the table? If not that many, say, less than a million, then doing that might not be a huge problem.

Or if you're using PostgreSQL, as noted, you can use an array of booleans. That, I think, would be preferrable to putting each column individually in the schema.

The "best" way, however, would be a bitfield. Say, an array of a hundred 4-byte integers, with each value being one of the bits in one of those fields.  So your application could have a map of tupes, something like:

has["dog"] = (25, 18)

Noting that 'dog' would be in the 26th field (zero based), in the 19th bit. When you want to query if a record has a dog, you'd need to pull the appropriate tuple and construct an SQL statement like...

(fieldNum, bitNum) = has["dog"] # python syntax

SELECt * from tbl WHERE (has[fieldNum] & (1<<bitNum)) > 0

Finally, Postgres also has bit strings, which I've never used, but it looks like they might do the job as well. http://www.postgresql.org/docs/9.4/interactive/datatype-bit.html