Retrieving latest record using DISTINCT ON is slow Using Postgres 9.4.
I have a table cartests with 5.5M rows. Each row is a car test:

\d log.cartests;
             Table "log.cartests"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 carid       | integer                  | not null
 timestamp   | timestamp with time zone | not null
 exhaust     | varchar                  |
 brakes      | varchar

 Indexes:
    "cartests_pkey" PRIMARY KEY, btree (id, "position", "timestamp")
    "cartests_carid_idx" btree (carid)
    "cartests_timestamp_idx" btree ("timestamp")

I need to retrieve the latest log for each car.

SELECT DISTINCT ON (carid) carid, timestamp, exhaust, brakes
FROM log.cartests
ORDER BY carid, timestamp DESC;

This one takes ~ 6 seconds. Running EXPLAIN ANALYZE gives:

Unique  (cost=933787.06..961490.34 rows=472 width=16)
         (actual time=4951.347..6072.534 rows=476 loops=1)
  ->  Sort  (cost=933787.06..947638.70 rows=5540656 width=16)
            (actual time=4951.345..5782.466 rows=5540656 loops=1)
        Sort Key: carid, "timestamp"
        Sort Method: external merge  Disk: 162504kB
        ->  Seq Scan on cartests
            (cost=0.00..123810.56 rows=5540656 width=16)
            (actual time=0.009..620.988 rows=5540656 loops=1)
Planning time: 0.107 ms
Execution time: 6095.598 ms

Why must it sort if I have an index on both carid and timestamp?
Is there a better method to use here?

Why must it sort if I have an index on both carid and timestamp?
  Is there a better method to use here?

Because you don't have an index on both carid and timestamp!  You have an index on carid, and a different index on timestamp.  If you want the query to use both, you need an index on (carid, "timestamp") as ypercube mentioned.

Likewise, I'd try switching from the DISTINCT ORDER BY to a GROUP BY, and benchmarking each.

With every tuning question, try writing the query in a few different ways, and compare them against each other - how fast are they, and how much resources do they use?


Left join on one or the other column without table-scanning I have this huge sql with a lot of left joins. Mostly on the tables primary key, so no problem. On one table though, my key could exist in either one column or the other.
Syntax-wise that is no problem:

LEFT JOIN table2 blk ON (t.rid=blk.transact OR t.rid=blk.transactchild)

I have created two indices on table2, one for each of the keys, but my Sybase ASE 15.7 decides to table scan in stead. So I tried creating a single index with both of the keys and forcing it in the join, but that didn't help on performance. Showplan indicates, that the index is being used, but a scan takes forever. Is there a better way to join on either one or the other column?

When you have two separate indexes on transact and on transactchild the server can't use them efficiently when searching for (t.rid=blk.transact OR t.rid=blk.transactchild).

The classic way to handle this type of OR search is to split it into two simple searches and then UNION results together.

So, you can try something like this:

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transact)

UNION

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transactchild)

In this case individual simple query should be able to use corresponding index, but the extra UNION step can be quite expensive, especially if it requires a sort.

If your data is such that you know that UNION ALL would produce correct result, then use UNION ALL, which is much more efficient than UNION. UNION eliminates duplicates, which is expensive. UNION ALL simply outputs results of two queries one after another.

If values in blk.transact do not overlap with values in blk.transactchild, then you can use UNION ALL.


Set up Firebird with shared storage? I am new to firebird and confused with SuperServer SuperClassic thing.

But what i wonder is how should i set up this scenario on Linux?

Single shared storage that store database file on SAN
4 firebird VPSes connect to the shared storage via NFS
a load balancer vps ( any suggestion?)

My system will be 50:50 read : write with transaction. What kind of server should i choose? SC, SS or mix?

Thank you!

If you have more than one cpu on the server you need to use Super Classic

http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-classic-or-super.html

Also we need more info on the arhitecture that you want to create
I would avoid point 2 (firebird doesn't work on shared nfs)


Security and Performance Implications of SQL Tuning Advisor I work on a relatively small development team that works with an Oracle (11g) database. It's recently been requested that all the developers be given the Advisor role so that we can utilize SQL Tuning Advisor when developing complex queries. Some have raised concerns that this may have significant performance and/or security implications, but I have not been able to find concrete answers to what these implications are.

If this role were to be given to the various members of my team, what are the major pitfalls we should be watching out for? I've included the tag for Oracle 12c as well, since we'll be upgrading to that in the near future. If there's a significant difference between the two, I'd appreciate it if it was at least pointed out.

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later.  Now, if your development machine has limited resources, there may be a reason to limit access.  But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may try parallel queries (if enabled), so that could be an issue, but you can play with the instance parameters parallel_adaptive_multi_user and parallel_degree_level to minimize the impact.

There are no changes between Oracle 11g and 12c that would affect your decision.


How to index/optimize sum of two columns in PostgreSQL? We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background process that removes old rows from the table. The background process removes up to 1000 lines during one transaction:

delete from temporarydata
    where id in (
        select id from temporarydata
            where (created + ttl) <= 1553755330 limit 1000
    )

The 1553755330 in the example is current number of seconds since UNIX epoch and created contains seconds since UNIX epoch the data was added and the ttl contains the number seconds the data should be kept alive.

Technically this does work but there are around 2M lines in the temporary data and the subselect gets pretty slow because the sum requires doing sequential scan over the table to find all matching rows. This causes extra background load on the database.

> explain (analyze,verbose,timing,buffers) select id from temporarydata
        where (created + ttl) <= 1553755330 limit 1000

Limit  (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1)
  Output: id
  Buffers: shared hit=3068 read=230500
  ->  Seq Scan on public.temporarydata  (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1)
        Output: id
        Filter: ((temporarydata.created + temporarydata.ttl) <= 1553755330)
        Rows Removed by Filter: 1916405
        Buffers: shared hit=3068 read=230500
Planning time: 0.402 ms
Execution time: 6735.849 ms

I'd prefer just adding a new index that can always contain the sum of created + ttl that PostgreSQL were able to use for this query automatically. Is this possible with high performance?

(I'm considering rewriting the application code to save created and expires instead of ttl where expires is created+ttl. Then I compute logical ttl as difference of those values. I think the application does not emit heavy queries on ttl alone.)

If you have used the same TTL for every record you can avoid functional indexing by simple moving the TTL to the right side of comparison (thanks to jjanes for correction):

DELETE
  FROM temporarydata
 WHERE id IN ( SELECT id
                 FROM temporarydata
                WHERE created <= 1553755330 - ttl
                LIMIT 1000
             )

Optimizer will calculate the difference only once and then will use it against the indexed created column.

If you need the different TTL for different records you can store not the created -- ttl pair but precalculated created -- expires one

And you can use functional indexing:

CREATE INDEX expires ON temporarydata ((created+ttl));


Why am I seeing poor performance on SQL server 2019 and asynchronous AlwaysOn I have recently migrated a 2008 sql dB that’s about 170 GB to 2019 with asynchronous always on.
The application that uses this dB hits it pretty hard and I’m seeing an increase of about 2 ms on every call to the dB, read, inserts and stored procs.( compared to 2008).
This is having an huge impact on application performance.
Same app server, same app.
Same vlan and data centre as before. SQL VMware VM has much better specs.
Gone from 8core 32GB memory. To 16 core 64GB. 55 GB allocated to SQL
We still have 2008 compatibility enabled.
Parallelism is set the same as previous instance.
I’m all out of ideas and looking for suggestions.

You have the AG set to async mode, which means the difference isn't due to HADR_SYNC_COMMIT waits.
Just the overhead of compressing and sending the log records to the secondary replica(s) could explain the small differences you've noticed though.  Especially if the workload is so sensitive that a 2ms difference per query results in noticeable changes.
Per the really useful Thread Usage By Availability Groups docs section, your primary server will use

1 Log Capture thread for each primary database. In addition, it uses 1 Log Send thread for each secondary database

In the background during your normal user query workload, these threads are reading log records as they are committed, compressing them, and then sending them to each of the secondary replicas configured.
These threads will use CPU time. If the workload is write-heavy (lots of inserts and updates), it could be a noticeable amount of CPU usage.  If there are multiple AGs, multiple databases in the AG, or multiple secondary replicas that need to be updated, even more CPU usage will be needed.
IF the increase in query runtimes is due to AG thread CPU usage*, one possible solution would be to enable trace flag 1462, described in Tune compression for availability group:

Disables log stream compression for Availability Groups with asynchronous replicas. This feature is enabled by default on asynchronous replicas to optimize network bandwidth.

You'll end up using more network bandwidth, but the primary server won't spend CPU cycles compressing log blocks before sending them to the secondary.
*You could try confirm this is the case, potentially by checking to see if queries are experiencing signal waits at a higher rate than on your previous server.

Note that this answer focuses on the availability group aspect, since that's a major difference between the systems per your question description.
The difference in performance could be numerous things, including even minor config differences that can make a big difference.  Considering using the free, open-srouce sp_Blitz procedure to check for anomalous settings, offline schedulers, etc.


DB2 Alter column type and index state RBDP vs. "needs a rebuild" I'm new to DB2 and databases overall.

I'm building a script to alter several column data types in several tables of which some are part of indexes and some not. So after ALTER-statements I assumed I have to rebuild the indexes. According this source:
"When the data type of the ACCTID column is altered from DECIMAL(4,0) to INTEGER, the IX1 index is placed in a REBUILD-pending (RBDP) state."

My  questions:
1. If index is in RBDP state, does it mean it has to be rebuild and how should I return the index to accessible state?
2. How can I find the indexes which are now in RBDP state? I tried this after a altering a column type:

SELECT tabname, indname, index_requires_rebuild
FROM TABLE(sysproc.admin_get_index_info('I','','')) AS t
WHERE index_requires_rebuild = 'Y';

but I still can't find any indexes requiring a rebuild, so I guess RBDP state and "requires rebuild" are different animals. So what is the difference and how can I be certain the index is still working as it should be after my ALTER statements?

The statements I use are in this form:

ALTER TABLE TABLE1 ALTER COLUMN COLUMN1 SET DATA TYPE BIGINT
GO
CALL SYSPROC.ADMIN_CMD('REORG TABLE TABLE1')

The issue here is that you're reading documentation for Db2 on z/OS, but you're running tests on Db2 for Linux, UNIX & Windows.

When you perform this particular alter on Db2 for Linux, UNIX and Windows, it will succeed, but the table will be placed immediately into reorg pending state, which you can see in SYSIBMADM.ADMINTABINFO (see the REORG_PENDING column).  INDEXES_REQUIRE_REBUILD will still show as 'N', even though performing a REORG on the table will rebuild the index.


How to convert execution time to a specific format? I am working on a report that allows people to print off data for physical signatures.  Part of the requirement is to format the exectuion date in the format DDMONYY (27Aug12).  I can not find a way to set the format of the date in the report.

How can I format the report exectuion time to the format DDMONYY?

I found this article: Working with Dates in Reporting Services

I can't test right now, but try this: =Format(Cdate(Parameters!Date.Value),"ddMMMyy")


How to merge consecutive record pairs over a table? I have a table where I need to merge consecutive record pairs (when ordered by id, data and from). Note that for pairs being merged, the first row's prevfrom is NULL and the second row's nextto is NULL.

id  data from        to          prevfrom    nextto
1   a    2015-01-01  2015-01-05  NULL        2015-01-10
1   a    2015-01-10  2015-01-14  2015-01-05  NULL
1   a    2015-01-15  2015-01-20  NULL        2015-01-22
1   a    2015-01-30  2015-02-04  2015-01-25  NULL
2   c    2015-01-01  2015-01-05  NULL        2015-01-10
2   c    2015-01-05  2015-01-10  2015-01-01  NULL

So in this case, I would want to merge rows 1&2, 3&4, and 5&6, and discard helper columns prevfrom and nextto. As a result of the merge, each row would have from value of the first record in the pair, and to value of the second record in the pair. Like so:

id  data from        to
1   a    2015-01-01  2015-01-14
1   a    2015-01-15  2015-02-04
2   c    2015-01-01  2015-01-10

I can do this by doing a SELECT and a JOIN inside a WHILE loop until I run out of record pairs (pseudo code):

WHILE table exists DO:
BEGIN
  INSERT INTO #temp
    SELECT TOP 2 * FROM table ORDER BY [id],[data],[from]  --get a pair
  INSERT INTO result            --join #temp with itself to merge rows
    SELECT t1.[id],t1.[data],t1.[from], t2.[to]
    FROM #temp AS t1
    JOIN #temp AS t2
      ON t1.[from]<t2.[from]    --later record joined on earlier record
  DELETE FROM table             --update original table
    IF record exists IN #temp
  TRUNCATE TABLE #temp          --empty temp table for next insertion
END

However I feel that doing an ORDER BY in every iteration is inefficient. Is there a simpler way to do it?

Basically, use common table expressions to isolate and number rows in 2 groups with one helper column NULL in each, and then join the two groups on row numbers.

WITH cte1 AS (
    SELECT [id], [data], [from] , ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [prevfrom] IS NULL),
    cte2 AS (
    SELECT [to], ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [nextto] IS NULL)
SELECT t1.id, t1.data, t1.from, t2.to
    FROM cte1 AS t1
    JOIN cte2 AS t2
    ON t1.[row]=t2.[row];


Find Index size in sql server The following two queries return different values for index size.

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

And

sp_msforeachtable 'sp_spaceused ''?''''

Can anyone suggest exact query to find Index size for each tables from system views in sql server?

If you have a look at this question here:
space usage on sys.allocation_units and sp_spaceused

You will fin this script below, that will tell you teh size of your indexes.

;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       IIF(sp.IndexID > 0, COALESCE(si.[name], sp.[PassThroughIndexName]), N'<Heap>')
             AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.objects so
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
--ORDER BY ????


Schema for stores and groups of stores and companies Perhaps a silly question. I am building a database where I keep a number of stores in a Store table. Each store obviously have some data as geo coordinates, address etc. Right now these stores are stores with actual products in Product table.

My dilemma is that some of these stores are part of a larger group for example A companies group has B store group and B store group has 100 stores. I am not sure exactly what would be the best way to organize this.

First option is perhaps to create a parent_id column to Store table and add a store without any products and make it parent to many other stores, and add company groups as parent to those store entries. I can add an extra boolean column store_group to mark such entries as groups. However I feel this may be having too many different logical entities in the same table perhaps?

Second option is to introduce a StoreGroup table where I keep the companies (A) and their store groups (B). Within StoreGroup I self-reference to itself using a parent_id column to associate (A) with (B). Then use a parent_id column in Store table to point stores (C) to store group (B).
This seems to be more logical as companies and store groups are logically different than stores. As they do not have products directly.

Could there be a better name? Company instead of StoreGroup perhaps?

Any ideas on how this can be made better?

Thanks!

Update: Perhaps a more realistic example could be Walmart Inc. (A). They have Walmart U.S., Walmart International, and Sam's Club U.S. store groups (B). Totaling up to 11k stores (C).

I would create a table store_group that represents the hierarchy of the groups using and adjacency model:

store_group (
   id integer primary key
   name text not null, -- maybe even unique?
   ... other attributes
   parent_group_id integer references store_group --- null for the "root"
)

If a group can be associated with only a single store group, make that an attribute of the store table:

store (
  id integer primary key,
  group_id integer references store_group,
  ... other attributes ...
)

If group_id is null, the store does not belong to a group.

If a store can belong to multiple groups, use a m:n relation:

store (
  id integer primary key,
  ... other attributes ...
);

store_assignment (
  store_id integer not null references store,
  group_id integer not null references store_group,
  primary key (store_id, group_id)
);

And you obviously need a m:n relation between product and store:

store_product (
  product_id integer not null references product,
  store_id integer not null references store,
  primary key (product_id, store_id)
);


SQL Databases Linked to applications I have just become a DBA for a large company they are currently looking to decommission a 2008 R2 server.
Is there any way of being able to find out if the databases are still being used and by which application without taking the databases off line and seeing who yells that it's broken.

Thanks In Advance

A couple of relatively straight forward ways may be to create a logon trigger (it also talks about event notifications in that article), writing the login events to some audit table, or periodically query the sys.dm_exec_sessions DMV say in a SQL Server Agent job and record the information from that.


SQL Server on Ubuntu - An existing connection was forcibly closed by the remote host after reboot I have been running SQL Server on Ubuntu for a while and everything has been working fine but I rebooted the server and now all connections to SQL Server fail with the error:

A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
(Microsoft SQL Server, Error: 10054)

I have tried uninstalling and reinstalling SQL Server, setting various mssql-conf values (since removed again) etc. but nothing I do makes any difference.

Articles relating to Windows seem to point to it being SSL-related but SSL was not enabled. I did try to enable it and set a certificate but then even though I made permissions to the certificate and private key 777 and chowned to the mssql user/group it just told me it couldn't read it and then SQL Server failed to start up so I removed again.

Can anyone suggest anything I can do to get it working again please as I am out of ideas?

Thanks
Robin

MSSQL expects OpenSSL v1.0 while Ubuntu 18+ uses OpenSSL 1.1, resulting in a OpenSSL version mismatch. The solution is to symlink ssl v1.0 as below:

Stop SQL Server

sudo systemctl stop mssql-server

Open the editor for the service configuration

sudo systemctl edit mssql-server

In the editor, add the following lines to the file and save it:

[Service]
Environment="LD_LIBRARY_PATH=/opt/mssql/lib"

Create symbolic links to OpenSSL 1.0 for SQL Server to use

sudo ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /opt/mssql/lib/libssl.so

sudo ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /opt/mssql/lib/libcrypto.so

Start SQL Server

sudo systemctl start mssql-server


Insert results from a stored procedure into a table variable I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.

I am trying to set these return values in another table variable but I can't figure it out.

Stored procedure

ALTER PROCEDURE [dbo].[GetOrSetDomainId]
@DomainName varchar(50),
@DomainUrl varchar(50)
AS
BEGIN
    DECLARE @DomainId bigint;
    DECLARE @NumberOfRwos bigint;

    DECLARE @DomainHistory TABLE
    (
        DomainId bigint,
        HasHistory bit,
        ServerOnline bit,
        DatabaseOnline bit,
        ServerPerformance bigint,
        DatabasePerformance bigint,
        SoldTickets bigint
    )

    SELECT  @NumberOfRwos =  COUNT(Id)
    FROM DomainData
    WHERE DomainName = @DomainName OR DomainUrl = @DomainUrl

    IF(@NumberOfRwos = 0)
    BEGIN
        INSERT INTO DomainData (DomainName, DomainUrl) VALUES (@DomainName, @DomainUrl)

         SELECT @DomainId =  @@IDENTITY

         INSERT INTO @DomainHistory(DomainId,HasHistory)VALUES(@DomainId, 0)

         SELECT * FROM @DomainHistory
    END

    ELSE
    BEGIN
    ---not important here----
    END

END

Calling code

I call the procedure using:

DECLARE @DomainHistory TABLE
(
    DomainId bigint,
    HasHistory bit,
    ServerOnline bit,
    DatabaseOnline bit,
    ServerPerformance bigint,
    DatabasePerformance bigint,
    SoldTickets bigint
)

SET @DomainHistory = EXEC GetOrSetDomainId 'test', 'test2'
---Other alternatives:---
INSERT INTO @DomainHistory(DomainId,HasHistory)
VALUES(EXEC GetOrSetDomainId 'test', 'test2')

How can I do this?

To insert the results into a table, you just want INSERT....EXEC... not the VALUES part of the query.

In your case, this would look like the following:

INSERT INTO @DomainHistory(DomainId,HasHistory)
EXEC GetOrSetDomainId 'test', 'test2';


Database design for a school we're designing the database for a school. Just a couple of questions:

do we create one (1) table for users or a table for each user type (parents, teachers, students, admins)? I'm thinking both should work, just looking for a second opinion.
is a course (like math, calculus, history) linked to a room (a location)? or is a class (a student can be in grade 1, class 1) linked to a room?

so to rephrase the 2nd question:
- is math linked to a room, and gym to another room?
- or do we link all students from grade 1, class 1 to a room?

do we assign homework to a class or to a student? I'm leaning towards student, seems to make sense, but not everyone agrees with me. And if link to a class why?

Thank you.

Do we create one (1) table for users or a table for each user type (parents, teachers, students, admins)?

You are correct - both will work.

The former could make for very wide table, containing many null values where those fields are not applicable.

The latter is, perhaps, a "better" model (from a Purist" point of view) but it does make the database structure more complicated.

A compromise might be to have a "core" Users table that contains the really basic, core information - like Name - and then additional tables for each Type if User that has other fields that are specific to that Type.

Bear in mind that one person may be more than one Type of User, either over time or even at the same time (e.g. a Teacher who is also a parent of a Student)!

  is a course (like math, calculus, history) linked to a room (a location)? or is a class (a student can be in grade 1, class 1) linked to a room?

Will a Course always run in the same Room?
Probably not (if only on the principle that "Stuff Happens").

A session - a Course, being run in a particular Room at a particular Time on a particular Day - represents the intersection of all three of these "parent" Entities - Course, Room and DateTime.

You almost certainly need a Calendar table that defines when classes can be run.

  do we assign homework to a class or to a student? I'm leaning towards student, seems to make sense, but not everyone agrees with me. And if link to a class why?

You distribute homework to an entire Class but you expect each Student to "do" their own (I hope!), so you care about which ones were given the assignment, which completed it (and, possibly, when), what mark they were given, and so on.

So, whilst your Logical process might be to give homework to a class, the underlying, Physical process has to be to "unbundle" all the students in that class, and link the homework assignment to each one of them.
The association of Student within Class can be used to perform any aggregated reporting that you require, at class level.


MySQL/MariaDB connection in asp code encodes wrongly charset utf8 at insert or update in data base I can't get a simple update or insert to save special äöü characters. Instead they are being stored as Ã¤Ã¶Ã¼.

The website displays äöü characters correctly if they are already stored as äöü in the database using phpMyAdmin (or some other tool like HeidiSQL), but for some reason I can't use my code/connection to the database to store them properly. This is where I stand:

I added charset=utf8 to my connection string:

MM_Connection_STRING = "PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};port=3306;SERVER=localhost;UID=xxx;PWD=xxx;database=xxx;Option=3;charset=utf8"

I added accept-charset="UTF-8" to all my forms.

I am running MariaDB and IIS 10.

Server version:         10.1.13-MariaDB mariadb.org binary distribution
Protocol version:       10

The page gets proxied using Apache 2.4. I am running asp (classic) code.

Calling the MySQL server for character and collation, I get this:

MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
  Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

The code worked when I was using iso-8859-1 as charset.

Any help will be very much appropriated. Thank you.

I had <%@language="VBSCRIPT" CODEPAGE="1252" LCID=1033%> and needed <%@language="VBSCRIPT" CODEPAGE="65001" LCID=1033%> for it to work. Also, after doing this I needed to drop charset=utf8 from the connection string.


MySQL NDB Cluster architecture? I am looking for information that I am not able to find about MySQL cluster.

I have to justify if it is possible to install in a production environment the three types of nodes (MySQL node + Data node + MGMT Node) on the same instance EC2 (AWS)

One of the scenarios is to have two EC2 instances. The three types of nodes are installed on each instance. The MySQL cluster is active / active between the two instances. I believe this configuration is acceptable for Oracle in a development environment. But in a production environment it would not be and I would like to know why because I don't see a single point of failure.

Oracle in its documentation seems recommend 6 servers to have a fully redundant solution. Okay but why technically this requires 6 servers?

The web application that will use MySQL doesn't require a large processing capacity.

Thank you in advance for your precious help.

Nb. I'm not a DBA.

Yes, you can put different node types on the same machine.  But...  You need to change the configuration of each.  Running out of RAM would be really bad for performance.

For a Web application, I see no need for the complexity of NDB Cluster.  Virtually all Web usages of MySQL simply use InnoDB.

Before digging into more details, please state your goals.  Redundancy?  Resiliency?  Scale?  Key-value store?  Other?  I don't hear any of those in your Question.


Optimize table by removing large field I have a MySQL table of articles. It's growing rapidly, and over 50% of the size on disk is accounted for by the article body field, as you might expect. There are no indexes on the body, but 8/16 fields on the table are indexed.

My question is: going forward, will it be more efficient if I move the article body into a separate table? Will I gain more than I lose by forcing extra joins? Average body length is 3.3 kB and maximum length is 414 kB.

CREATE TABLE `articles` (
    `id` CHAR(32) NOT NULL DEFAULT '',
    `site_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
    `trend_id` CHAR(32) NULL DEFAULT NULL,
    `date_added` DATETIME NOT NULL,
    `date_scraped` DATETIME NULL DEFAULT NULL,
    `date_published` DATETIME NULL DEFAULT NULL,
    `date_shared` DATETIME NULL DEFAULT NULL,
    `language` VARCHAR(5) NULL DEFAULT NULL,
    `reach` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
    `title` TINYTEXT NULL,
    `summary` TEXT NULL,
    `keywords` TEXT NULL,
    `thumbnail` TEXT NULL,
    `body` MEDIUMTEXT NULL,
    `url` TEXT NOT NULL,
    `status` ENUM('published','pending','disabled') NOT NULL DEFAULT 'published',
    PRIMARY KEY (`id`),
    INDEX `site_id` (`site_id`),
    INDEX `date_published` (`date_published`),
    INDEX `language` (`language`),
    INDEX `reach` (`reach`),
    INDEX `trend_id` (`trend_id`),
    INDEX `date_added` (`date_added`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

PARTITION is unlikely to help.

CHAR(32) -- is it utf8?  If so, then it is taking 96 bytes; what a waste.  And it is hex, correct?  Consider converting to binary and using BINARY(16) to save space.  Note that (in InnoDB) the PRIMARY KEY is included in each secondary key.  So, 8 indexes times (96-16) bytes = 640 bytes per row saved.

In MySQL there are HEX() and UNHEX().  Your programming language probabls has something.

Yes, do consider "vertical partitioning" to move the body to another table (body + id; primary key is id):

Searching in the main table will be faster because of not tripping over the bodies.
The JOIN to get the body won't be bad, especially since you won't be fetching many bodies at once.
Consider making the body a MEDIUMBLOB and compressing (in the client) the text.  This will give you a big savings in disk space.  (And it is the only significant saving.)  Text typically compresses 3:1.

Make a decision soon.  Any change is likely to need both the old copy of the bodies and the new during the conversion.  The 50% is scary.

If you have 8 indexes, are any of them "composite"?  Are any just on a flag?  Please provide SHOW CREATE TABLE.


what should be the good normalization for the following scenario? ITS JUST FAKE TABLE DATA TO SIMPLIFY MY PROBLEM

I have tables ,,  Users , shirts, pants, belts .... each will have some data...

so by some research i have made some scenarios of links between the tables so what would be the Optimized and very good ..

Scenario 1

I will have tables

user_shirts  ( user_shirt_id (PK), user_id(FK), shirt_id(FK))
user_shirts_pants  (user_shirts_pants_id (PK), user_shirt_id (Fk),  pant_id(FK))
user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_shirts_pants_id (Fk), belt_id (Fk))

If there is

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

there will be 1 row in users table...
there will be 2 rows in users_shirts table...
there will be 6 rows in user_shirts_pants table...
there will be 12 rows in user_shirts_pants_belts  table....

then this will have data

Scenario 2

I will have tables

user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_id(FK), shirts_id (Fk),  pant_id(FK), belt_id (Fk))

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

there is 1 row in users table
-there will be 12 rows in user_shirts_pants_belts  table....

**So which scenario is good.. and in normalization form... i should adopt whi9ch scenarion... if any better scenario then this then suggest me...? So i can make more and more relational combination and i easily update and delete them... **

What is it we are trying to get to here? Do you want to list out all the outfits (combinations of shirts, belts, and pants) a user can wear? Or just what they have worn for a particular day? Sorry, I'm not following the needs for the three relational tables (user_shirts, user_shirts_pants, user_shirts_pants_belts).
I would just have one table for each item (with pkid and description of item), then a table that has a separate foreign key to those item tables:

users (id int, name varchar(100))
shirts (id int, desc varchar(100))
pants (id int, desc varchar(100))
belts (id int, desc varchar(100))
user_outfits (id int, user_id int, shirt_id int, pant_id int, belt_id int, dt date)
Then you could enter any item they wore or null if they didn't wear that item.


SQL Server RandomNumbers Stored Procedure Error I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record.

CREATE PROCEDURE Update_Driver_Position
@TempName VARCHAR(8000)
AS
DECLARE @maxvalueX INT
DECLARE @minvalueX INT
DECLARE @maxvalueY INT
DECLARE @minvalueY INT

DECLARE @X INT
DECLARE @Y INT

SET @maxvalueX = 3357
SET @minvalueX = 273
SET @maxvalueY = -320
SET @minvalueY = -3096

SET @X = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX
SET @Y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

UPDATE driver_tbl SET pos_x=@x , pos_y=@y

EXEC ('
Select *
Into '+@TempName+'
From  dbo.Driver_Tbl
')

I also couldn't EXEC the stored procedure more than once. Here is that call;

 EXEC dbo.Update_Driver_Position '##Temp'
 SELECT * FROM ##Temp

Any ideas ?

Because you evaluate @X and @y once before the update.

You need to do in the UPDATE itself

Update driver_tbl
set
    pos_x = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX,
    pos_y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

The answer I gave on your SO question shows this: https://stackoverflow.com/q/8546985/27535


To select from all tables inside the schema in PostgreSQL Is it possible to select from all tables inside the schema?
I got all the table names from

select table_name from information_schema.tables

but I am unable to use it to do my query.

You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery).  But you can build and execute a dynamic SQL statement for this.  For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name
           FROM information_schema.tables
          WHERE table_schema = 'your_desired_schema'
LOOP
    sql_string := sql_string || format($$
        -- some whitespace is mandatory here
        UNION
        SELECT name FROM %I
    $$,
    i.table_name);
END LOOP;

EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable.  You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables.  If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

the format() function was introduced in version 9.1
I've omitted some mandatory elements of any PL/pgSQL block to keep it simple
the earlier version mentioned using a DO block.  The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.


DELETE LOW_PRIORITY row visibility I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and actually delete the rows from disk when no clients are accessing the table, or is the point delaying removal of visibility?

From the docs:

  If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking.

All this means is that the DELETE LOW_PRIORITY statement will not begin processing until all read locks are finished. If another read lock comes in before the DELETE LOW_PRIORITY statement begins, it will wait.

Once the DELETE LOW_PRIORITY statement has the lock though, it will acquire the table-lock and run until complete (or killed).

Under normal table lock situations, any write requests will be given higher priority than read requests. Example: if you have a write lock going on, then a read request goes in the read queue, and then another write request comes in, the second write request will execute before the read requests.

Using LOW_PRIORITY, the situation is reversed. If a read lock is going on, and a write comes in, it will wait. If another read comes in before the write gets the lock, the second read will execute and the write will wait.

So, if you are using DELETE LOW_PRIORITY on a table that is frequently read from, the DELETE LOW_PRIORITY statement could be waiting a long time.

Once a write lock is acquired in a table-locking situation, any reads will wait in the queue until the write lock is finished.


How can I improve performance of update query in PostgreSQL I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth).
If the heigth is between 0 and 6, I want to fill the heigth_category attribute with 1.

UPDATE schema.table t1 SET heigth_category = '1'
WHERE heigth >= 0
AND heigth< 6;

This took about 13 hours. Is that normal for such a query?

The explain gives this:

Update on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
  ->  Bitmap Heap Scan on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
        Recheck Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))
        ->  Bitmap Index Scan on boom_hoogt_idx  (cost=0.00..13208.50 rows=579346 width=0)
              Index Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))

I working with PostgreSQL 9.1.12

In general mass-updates like this can take a long time, independent of the RDBMS used.

Often a lot of time can be saved by running the update in batches of a few thousand rows at a time. Something like:

UPDATE schema.table set ...
 WHERE id >=0 and id < 10000

UPDATE schema.table set ...
 WHERE id >=10000 and id < 20000

...

Instead of manually typing the different update statements you certainly can use a loop too. Just make sure that there is no overarching transaction, as that would negate the effort.


Linking foreign keys across multiple databases: direct, or using an intermediary table? I want to make a part of my application reusable, and that warrants moving the corresponding tables into a separate database. So for the sake of an example, please consider the two imaginary databases in the list that follows. (More databases sharing the same logic may be added as the project grows.)

users containing tables related to user sign ups, login and e-mail history, password reset tokens etc., as well as the accounts themselves; and
blogs having tables for posts, media files, comments, etc.

Each table in the blogs database must obviously have an account_id column referring as a foreign key to users.accounts.id. (I do realise that to make it work both databases must use InnoDB and reside on the same server.)
My question is what would be a better practice:

direct reference to another database:

simply refer blogs.posts.account_id to users.accounts.id (repeat with all other blogs.* tables),
make each reference CASCADE ON DELETE; or

using an intermediary table:

create an intermediary table blogs.accounts having only one column called id; then
on one hand, refer every table inside the blogs database to that intermediary table (so blogs.posts.account_id to blogs.accounts.id, CASCADE ON DELETE); and
on the other hand, finish by referring this blogs.accounts.id to the 'upstream' users.accounts.id, make sure to CASCADE ON DELETE as well.

The latter seems like an unnecessary complication. But the only advantage I can think of is this can make the setup future proof in case we end up having to still migrate one (or some) of the databases to another server:

If we link the tables directly, after the migration the blogs database will have lots of disparate account_id columns that won't CASCADE ON DELETE
But if these intermediary tables get disconnected from the upstream users.accounts.id, their neighbouring tables in each respective database are still linked to them. This way we can continue benefitting from at least somewhat integrity and CASCADEs. In other words, if a user gets deleted, all we have to do is have a script go through each of these *.accounts connector tables and delete the id counterpart once, and CASCADE will take care of the rest of the tables inside of that database automatically.

Am I on the right track with this logic, or am I missing some other ways to handle this more effectively, and therefore reinventing the wheel?

This is bordering on an opinion-based question, because of course anyone can have their own justification for either choice.
I would not choose to add the intermediary table, for a few reasons.
One is that as you know, the intermediary table cannot have a foreign key reference to a table on another MySQL instance. Foreign keys can span schemas, but not instances.
If you need to run special scripts to clean up the intermediary table anyway, that special script should be able to encode clean up tasks for all the blog tables. I assume at least the number of blog tables remains relatively stable. You aren't going to be adding so many blog tables in custom ways that this becomes a burden to maintain the cleanup script.
The intermediary table therefore seems like an extra step that just complicates things, and takes extra storage.
I've worked at several companies that eschewed the use of foreign key constraints in any case, because they make some operation tasks harder, for example schema changes. Also because they incur locking behavior that you probably don't expect. In the absence of the constraints, you can't get the ON DELETE CASCADE feature.
Like I said above, this is subject to opinion. You could easily ask, "but what if the priorities are a bit different...?" Of course, you must make this decision based on your requirements and priorities.


Concatenate with SELECT * I want to concatenate columns with a SELECT *. For example:

SELECT * FROM Table1

Result:

Col1 Col2
---- ----
Joe  Smith

I want to do something like SELECT CONCAT(*) FROM Table1

The reason I can't mention the columns by name is that they are being produced by a PIVOT from rows, so there could be any number these columns.

If anyone knows the answer, or knows how to Get the data from any number of rows into a single cell, separated by a comma, please let me know.

The CONCAT function requires passing column names explicitly and there is no such thing as CONCAT(*).

On the other hand, PIVOT requires an explicit definition of the columns you are pivoting: if you are doing it dynamically, you probably have a variable that holds the names of the columns you are pivoting on and you could use the same variable in dynamic SQL with the CONCAT function.


How to design a main list and sublists schema I am finding it difficult to design a database schema for a list feature. there would be a master list which then can be subsectioned into further lists.

each user is able to mark a candidate as a favourite candidate and can have many favourite candidates.

if a candidate is deleted from a list then it should still remain part of favourites, or be part of the main favoirtes list without belonging to a particular list.

a user can mark many candidates as favourite
a user can have many favourites lists
a favourites list can have many candidates

the following is what I have come up with so far.

as shown in the above image I am allowing the FavouritesListId to be nullable, this way if a list item is not associated with a certain list this means it is still favourited but not part of a particular list. I want to know if this is the optimal solution or if there is a better design approach available.

and here is the script to the schema:

    USE [Favourites]
GO
/****** Object:  Table [dbo].[Candidates]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Candidates](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Candidates] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesList]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesList](
    [Id] [int] NOT NULL,
    [Title] [nvarchar](50) NULL,
 CONSTRAINT [PK_FavouritesList] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesListItem]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesListItem](
    [Id] [int] NOT NULL,
    [FavouritesListId] [int] NULL,
    [CandidateId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
 CONSTRAINT [PK_FavouritesListItem] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Users]    Script Date: 10/05/2015 18:43:52 ******/

One issue I have with your current model is that it would be possible for a FavouritesList to contain FavouritesListItems that belong to different Users, which to my understanding of your business logic shouldn't happen. I think a better way would be to store all FavouritesListItems that don't belong to a particular FavouritesList in some entity that is designated as a User's "UngroupedFavouritesList". There are a few ways to create that entity.

Method 1
Make the Title attribute in FavouritesList nullable. If it is null, then it is considered to be the User's "UngroupedFavouritesList".

Method 2

Add an "isUngrouped" flag to the FavouritesList table. If it's set to true, the it's considered the User's "UngroupedFavouritesList". The title attribute of FavouritesList would be null in this case.

Method 3

Make FavouritesList a parent table with UngroupedFavouritesList and NamedFavouritesList as child tables. This is achieved by having the primary key of UngroupedFavouritesList and NamedFavouritesList also be a foreign key to the FavouritesList's primary key.

Method 4

Create an "IFavouritesContainer" interface table, which both FavouritesList and User implement. This is achieved by User and FavouritesList having a foreign key to the IFavouritesContainer table. The FavouritesListItem then has a reference to the IFavouritesContainer that contains it. For ungrouped list items, its IFavouritesContainer would be the User. Note that the idea of implementing interfaces isn't a built-in thing with any RDMS that I know of, I'm just using the terms as an analogy to object oriented programming.

Pros and Cons for each method

        Method 1
        Pros:

            Simple

        Cons:

            It's not obvious that a null title means it's an "UngroupedFavouritesList
            It's possible for a User to have multiple "UngroupedFavouritesLists"

        Method 2
        Pros:

            Simple
            Clear what is an "UngroupedFavouritesList" and what isn't

        Cons:

            Storing a title for the "UngroupedFavouritesList", even though it will always be null. This is very minor.
            It's possible for a User to have multiple "UngroupedFavouritesLists"

        Method 3
        Pros:

            Clear what is an "UngroupedFavouritesList" and what isn't
            Tables don't contain unnecessary fields

        Cons:

            It's possible for a User to have multiple "UngroupedFavouritesLists"
            More complex

        Method 4
        Pros:

            Tables don't contain unnecessary fields
            The business logic of a User containing only one "UngroupedFavouritesList" but multiple "NamedFavouritesLists" is preserved

        Cons:

            More complex
            Not obvious what it means for a FavouritesListItem to be contained by Users vs FavouritesList


Conditional string concatenation in PostgreSQL I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new field, owner_addr where each of the above fields is concatenated with // between each of them.

But if one or more of the original columns is NULL, I don't want to concatenate // to the result column. So for instance, if owner_addr1 is 123 4th Avenue SE and owner_addr2 and owner_addr3 are NULL, then I want the result column to just be 123 4th Avenue SE, not 123 4th Avenue SE // // (which would happen if I just did CONCAT() with // between the NULL strings ... I only want to add // between non-NULL columns, or leave it out altogether if there is only one non-NULL column.

Is there an easy way to do this kind of conditional concatenation in Postgresql, where it leaves out empty lines? Or should I write a python script to do this?

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored:

select concat_ws('//', owner_addr1, owner_addr2, owner_addr3)

Test:

red=# select concat_ws('//', 'abc', null, null, 'xx', null, 'xyz', null)
          as address;
   address
--------------
 abc//xx//xyz
(1 row)


Function Based Index with XPATH extractValue // Oracle Version 11.2.0.4  Standard Edition
// OS RedHat

I am trying to create a function index on a large table, based on using an XML object.

So I have done stuff such as:

CREATE INDEX test_function_itemgroup_id
ON test_function
(
    extractValue
    (
        RECORD,
        '/Product/PublishingDetail/ItemGroup/ItemGroupId',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    )
)

So when I have a query such as:

select PRODUCT_ID
from TEST_FUNCTION pr
where
    extractValue
    (
        RECORD,
        '/Product/PublishingDetail/ItemGroup/ItemGroupId',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    ) = 'NAME-CLASS'

The function Index is used and I know I have the right namespace for the xml.

When I do stuff such as:

CREATE INDEX test_function_nbd
ON test_function
(
    extractValue
    (
        RECORD,
        '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    )
)

select PRODUCT_ID
from TEST_FUNCTION pr
where
    extractValue
    (
        RECORD,
        '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    ) = 'Other'

The system does a full table scan instead of using the function index.

I have used an XMLIndex, but the performance of that has been so slow and does not seem to allow the function based indexes to be used.

Has anyone got any suggestions about how to proceed with this?

Update:

Added the full xmlns as have enough reputation now

There are no other indexes on the table.

What appears is that the Optimizer seems to be converting the XPATH to
two different queries.

/Product/Extras/ExtrasIDType  and
/Product/Extras/IDValue

which are not indexed by the function.

In my particular case I can't index these individually as these can have multiple values and function based indexes can't use multiple values.

Also if I have to use two function based indexes the system will do a full table scan. (According to oracle 11 documentation)

Use the /*+ NO_XML_QUERY_REWRITE */ hint. My environment for the below is 11.2.0.3.0 on Linux_x86-64 platform, Enterprise Edition.

create table xml_table (c1 xmltype) pctfree 99;

(PCTFREE 99, just a simple trick to spread the table over many blocks, thus making full table scan even more costly and inefficient. 1 row/block in this case.)

insert into xml_table select
xmltype('
  <stuff>
    <something>
      <x1>' || rownum || '</x1>
      <x2>' || rownum || '</x2>
    </something>
  </stuff>
')
from dual connect by level <= 10000;
commit;

So I have a table with 10000 rows, with XMLType values like:

<stuff>
   <something>
      <x1>1</x1>
      <x2>1</x2>
   </something>
</stuff>

<stuff>
   <something>
      <x1>2</x1>
      <x2>2</x2>
   </something>
</stuff>

...

And I want to run queries like, let's say:

select * from xml_table
where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

Create the function-based index and gather statistics:

create index xml_table_fbi1 on
  xml_table(extractvalue(c1, '/stuff/something[x1=5]/x2/text()'));

exec dbms_stats.gather_table_stats(user, 'XML_TABLE', no_invalidate=>false);

Run the query and see what happened:

alter session set statistics_level=all;

select * from xml_table where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

C1
--------------------
<stuff>
  <something>
    <x1>5</x1>
    <x2>5</x2>
  </something>
</stuff>

Check the plan and runtime statistics:

select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  a78z6npnp94va, child number 0
-------------------------------------
select * from xml_table where extractvalue(c1,
'/stuff/something[x1=5]/x2/text()') = '5'

Plan hash value: 3307077916

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |   226K(100)|      1 |00:00:00.43 |   10239 |
|*  1 |  FILTER              |           |      1 |        |            |      1 |00:00:00.43 |   10239 |
|   2 |   TABLE ACCESS FULL  | XML_TABLE |      1 |  10000 |  2764   (2)|  10000 |00:00:00.04 |   10239 |
|   3 |   SORT AGGREGATE     |           |  10000 |      1 |            |  10000 |00:00:00.37 |       0 |
|   4 |    NESTED LOOPS SEMI |           |  10000 |    667K|   223K  (1)|      1 |00:00:00.28 |       0 |
|   5 |     XPATH EVALUATION |           |  10000 |        |            |  10000 |00:00:00.12 |       0 |
|   6 |     XPATH EVALUATION |           |  10000 |        |            |      1 |00:00:00.14 |       0 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_XMLTYPE2SQL(SYS_XQSEQ2CON(SYS_XQEXTRACT(,'/something/x2/text()')))
              ='5')

The database transformed the XPath expression, so the function-based index is not a valid candidate anymore, so it is  a full table scan with over 10000 buffer gets.

While running the same with the above hint:

select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

C1
--------------------
<stuff>
  <something>
    <x1>5</x1>
    <x2>5</x2>
  </something>
</stuff>

Plan and runtime statistics:

select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  gby3tfs2z7mqm, child number 0
-------------------------------------
select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5'

Plan hash value: 2259369741

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |     2 (100)|      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| XML_TABLE      |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | XML_TABLE_FBI1 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("XML_TABLE"."SYS_NC00003$"='5')

Much better.

Also notice the Cost column in the plans. The cost of the first plan was 226K, but with disabling XML rewrite, the cost of the second plan was 2. I didn't investigate this topic deeper, but it seems this is another kind of query transformation, that is not based on the cost.


Storing NULL Values in large VARCHAR fields I have a table that represents a "note". This note has things like a title and various other meta data. However, I'm trying to figure out the best practice for storing the contents of the note.

Currently, my strategy is to have the contents of the note be a VARCHAR(10000) and just store a NULL value if there are no contents. But, I'm not sure if that's the right thing to do, or if this will cause performance or storage issues.

I am very new to constructing databases - am I going about this the right way?

A note with no contents sounds like a fairly meaningless object to me. I would model this by the absence of such tuple in the notes table!

p.s. a note's title is an attribute. An example of metadata ("data about data") would be the date a note's title attribute was most recently updated.


SQL Server - select column_name from (multiple tables_name stored in a variable) Let's consider 4 Tables: [Starter], [Meal], [Dessert], [Drinks]. I need to check in each table if the column [Price] exceed at least one time 15. And retrun the value into the table [My_table] to insert the boolean into the row with the table name.

 update [DB].[dbo].[My_table]
          set [Check_column]=
             case when [price] > 15 then 'OK'
             else 'WRONG'
             end
    from @multiple_tables_names

I would like to use this update statement for checking the content of the column [Price] in multiple tables (which have the same structure).

This would be the table My_Table:

 [Type] |               [Check_column]
----------------------------------------------------------
Starter |                   OK
-----------------------------------------------------------
Meal    |                  WRONG
-----------------------------------------------------------
Dessert |                    OK
-----------------------------------------------------------
Drinks  |                  WRONG

Assuming your table is "similar" to next one and table names are stored there:

CREATE TABLE [MyTable]
(
    [Type] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Type]) VALUES
    ('Starter'), ('Meal'), ('Dessert'), ('Drink');

You can dynamically build and execute UPDATE sentences using a CURSOR.

DECLARE @cmd nvarchar(max);
DECLARE @type nvarchar(10);

DECLARE cur CURSOR FOR
    SELECT [Type] FROM [MyTable];

OPEN cur;

FETCH NEXT FROM cur INTO @Type
WHILE(@@FETCH_STATUS = 0)
BEGIN
        SET @cmd = 'update [MyTable]
            set [Check_column] =
                case when exists(select 1
                                 from   ' + @Type
                              + ' where left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')'
             + ' then ''FAILED'' else ''OK'' end'
           + ' where [Tabs] = ''' + @Type + '''';

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur INTO @Type
END

CLOSE cur;
DEALLOCATE cur;

db<>fiddle here


JDBC prepared statement - make a token null So, I have an insert statement in a Java program I'm writing. Under some conditions, I want it to insert some values as null. However, before I can execute the statement, I have to set all of the tokens to values. How can I make it set something to null instead?

Edit:

Example:

    sql = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
            + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
            + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES (?, ?"
            + ", 'STI.PHYSICIAN', 1, 0, ?"
            + ", ?, ?)";

Sometimes, I want to insert nothing for POSTAL_CODE (for example)

You can use setNull():

preparedStatement.setNull(8, Types.VARCHAR);

Although I have seen drivers that do not properly support that, in that case simply try:

preparedStatement.setString(8, null);


aggregating sums for specific time but would like to have multiple times in the same query I am trying to sum the number of orders for two years and have them side-by-side but am not sure how to do this. To summarize one year, I currently have:

select menu_item_id,
       menu_item_header,
       sum(quantity)
from line_items
where order_id in ( select id
                    from orders
                    where menu_id=807
                      and stripe_transaction_state='paid'
                      and created_at between '2019-01-01'
                                         and '2020-01-01' )
group by menu_item_id, menu_item_header order by menu_item_id;

which gives me:

| menu_item_id | menu_item_header    | sum |
|--------------|---------------------|-----|
| 21562        | bacon-wrapped dates |  82 |

but would like

| menu_item_id | menu_item_header    | sum 2018 | sum 2019 |
|--------------|---------------------|---------|----------|
| 21562        | bacon-wrapped dates |  82     | 103      |

One problem is that the menu_item_header can be different? Is there a way that I can take the first one and ignore the rest? I could also join it to the menu_items table too.

How would I do this?

select menu_item_id,
       menu_item_header,
       sum( case when created_at between '2018-01-01'
                                     and '2018-12-31'
                 then quantity
                 end ) sum_2018,
       sum( case when created_at between '2019-01-01'
                                     and '2019-12-31'
                 then quantity
                 end ) sum_2019
from line_items
where order_id in ( select id
                    from orders
                    where menu_id=807
                      and stripe_transaction_state='paid'
                      and created_at between '2018-01-01'
                                         and '2019-12-31' )
group by menu_item_id,
         menu_item_header
order by menu_item_id;


Can't find compiled parameters values in the plan cache I have found, through the query store, a query that's performing on average 297582 logical reads.

I wanted to see if I was able to tune that query a bit, and after that, try to execute the query again to see if there was any improvement.

The problem is that I couldn't find compiled parameters value in the cached plan.

Am I missing something? Maybe some reason/setting that prevents the caching of parameters values?

I can't find the parameters even if I open the execution plan as XML.

Additional informations: the query is executed by a third party application that prepares the statements and then executes them with sp_prepare and sp_execute.

Full XML plan

Query store:

Plan cache DMV's:

sp_prepare doesn't include the compiled values for parameters, because it doesn't 'sniff' them. When a query is issued with it, cardinality estimates are made using the density vector rather than the statistics histogram.

I blogged sort of recently about that here:

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

If you read the blog post, some of the terms I've used in this answer are explained a bit more.


Report in MSSSRS without Star Schema? I have to create a single report in MSSSRS to view calculated columns (Salary, Total Compensation, Performance Bonus, 401 (k)) by Employee, Department, Service Line, Geography, Quarter, etc.

My modeler is going to give me  one and only one single flat TABLE (No FACTS and Dimensions) that he has developed in MS Access and now he is going to load into MS SQL SERVER 2008 R2.

Therefore, the question is how do I develop this report without having Dimensional Structure in place? Do I create a Multi-dimensional Views on a given single table or no matter what but he should create Star-Schema model?
What if he doesn't create a Star schema structure then what are my options to create a report that requires viewing calculated columns by Employee, by Department, by Service Line, by Geography, by Quarter?

Also, should I write multiple Stored Procedures or perform simple calculations in MSSSRS?

It sounds like you are talking about creating a report that allows the users to choose the grouping of the data, e.g. there is a report parameter that allows the user to select "Employee", "Department" etc. which will then drive the grouping in the report data (presumably a table).

You have a couple of options to achieve this:

1) Create a dataset that uses dynamic SQL to build the query selected columns and GROUP BY clause based on the user's selection. This is probably the best performing option but in my experience is more trouble than it's worth due to the inherent difficulties of debugging or changing dynamically built SQL statements.

2) Create a dataset that contains all the possible grouping columns using a simple select statement, and then design the report to dynamically group the results based on the user selection. In your case this is the simplest choice since your data is already in a single table.

e.g. if you have a report parameter called "GroupBy", then you can set the grouping expression in the report data region (e.g. a table) to:

=Fields(Parameters!GroupBy.Value).Value

So if one of the dataset columns is called "Employee" then you need a parameter option with a value of "Employee". If the user selects this parameter option then the table grouping is set to employee. You also need to use a similar expression to set the display value in the grouping column (e.g. if the grouping is by Employee, then you need to display the Employee name).


SQL Server: How to add a full join to two tables but have matches line up? I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences and matches between the two db's alternate code tables.

To setup my environment here is a small sample:

CREATE TABLE [dbo].[alt_code_ERP](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [alternate_code_ERP] [nvarchar](50) NOT NULL,
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[web_alt_code](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [web_alternate_code] [nchar](50) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (3, 'part2' , 'part2b_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (4, 'part2' , 'part2c_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (5, 'part10' , 'part10_alt')
GO

INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (3, 'part2' , 'part2d_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (4, 'part8' , 'part8_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (5, 'part10', 'part10b_alt')
GO

I want a result that will have the following:

List each alternate code from each table once.
If there are any matching alternate codes from the two tables, I want to align them side by side.
Any without a match in the other table to be paired with a NULL

Something that looks like the following:

 item_id      alternate_code_erp   web_alternate_code

part1       part1_alt   part1_alt
part2       part2a_alt  part2a_alt
part2       part2b_alt  NULL
part2       part2c_alt  NULL
part2       NULL        part2d_alt
part8       NULL        part8_alt
part10      part10_alt  NULL
part10      NULL        part10b_alt

You can use a FULL OUTER JOIN.

SELECT ISNULL(a.item_id, w.item_id) AS item_id,
    a.alternate_code_ERP, w.web_alternate_code
FROM alt_code_erp a
FULL OUTER JOIN web_alt_code w
    ON a.item_id = w.item_id
    AND a.alternate_code_ERP = w.web_alternate_code
ORDER BY 1, ISNULL(a.alternate_code_ERP, w.web_alternate_code)

I added an ORDER BY in case you need it.  It's still not exactly the same but without doing some string manipulation you aren't going to get an order of item_id like you want.


Oracle 11g see transaction or session start time I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?

I've got a transaction id "TX-1234-abcd", a session number and some "DID 000-000ABCD" number.

Franz

The view V$TRANSACTION likely gives you the information you require. The transaction id is the XID column,


Grant MonitorAdmin / PROCESS privilege to user I need to assign the monitoradmin privilege to a user because this is what an application called "dynatrace" needs to monitor the server (some details in a post on their site).

According to https://mariadb.com/kb/en/grant/ there is a PROCESS privilege but I can't assign it.

MariaDB [(none)]> CREATE USER dynatrace IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> GRANT MonitorAdmin TO dynatrace;
ERROR 1959 (OP000): Invalid role specification `MonitorAdmin`

Same result with PROCESS:

MariaDB [(none)]> GRANT PROCESS TO dynatrace;
ERROR 1959 (OP000): Invalid role specification `PROCESS`

Where is my mistake ? Although my knowledge about mariaDB is more than limited I have the password of the root account ;-) and can run the commands as this user.

From the MariaDB documentation:

  To set a global privilege, use *.* for priv_level.

So your command should be

GRANT PROCESS ON *.* TO dynatrace;


SQL Server Transactional Replication snapshot delivery loop I have configured transactional replication from SQL Server 2008 to SQL Server 2014. And everything is OK, except one thing: after the initial snapshot is delivered to subscriber, distribution agent starts to deliver it again, the same articles in the same order, instead of applying undistribution transactions, pending delivery.

I tried to reinitialize subscription, re-create subscription and generate new snapshot, but it doesn't help.
There are no any errors in any of agents history, but snapshot delivery is just looped.

How can it be fixed?

So, by some reasons it was happened because triggers and non-clustered indexes were included in replication. After they were excluded the snapshot delivery didn't loop anymore. And after snapshot was delivered, I just created all missing indexes and triggers manually on the subscriber as a workaround.


Mysql utf-8 behaves differently into different environments I have MySQL server under docker in 2 different environments:

This is my create database:

CREATE DATABASE guests CHARACTER SET utf8mb4 COLLATE UTF8MB4_UNICODE_CI;

This is the create table:

USE guests;
CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL);

This is how I Insert data:

insert into MyGuests (firstName)  VALUES("בדיקה");
insert into MyGuests (firstName)  VALUES("ة التي تح");
insert into MyGuests (firstName)  VALUES("7");

In the one environment, I get question marks in the result.
In the other environment, I get the correct characters

The good environment is mysql in a docker under windows 10.
The bad one is mysql in a docker under Ubuntu.

This is the result for running:

This is the output of the command mysql> show variables like '%collation%'; in the bad env.

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

This is the output of the command mysql> show variables like '%collation%'; in the good env.

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_bin        |
| collation_server     | latin1_swedish_ci  |
+----------------------+-------------------=+

Thanks

I had missing in the connection string CharSet=utf8mb4 in the bad env

Server=10.5.0.5;Database=db;Port=3306;Uid=root;Pwd=root;CharSet=utf8mb4


Referential integrity after disable/enable trigger all I wonder how to check referential integrity on a table after disabling and reenabling foreign key constraints.

For example :

postgres=# CREATE TABLE main(id serial, id_delta integer, PRIMARY KEY (id));
CREATE TABLE
postgres=# CREATE TABLE delta (id serial, PRIMARY KEY (id));
CREATE TABLE
postgres=# ALTER TABLE main ADD CONSTRAINT fk_main_delta FOREIGN KEY (id_delta) REFERENCES delta (id);
ALTER TABLE

Let's try to insert a value that do not respect fk_main_delta:

postgres=# INSERT INTO main (id_delta) VALUES (1);
ERROR:  insert or update on table "main" violates foreign key constraint "fk_main_delta"
DÉTAIL : Key (id_delta)=(1) is not present in table "delta".

=> it logically fails, so I disable FK constraints to force insert:

postgres=# ALTER TABLE main DISABLE TRIGGER ALL;
postgres=# INSERT INTO main (id_delta) VALUES (1);
INSERT 0 1
postgres=# ALTER TABLE main ENABLE TRIGGER ALL;

But now, referential integrity is no longer respected.

postgres=# SELECT * FROM main;
 id | id_delta
----+----------
  2 |        1
(1 ligne)

So is there a way to check integrity afterwards?

As it seems that there is no way to check referential integrity after using DISABLE TRIGGER ALL / ENABLE TRIGGER ALL, I finally used DROP/CREATE CONSTRAINT instead.

Inspired by this post on Magnus Hagander's blog I used such queries to generate alter commands for removing and creating FK:

-- Generate ALTER TABLE ... ADD CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname DESC, relname DESC, conname DESC

-- Generate ALTER TABLE ... DROP CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname, relname, conname

Here is my process:

Start Transaction
Drop Constraints
Do some stuff... (insert, update, whatever...)
Create Constraints
if it fails then rollback, else Commit Transaction


Unable to uninstall Sql Server 2012 Express (With SP1) My Stack:
. Windows 10 Enterprise
. SQL Server 2012 Express (with SP1 bundled).

ISSUE:
. Installed SQL to test for backward compatibility.
. When I tried to Un-Install the following prompt is thrown.

Attempted Resolution Step:
. uninstalling individual components.
. re-installing SP1 for that edition.

None of these steps have worked. One last resort was to delete registry entries and then reattempt to delete.

Any ideas?

A month later, I need to test with a full version of SQL locally but remembered I had this pain in the a** to deal with.

TO RESOLVE:

Change SQL compatibility mode to "Windows Vista SP1/SP2". Go to where it was installed. Path belows for x64 version. For 32x it will be in the ~\program Files (86) directory.
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

Launch CMD, run as an Administrator. Navigate to the directory above.
Type command Setupup.exe /action=uninstall
Follow the prompts.
Find your life ~ Shaun T :)


oracle regexp_replace to remove duplicate duplicate strings and double separators Been using the following regexp_replace to create a comma separated string for those values in 'col_name' and remove any duplicates.

REGEXP_REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,col_name,',').EXTRACT('//text()')).GetClobVal(),','),'([^,]+)(,\1)+', '\1')

Works ok but with 1 exception - if 'col_name' has empty string values. What I am ending up with is:

value1,value2,,value3,value4,,value5 etc.

Looking for that elusive regex that can remove any duplicate values and those repeating commas.

Thanks

--- updated ----

        col-name
-----------------
row1    value 1
row2    value 2
row3
row4    value 2
row5    value 3
row6    value 1
row7

Expected Result: value1,value2,value3

Would the following be acceptable (not a REGEXP_REPLACE, but maybe easier to understand)?

Test table/data:

create table t as
select 'row1' c1 , 'value1' c2 from dual union all
select 'row2' ,  'value2' from dual union all
select 'row3' ,  null from dual union all
select 'row4' ,  'value2' from dual union all
select 'row5' ,  'value3' from dual union all
select 'row6' ,  'value1' from dual union all
select 'row7' ,  null from dual;

select * from t;

C1      C2
row1    value1
row2    value2
row3    null
row4    value2
row5    value3
row6    value1
row7    null

Query:

select
  listagg(c2 , ',') within group ( order by c2 )
from (
  select distinct c2
  from t
  where c2 is not null
);

result
value1,value2,value3

Dbfiddle here.


With SSMS on my local machine, is it possible to login to a remote SQL server on my VPN network via my Active Directory credentials? I'm trying to login to a SQL server on my work network with a VPN connection to the network and by using my AD credentials.
When I'm directly on my office machine, I use "Windows Authentication" as the Authentication method in SSMS and that works fine.

When I'm VPN'ed onto the network and using my local machine, Windows Authentication doesn't work (understandably) but if I try to use the "Active Directory - Password" Authentication method (I was hoping I could put in my work's AD account to connect to the remote server) I get the following error:

It sounds like it can't find the server, but when I ping the server from my local machine, I'm getting a response.

Note: "Allow remote connections to this server" is checked.

As I said in my comment, I do not fully understand what your issues are.

But I think the solution is to open SSMS as another user.

Personally I have the SSMS icon in my task bar:.

Shit + R click
Select "Run as a different user"
Enter the account and password you would normally use on your office machine.

SSMS will connect to instances as if you where on your office machine and had opened SSMS.

Note I assume you are working remote for possibly the first time.  There are a number of security barriers, that your organization may have in place to prevent you from connecting from home.  I can't address those, and this is probably not the site to address them.


SQL DATABASE ENGINE permisions I want to deny query for insert,update,delete,alter in database engine only? is there a way to do that ? Because I have an application that uses the SQL database server and has a lot of users that consists of a lot of levels. I use the connection to the database with ADODB. How do I only can execute insert, update, delete or procedure only on the application, but can not run in SQL server database engine?

example: users A can insert,delete or update data into 'customer' through application, but cannot execute query that contain insert,delete, or update from database engine. because is too risky if users do that and can manipulate data from there.

Thanks Before

Since the application is running on user workstation, it is likely to be run on user's own account. This, unfortunately, means you are out of luck with the simple solution - setting up user permissions.

What could work is an approach based on stored procedures with EXECUTE AS.

There are lots of tutorials in the net. For a summary:

Create a user account for application usage and grant it appropriate rights to the database objects.
Create stored procedures to handle all the required data operations. Use
EXECUTE AS to run those procedures as the application user.
Grant your users rights to execute the procedures.
Revoke other permissions from the users.


Migrate to sql 2017 and add ag's A client wants to migrate from sql 2008 to sql 2017 and add availability groups. All enterprise. All vm's.

Question: (without using any words starting with Az~ smile) should I set up the ag's first or last?

My instinct is to (side by side) migrate as is. Then once live and stable, separately, add always-on ag's.

My instinct is to (side by side) migrate as is. Then once live and stable, separately, add always-on ag's.

That is perfectly reasonable and supported.

However.

Are you sure you really want to perform the cluster creation, configuration and AG provisioning and testing on a live (post-migration) system?

If you do the AGs first, you can perform all that configuration and testing as many times as you want before the migration.


capture output of table-valued function Is it possible to capture output of table-valued function? For example, function is defined as follows.

CREATE FUNCTION [dbo].[ufn_MyFunction]
(
    @ItemId INT
)
RETURNS @ReturnTable TABLE
(
    RowId int primary key NOT NULL,
    Value nvarchar(250) NOT NULL
)
AS
BEGIN
    -- ...
    RETURN;
END

I understand you can do SELECT * FROM ufn_MyFunction(@id), but I need to use values from every row of the returned table. Is there a way to achieve this?

You can also do SELECT...INTO, Use the table in a join or use APPLY, eg

SELECT *
INTO #tmp
FROM [dbo].[ufn_MyFunction] ( 1 )

SELECT *
FROM [dbo].[ufn_MyFunction] ( 1 ) f
    INNER JOIN someOtherTable t ON f.RowId = t.rowId

SELECT *
FROM someOtherTable t
    CROSS APPLY [dbo].[ufn_MyFunction] ( t.rowId ) f


How to normalize a lookup table containing NULL? - or should I bother? I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what description the fee is for. What is the best way to normalise the two given the issue I describe later?

The original data

fee
===
fee_id | description
-------------------------
1      | subscription
2      | joining

and

fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35

To obtain the amount I need to invoice I join the tables fee and fee_amount on the fee.id and look up the fee amount from the fee_amount table using the keys of fee_amount.year, fee_amount.joint and fee.description. i.e something like

SELECT amount
FROM fee_amount
JOIN fee ON (fee_amount.fee_id = fee.fee_id)
WHERE
   fee_amount.year = 2020
AND
   fee_amount.joint = TRUE
AND
   fee.description = 'joining'

The problem

I introduce a new fee description 'booking', whose amount only depends upon the year i.e. I don't care if it's joint or not. This gives rise to N/A or NULL in the fee amount lookup table as shown below which rather complicates looking up fee amounts.

What is the best way to normalise this to avoid the NULLS? or shouldn't I bother and just have a more complicated SELECT.

Example new data with a fee type that doesn't require a 'joint' value.

fee
===
fee_id | description
-------------------------
1      | subscription
2      | joining
3      | booking

 fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  NULL |  77
   3   | 2020 |  NULL |  88

Edit... Or maybe should I simply add two rows in the fee_amount table for the same year, one with joint = true and one with joint = false, both with the same amount and then it won't matter what I put in the WHERE clause for fee_amount.joint
ie

 fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  TRUE |  77
   3   | 2019 | FALSE |  77
   3   | 2020 |  TRUE |  88
   3   | 2020 | FALSE |  88

Perhaps a better policy:

When registering a user, store the fee charged into the user's history.
Derive the fee from some business logic that may or may not be stored in a database table.

Note how this policy freezes the fee as soon as it is billed.  This is safer than having it in some other table that might accidentally be changed for unrelated reasons.


Combine two queries to one I need to write query that finds the average for each classname, total average for each student, I already wrote two separate query for each one of those, but how to combine to one query?

My 1st query:

select distinct  StudentFirstName,StudentLastName, ClassName, avg(Grade) as 'average for this subject'
from tests
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName,ClassName;

My 2nd query:

select distinct  StudentFirstName,StudentLastName, avg(Grade) as 'total average'
from tests
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName;

Students table:

  pk-INT           VARCHAR             VARCHAR
+-----------+------------------+-----------------+
| StudentID | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
|         1 | agam             | rafaeli         |
|         2 | amir             | aizinger        |
|         3 | avi              | caspi           |
|         4 | avia             | wolf            |
|         5 | ben              | moskovich       |
|         6 | chen             | segalovich      |
|         7 | dana             | levy            |
|         8 | daniel           | marcus          |
|         9 | daphna           | chwarts         |
|        10 | david            | cohen           |
+-----------+------------------+-----------------+

Tests table:

  PK-VARCHR    PK-VARCHR     PK&FK-INT   INT
+------------+------------+-----------+-------+
| TestDate   | ClassName  | StudentID | Grade |
+------------+------------+-----------+-------+
| 2017-07-01 | Algebra    |         1 |    88 |
| 2017-08-02 | Algo       |         1 |    97 |
| 2017-09-01 | Algebra    |         1 |    80 |
| 2017-09-01 | Algebra    |         1 |    97 |
| 2017-09-01 | Set-theory |         1 |    85 |
| 2017-09-04 | Calcules   |         1 |    86 |
| 2016-05-03 | Set-theory |         2 |    84 |
| 2016-07-02 | Calcules   |         2 |    89 |
| 2016-07-04 | Algo       |         2 |    83 |
| 2016-07-05 | Algebra    |         2 |    79 |
| 2016-06-03 | Algebra    |         3 |    99 |
| 2016-07-02 | Algo       |         3 |    97 |
| 2016-07-03 | Calcules   |         3 |    96 |
| 2016-09-03 | Set-theory |         3 |    95 |
| 2016-06-03 | Algebra    |         4 |    78 |
+------------+------------+-----------+-------+

That's not possible, because you have different granularity in the two queries. You would have to calculate one average by yourself with a formula like sum(grades) / count(distinct full-student-name). (btw, your use of distinct is not necessary, the group by does that already for you)
With this you would get the count correct, but you would sum too much grades, because they are multiplied by the grouping for classes. You can't do sum(distinct grades) because a student could have the same grade in different classes.
You also can't calculate the average of the averages. That's usually close, but still not correct.


Data Warehouse Kimball Customer and Customer Address Dimensions I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc.

In Kimball data warehouse, would I utilize a bridge table to go between Customer and Customer Address information?

(a)
So [DimCustomer] to [DimCustomerAddressBridge] to [DimAddress] Table?
Would this be the proper technique?

(b)
Or would I just have a Fact Table, like CustomerTransactionPayment Table, that connects the two Dimensions [DimCustomer] and [DimAddress]?

So one strategy is the connected Dims using bridge, and another is disconnected Dims connected by a Fact transaction table?

The right schema depends on how you intend to use this data. Let's talk about your options:

a) Bridge table.

I would not use this approach, because bridge tables are used to express N:N relationships. If you go for it, that would not be possible to precisely say what is the right address for your transaction; so it would be useless.

Following Kimball group -> "Bridge tables are used...where a many-to-many relationship can’t be resolved in the fact table itself..." https://www.kimballgroup.com/2008/09/design-tip-105-snowflakes-outriggers-and-bridges/

b) Create another dimension DimAddress (DimGeography?)

This looks a good approach, especially if this Address information could be related to other Fact tables. Maybe you could expand it into a DimGeography, or something like that. In this case, DimGeography and DimCustomer would not be related directly.

Other options

c) You could also change the granularity of your Customer dimension to the Address level; so you would have one row per address and the "master customer data" would be the same for the same customer in this dimension. -> Do that if most of your data is related to the Address level.

d) Another approach would be snowflaking your schema. Creating then a DimCustomerAddress that can be used on this fact; where your fact table points to DimCustomerAddress and DimCustomerAddress points to DimCustomer. In this way, you don't change your Customer table neither the other fact tables and can create the Customer -> Address hierarchy.

e) If you have to track historical address changes (based on comments)

This solution is similar to d), but the DimCustomerAddress would be an SCD type 2. This will make the granularity of your new DimCustomerAddress be the "address history".
I.E: I would assume that your business key for this dimension is the Customer ID, AddressID and Address type, so you should add a new row everytime you get an address change; keeping tracking of the start and end dates that the address is valid. Your fact table should be related with DimCustomerAddress by the surrogate key "CustomerAddressKey", and DimCustomerAddress should relate with DimCustomer using "CustomerKey". The Start and End dates can be used during the ETL time to populate the Fact table to determinate the right "CustomerAddressKey".